SQL Server Understanding transaction log space used

1
votes

I am an Novice SQL server user and by boss has asked me to record transaction log growth.I am testing transaction log behavior on a dummy database for my production server and do not understand certain things. The production server will have only bulk inserts on the database(around 250 - 400 bulk inserts a day). The methods tested are

  1. Separate bulk inserts for each file.Begin /Commit/Rollback transaction for each files.
  2. All the files under a single bulk insert.Single Begin /Commit/Rollback transaction for all files.(What i don't want to do but my boss wants it done this way :D)

The database is in bulk log mode as all the inserts are in bulk. The log file was initially set to 3 GB and in the first scenario the log file size remained the same only the Log space used increased.

DBCC SQLPERF(Logspace)

While in the second case when the bulk insert was performed the log file size increase to a whooping 27 GB but the space used remained minimal 0.5%. I am not understanding the sudden increase in the log size while the space used is still minimal. Does Begin /Commit/Rollback transaction have any effect on transaction logs? Data is being inserted only once in the day, So is keeping a production database in simple recovery mode really stupid?

1
sqlsql-servertransactions

1 Answers

1
votes

Does Begin /Commit/Rollback transaction have any effect on transaction logs?

All log data since the oldest active transaction must be kept active. Now why is only 0,5% of that space in the log used? SQL Server reserves log space for active transactions to support rollback operations. A rollback uses log space. SQL Server is conservative when it reserves space. The 27GB are probably its estimate for how much log is needed in the worst case to rollback all the bulk inserts you made.

You are probably getting minimally logged bulk inserts here. These cause minimal log writes. But the surprise comes later when you back up the log: All bulk-changed pages are copied into the backup so that the log will actually be able to roll-forward the writes you made.

Can you use the simple logging model? That might make these concerns go away. The simple log model does not cause data safety issues by itself. It just means that you can no longer do log backups (which might imply a higher risk for data not backed up yet in case the server explodes).

Whether you should use a single transactions or multiple smaller ones depends on:

  1. Do you need the atomicity guarantee? Note, that a rollback of huge bulk inserts might be extremely slow. I have never tested that. Be sure to test it.
  2. Test, which variant is faster. There are reasons for both to be possibly faster. Try it.