SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How would you recover from shutdown due to lack of drive space


How would you recover from shutdown due to lack of drive space

Author
Message
Elizabeth.Block
Elizabeth.Block
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 702
We have a database server hosting SharePoint sites. I'm not the SharePoint admin so I don't know about its admin processes. Our SharePoint admin tried to delete a site, which almost shut down the SQL Server because tempdb and the SharePoint database log balooned up. We're working on figuring out a better way to do this. So... Obviously we try to avoid running out of space with various monitoring tools but how do you recover if a rogue process fills up the drive and the SQL Server shuts down? We're going to do our best to avoid this scenario but we almost got to that point today in less than an hour...
Thanks for any pointers.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86594 Visits: 45246
SQL shouldn't shut down due to lack of space, unless something bad went wrong in the system DBs. It would normally just roll back the offending transactions, at most make the databases read only if the logs are full.

Can you check the SQL error log, look for messages relating to the shutdown?

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


Elizabeth.Block
Elizabeth.Block
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 702
This is just a hypothetical situation, fortunately. I had heard eons ago that SQL Server would shut itself down if it ran out of disk space. Is this an urban myth? What did happen was the database log file and tempdb both ballooned up to around 17G each and there was only 7M left on the drive. I then shrank the database log file, deleted backups that I had secondary copies of and killed the rogue process. I just thought I should figure out what to do if tempdb or a database log file filled up the drive.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86594 Visits: 45246
Elizabeth.Block (3/27/2013)
I had heard eons ago that SQL Server would shut itself down if it ran out of disk space. Is this an urban myth?


It shouldn't do. There's probably some set of circumstances where it can, but it would be more than just tempDB and a user database log running out of space. TempDB runs out of space, current queries using it will be rolled back, user database log runs out of space, data modifications fail and roll back and database goes read only.

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


Elizabeth.Block
Elizabeth.Block
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 702
Wow, I wonder where I got that misinformation. Good to know. How would you recover a user database in that scenario?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86594 Visits: 45246
Free up space on the drive and the database will carry on working with no additional work on your side. Worst case, if you can't, add a second log file on another drive then figure out why the log space is not being reused.

Also, depending on recovery model, the space in the log should be marked reusable on log backup or checkpoint, if that happens then even if the drive is full the DB goes back to normal because there's free space inside the log file for it to use.

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


Elizabeth.Block
Elizabeth.Block
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 702
Fabulous. Thanks so much.
Elizabeth
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86594 Visits: 45246
On the log file, too tired to explain, but these should cover most.
Managing Transaction Logs
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

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


Elizabeth.Block
Elizabeth.Block
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 702
Very nice article. I didn't know the bit about the log_reuse_wait_desc. In the situation today, SharePoint had a nasty piece of code which was deleting everything in sight. After I killed that script, it took a long time for SQL to roll all of those deletes back.
Thanks again!
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