November 13, 2012 at 3:10 am
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 ?
November 13, 2012 at 3:24 am
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?
November 13, 2012 at 3:36 am
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 ?
November 13, 2012 at 3:43 am
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
November 13, 2012 at 3:44 am
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.
November 13, 2012 at 3:53 am
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 ?
November 13, 2012 at 3:59 am
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
November 13, 2012 at 4:13 am
Thanks a lot Anthony..!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply