October 17, 2010 at 7:35 am
Hi,
I have an open transaction on ABC db, now I have to shrink the db file without wait. As .ldf file is full and there is no space available on disk. As I have checked the status of that session it is showing <B>sleeping</B> since very long.Can anybody suggest me how can I shrink .ldf file because if I shrink .ldf with open transaction is there any chance of db corruption or data lost ? Can I commit open transaction from different session ?
Thanks in advance.
October 17, 2010 at 8:04 am
You can't commit it from a different connection. Either find where it's coming from and tell the user to commit, or kill it and SQL will roll back all changes that the transaction has done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2010 at 8:31 am
Hi Gail,
I cannot contact to the remote user, as they are using some updates in db so I cannot kill the session as well. So , what shall I do now ?
Kindly suggest.
October 17, 2010 at 9:07 am
As my .ldf has become 140 gb and disk space is also 140 gb what I can do in this situation ?
First : - The query that is being run by user will be stopped or what ?
Second : - If I shrink the .ldf file what will happen?
Third : - Can I shrink file only to its initial size ? If I am not wrong shrinking reduce the physical size of file only.
October 17, 2010 at 9:46 am
beejug1983 (10/17/2010)
As my .ldf has become 140 gb and disk space is also 140 gb what I can do in this situation ?First : - The query that is being run by user will be stopped or what ?
Second : - If I shrink the .ldf file what will happen?
Third : - Can I shrink file only to its initial size ? If I am not wrong shrinking reduce the physical size of file only.
Probably exactly nothing will happen if you shrink the .ldf file, because the oldest part of it is likely to be in your "open for a long time" transaction, so shrink will be unable to change the size. But you could try it and see what happens - it may not have got that bad yet. If you are one full recovery model you should backup the log first (to enable space to be freed) - but if there is really no free space you can't do that because the backup has to be logged.
If you can't contact the user, and the system is unable to make progress because it's out of disc space, you have two options: leave the system stuck until the user becomes contactable (with some risk that the situation will deteriorate further, so that the transaction can't be rolled back or committed because there isn't space to log that action) or roll the transactiopn back now, as Gail suggested - but that too may not work if there isn't enough space on the disc to do it.
So you may be in the situation where you have to make space by moving some other file off the disc or shrinking something other than this log file before you can do anything to alleviate the situation.
Tom
October 17, 2010 at 10:52 am
beejug1983 (10/17/2010)
First : - The query that is being run by user will be stopped or what ?
No, but if your tran log runs out of space no other data changes will be allowed. In essence, your system will come to a complete halt
Second : - If I shrink the .ldf file what will happen?
Absolutely nothing. That active transaction is preventing the log space from being reused. There is no free space in your log. Shrink releases unused space to the OS.
You can shrink as many times as you like, until that open transaction is either committed (by the user who began it) or rolled back nothing will happen at all.
You really don't have many options here.
1) Contact the user and get that transaction committed
2) Kill the process and it will roll back, once rollback is complete the space in the log will be reusable (after next log backup in full recovery)
3) Do nothing. As soon as your log fills every single transaction that occurs will cause an error because there's no free space in the log. The system will remain like this (unusable) until either option 1 or 2 is followed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2010 at 10:54 am
Tom.Thomson (10/17/2010)
(with some risk that the situation will deteriorate further, so that the transaction can't be rolled back or committed because there isn't space to log that action) or roll the transactiopn back now, as Gail suggested - but that too may not work if there isn't enough space on the disc to do it.
A transaction can always be rolled back. SQL always reserves enough log space when it performs operations for a rollback should it be necessary.
This has to be like this, as otherwise a full transaction log would almost certainly result in a suspect database if space was not reserved for rollback
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2010 at 11:42 am
GilaMonster (10/17/2010)
A transaction can always be rolled back. SQL always reserves enough log space when it performs operations for a rollback should it be necessary.This has to be like this, as otherwise a full transaction log would almost certainly result in a suspect database if space was not reserved for rollback
Good - not quite as bad as I thought then.
Tom
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply