Advisable to restart a SQL Server running Windows when disk space is low?

Question

We've had a sudden drop in hard drive space on the OS partition of our SQL Server (as databases are being stored there). It's causing various issues, and we're unable to move one of the databases, as the OS now seems to be placing pre-emptive locks on the database to ensure that the machine at least stays running.

We've tried everything we can think of. Is it advisable to now stop the SQL Server instance then restart Windows?

We're thinking this is the only way to clear things up, however our infrastructure guy is concerned that Windows will first try to write its state to disk before it restarts which could make the issue worse.

Any advice/experience in this situation?

Answer 1

The short answer is that we eventually literally pulled the plug on the SQL server and got away with it, (although I wouldn't necessarily recommend the approach). If you're in a situation like we were with this though, here's the full scenario:

• We couldn't log onto the box at all, and all services dependant on the database were running very slowly. The OS seemed to be partially shutting down.
• Eventually got lucky and managed to allocate an extra 6Gb to the server (from a low of 4Mb free), by connecting to it using computer management (after several attempts).
• After this, all attempts to detach the database we wanted to move failed (we tried setting the database into single user mode with immediate rollback, also setting it into simple recovery mode, manually killing off SPIDs, nothing worked).
• We were seeing pre-emptive database locks placed by the OS (presumably for the OS to ensure it could keep running), which seems to have been what was preventing us detaching the database in order to move it to a different drive.
• We tried to stop the entire database instance, however this didn't work and eventually we literally pulled the plug on the server.
• When it restarted we were able to immediately detach the database as the restart straightened out the OS. Moved the database, re-attached it and we're back in business :)
• The database we moved is (initially) stuck in 'read only' state, however we do only read from it.

Answer 2

> We've tried everything we can think of.

As stated in all the site guides for StackExchange you should include what you have tried already in your question. Also include the results of what you tried - i.e. any error messages that resulted. Otherwise we are probably missing vital clues that would help us help you, and we may waste time suggesting things you've already tried.

To answer the direct question: it should be OK to restart everything, but it almost certainly won't help you fix the problem.

You certainly shouldn't just move database files around while SQL Server is off, instead either backup the restore to another location (using WITH MOVE if talking to the DB directly or the relevant file location options in SSMS's restore dialogue) of (faster, using less temporary space, but slightly less safe) detach the a database with sp_detach_db, move the files, and reattach with sp_attach_db (or there are options in SSMS for detach and attach, which you may prefer instead of hand-writing calls to those to system procedures).

Before doing anything, including restarting services, make sure you know why the space is suddenly low. If it is obviously due to database files growing carry on, but if there is no obvious use of space like that do check for filesystem corruption just in case, and make sure that is fixed before proceeding.

If the problem is partly due to sudden growth in transaction log files because of a glut of large transactions since, you can probably buy yourself some breathing room back taking backups then truncating the transaction logs. Remember that if you are log shipping or using incremental/differential backups as part of your normal backup regime this will break your backup chain and you'll need to restart it.