SQL Server 2000 master6.ldf question

"Jason Huang" wrote in message

> Hi,
>
> In my SQL Server 2000, I have a master6.ldf. It's size is huge, and it's
> also growing.
> I am wondering why it is master6.ldf and not master.ldf.
> Since it's growing, I am wondering how to make it smaller.
> Thanks for help.
>
>
> Jason
Hi

I suspect that the file is not part of the master data unless you are using
it instead of a user database which is never a good idea!

The following query in SQL 2005 will give you the name of the database the
file is associated with:

SELECT db_name(database_id),
name,
CASE TYPE WHEN 0 THEN 'Data' WHEN 1 THEN 'Log' ELSE 'Unknown' END AS
[Type],
file_id,
physical_name
FROM sys.master_files
WHERE physical_name like '%master6%'

for SQL 2000 try:

SELECT db_name(dbid),
name,
fileid,
filename
FROM sysaltfiles
WHERE filename like '%master6%'

John
 >> Stay informed about: SQL Server 2000 master6.ldf question 

> Double check that Master6.ldf belongs to Master
<snip>

I doubt it does. I don't think you can add database files to the master database. And also, master
is *effectively* stuck in simple recovery model. So my guess is that this ldf file belong to some
other database (and is badly named). And I fully agree with the rest. Determine what database it
belong and take it from there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Paddy" wrote in message

> Double check that Master6.ldf belongs to Master by running the
> following.
>
>
> exec sp_helpdb 'master'
>
> If it does
>
> Backup Master
> Backup Master Transaction Log
>
> You can then shrink the master6 transaction log file. It is problaly
> as easy to do this by using enterprise manager (Right click the
> database)
>
> Read up on backups and transaction logs, as you need to schedule these
> operations with a maintenance plan.
>
> Maybe the master databases is being used for stuff it shouldn't be as
> well, do you create tables etc.. in it that should be in user
> databases?
>
> Paddy
>
>
 >> Stay informed about: SQL Server 2000 master6.ldf question 

On 19 May, 03:50, "Jason Huang" wrote:
> Thank you guys so much!
> After I executed the SELECT db_name(dbid) ...FROM sysaltfiles command,
> found the master6.ldf file is a log file of a database dbAP65Bak,
> and that database is orginally transferred from a  SQL 6.5 database server.
> But I have no idea why that log file keeps growing everyday, since that
> database is just for backup purpose,
> and we just once in a while (e.g., every 6 months) dump some data from the
> dbAP65.
>
> "John Bell"
> ???????:D768DF0D-425E-4B87-BBBA-5D6DFD904...@microsoft.com...
>
>
>
>
>
> > "Jason Huang" wrote in message
> >
> >> Hi,
>
> >> In my SQL Server 2000, I have a master6.ldf.  It's size is huge, and it's
> >> also growing.
> >> I am wondering why it is master6.ldf and not master.ldf.
> >> Since it's growing, I am wondering how to make it smaller.
> >> Thanks for help.
>
> >> Jason
> > Hi
>
> > I suspect that the file is not part of the master data unless you are
> > using it instead of a user database which is never a good idea!
>
> > The following query in SQL 2005 will give you the name of the database the
> > file is associated with:
>
> > SELECT db_name(database_id),
> >  name,
> >  CASE TYPE WHEN 0 THEN 'Data' WHEN 1 THEN 'Log' ELSE 'Unknown' END AS
> > [Type],
> >  file_id,
> >  physical_name
> > FROM sys.master_files
> > WHERE physical_name like '%master6%'
>
> > for SQL 2000 try:
>
> > SELECT db_name(dbid),
> >  name,
> >  fileid,
> >  filename
> > FROM sysaltfiles
> > WHERE filename like '%master6%'
>
> > John- Hide quoted text -
>
> - Show quoted text -

Jason

if that is the case you can change the database to change the recovery
mode to simple. You sound like you don't need transaction logs anyway.

