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

How would you recover from shutdown due to lack of drive space Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 3:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:29 AM
Points: 89, Visits: 567
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.
Post #1436204
Posted Wednesday, March 27, 2013 4:07 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 1:00 PM
Points: 42,308, Visits: 35,365
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 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 #1436206
Posted Wednesday, March 27, 2013 4:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:29 AM
Points: 89, Visits: 567
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.
Post #1436210
Posted Wednesday, March 27, 2013 4:23 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 1:00 PM
Points: 42,308, Visits: 35,365
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 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 #1436212
Posted Wednesday, March 27, 2013 4:29 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:29 AM
Points: 89, Visits: 567
Wow, I wonder where I got that misinformation. Good to know. How would you recover a user database in that scenario?
Post #1436215
Posted Wednesday, March 27, 2013 4:33 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 1:00 PM
Points: 42,308, Visits: 35,365
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 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 #1436216
Posted Wednesday, March 27, 2013 4:35 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:29 AM
Points: 89, Visits: 567
Fabulous. Thanks so much.
Elizabeth
Post #1436217
Posted Wednesday, March 27, 2013 4:41 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 1:00 PM
Points: 42,308, Visits: 35,365
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 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 #1436218
Posted Wednesday, March 27, 2013 5:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:29 AM
Points: 89, Visits: 567
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!
Post #1436223
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse