SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Recovery of TempDB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

InfraDBA
Starting Member

38 Posts

Posted - 01/09/2011 :  09:13:30  Show Profile  Reply with Quote
Since each time SQL server gets restarted the data and log of tempDB is also newly created.Then where does TempDB gets its recovery information from?

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/09/2011 :  09:21:12  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Recovery information?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

InfraDBA
Starting Member

38 Posts

Posted - 01/09/2011 :  09:50:55  Show Profile  Reply with Quote
As far as I know recovery will consist roll forward and roll back to bring the database into consistent state.For this database needs a transaction log.How does it happen in case of TempDB.
Go to Top of Page

InfraDBA
Starting Member

38 Posts

Posted - 01/09/2011 :  10:57:05  Show Profile  Reply with Quote
Please reply!
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/09/2011 :  11:30:57  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Please be patient! Bumping your post after an hour is just rude, especially considering it's a weekend. We're volunteers, we post in our spare time.

TempDB doesn't need to be recovered, it's recreated completely when SQL starts. Nothing that was in there before the restart will remain afterwards.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

InfraDBA
Starting Member

38 Posts

Posted - 01/09/2011 :  23:24:50  Show Profile  Reply with Quote
Thank you so much....
Go to Top of Page

InfraDBA
Starting Member

38 Posts

Posted - 01/10/2011 :  11:44:38  Show Profile  Reply with Quote
I have one more confusion..when the statement hold good when you say tempdb allows only roll back not roll forward,when the statement hold good?
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/10/2011 :  12:25:49  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Sorry, don't understand what you're asking.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/10/2011 :  12:57:35  Show Profile  Visit russell's Homepage  Reply with Quote
There is no recovery phase for tempdb. It is recreated from scratch (with no user objects) every time SQL is restarted. Thus, no roll forward.

The only rollback will occur if an explicit transaction is rolled back, or if the service is shutdown during a transaction.
Go to Top of Page

InfraDBA
Starting Member

38 Posts

Posted - 01/10/2011 :  13:49:46  Show Profile  Reply with Quote
So even if we delete Tempdb log file it will come up without any issues?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/10/2011 :  13:58:08  Show Profile  Visit russell's Homepage  Reply with Quote
Yes. But why would you do that?
Go to Top of Page

InfraDBA
Starting Member

38 Posts

Posted - 01/10/2011 :  14:10:39  Show Profile  Reply with Quote
Just out of curiosity...
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/10/2011 :  14:12:45  Show Profile  Visit russell's Homepage  Reply with Quote
Sure, you can delete it (as long as the SQL Service isn't running), won't hurt anything.

Edited by - russell on 01/10/2011 14:13:10
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/10/2011 :  14:35:55  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Don't go randomly deleting database files out of curiosity (unless on an instance that you're happy to completely reinstall). You could break things beyond repair


--
Gail Shaw
SQL Server MVP
Go to Top of Page

InfraDBA
Starting Member

38 Posts

Posted - 01/10/2011 :  14:42:45  Show Profile  Reply with Quote
well just asking in case of TempDB...:-) Thanks to all.....
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000