SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrink Transaction Logs in SQL 2005


Shrink Transaction Logs in SQL 2005

Author
Message
ramdas.narayanan
ramdas.narayanan
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 188
Hi folks,
What would be the fastest way to shrink the transaction logs in SQL 2005, would it be using DBCC SHRINKFILE after taking the full backup of the log.

Thank you
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10224 Visits: 13687
it would be, but unless you really need the space back, don't bother, you will fragment you log file at the OS level.

---------------------------------------------------------------------
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19650 Visits: 17239
how big is the log file?

the file will not shrink if there are transactions occupying the file. You would need to truncate the log first to clear transactions then shrink. This breaks your backup chain so a backup afterwards would be required. Do you really wanteed to shrink the file?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7763 Visits: 25280
If your log file was created without a growth factor (either percentage or maximum size )-- a most unusual configuration. That said unless your database has undergone a significant increase in activity, i.e. many new rows inserted, many updates and deletes, and this level of activity is NOT expected to be repeated, shrinking the log file can be deleterious as noted by George Sibbald
you will fragment you log file at the OS level.
and as with any fragmented file the time for IO operations will increase. Further if the size is a result of normal operations, after shrinking the log file it will just grow again. Remember the log file is truncated as a part of normal operation of the database engine (check points) and in the Simple backup mode. The freed up space is reused. So attempt to learn what the normal every day operational size is and leave the log file alone.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
rinu philip
rinu philip
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 506
ramdas.narayanan (7/18/2008)
Hi folks,
What would be the fastest way to shrink the transaction logs in SQL 2005, would it be using DBCC SHRINKFILE after taking the full backup of the log.

Thank you



Think this method below would help you : Smile
BACKUP LOG name WITH TRUNCATE_ONLY
USE db
DBCC SHRINKFILE (name_Log, 1)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86032 Visits: 45226
rinu philip (7/20/2008)

Think this method below would help you : Smile
BACKUP LOG name WITH TRUNCATE_ONLY
USE db
DBCC SHRINKFILE (name_Log, 1)


Be very careful of Backup log with truncate. If you are running in full recovery mode, truncaet will break the log chain and will leave you unable to restore to a point in time after the truncation. You will have to take another full/diff backup after doing the truncation to ensure hat you can do a point-in-time recovery.

You may also like to note that BACKUP LOG < DB name > WITH TRUNCATE_ONLY is currently deprecated and will not work in SQL 2008.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ramdas.narayanan
ramdas.narayanan
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 188
Thank you for the information and suggestion.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search