Wednesday, June 6, 2012

What is tempdb database?

Tempdb database is a global resource that is available to all users connected to the instance of SQL Server.
1) TempDB will cleared everytime when the SQL Server is stopped and started, It will recreate only if the file path is moved using alter database tempdb.
2) By default TempDB creates an MDF file with 8 MB and LDF file with 1 MB and grows upto 2 TB
3) Each SQL Server Instance have only one Temp DB Database.
4) We cant change the recovery model and of TempDB Database, It is always in Simple Recovery Model
5) It is not possible to drop or detach tempdb database
6) Not possible to change database options like (e.g. Database Read-Only, Auto Close, Auto Shrink Etc)
7) Like all other databases we can't backup or restore or setup mirroring for tempdb database
Below command is used to move tempdb database into new location
USE master;
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\Data\TEMPDB.mdf');
MODIFY FILE(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\Datatemplog.ldf');

1 comment: