How MS SQL Server Transaction Logs Work?

[sheong]

Hi,

I'm new SQL database administrator. May i know how MS SQL Server Transaction Log works ? Is it every time when the COMMIT statement issues, SQL Server will write the data in the database file ?
How come after i did the full backup, my LDF file size not reduce? I thought SQL Server will auto truncate the log and recude the size. Anyway, I have manually issues sql statement to shrink it. Any ideas ? If i shrink the transaction log file, anything will happen to my database ?
Thankx alot....

 

[nigelrivett]

The tr log will not shrink due to a backup. The log will not release entries due to a full backup.
To release space for re-ues you have to backup the log or truncate it.
To shrink the file you will have to perform a dbcc shrinkfile (or use enterprise manager).

The log holds entries to record updates to the database. These are used to roll back transactions and to control the recovery of databases. If the database is in simple recovery mode then the entries are available for reuse after the transaction is committed and a checkpoint is performed. If the database is in bulk logged or full recovery mode then the log must be backed up to release the entries. If the tr log is not part of your recovery strategy then set it to simple. The tr log is a circular file and entries will be re-used up to the earliest free entry.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[sheong]

Thankx nigelrivett,

How do i check wether my SQL Server is under Simple Recovery mode, bulk logged or fully recovery mode ?
If i have the full backup copy, do i need to keep a copy of transaction log ?

 

[ecobb]

If you're using Enterprise Manager, right click on the database and select "properties" and then select "options". This will show what recovery model you're using and allow you to change it.

Hope This Helps!

Ecobb

"Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer." - Homer Simpson

 

[sheong]

Hi Ecobb,

What is the different for the recovery model ? Simple, Full and Bulk-Logged?

Any impact if i selected Simple Model ?
Thankx

 

[nigelrivett]

If you are not including tr logs as part of your backup/recovery model then set it to simple (I have requested that this should be the default because of just this problem).

See Seelecting a Recovery Model in BOL.

If you don't know then you should set it to simple.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[sheong]

Hi all,
Thank you very much!

I got the information from BOL.