Lookup recovery mode in BOL though.

Paddy
 >> Stay informed about: SQL Server 2000 master6.ldf question 

It is not an easy thing to delete, as it is required by the DB
regardless of your recovery mode.

You could certainly backup the DB, delete the whole thing and then
restore to a more apt name.

Paddy
 >> Stay informed about: SQL Server 2000 master6.ldf question 

Thank you guys so much!
After I executed the SELECT db_name(dbid) ...FROM sysaltfiles command,
found the master6.ldf file is a log file of a database dbAP65Bak,
and that database is orginally transferred from a SQL 6.5 database server.
But I have no idea why that log file keeps growing everyday, since that
database is just for backup purpose,
and we just once in a while (e.g., every 6 months) dump some data from the
dbAP65.



"John Bell"
???????:D768DF0D-425E-4B87-BBBA-5D6DFD904B03@microsoft.com...
>
> "Jason Huang" wrote in message
>
>> Hi,
>>
>> In my SQL Server 2000, I have a master6.ldf. It's size is huge, and it's
>> also growing.
>> I am wondering why it is master6.ldf and not master.ldf.
>> Since it's growing, I am wondering how to make it smaller.
>> Thanks for help.
>>
>>
>> Jason
> Hi
>
> I suspect that the file is not part of the master data unless you are
> using it instead of a user database which is never a good idea!
>
> The following query in SQL 2005 will give you the name of the database the
> file is associated with:
>
> SELECT db_name(database_id),
> name,
> CASE TYPE WHEN 0 THEN 'Data' WHEN 1 THEN 'Log' ELSE 'Unknown' END AS
> [Type],
> file_id,
> physical_name
> FROM sys.master_files
> WHERE physical_name like '%master6%'
>
> for SQL 2000 try:
>
> SELECT db_name(dbid),
> name,
> fileid,
> filename
> FROM sysaltfiles
> WHERE filename like '%master6%'
>
> John
>
>
 >> Stay informed about: SQL Server 2000 master6.ldf question 

"Jason Huang" wrote in message

> Thank you guys so much!
> After I executed the SELECT db_name(dbid) ...FROM sysaltfiles command,
> found the master6.ldf file is a log file of a database dbAP65Bak,
> and that database is orginally transferred from a SQL 6.5 database
> server.
> But I have no idea why that log file keeps growing everyday, since that
> database is just for backup purpose,
> and we just once in a while (e.g., every 6 months) dump some data from the
> dbAP65.
>
>
>
Hi

You may want to see if there are any transactions that have not been
committed using

DBCC OPENTRAN ( 'dbAP65Bak' )

If the recovery mode is full then you would need to do log backups or you
can change the recovery mode to simple as Paddy suggests

John
 >> Stay informed about: SQL Server 2000 master6.ldf question 

Can I delete it anyway?
What would be the side affect?

"Paddy"
???????:2929c312-e683-4ad2-ba6f-857ba15058ea@s50g2000hsb.googlegroups.com...
On 19 May, 03:50, "Jason Huang" wrote:
> Thank you guys so much!
> After I executed the SELECT db_name(dbid) ...FROM sysaltfiles command,
> found the master6.ldf file is a log file of a database dbAP65Bak,
> and that database is orginally transferred from a SQL 6.5 database server.
> But I have no idea why that log file keeps growing everyday, since that
> database is just for backup purpose,
> and we just once in a while (e.g., every 6 months) dump some data from the
> dbAP65.
>
> "John Bell"
> ???????:D768DF0D-425E-4B87-BBBA-5D6DFD904...@microsoft.com...
>
>
>
>
>
> > "Jason Huang" wrote in message
> >
> >> Hi,
>
> >> In my SQL Server 2000, I have a master6.ldf. It's size is huge, and
> >> it's
> >> also growing.
> >> I am wondering why it is master6.ldf and not master.ldf.
> >> Since it's growing, I am wondering how to make it smaller.
> >> Thanks for help.
>
> >> Jason
> > Hi
>
> > I suspect that the file is not part of the master data unless you are
> > using it instead of a user database which is never a good idea!
>
> > The following query in SQL 2005 will give you the name of the database
> > the
> > file is associated with:
>
> > SELECT db_name(database_id),
> > name,
> > CASE TYPE WHEN 0 THEN 'Data' WHEN 1 THEN 'Log' ELSE 'Unknown' END AS
> > [Type],
> > file_id,
> > physical_name
> > FROM sys.master_files
> > WHERE physical_name like '%master6%'
>
> > for SQL 2000 try:
>
> > SELECT db_name(dbid),
> > name,
> > fileid,
> > filename
> > FROM sysaltfiles
> > WHERE filename like '%master6%'
>
> > John- Hide quoted text -
>
> - Show quoted text -

