November 16, 2010 at 4:09 am
Paul
First off, Thank you for all your help and time you spent on this.
I now have a greater understanding of how this works and what type of logging is
going to happen. This is critical in are environment as many of our project and singles
table surpass the multi terabyte range and are data center is now over a petabyte of storage.
That being said it becomes very easy to make bad choice and crush a server by running out of space.
The one question is how did you calculate the byte used I would like see
that script and the method behind it
Test A (empty clustered table)
Minimal Logging: 35 log records; 23,800 bytes used; 43,876 bytes reserved
Full Logging: 464 log records; 3,220,716 bytes used; 52,158 bytes reserved
Test B (non-empty clustered table)
Minimal Logging: 74 log records; 321,064 bytes used; 11,521,977 bytes reserved
Full Logging: 3069 log records; 3,689,584 bytes used; 970,684 bytes reserved
The only other question I have is what books or resources would recommend in help solve issue like this one.
Thanks and Cheers
Scott
November 16, 2010 at 5:19 am
shump66 (11/16/2010)
First off, Thank you for all your help and time you spent on this.
No worries. It was an interesting and fun question to answer.
The one question is how did you calculate the byte used I would like see that script and the method behind it
The basic query is:
SELECT T.database_transaction_log_record_count,
T.database_transaction_log_bytes_used,
T.database_transaction_log_bytes_reserved
FROM sys.dm_tran_database_transactions T
WHERE T.database_id = DB_ID();
You have to run that in the same transaction as the INSERT statement that loads the data.
The only other change was to run the tests on a database using the full recovery model, for comparison.
I don't have the script in a format suitable for uploading yet. If I do that work, I'll upload it here, or perhaps I will blog about it.
The only other question I have is what books or resources would recommend in help solve issue like this one.
I haven't found a good book. Sunil's various blog posts on the subject are the best online resource I've seen.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 17, 2010 at 4:53 am
Hi Paul
You have to run that in the same transaction as the INSERT statement that loads the data.
The only other change was to run the tests on a database using the full recovery model, for comparison.
I don't have the script in a format suitable for uploading yet. If I do that work, I'll upload it here, or perhaps I will blog about it.
Just to clarify you are say use Begin Tran run the insert then measure then commit Tran
Scott
November 17, 2010 at 7:11 am
shump66 (11/17/2010)
Just to clarify you are say use Begin Tran run the insert then measure then commit Tran
Yep.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply