June 9, 2008 at 11:22 am
I had a huge table in MSDBDATA from a trial copy of software that reads logs, which I deleted after the trial period expired.
I then shrank the database, and the data file is now back to a more or less normal size, but the log file is still huge.
What can I do to clean up the log file?
June 9, 2008 at 11:40 am
make sure that recovery model is 'simple'
exec 'checkpoint' or make any minor db conf change, it will clean up the trans log file
shrink trans log file
June 9, 2008 at 12:31 pm
The recovery mode is currently "full".
Why is it important for it to be "simple" for the procedure you describe?
Thank you for your help.
June 9, 2008 at 12:42 pm
If the database is using the Full RecoverY model, you need to run a transaction log backup to remove log records, thereby allowing you to then shrink the transaction log file.
The other way is to switch the database to use the Simple Recovery model, shrink the transaction log file, reset the recovery model to Full, the do a full backup of the database.
😎
June 9, 2008 at 1:05 pm
I ran a log backup and then shrank the database, which took care of the problem.
Thanks for your input!
June 9, 2008 at 2:55 pm
I wouldn't think you would need your msdb database to be in full recovery model. What are you doing that necessitates full recovery model for that database?
The Redneck DBA
June 10, 2008 at 6:25 am
That is actually a good question, and I will look into it: our SQL Server database is part of an off-the-shelf accounting and manufacturing software package we bought a few years ago and, since I was not familiar with SQL Server, we had a third-party firm do all the setup work for us.
Now that I know at least a few things about SQL Server, I do see your point that there should be no need to have MSDBDATA in full recovery mode.
All we use it for, that I'm aware of, is for several backup and data maintenance jobs that run automatically at night.
Anyway, thanks for forcing me to think about this again!
June 10, 2008 at 6:30 am
yep, this is the difference, right there between the Old Hand and Grasshoppers 😉
June 10, 2008 at 6:30 am
Oh...if it's a 3rd party application, you might want to check with them first. Sometimes if you mess with things they set up it can cause you to lose support. I'd be suprised if that would happen for something as simple as this, but you might check.
The Redneck DBA
June 10, 2008 at 6:51 am
You are right again...
I will check with tech support before I do anything.
Thanks again to all of you for your advice!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply