<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Shrink Transaction Logs in SQL 2005
Shrink Transaction Logs in SQL 2005
Rate Topic
Display Mode
Topic Options
Author
Message
ramdas.narayanan
ramdas.narayanan
Posted Friday, July 18, 2008 2:14 PM
Old 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
george sibbald
george sibbald
Posted Friday, July 18, 2008 3:52 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 5,317,
Visits: 11,307
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
Perry Whittle
Perry Whittle
Posted Saturday, July 19, 2008 1:43 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 4:02 AM
Points: 5,244,
Visits: 11,264
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
bitbucket-25253
bitbucket-25253
Posted Saturday, July 19, 2008 6:30 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 5,123,
Visits: 20,371
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
rinu philip
rinu philip
Posted Sunday, July 20, 2008 1:54 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, June 05, 2013 5:35 AM
Points: 202,
Visits: 325
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
GilaMonster
GilaMonster
Posted Monday, July 21, 2008 12:01 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 38,118,
Visits: 30,403
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
ramdas.narayanan
ramdas.narayanan
Posted Monday, July 21, 2008 6:39 AM
Old 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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
