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

Commit open transaction. Expand / Collapse
Author
Message
Posted Sunday, October 17, 2010 7:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:00 AM
Points: 274, Visits: 1,469
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.
Post #1005863
Posted Sunday, October 17, 2010 8:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
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

Post #1005865
Posted Sunday, October 17, 2010 8:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:00 AM
Points: 274, Visits: 1,469
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.
Post #1005871
Posted Sunday, October 17, 2010 9:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:00 AM
Points: 274, Visits: 1,469
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.
Post #1005873
Posted Sunday, October 17, 2010 9:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:47 AM
Points: 7,855, Visits: 9,603
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
Post #1005877
Posted Sunday, October 17, 2010 10:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
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

Post #1005887
Posted Sunday, October 17, 2010 10:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
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

Post #1005888
Posted Sunday, October 17, 2010 11:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:47 AM
Points: 7,855, Visits: 9,603
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
Post #1005897
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse