TempDB Disk Goes Missing!

 General  No Responses »
Sep 252008
 

I received a phone call from a colleague last night who was trying to remember the process for fixing TempDB if the disk goes missing.  The server had crashed, and the disk that TempDB was assigned to was not available, and so SQL Server could not start.  It’s not the most common problem so it took a few minutes to remember how to do it.

Restart SQL Server with the command line parameter -T3608, and then run alter database commands to move the location of TempDB:

ALTER DATABASE TempDB MODIFY FILE  (name = tempdev, 
    filename = 'E:\Data\tempdb.mdf')
GO

ALTER DATABASE TempDB MODIFY FILE (name = templog, 
    filename = 'F:\Logs\templog.ldf')
GO

Restart SQL Server without -T3608, and TempDB should be created in the new location. 

The alternative is to not reconfigure SQL Server at all, but rename an existing disk to the missing drive letter, just to get SQL Server started.

Attaching a .MDF without a .LDF

 General  1 Response »
Sep 222008
 

A question that seems to come up often is how to attach a data file (.MDF) without a transaction log?  If a database has been detached from SQL Server cleanly, then there should be no information inside the transaction log of any use.  If the database was not detached cleanly (for example, if the machine crashed, and SQL Server has not been restarted, or if a copy of the file is made when SQL Server is still running) then the database may be in an inconsistent state, requiring the information currently contained inside the transaction log file.

Assuming that the database was shut down cleanly, the transaction log file can be recreated.  There are two methods: sp_attach_single_file_db and CREATE DATABASE.

The sp_attach_single_file_db is a shorthand way of calling CREATE DATABASE FOR ATTACH.  You can see exactly what sp_attach_single_file_db does by running “sp_helptext sp_attach_single_file_db” in Management Studio.  The main restriction for FOR ATTACH when used in this fashion is that only one transaction log can be created.  The new transaction log will be created in a default location, or if the database is read only, in the original location specified in the .MDF file.  The basic syntax is: 

EXEC sp_attach_single_file_db @dbname='MyDatabase', 
    @physname='E:\Database\MyDatabase.mdf'

If you need two transaction log files recreated, you need to use CREATE DATABASE FOR ATTACH_REBUILD_LOG.

CREATE DATABASE MyDatabase ON 
    (FILENAME = 'E:\Database\MyDatabase.mdf') 
FOR ATTACH_REBUILD_LOG

If possible, I recommend taking backups and then restoring the backup, rather than try to attach a .MDF file.  It’s a safer and more reliable method of moving a database from server to server.