Shrink Transaction Logs in SQL 2005

Question

Post reply

Shrink Transaction Logs in SQL 2005

ramdas.narayanan

Ten Centuries

Points: 1214
More actions
July 18, 2008 at 2:14 pm

#123228

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

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 SSC Guru Points: 234013 More actions · Answer 2

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" 😉

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

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[/url]
Before posting a performance problem please read[/url]

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 4

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 : 🙂

BACKUP LOG name WITH TRUNCATE_ONLY

USE db

DBCC SHRINKFILE (name_Log, 1)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

rinu philip (7/20/2008)
Think this method below would help you : 🙂
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 Ten Centuries Points: 1214 More actions · Answer 6

ramdas.narayanan

Ten Centuries

Points: 1214

July 21, 2008 at 6:39 am

#845721

Thank you for the information and suggestion.