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

how to shrink log file? Expand / Collapse
Author
Message
Posted Tuesday, November 13, 2012 3:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 8:59 AM
Points: 209, Visits: 567
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
Posted Tuesday, November 13, 2012 3:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 5,218, Visits: 5,067
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
Posted Tuesday, November 13, 2012 3:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 8:59 AM
Points: 209, Visits: 567
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
Posted Tuesday, November 13, 2012 3:43 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Posted Tuesday, November 13, 2012 3:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 5,218, Visits: 5,067
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.




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 #1384016
Posted Tuesday, November 13, 2012 3:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 8:59 AM
Points: 209, Visits: 567
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
Posted Tuesday, November 13, 2012 3:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 5,218, Visits: 5,067
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




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 #1384019
Posted Tuesday, November 13, 2012 4:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 8:59 AM
Points: 209, Visits: 567
Thanks a lot Anthony..!
Post #1384023
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse