how to shrink log file?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » how to shrink log file?

how to shrink log file?

Rate Topic

Display Mode

Topic Options

Author

Message

jitendra.padhiyar

Posted Tuesday, November 13, 2012 3:10 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:09 AM
Points: 167, Visits: 440

Hello Master,

There was an issue of disk space at one of my live MSSQL Server, I tried to shrink some files and freed some disk space. There was one DB whose log file has about 900 MB of free available space. I tried to shrink that file but nothing shrink in that log file. I found that some transaction was going on that file. But one of my senior shrink that file !! and free almost 800 MB space. I dont know what he did, according to him he just shrink nothing else he did.

Is there anyway to shrink files when some transaction is going on ? Can we bypass all the live transaction to shrink file ?

Post #1384000

anthony.green

Posted Tuesday, November 13, 2012 3:24 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

You cannot shrink past the marked active portion of the log.

If the portion of the log was released and another earlier portion of the log marked as active then you could shrink the file to the new active portion of the log.

Managing Transaction Logs - http://www.sqlservercentral.com/articles/Administration/64582/
Why is my transaction log full - http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

Now have you done your investigations into what caused the log to grow? Is it general day to day usage? Missing transaction log management? etc etc.

You detailed you shrunk some files, what files did you actually shrink? Data files, log files? Have you rechecked for the after effects of shrinking files like fragmentation etc and took the nessesary steps to remove the after effects?

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1384007

jitendra.padhiyar

Posted Tuesday, November 13, 2012 3:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:09 AM
Points: 167, Visits: 440

Thanks master for your reply,

Before i shrink some data files as well as log file of other databases. After shrinking there no bad effect on other db. I am shrinking many times when found disk space issue. But today this spacific database's log file had about 900 MB available space, and i was not able to shrink that. So I thougt it might be used by some live transaction. If so, how can my collegue did that ? what he did differently ?

Is below command help me ?

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Is truncation on live environment will be advisable ?

Post #1384012

Jason-299789

Posted Tuesday, November 13, 2012 3:43 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 03, 2013 5:35 AM
Points: 803, Visits: 2,122

what version os SQL is the server running?

The reason for asking is that I thought they'd removed the functionality for BACKUP LOG WITH TRUNCATE_ONLY from SQL2008.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1384014

anthony.green

Posted Tuesday, November 13, 2012 3:44 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Shrinking data files causes fragmentation in your indexes, so I would strongly recommend you go and check for index fragmentation.

If a database has grown to a size it is usually because it needs to be that size. Granted one off imports and certain eronious instances where things just blow out of control happen and shrinking etc is the only way to reclaim the space, but 99% of the time you should never shrink a database unless you have a just and defined reason for doing so as the consequencies can be catestrophic if not immediatly resolved.

As you posted in the 2008 forum I am going out on a whim and saying your running SQL 2008, if so TRUNCATE_ONLY is not a valid backup option, it was removed in 2008 onwards due to the problems it causes.

As I have said in another article once today, go and check if your databases are in the right recovery model for your recoverability needs
http://msdn.microsoft.com/en-us/library/ms189275.aspx

Then put in place the proper transaction log management that is needed by refering to the two links I have already posted above.

If your in full, bulk-logged recovery but you dont do transaction log backups, you really need to start asking yourself do I really need to be in full, bulk-logged recovery.

Post #1384016

jitendra.padhiyar

Posted Tuesday, November 13, 2012 3:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:09 AM
Points: 167, Visits: 440

I am agree with you Anthony, that shrinking is not best practice. But here I am eager to know that how did my senior was able to shrink log file that I was not ? He is not ready to tell that how he did that. So as DBA and as curiocity I would like to know what he used to shrink that log file? Is there any other way to shrink file ? If truncation is not allowed what other way we had ?

Post #1384018

anthony.green

Posted Tuesday, November 13, 2012 3:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Transaction log's active portition of the log moved to an earlier point in the file, allowing to shrink more
Took a transaction log backup then shrank the file
Switched recovery to simple, shrunk the file, switched recovery to full

Post #1384019

jitendra.padhiyar

Posted Tuesday, November 13, 2012 4:13 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:09 AM
Points: 167, Visits: 440

Thanks a lot Anthony..!

Post #1384023

« Prev Topic | Next Topic »

Permissions