A way to estimates the size of the transaction log file

Question

Post reply

A way to estimates the size of the transaction log file

Carl B.

SSCertifiable

Points: 7931
More actions
February 14, 2005 at 7:22 am

#87596

Hello everyone,
Is there any articles (found nothing about that in BOL) that shows examples or explain on how to estimates the size of the transaction log file needed for DML operations.
Thank's to all of you,
Carl

Viewing 10 posts - 1 through 9 (of 9 total)

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

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 1

If I can remeber correct there was article in SQLServeCentral.com. search the site. good luck

My Blog: http://dineshasanka.spaces.live.com/

Carl B. SSCertifiable Points: 7931 More actions · Answer 2

Carl B.

SSCertifiable

Points: 7931

February 15, 2005 at 6:13 am

#542252

Thanks Dinesh,

Have a nice day.

Carl

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Carl, when you find something useful, can you post the link here?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Carl B. SSCertifiable Points: 7931 More actions · Answer 4

Hello Frank,

The only thing I found on SQL Server Central is this thread :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=133493

With answers from Steve and Andy but not something very "consistant".

If I find something else a let you know.

Regards,

Carl

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Thanks Carl.

Consider this just a thought:

What about estimating the row size (maybe average) * # of rows in that table (or that are affected) + index size * # of indexes * # of rows in that table (or that are affected) ?

I would factor in a security buffer of say 10% or more.

Anyway, just a thought.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Carl B. SSCertifiable Points: 7931 More actions · Answer 6

Its a good start Frank

But Steve had a good point stating: "At the least, it will have the old and new values and PK for each row."

I've read somwhere else (don't remember where) that the old and new value were both kept in the TLog.

So if we put this in your formula we got:

everage row size * # of rows in that table (or that are affected) * 2 + index size * # of indexes * # of rows in that table (or that are affected) * 2 ?

+ 10%...

Thanks Frank,

Carl

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Hm, yes. * 2 for old and new might turn out useful. I must confess, I don't know exactly what all is stored in the log. However, it's better to overestimate this than the other way. And once you completed a script, I would love to have a copy

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Carl B. SSCertifiable Points: 7931 More actions · Answer 8

Not sure I have time to script it but if I do, I'll sent a copy to you for sure.

At my sense it is something that should be documented by Microsoft.

Also with the coming new release of SQL Server 2005 (almost SQL Server 2006 ), the ones (like us) that will use row versioning - to have readers not blocked by writers (and vice versa) - will also have to care about the size of tempdb database. Hope this will be more documented.

Its fun to dream.

Regards,

Carl

sa24 SSCrazy Eights Points: 8027 More actions · Answer 9

I think you might want to have more than 10% additional space.If you run dbcc log or also ::fn_dblog, it shows almost 80 columns which includes objectid, pageid, element length, row length, current/previous LSN and many more.