February 19, 2010 at 7:10 am
Hi all,
Using SQL 2K sp4 on Win2K3.
I have a 2GB DB, with a 130 MB log file on our prod server.
Each night, a DTS database transfer task copies it to a backup server.
Data used on source is 1954.5 MB, log used 5.3 MB, total DB size 2163.6 MB.
At destination, the data used is 1698.6 MB, log used 37.9 MB, total DB size 14458.5 MB (that's not a typo: it's 14.5 GB)
Why is the log file expanding so much on the transfer? I shrunk it to 200 MB yesterday, this morning it's 12 GB. This server is not used otherwise.
Thanks,
Paul
February 19, 2010 at 8:40 am
Is the destination database set to full recovery model? If so, are you running frequent transaction log backups?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 19, 2010 at 8:44 am
Yes, destination is set to full recovery. A full backup is done immediately prior to transfers.
No tran. log backups required, since there is no other activity on the server.
It's just backup, transfer, wait 24 hours, repeat.
February 19, 2010 at 8:53 am
schleep (2/19/2010)
Yes, destination is set to full recovery. A full backup is done immediately prior to transfers.No tran. log backups required, since there is no other activity on the server.
It's just backup, transfer, wait 24 hours, repeat.
And there is your problem. When your database is in the full or bulk-logged recovery models, you have to perform regular transaction log backups. If you don't, then the transaction log will just continue to grow until it fills the drive.
Please review the article in my signature on Managing Transaction Logs by Gail.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 19, 2010 at 8:58 am
Respectfully, that is not borne out by my experience.
We do a single daily full backup of the prod DB, and it has *never* -- in 10 years -- grown as big as the backup server's log file.
The prod DB processes 6-10K transactions / day.
February 19, 2010 at 9:23 am
Furthermore, the same source DB is transferred to 2 other servers.
Of these, one is exhibiting the same behaviour as noted above.
The other, SQL 2K sp2 on Win2K, is not: it's log file size is 611 MB, 1/20th the size on the other two. And this server is used for reporting.
Same (full) recovery model, with single daily backups.
February 19, 2010 at 6:52 pm
Did you read the article I linked to? It explains this all in great detail.
Regardless of your experience, if your database is in full or bulk-logged recovery model you have to backup the transaction log. If you don't, the transaction log will just continually grow.
On those other systems, I would bet you anything that someone has built in a process that truncates the transaction log (e.g. BACKUP LOG ... WITH TRUNCATE_ONLY).
If you do not need to recover to a point in time, then you should change the recovery model to simple. Then, you don't have to back up the transaction log.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 22, 2010 at 7:27 am
Yes, I read it.
And you're correct, on the one system, there is a backup log w/ truncate only.
However, the log file kept growing on one of the "twins" over the weekend, while the other stayed at 12 GB.
Anyway, I've standardized the nightly maintenance to do the log backups.
Thanks for the help!
P
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply