SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to shrink log file?


how to shrink log file?

Author
Message
Jitendra Padhiyar
Jitendra Padhiyar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 674
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 ?
anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23762 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


Jitendra Padhiyar
Jitendra Padhiyar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 674
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 ?
Jason-299789
Jason-299789
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4863 Visits: 3232
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
anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23762 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


Jitendra Padhiyar
Jitendra Padhiyar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 674
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 ?
anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23762 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


Jitendra Padhiyar
Jitendra Padhiyar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 674
Thanks a lot Anthony..!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search