Sometimes you might need to re-organize your SQL server files around the server in order to improve I/O efficiency or simply because the current structure cannot coupe with the transaction log file or data file growth rate, and you need to expand onto other drives.

This post goes through how one might go about changing the SQL Server 2008 transaction log or data file location, commit this file move, and also diagnose and resolve any errors that could arise from performing such a move.

The statement for re-organizing SQL Server files and moving them from disk to disk is very simple, and looks like this:

Now this will change the location of the database file in the Master database, and raise a flag to rebuild the file in the new specified location. SQL Server will rebuild the file once the instance is restarted. So in order to finalize the SQL Server file or transaction log location changes, and rebuild the file at the new location, ensure that there is no critical transaction is running on the database (or wait until the next maintenance window) and restart the SQL Server service from the “SQL Server Configuration Manager” or use the “Windows Services” snap-in.

Now if you have not done your homework properly, sometimes you might run into issues (like I did). For example, if the new file path for the transaction log or data file does not contain enough disk space to rebuild the file fully. This issue is easy to solve if the data or transaction log file being moved belongs to a user database, just move it to a new location with enough disk space using the SQL statement above and then restart SQL Server service again.

If the file being moved belongs to a system database (such as TempDB), this will cause the SQL Server instance service to fail when starting, basically this will throw a generic error such as “SQL Server service could not be started”, if you check your Event Viewer you will find further details on this error and the specific database causing the error .

In order to recover from this situation, you need to start the SQL Server instance with Minimal Configuration, then perform the file move using the SQL statement above, then restart the instance service without the Minimal Configuration flag. The flag that needs to be used when starting the SQL Server service in Minimal Configuration mode is:

sqlservr.exe -f

More details on SQL Server service start-up options and flags on MSDN