Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Shrink Transaction Logs in SQL 2005 Expand / Collapse
Author
Message
Posted Friday, July 18, 2008 2:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 14, 2011 7:42 AM
Points: 360, 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
Post #537056
Posted Friday, July 18, 2008 3:52 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 5,871, Visits: 12,972
it would be, but unless you really need the space back, don't bother, you will fragment you log file at the OS level.

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

Post #537094
Posted Saturday, July 19, 2008 1:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:54 AM
Points: 6,414, Visits: 13,796
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"
Post #537182
Posted Saturday, July 19, 2008 6:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:17 PM
Points: 5,332, Visits: 25,261
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
Post #537204
Posted Sunday, July 20, 2008 1:54 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 2:22 AM
Points: 207, Visits: 427
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)
Post #537227
Posted Monday, July 21, 2008 12:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 39,957, Visits: 36,312
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 2008, MVP
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

Post #537400
Posted Monday, July 21, 2008 6:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 14, 2011 7:42 AM
Points: 360, Visits: 188
Thank you for the information and suggestion.
Post #537618
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse