SQLTeam.com | Weblogs | Forums

SQL MSDB log file is 1gb


#1

Hi, I have a query. On our clients SQL server, Ive noticed that the MSDBdata.mdf is 500mb, but the LDF is 1.1GB. Whilst this isnt too much to worry about, I was wondering how I reduce this LDF? The Db is set to simple, so I was expecting this to be only 100mb or so like the other live databases?

Many thanks


#2

Even though the db is in simple mode, inserts/updates/deletes are still logged. The log file will grow as needed to accommodate that activity. You can truncate the log file if you like (google it) and you want to keep disk space usage low


#3

Truncating the log won't help with a database in SIMPLE recovery model.

You can do a one-time shrink of the log file to get it below 500MB. Check out DBCC SHRINKFILE, or you can use the GUI (right click on database, it's in there somewhere as a shrink, just make sure to select the log file (it'll default to the mdf file)).

Whether or not it needs to be 500MB, 1GB, 100MB, etc will depend on your largest transaction size. Perhaps that database has bigger transactions than the other databases. After you shrink it and if it grows out again, you'll know that it needs the larger log file size. Leave it alone at that point.


#4

Right. "Shrink" was what I meant!


#5

I figured that was the case but wanted to be sure we didn't steer ja5224 the wrong way. :wink:


#6

For performance reasons, you also want to make sure you don't have too many VLFs, so run these commands:

USE msdb;
DBCC SHRINKFILE(2);
-- adjust the "100MB" below to whatever you want the total log size to be
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, SIZE = 100MB );


#7

However shrinking is not a good option for reducing the size of transaction log file. take a look on this post to know what should be more preferable in such situations http://www.sqlserverlogexplorer.com/how-to-clear-transaction-with-dbcc-shrinkfile/


#8

Easy enough to shrink it ... but all sorts of possible consequences, and those almost certainly need considering.

if it was a one-off batch process that went haywire then shrinking will be a reasonable solution. Provided the haywire process is not run again, or not very often (I would define anything more frequent than "once a year" as too often :slight_smile: )

My suggestion would be:

Shrink the log file

Put an alert on it so that if (when?!!) if grows back again you know exactly when it happened.

Then find what the cause was and "improve" that so that the log file expansion doesn't happen.

It might be that an Index Rebuild is causing this (although I'm not sure how likely that is in Simple Recovery Model - it would require a massive table within the DB), but if something like that is the case then the log file growth will happen every time the Index rebuild rubs (might be "every Saturday night", for example).

Repeatedly shrinking the LDF file, to keep it under control, will result in physical fragmentation of the file and CPU cycles and I/O each time that SQL needs to extend it, after shrinking.

If it DOES need to be big then I would manually extend it to that size in a large increment so that there aren't loads of VLFs as a consequence of loads of, say, 50MB file-extensions ... (although 1GB shouldn't be a problem for VLFs compared to a file much much bigger than that ... but Every Little Helps :slight_smile: )