September 6, 2010 at 11:25 am
Hi All,
I have a sql server 2000 database (simple recovey model) which has a 750mb of mdf and 12gb of log file. How to reduce the log file size without any data loss.
[font="Verdana"]Thanks
Chandra Mohan[/font]
September 6, 2010 at 11:44 am
Before you try to shrink the file - you need to identify why it grew that large in the first place. If it grew that way because it was originally in full recovery and the log was not being backed up, then you can perform a one-time shrink (DBCC SHRINKFILE).
Also, if that is the case - are you sure you want to keep the database in simple recovery model? Are you okay with losing a day's worth of data (and possibly more)?
And finally, if the log has grown to that size due to processing - it will just grow again and there really isn't any reason to shrink it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 6, 2010 at 9:07 am
The first thing you need to do is to truncate the t-log. backup log <db> with truncate_only.
Then issue the dbcc shrinkfile (filename,size)
If your going to keep this in simple recovery mode I would add a step in your backup job to truncate the logfile, or put it in full recovery mode and do hourly backups of the logfile.
Thanks
Tom
October 6, 2010 at 9:29 am
Tom Goltl (10/6/2010)
If your going to keep this in simple recovery mode I would add a step in your backup job to truncate the logfile
Do not do this. It will lead to log file fragmentation, a huge number of VLFs, and performance will continue to degrade from now until eternity.
October 6, 2010 at 9:47 am
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 9:52 am
Tom Goltl (10/6/2010)
The first thing you need to do is to truncate the t-log. backup log <db> with truncate_only.Then issue the dbcc shrinkfile (filename,size)
No, no, no, no! Both are bad things to do to a database.
Truncate should not be used. Either back the transaction log up regularly or switch to simple recovery model.
Shrinking should be done rarely, when there has been some strange, usual, unexpected operation that grew the files beyond what the should be.
I've already ranted about this once today: http://www.sqlservercentral.com/Forums/FindPost999124.aspx I'm not going to repeat myself.
If your going to keep this in simple recovery mode I would add a step in your backup job to truncate the logfile
Why? In simple recovery the log is auto truncated whenever a checkpoint occurs. That's every minute or so in regular operation. Why, when SQL is truncating the log itself on a regular basis, would you write a job to do it manually?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 2:13 pm
Derrick Smith (10/6/2010)
Tom Goltl (10/6/2010)
If your going to keep this in simple recovery mode I would add a step in your backup job to truncate the logfileDo not do this. It will lead to log file fragmentation, a huge number of VLFs, and performance will continue to degrade from now until eternity.
Shrinking will do that. Merely truncating will not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply