Transaction Log and TempDB Calculations

Question

Transaction Log and TempDB Calculations

alex.sqldba

SSChampion

Points: 10254
More actions
March 13, 2014 at 9:38 am

#305082

Evening All,
Is there a formula for calculating how expensive a transaction will be in terms of disk space used before its run. I dont want it accurate to the MB, but rough enough so I can determine how much additional space to assign to a transaction log or SAN volume.
Currently we're reindexing ~25billion rows, nothing too wide, say 12 columns consisting of 1 varchar(50) and the rest ints, bits and money.
Rouhgly speaking if I reindex the clustered index on an int indetity, (with sort_in_tempdb) how would I calculate the the disk space used? Is tehre a way to do this?
Any help or thoughts is appreciated
Al

Viewing 3 posts - 1 through 2 (of 2 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

It's all depends on DB size and application. Also you can have benchmark if your current application is running on other server.

However as rule of thumb log size is 25% of database.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

alex.sqldba (3/13/2014)
Evening All,
Is there a formula for calculating how expensive a transaction will be in terms of disk space used before its run. I dont want it accurate to the MB, but rough enough so I can determine how much additional space to assign to a transaction log or SAN volume.
Currently we're reindexing ~25billion rows, nothing too wide, say 12 columns consisting of 1 varchar(50) and the rest ints, bits and money.
Rouhgly speaking if I reindex the clustered index on an int indetity, (with sort_in_tempdb) how would I calculate the the disk space used? Is tehre a way to do this?
Any help or thoughts is appreciated
Al

Rebuilding a clustered index can be done in a minimally logged fashion if done in the BULK LOGGED or SIMPLE recovery models. If you also do a CREATE WITH DROP EXISTING, it (according to BOL. I've not actually ever tested it) won't cost you much in the MDF file, either. The key will be, does this need to be done online or can it be done offline?

As a bit of a sidebar, if the clustered index is already based on an INT IDENTITY, you shouldn't have to rebuild the clustered index unless you screwed up somewhere and allow IDENTITY INSERT to be turned on.

--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)