Modellog.ldf Growing (26GB+)

[dfrazell]

I’ve never really paid any attention to the properties on the model.mdf database but I just noticed the modellog.ldf file is greater than 26GB (because the recovery model is set to Full and the transaction log isn't being backup). I'm running SQL 2000 with SP3a.

1. If the model.mdf database is a template for new databases to be created from, why is there any transaction activity that would cause the modellog.ldf to grow?

2. Can I set the Recovery Model to Simple instead of Full?

3. If I set it to Simple will this keep it from growing and will it reduce its size or do I need to take some other action?

Thanks.

 

[JayKusch]

The Model DB, as its name states, and you have correctly stated, is used for the "modeling" of new database that are created.

You use the Model to store any schema objects you want created with new DBs.

It should not be this large and there should, in most cases, not have any activity within it.

I have seen in a couple rare cases that the model was used in a production manner so that a new DB that was created would be "somewhat" of a snapshot of the current live produciton DB (though this is not a recommended practice).

I would advise you look into what is using the model DB by talking with others or setting up a Profiler trace to see what was happening in the Model DB.

You could set the Model DB to Simple and shrink the TLog but remember, any changes you make to the Model DB will be reflected in all new DBs created.

Hope this helps

Thanks

J. Kusch

 

[dfrazell]

I've not used the Profiler too much. What events would I want to trace and what filtering options would I want to set? I tried to set filter for model but that didn't seem to work.

Thanks.

 

[dfrazell]

I filtered the trace by DatabaseID (3). here is what I'm seeing so far:

Event Class: SQL:BatchCompleted
TextData:

use [model]
select (sum(size) * (8192/1024)) / 1024 from dbo.sysfiles WHERE (status & 0x40) <> 0
DBCC SQLPERF(LOGSPACE)
select ceiling((sum(size) * (8192.0/1024.0)) / 1024.0) from dbo.sysfiles
exec sp_spaceused
DBCC SQLPERF(LOGSPACE)

Am I looking at the correct information?

Would this cause the transaction log to grow?

 

[JayKusch]

That looks like a completed batch job. Not to much to be worried on that one.

Are there any Non-System tables in the Model DB?

What tables in the Model DB hold the most data?



Thanks

J. Kusch

 

[dfrazell]

As far is I can tell the Model DB hasn't been modified.

What is the best way to shrink the transaction log after I back it up? through the GUI or by a command?

 

[JayKusch]

Either way will shrink the DB. You still need to find out what is causing the activity in Model though.

Thanks

J. Kusch

 

[dfrazell]

Would the database Maintenance Plan that is set to run for "All system databases" cause the transaction log to grow? I thought I read somewhere this might cause this to happen. Also, on the Transaction Log Backup tab the "back up the transaction log ..." is not checked.

 

[JayKusch]

I am not aware of any issues to the Model DB that would be caused by maintenance plans.

Now the MSDB DB could be affected by maintenance plan activity on a server.

The reason the "back up the transaction log ..." is not checked." is that way is probably due to the fact that by default the Model DB is set to Simple mode thus it has not TLog to back up (unless someone has set Model to be in FULL recovery mode).

Thanks

J. Kusch

 

[dfrazell]

The Recovery Model for the Model DB was set to FULL recovery but I changed it to SIMPLE yesterday. Was FULL recovery causing the log to grow?

 

[JayKusch]

Yes ... but 26GB of TLog means something was/is going on transaction-wise in this DB unless this DB has been around for a VERY long time.

Have you checked the table sizes yet in the Model DB?

Lets see what, if anything, is in the system tables or user tables if there are any.



Thanks

J. Kusch

 

[dfrazell]

Here are the rows counts for some of the tables:
syscolumns - 356 rows
syscomments - 127
sysdepends - 309
sysindexes - 31
sysobjects - 54
syspermissions - 49
systypes - 26
sysusers - 12

All other tables have 0 or l row.

There are no user tables.

Model DB has been around for 5 years with the Recovery Model set to FULL.

 

[JayKusch]

OK then, you are pretty safe to set the Model to Simple and shrink the TLog.

Shrinking it via the GUI is the fast way to shrink the database (and associated TLog file).

Thanks

J. Kusch

 

[dfrazell]

It looks like I'm good to go. The TLog is now 512KB. 26GB freed.

Thanks for your help.