May 21, 2009 at 8:15 am
I have checked many sites for Tempdb.mdf and .ldf.
but what does each contain?
Thanks for any help.
May 21, 2009 at 8:18 am
It's the data and log file for TempDB. That's the DB where temp tables and table variables are stored and where SQL stored intermediate results for queries (spools, hashs, etc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2009 at 9:26 am
thanks ;
just want to confirm if this tempdb.ldf is transaction logs for temporary SP's and DML between a Begin and Commit/Rollback on a temporary objects etc.
May 27, 2009 at 5:03 am
Yes, you are right.
But not only that, as we know ldf maintains LSN's and will get truncate once it is committed to mdf agin its depends on the recovery which we use.
FYI:
By default only model db will be in Full recovery model and rest in Simple recovery.
Thanks,
Raj
May 27, 2009 at 2:17 pm
draj108 (5/27/2009)By default only model db will be in Full recovery model and rest in Simple recovery.
Please note Original Poster is refering to TempDB where transactions are minimally logged and database itself gets recreated at startup time; talking about recovery model for TempDB is kind of meaningless.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 28, 2009 at 3:21 am
Have a look in SQL Server Books Online (BOL) in the section 'tempdb databases'. This will tell you more about what tempdb is used for.
After you have read this, if you have any further questions there aer people here who are happy to help.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 28, 2009 at 5:33 am
Give me your email address and i will send you the tempdb PPTX for the triage that I took @ microsoft ..
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
May 28, 2009 at 6:17 am
May 28, 2009 at 7:39 am
its done ........
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
January 28, 2015 at 2:55 am
hi_abhay78 (5/28/2009)
Give me your email address and i will send you the tempdb PPTX for the triage that I took @ microsoft ..
Dear Abhay Chaudhary
I also interested in Tempdb SQL server. Could you share to the tempdb PPTX file, please?
Thanks so much for your supportive.
My Email: vohau2002@yahoo.com
January 29, 2015 at 1:42 pm
hau.username (1/28/2015)
I also interested in Tempdb SQL server. Could you share to the tempdb PPTX file, please?
I too would love to see that if it is not a violation of some copyright.
I remember that index builds and rebuilds have an option to build in TempDB. We had some reports that went nuts and returned whole tables and then filtered down to what was needed. Those intermediate results were too large to stay in memory so SQL Server paged them out to (guess where). After a while the server began again to be responsive but TempDB was enormous. We figured that the fastest way to deal with that was to stop and restart the service. Run the following:SELECT
[name], [create_date]
FROM [sys].[databases]
ORDER BY [create_date] DESC Note that TempDB shows at the top of the list most of the time. Also can serve a a quick way to see when the service was last started.
ATBCharles Kincaid
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply