Recently I was involved in testing the performance of SQL Server 2005 in the event of an external attack. I used a machine with Pentium Dual Core 2.0 GHz, 3 GB RAM and sufficient amount of hard disk space.
I used a script to create 2 simple Products and Orders Tables and tried to insert 100,000,000 (A hundred million) rows into it. I used windows system monitor to monitor a few things. The important ones are
- Free space in tempdb
- Data File Space of my database
- Log file space of my database
- Lock request per second and
- Page writes per second
The whole insertion part was in a single transaction.The script kept running for around 25 min. and then the Management studio gave me an error of "Insufficient Memory Resource". The transaction was automatically cancelled. By that time around 4,700,000 rows were transferred in the database.
The data file size at that time was around 59 MB, but the Transaction Log size was grown upto a whopping 5.87 GB. Well all this was still very OK for me, but then I started taking the backups of the database.
I started with a full database backup. I thought, according to my knowledge, that it won't have any effect on the Log file size. but the full database backup brought down the "Used Log File Space" down to 1/10th of its size.
After that I tried to take the Log backup and it had no effect on the size of the database. can u guess why? Because the full backup already committed the inactive portion of my transaction log and removed them. And there were no new transactions to be transferred. So the used space in log file remain unchanged. Then I used the DBCC SHRINKDATABASE command using WITH TRUNCATE option. Before executing this command the log file size was still 5.87 GB. What do you think, was the size of this database after execution of this command?
If you said 5.87 GB you are......... wrong. It's not 5.87 GB, not 4 GB, not 1 GB......, not even 200 MB. It was just 2.31 MB. Any guesses about how it turned out to be so small? Because the data insert operation was performed in a transaction and the transaction was rolled back when OS returned me the error of Insufficient resources. That error rolled back the transaction. But even then, the transaction was available in the transaction log. When we truncated the transaction log, the whole Rolled back transaction got removed from the transaction log. Another thing that even I was not able to find a reason for was, as soon as I used DBCC SHRINKDATABASE, for some strange reason, the "Used Transaction Log Space" shoot up for a few seconds and then started coming down. If anybody has the reason for that, he/she can write a comment.
Hope that may give you some insight about the backups and Transaction Logs.
