March 27, 2013 at 3:58 pm
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.
March 27, 2013 at 4:07 pm
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
March 27, 2013 at 4:15 pm
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.
March 27, 2013 at 4:23 pm
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
March 27, 2013 at 4:29 pm
Wow, I wonder where I got that misinformation. Good to know. How would you recover a user database in that scenario?
March 27, 2013 at 4:33 pm
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
March 27, 2013 at 4:35 pm
Fabulous. Thanks so much.
Elizabeth
March 27, 2013 at 4:41 pm
On the log file, too tired to explain, but these should cover most.
Managing Transaction Logs[/url]
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
March 27, 2013 at 5:15 pm
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!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply