Link to home
Start Free TrialLog in
Avatar of Pau Lo
Pau Lo

asked on

tempDB in SQL

(not a SQL DBA) but.. what kind of data goes into the default system database "tempDB", i.e. if you didnt back it up, what is the risk, why would you ever need to restore it, or what data would you lose if you did not restore it? can you not just create a fresh tempDB if it doesnt really store any permanent data?

I know some of the system databases store important configuration data which I can then see the logic in backing them up, but is tempDB actually storing any specific useful data permanently?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Pau Lo
Pau Lo

ASKER

why do you need to set page verifacation and to run DBCC CHECKDB over tempDB then if it is recreated every time SQL starts again?
DBCC CHECKDB uses tempdb for sorting purposes. We can run dbcc checkdb on tempdb but really we don't need it, it will not run properly as you can not have a snapshot of tempdb.

Read more:
https://msdn.microsoft.com/en-us/library/ms176064(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/aa937548(v=sql.80).aspx
http://blog.consultdba.com/2010/05/dbcc-checkdb-and-tempdb.html
http://blogs.extremeexperts.com/2010/05/24/checkdb-and-tempdb/
why do you need to set page verifacation and to run DBCC CHECKDB over tempDB
You don't need to but since is possible some DBAs run it.
Talking by myself I don't do that but can accept who do it. No warm on that for sure.