Does SQL Server recreate the primary files, secondary files for the TempDB database every time the server is restarted? Or does it just refresh the .mdf, .ndf or log files every time the server restarts?
Asked
Active
Viewed 3,313 times
0
marc_s
- 732,580
- 175
- 1,330
- 1,459
user2728106
- 185
- 2
- 5
- 15
-
1the answer is a 5 second search away.... – Mitch Wheat Mar 09 '16 at 03:04
-
Yes. but I am having a bit confusion over the mdf file recreation ,that Whether it deletes the mdf also when server shut and again recreates a mdf file when server restarts. – user2728106 Mar 09 '16 at 04:25
1 Answers
2
As per msdn documentation, yes it is re-created everytime SQL Server is started:
Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.
Harsh
- 1,309
- 8
- 14
-
And does it recreates the files on the same drive path as earlier ? – user2728106 Mar 09 '16 at 04:14
-
The location *where* the database is created is defined in SQL Server - so yes, unless you **change it**; it will be the same location on disk – marc_s Mar 09 '16 at 05:50