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:
- Stop the SQL Server service. (Actually SQL was so hung that I had to restart the physical server to make this work)
- Move all of the datafiles to their new homes.
- 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.
- 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.
- 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
- Repoint the files:
ALTER DATABASE database name MODIFY FILE( NAME = 'logical file name', FILENAME = N'path + physical file name'); GO
- 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.