Help on SQL Server Transaction Log 1

[Sridharan]

I have a few clarifications on Sql Server Administration. Can you pls help me..

1. How to shrink a transaction log file? (The transaction log is around 1.8GB and the database is only 400MB. Related dbcc commands did not shrink the transaction log file). I am not using checkpoint to truncate log file. Instead, I am using backup transaction log, which automatically truncates the log file.
2. I have the following backup strategy:
# Complete database backup daily - 9pm
# Differential database backups - every 2 hrs from 8am to 8pm
# Transaction log backups -
(Whenever log growth rises over 20%)

I have created a job for the transaction log backup which has the following code:

backup log <dbname> to <backup device>

I find that the transaction log backup size keeps growing and is almost 3 times more than the database size. Which is the best way to take the backup of the transaction log? Is the above syntax correct? Can I use &quot;WITH INIT&quot; in the backup log command?

Or can some one suggest a good alternative way.

Any help will be highly appreciated.

Sri

 

[SQLBill]

WITH INIT only causes the backup job to overwrite the backup file.

Questions:

1. What kind of transactions do you have on a daily basis?

2. Do you have a lot of transactions daily?

3. How is your transaction log set to grow?

The issue with shrinking the transaction log is that it has to wait until the log isn't active.

Imagine this represents your log:

IIIIIIIIIIIIIIIIIAAAAAAAAAA

The I's are the inactive portion and the A's are the active portion. You do your backups. The log CANNOT shrink until the Active portion is moved to the front. Look at it this way, you can only remove from the tailend. When a backup happens, the active portion does move to the front, but it's not immediate. If you have a lot of transactions happening it can take a while.

I find the best way to truncate the LOG is:

1. backup the log.
2. run:
BACKUP LOG mydatabase WITH TRUNCATE_ONLY
3. run a full backup

-SQLBill

 

[Sridharan]

SQLBill,

Thanks for your answers. Infact I'm asking this for another person on my behalf. I have forwarded your questions to him. Once he replies I will put down here. Hope you will be able to help him out again.

Thanks Again
Sri