Issue
It may be required to move the SQL Server databases files from one location to another.

Product Line
SQL Server

Environment
SQL Server Management Studio

Cause
The tempdb is used extensively during many SQL Server database operations. At times, given certain types of transactions, the tempdb files (tempdb.mdf and templog.ldf) can grow very large and run out of disk space. These transactions will fail if tempdb runs out of drive space. It may be necessary to move the SQL Server tempdb files to a location in which more drive space is available. This can apply to any database.

The following information is included with the event. We will use tempdb as an example.

D:\Program Files\Microsoft SQL Server\MSSQL10_50.STXPWRMON\MSSQL\DATA\templog.ldf
112(There is not enough space on the disk.)

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*



- Run the Windows Disk cleanup tool to free up disk space and delete temporary files.
- Move the 'tempdb' database and log files to another location by following the steps below:

Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.

1. Determine the logical file names of the tempdb database and their current location on the disk by executing the following query:
   
   SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
   
2. Change the location of each file by using ALTER DATABASE by executing the following query in SQL:
   
   The new location of the tempdb database mdf and ldf files is defined as highlighted below, this may be modified to meet your requirements. The file path will need to be created, if it does not already exist.
   

   USE master;
   ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
   
3. Stop and restart the instance of SQL Server.
4. Verify the file change by executing the following query in SQL:
   
   SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
   
5. Delete the tempdb.mdf and templog.ldf files from the original location.

Released for:Schneider Electric New Zealand