February 8, 2012 at 6:08 am
Working on transferring a large SQL server bak file the source of which shares space with DB log files. I have alerts in place for disk space % remaining and noticed that a differential was taking up enough space to almost fill the drive due to the suspension of a full backup.
This got me thinking:
1) in the event that i had to cancel the differential backup while it was running how can i do this ?
I understand how to identify and kill the SPID but would steered away from this due to the potential for rollback (could take hours) or corruption.
2) in the event that i had run our of disk space for the LDFs (noting plenty of space for MDFs on another array).
a) what is likly to happen to the database when the LDF cant log transactions ?
b) how would i have resolved has this occurred ?
Note that LDF is using simple logging already.
Thank you for any advice
February 8, 2012 at 6:34 am
scott_lotus (2/8/2012)
Working on transferring a large SQL server bak file the source of which shares space with DB log files. I have alerts in place for disk space % remaining and noticed that a differential was taking up enough space to almost fill the drive due to the suspension of a full backup.This got me thinking:
1) in the event that i had to cancel the differential backup while it was running how can i do this ?
I understand how to identify and kill the SPID but would steered away from this due to the potential for rollback (could take hours) or corruption.
Kill the session with the KILL command. There's really no other way. Backups don't make (many) changes, so there won't be much to roll back.
Nothing that you can do in SQL, and that includes killing backups, shrinks or any other operation can cause database corruption. Corruption is 99% hardware problem, 1% SQL bug (roughly)
2) in the event that i had run our of disk space for the LDFs (noting plenty of space for MDFs on another array).
a) what is likly to happen to the database when the LDF cant log transactions ?
b) how would i have resolved has this occurred ?
Your database essentially becomes read-only, any operation that tries to make any changes fails. You would resolve that by finding out what is preventing the log from been reused and resolving whatever is the root cause.
Please read through this - Managing Transaction Logs[/url] and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
February 8, 2012 at 6:53 am
Thank you , great help.
February 8, 2012 at 6:58 am
GilaMonster (2/8/2012)
Your database essentially becomes read-only, any operation that tries to make any changes fails. You would resolve that by finding out what is preventing the log from been reused and resolving whatever is the root cause.
Hi Gail,
I ran into that situation once when one of the developer has issued a delete command (for millions of records). The log file for the database increased exponentially & the disk drive was full. Now, since the delete command was still not complete and there was no space available on the log file drive, therefore, SQL Server started to rollback the transaction (as expected) & it changed the database status to "InRecovery".
But again, since there was no space left on log drive, that recovery process too failed after several attempts.
In that case, I restored the database using the full, diff & log backups that we have to resume the functionality.
However I am interested in hearing from you if there is any even better way to resolve such kind of situations.
February 8, 2012 at 7:08 am
Divine Flame (2/8/2012)
GilaMonster (2/8/2012)
Your database essentially becomes read-only, any operation that tries to make any changes fails. You would resolve that by finding out what is preventing the log from been reused and resolving whatever is the root cause.Hi Gail,
I ran into that situation once when one of the developer has issued a delete command (for millions of records). The log file for the database increased exponentially & the disk drive was full. Now, since the delete command was still not complete and there was no space available on the log file drive, therefore, SQL Server started to rollback the transaction (as expected) & it changed the database status to "InRecovery".
Technically it's a linear increase, not an exponential one...
In Recovery means that the database restarted for some reason. A rollback won't do that. There's a specific case under snapshot isolation where it will (documented in BoL), or the service was restarted (maybe because the rollback was taking too long)
But again, since there was no space left on log drive, that recovery process too failed after several attempts.
So the database went into the suspect state?
In that case, I restored the database using the full, diff & log backups that we have to resume the functionality.
However I am interested in hearing from you if there is any even better way to resolve such kind of situations.
If the database went suspect, then you restore from backup, that's the only safe way.
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
February 8, 2012 at 7:29 am
GilaMonster (2/8/2012)
Technically it's a linear increase, not an exponential one...
Yeah right. I just used the word because at that time it felt like that ( the log file size increasing speed) :hehe:.
Thanks for the help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply