January 19, 2013 at 11:20 pm
I am reading a tutorial of Randal Paul that says,
Checkpoints don't flush tempdb data to disk. Log records are not flushed to disk on transaction commit. Tempdb data pages and log buffers are not written to disk.
But how can I explain our tempdb .mdf and PDF files keep getting bigger.
Thanks
January 20, 2013 at 6:28 am
it will write tempdb stuff to disk all right, it's just not at checkpoint time, like with regular db.
Why: tempdb is re-created every time SQLServer is started, so there is no need with respect to recovery of db.(which is the need with regular db)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 20, 2013 at 8:21 am
The automatic checkpoint that SQL Server issues is not the only mechanism that writes the data into the disk. If you issue manual checkpoint command, it will write the TempDB's data to the disk. When Lazywriter writes the data to the disk it will also write the TempDB's data.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 20, 2013 at 5:29 pm
ALZDBA (1/20/2013)
it will write tempdb stuff to disk all right, it's just not at checkpoint time, like with regular db.Why: tempdb is re-created every time SQLServer is started, so there is no need with respect to recovery of db.(which is the need with regular db)
I understand the Why: statement above you mentioned,
But i am not sure what it means: when you say it will wirte tempdb stuff to disk all right.
January 20, 2013 at 5:31 pm
Adi Cohn-120898 (1/20/2013)
The automatic checkpoint that SQL Server issues is not the only mechanism that writes the data into the disk. If you issue manual checkpoint command, it will write the TempDB's data to the disk. When Lazywriter writes the data to the disk it will also write the TempDB's data.Adi
We don't usually do the manul checkpoint command.
SO what else make it to write to disk and cause tempdb space grow?
Thanks
January 20, 2013 at 10:34 pm
We don't usually do the manul checkpoint command.
SO what else make it to write to disk and cause tempdb space grow?
Thanks
As I wrote in the previous message - Lazywriter will also write TempDB's data to the disk.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2013 at 1:50 am
as you will know Tempdb is a special db, available and used by everyone in the instance.
General usage of tempdb: http://msdn.microsoft.com/en-us/library/ms190768.aspx
Great post by Gail Shaw which highlights indexing and how operations spill data to tempdb
another nice usage article : http://www.databasejournal.com/features/mssql/tempdb-space-usage-in-sql-server.html
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply