Unresponsive SQL Server due to no disk space

An instance of SQL Server had become unresponsive due to disk space on the drive housing all the database data files dropping to zero.

Unfortunately on this instance the remote admin connections was not enabled, therefore I could not connect to the instance via that method.

I looked around the various drives available and came up with a plan to move various datafiles around and release some space.

Here was the procedure:

  1. Stop the SQL Server service. (Actually SQL was so hung that I had to restart the physical server to make this work)
  2. Move all of the datafiles to their new homes.
  3. Open a command window and run:
    net start MSSQLSERVER /f /T3608

    This starts SQL Server in minimum config mode, in particular this prevents SQL Server from attempting to recover any databases which would of course error because I had moved all of the datafiles.

  4. Because SQL Server was in single user mode and I had no DAC available I had to use SQLCMD (in a CMD window run SQLCMD). The gotcha here for me was remembering to use the go command. This terminates a multiline batch and delivers it to the database engine for processing.
  5. Discover the contents of the data file catalogs:
    SELECT DB_NAME(database_id),physical_name FROM sys.master_files
    WHERE database_id = DB_ID('database name');
    GO
  6. Repoint the files:
    ALTER DATABASE database name MODIFY FILE(
    NAME = 'logical file name',
    FILENAME = N'path + physical file name');
    GO
  7. Exit SQLCMD (type EXIT) back to the CMD window, stop SQL Server and restart normally:
    NET STOP MSSQLSERVER
    NET START MSSQLSERVER

By this stage I was able to launch SSMS and review the logs and move around some backup files and other tidy up jobs.

The key thing was an unresponsive server was brought back online, it felt good to do that. I hope I/you never need this process again – but its nice to know it works of you do.

Advertisements
This entry was posted in Disaster Recovery, SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s