Log file is huge, help

[katbear]

Hi,

version: sql server 2000

We have a database that is FULL recovery mode. That last full backup was yesterday.

A check of taskpad shows that the last transaction log backup was in 2005. How is this possible? I thought the log file is backup up WITH the full backup each day. I guess I'm wrong. It's been too long since I've looked at a backup script.

Help. I forgot how to do this............ I think it's backup log with shrinkfile or something like that.

 

[ptheriault]

No log backups are not part of the full database backup. you must backup the transaction log separately. The point of have transaction log backups is so you can backup the tlog throughout the day and recovery to a point in time. you must create a tlog backup, then you can shrink your log down to normal operational size for your database. To srink you log use DBCC SHRINKFILE.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

duh, i found it

dbcc shrinkfile (logname, truncateonly)

i guess i need to review this again!

it's amazing how the data GOES when you don't use it

 

[katbear]

crud, that didn't work!!

how to shrink this puppy down?

 

[katbear]

right, i need to take a backup first?

 

[katbear]

it's coming back, slowly

 

[ptheriault]

Correct. also, keep in mind that truncateonly will will not remove open transactions. so if you have a long running transaction that is huge. it will leave that in the log till the transaction completes.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Right, but clearly the problem here is that the last log backup was in 2005!!

NO, I am not the one who set up these backup jobs! Honestly, I used to know how to do this

 

[SQLBill]

You have two ways to do the log backups.

1. via maintenace plans - they give the option to backup the logs.
2. via script using the BACKUP LOG command.

Log backups should be run on a regular basis during each day (every hour, half-hour, whatever based on business needs).

If you cannot do the log backup the first time, one option is to run BACKUP LOG dbname WITH TRUNCATE_ONLY which will clear out empty space. Then shrink the log and do a full backup.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875