Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Commit open transaction.


Commit open transaction.

Author
Message
beejug1983
beejug1983
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1482
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44388
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


beejug1983
beejug1983
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1482
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.
beejug1983
beejug1983
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1482
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.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12008
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

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44388
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44388
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12008
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

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