Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tempdb and disk Expand / Collapse
Author
Message
Posted Saturday, January 19, 2013 11:20 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:31 PM
Points: 1,773, Visits: 3,209
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

Post #1409257
Posted Sunday, January 20, 2013 6:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 7,010, Visits: 8,461
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1409272
Posted Sunday, January 20, 2013 8:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:26 PM
Points: 2,122, Visits: 5,473
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/
Post #1409281
Posted Sunday, January 20, 2013 5:29 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:31 PM
Points: 1,773, Visits: 3,209
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.
Post #1409328
Posted Sunday, January 20, 2013 5:31 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:31 PM
Points: 1,773, Visits: 3,209
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
Post #1409329
Posted Sunday, January 20, 2013 10:34 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:26 PM
Points: 2,122, Visits: 5,473
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/
Post #1409354
Posted Monday, January 21, 2013 1:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 7,010, Visits: 8,461
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
http://sqlinthewild.co.za/index.php/2011/11/07/sql-university-advanced-indexing-sorting-and-grouping/

another nice usage article : http://www.databasejournal.com/features/mssql/tempdb-space-usage-in-sql-server.html


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1409416
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse