SQL Server Transaction log problem

[Jeppedal]

Hi,

Sorry for asking such a basic question, but I have ever been in this situation before, and I'm a bit scared of doing anything wrong.

I just discovered that the logfile for one of the databases at a customer site was totaly out of control, and had grown to about 17GB. The size of the database is about 1GB.

They are allmost out of diskspace, and because of that, I cannot take a backup of the transaction log.

I believe the reason for this situation is because the database is in full recovery mode, and they have never done any backup of either the database or the transaction log.

I have enough space to back up the database itself, and I just did.

What would be the safest routine to get out of this situation, and shrink the log file?

I was thinking of:
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE ('databasename',TRUNCATEONLY)
And run a new database backup.

Would this do it? What would be the risk, if any?

If I make a new maintenance plan for the database including a daily backup of the database and an hourly backup of the transaction log, would the size of the logfile still grow?

On what other databases do I need a maintenance plan to be able to make a full disaster recovery?

Best regards
Jeppedal

 

[mrdenny]

BACKUP LOG databasename WITH TRUNCATE_ONLY
go
use databasename
go
DBCC SHRINKFILE ('log_file_name',TRUNCATEONLY)
go
backup database databasename to disk='path_to_backup'
go

Will clear the log, and shrink the log file.

Yes setting up backups of the transaction log will clear out the log every time the log is backed up. You will want to save these backups to another server. If you are only backing up the log to the local disk, and not moving them somewhere else, then there is no point in backing up the logs, and you should set the recovery mode to simple.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[nigelrivett]

see

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

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

Jeppedal,

Try to use Denny's suggestion (Nigel's link also has the same suggestion). If that doesn't work for whatever reason, the final thing to try is the last suggestion in Nigel's link. But where he says to delete the log file, I suggest renaming it. That was if something else goes wrong, you can always rename it back to what it should be and reattach it. If you use Nigel's method of detaching, then once everything is working you can delete the old/renamed log file.

-SQLBill

 

[Jeppedal]

Hi,

Thanks for all help. It seems like everything worked out just fine by trunkating and resizing the logfile.

Best regards
Jeppedal