Wednesday, November 10, 2010

SQL Server Top 10 Secrets of Tempdb database

1. Tempdb is re-created every time when SQL Server restarts
2. Tempdb is always SIMPLE recovery mode
3. Certain database options can not be set for tempdb like OFFLINE, READONLY & CHECKSUM
4. You can not drop or backedup tempdb database
5. You can not run DBCC CHECKDB against Tempdb
6. You can not drop the guest user from Tempdb
7. Snapshot database can not be created against Tempdb
8. Shrinking Tempdb is not recommended
9. Move Tempdb database to new location after installation of sql server
10. Create a multiple Tempdb files give you better performance one file per CPU( Microsoft Best practice guide line says, It may be good for SAN), You need carefully plan this if you server really want it)