Friday, 22 February 2008

SQL Server TempDB useful tips

Moving Temp Database Files
Step 1:
Move the tempdb files to a new physical location (mostly on disk drive in RAID protection 1, 1 + 0 or 5 )
use master

GO
Alter database tempdb modify file (name = tempdev, filename = '\tempdev.mdf')
GO
Alter database tempdb modify file (name = templog, filename = '\templog.ldf')
GO
Step 2:
Restart the SQL Server service
Step 3:
On Restart the tempdb will be created with the new location specified
Step 4:
Remove the data and log file of the tempdb from the old location


Starting SQL Server without TempDB
SQL Server cannot operate with out the tempdb database.When the tempdb filesare corrupted / deleted accidentaly we can restart the SQL Server using the following commandline utlitity
SQLSERVER.exe –s -f -c -T3609


TempDB Best Practices
The below link explains some of the best practices on using the tempdb
http://www.mssqltips.com/tip.asp?tip=1432

3 comments: