Moving tempdb files

A common task for a DBA is to move tempdb files to a seperate drive.  During a SQL Server installation, the tempdb files (.mdf and .ldf) are stored in the same drive the sql binaries are installed along with master, msdb and model files.  As a best practice, tempdb files should always be moved to a seperate drive. The following provides the steps involved in moving tempdb files:

1) Get the logical and physical name for tempdb files.

SELECT d.name
, mf.name logical_name
, mf.physical_name
, mf.type_desc
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = ‘tempdb’
ORDER BY mf.type, mf.name

2) Modify the “NAME” adn “FILENAME” with the new value for tempdb

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdb, FILENAME = ‘T:\SQLDATA\tempdb.mdf’ )
GO

3) Validate changes took place.

SELECT d.name
, mf.name logical_name
, mf.physical_name
, mf.type_desc
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = ‘tempdb’
ORDER BY mf.type, mf.name

4) Restart the sql server service

After SQL Server is restarted, the new files will be created in the new location. The old tempdb files will remain in previous location and they will need to be deleted manually.

Advertisements

The log for database “dbname” is not available

Today, there was a log backup failure on SQL Server.  When I started looking at the SQL Server logs, I found the following error: “The log for database ‘dbname’ is not available. Check the event for related error messages. Resolve any errors and restart the database.  Error: 9001, Severity: 21, State:1.” I ran the sys.master_files view to make sure it had the correct physical name associated to the database but all looked just fine. At that point, my first thought was to run the DBCC CHECKDB command to look for corruption but I immediately received the same error. I decided to detach and attach the database but I was not successful. The last option I had was to set the database offline and bring it online which was the final solution to this issue. When bringing the database online, it recovered the database and rolled transactions forward.