Temp database is a global resource for all users connected to an instance. We are not using tempdb directly, but we are using tempdb while creating temp tables, cursors, rebuilding indexes etc. Today I am going to explain in detail about tempdb. Tempdb is mostly like a user database in physical level i.e. it contains two files ldf and Mdf.
Tempdb is in simple recovery model, which means it is minimally logged.
Note: Tempdb is created each time when SQL Server instance is restarted. So there is no need for moving tempdb to another server. We can change the location of the tempdb files such as ldf and mdf to new location using the command
USE master;
GO
ALTER DATABASE tempdb
MODIFYFILE(NAME = tempdev, FILENAME = 'C:Programfiles\SQL Server \SQL Files\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFYFILE(NAME = templog, FILENAME = 'C:Programfiles\SQL Server \SQL Files\templog.ldf');
GO
What does tempdb consists of?
- When we are creating temporary tables, global temporary tables, cursors.
- Tempdb is used by the SQL Server database engine, for creating work tables for doing sorting operations such as Group by, Union etc.
- Row versioning that are generated by data modifications transactions in a database.
- Row versioning that are generated by data modification such as online index operations, Multiple Active result sets and After triggers.
Dipu Joseph
12 years agoDear Varun,
I need to restore Backup.bak file from sql server 2012 to sql server 2008. I searched for lot of tools. but didnt find. Then I found SSIS Package. Is it hopeful to clear my probls. If can pls reply me with solutions. I need it with urgently…
Thanks in advance
Varun R
12 years agoYou need to change the coimpatiblity of SQL 2012 database to 2008. After that you need to run a full backup , which you can restore in the 2008 server.
jagadeesh sai
12 years agocould you please share the details of sql interview quires
Marylou Daugherty
12 years agoAny user can create temporary objects in tempdb. Users can only access their own objects, unless they receive additional permissions. It is possible to revoke the connect permission to tempdb to prevent a user from using tempdb, but this is not recommended as some routine operations require the use of tempdb.
Hope Barron
12 years agoToday I will show how we can move the files of the TempDB database to different drives.