Trans log backup issue

[DrewConn]

I backup several user DB nightly, all contained in a maintenance plan. The backups are working fine however I have two problems.

One, the size of the transaction log is not shrinking. In fact, even when I manually run my SP to truncate the log, the next day the log file will be exactly the same size. Almost as if it were restoring the same trans log every night.

The second issue is that I have the maintenance plan set to only kepp DB data files and translog files for 5 days. However this also is not working. It is jsut appending files daily. I have to manually delete f\the files from the backup folder.

Any ideas?

 

[sqlsupport]

did you check your database model? is it simple or full?

In simple model, it won't take any transaction log backup rather the trans-log file size would remain constant.

With full model, if you take transaction log backup then it will shrink down.

 

[DrewConn]

Yes, all of the DB models being backed up are full.

 

[SQLSister]

The log will not shrink beyond it's original specified size. Could that be the size you see everyday?

 

[DrewConn]

Not the original size specified. For example, one DB keeps coming back with a log file size of 5.2 GB. Well beyond original size.

 

[SQLSister]

what is the command you are using to truncate the log?

 

[DrewConn]

I am only truncating the log manually via a fairly involved sp I have and only doing that because the log is not truncating in the backup.

 

[eja2000]

is it possible to increase the transaction log file space on a live database?
what is the impact and how do i do it in the enterprise manager?

 

[nigelrivett]

See

http://www.nigelrivett.net/TransactionLogFileGrows_1.html

I'm guessing that either you have an open transaction which is preventing the log entries from being freed or that you have some process running which causes the log to grow t the large size again - if that is so then you shouldn't shrink it as that will just slow things down when it has to grow again.

I don't use maint plans as you can't be sure what they are doing - better code it yourself.

======================================
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.

 

[SQLBill]

eja2000,

Please create your own threads, your request really has nothing to do with this thread AND this thread is from 2003.

That said, yes it is possible. In Enterprise Manager drill down to the Database, right click and select Properties, go to the Transaction Log tab.

I believe if you right click on the Space Allocated column you can change that size. Or you can go to the bottom of that window, check allow autogrow and put in a size to allow the growth.

-SQLBill

Posting tips: FAQ481-4875 and FAQ222-2244