Calculate tempdb size for Read commited snapshot enabled

Question

Calculate tempdb size for Read commited snapshot enabled

vkundar

Hall of Fame

Points: 3431
More actions
April 14, 2014 at 10:57 am

#290249

Hi All,
I receive Error: 3967, Severity: 17, State: 1. Insufficient space in tempdb to hold row versions. We have 8 data files for temp db of 10210 GB size and given 10240 GB as max size.
As MS suggest to calculate the temp db file size and growth rate we need to monitor the perform counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object.
basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction
My report disk utilizations says tempdb is full ? I thonk I need a shrink for the file .
Still I am confused in calculating the size , Please provide your input.
My perform counter gives me data as such
Free Space in tempdb (KB)              279938496
Version Generation rate (KB/s)          53681040
Version Cleanup rate (KB/s)       53422320
Version Store Size (KB)      258720
Version Store unit count      22
Version Store unit creation                     774
Version Store unit truncation         752
Cheers,
Got an idea..share it !!
DBA_Vishal

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

could be any number of things causing full tempdb: improper Cartesian products blowing up rowcounts, version store (possibly bloated due to suboptimal design/coding practices), massive sorts, massive hashes (often due to bad design/code and/or suboptimal indexing), bad statistics leading to poor query plans, transactions left open too long, etc, etc, etc

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

vkundar Hall of Fame Points: 3431 More actions · Answer 2

Thanks Kevin.

Yes I agree I do see many transacations ( data loading runs for a long time with open_tran value euther 1 or 2)

I have asked the team to look into it.

But incase I need to plan the temp db size what should be value of the data files considering the perf counters output and MS formula.

Cheers,

Got an idea..share it !!

DBA_Vishal

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

vkundar (4/14/2014)
Thanks Kevin.
Yes I agree I do see many transacations ( data loading runs for a long time with open_tran value euther 1 or 2)
I have asked the team to look into it.
But incase I need to plan the temp db size what should be value of the data files considering the perf counters output and MS formula.

I honestly have no idea how big you should make it. My guess is that your environment doesn't allow for some standard formula to do sizing on tempdb. Just get larger space, make tempdb as big as you get (or size it reasonably large and then have autogrowth bump it up as needed). Just make sure to have a large autogrowth (except for the tlog) and enable instant file initialization if your security plan allows it. tlog growth WILL be zero-initialized regardless so I rarely make that larger than 1-2GB at clients unless it is on a very fast IO system.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

vkundar (4/14/2014)
We have 8 data files for temp db of 10210 GB size and given 10240 GB as max size.

Are you sure? 10240GB is 10.2 Tera Bytes! You have a 10.2 Tera Byte TempDB???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)