Data modification while backing up the transaction log

[sqlturbo]

Up until now, I did a full backup of the databases on a nightly basis. Didn't do transaction log backups and as result my log file has grown big and now I'd like to truncate it regularly (I was under the impression that truncation occurs with a full database backup!).
With business growing and more transactions taking place, I'd like to backup the transaction log atleast once during the day. My questions is. While the backup log statement is running, would new transactions be logged? Also, would users be able to insert/delete/updata data while the back up log is running?

Thanks in advance for you inputs.
L'Tayn

 

[SQLBill]

Yes. Transactions still happen while the log is being backed up. I have bulk inserts happening every 2 minutes and they don't interfere with ANY of my backups, nor do the backups interfere with the inserts.

The simplest way to explain it (as I understand it to be), is that SQL Server makes note of the state of the transaction log and knows where the end of is as the backup starts. It backs up to that point while any additional transactions are being appended after that point.

A more technical explanation is in the BOL and deals with the state of the log and the active and inactive portions.

-SQLBill

 

[sqlturbo]

Thanks SQLBill.

After I backed the transaction log, I wanted to shrink the log file. It was 24 G before the backup and is still 24 G after the backup. After the backup, I checked the %space used by the logfile and it said 0.77%.

I ran DBCC SHRINKFILE (logfilename, TRUNCATEONLY. It came back with...
Current size : 2898872
Minimum Size : 1280
Used pages : 2898872
Estimated pages : 1280

I ran the DBCC SHRIFILE command again but it came back with the same answer. If it occupies, only 0.77% space, I assume it could let go many virtual log files. Why does it not reduce the file size to 1280 pages?

Am I all wet here?

 

[SQLBill]

Database (log) shrinkage doesn't happen immediately. It has to do with the active portion of the log space. I suggest looking at the FAQs for this forum and the FAQs for the MS SQL Server Programming forum. There's a great FAQ on Why don't my logs shrink?

-SQLBill

 

[SQLBill]

Check out this FAQ (it's in the Programming forum).

FAQ183-345

-SQLBill