Jason

if that is the case you can change the database to change the recovery
mode to simple. You sound like you don't need transaction logs anyway.

Lookup recovery mode in BOL though.

Paddy
 >> Stay informed about: SQL Server 2000 master6.ldf question 

> Can I delete it anyway?

Delete what? Never ever delete any of the database files for a database.
First, make sure you understand backup and restore, recovery model etc. See for instance
http://msdn.microsoft.com/en-us/library/ms345583.aspx

Now, if you have an exceptionally large log file, you might consider to after determining recovery
model and backup type to do a one-time shrink of the file. I have some details about file shrinking
in:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Jason Huang" wrote in message

> Can I delete it anyway?
> What would be the side affect?
>
> "Paddy"
> ???????:2929c312-e683-4ad2-ba6f-857ba15058ea@s50g2000hsb.googlegroups.com...
> On 19 May, 03:50, "Jason Huang" wrote:
>> Thank you guys so much!
>> After I executed the SELECT db_name(dbid) ...FROM sysaltfiles command,
>> found the master6.ldf file is a log file of a database dbAP65Bak,
>> and that database is orginally transferred from a SQL 6.5 database server.
>> But I have no idea why that log file keeps growing everyday, since that
>> database is just for backup purpose,
>> and we just once in a while (e.g., every 6 months) dump some data from the
>> dbAP65.
>>
>> "John Bell"
>> ???????:D768DF0D-425E-4B87-BBBA-5D6DFD904...@microsoft.com...
>>
>>
>>
>>
>>
>> > "Jason Huang" wrote in message
>> >
>> >> Hi,
>>
>> >> In my SQL Server 2000, I have a master6.ldf. It's size is huge, and it's
>> >> also growing.
>> >> I am wondering why it is master6.ldf and not master.ldf.
>> >> Since it's growing, I am wondering how to make it smaller.
>> >> Thanks for help.
>>
>> >> Jason
>> > Hi
>>
>> > I suspect that the file is not part of the master data unless you are
>> > using it instead of a user database which is never a good idea!
>>
>> > The following query in SQL 2005 will give you the name of the database the
>> > file is associated with:
>>
>> > SELECT db_name(database_id),
>> > name,
>> > CASE TYPE WHEN 0 THEN 'Data' WHEN 1 THEN 'Log' ELSE 'Unknown' END AS
>> > [Type],
>> > file_id,
>> > physical_name
>> > FROM sys.master_files
>> > WHERE physical_name like '%master6%'
>>
>> > for SQL 2000 try:
>>
>> > SELECT db_name(dbid),
>> > name,
>> > fileid,
>> > filename
>> > FROM sysaltfiles
>> > WHERE filename like '%master6%'
>>
>> > John- Hide quoted text -
>>
>> - Show quoted text -
>
> Jason
>
> if that is the case you can change the database to change the recovery
> mode to simple. You sound like you don't need transaction logs anyway.
>
> Lookup recovery mode in BOL though.
>
> Paddy
>
 >> Stay informed about: SQL Server 2000 master6.ldf question