.ldf file grown to fill disk

[VicM]

I'm using SQL 2005. I have a DB whereby the tables are refreshed every evening. Over time the .ldf file has increased to 60GB. Currently, the disk's logical size is 72GB.

This has created multiple failures during updates and backups of the disk's contents.

I know that the logfile can be backed up with truncation, but there is only about 2GB of free space left on the disk and when I try doing the backup the size does not change.

Can I delete the logfile? If so, should I do a detach, delete and then attach? Will this cause the DB to recreate a logfile?

This database will become obsolete in a few months, but in the meantime it's critical to the operation of business.

Anyone have any suggestions?

Thanks,
Vic

 

[tran008]

Put the Recovery Model to simple instead of Full.

 

[mrdenny]

First never delete the log file.

If you need to be able to do point in time recovery then you need to start backing up the log using the BACKUP LOG command as well as full backups (I assume you are doing full backups).

If you don't need to do point in time recovery then you can change the database recovery from FULL to SIMPLE.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[VicM]

Folks,

Thanks for your input.

It appears that my commands to backup log with truncate were queued. Apparently when the 3rd party disk backup ended, those commands were executed. When I checked this morning, the log file had reduced from 60GB to 1MB!

Also, I changed the recovery model from full to simple. Since this DB is a subset of another 3rd party app, it's used to generate reports that the original canned app can't. So recovery is never an issue since the DB can always be recreated from the original 3rd party app.

Again, thanks for your input!!

Vic

 

[mrdenny]

You'll want to remove the DBCC SHRINKFILE commands from the jobs. All shrinking the file daily does is increase physical file fragmentation as well as increase VLF fragmentation.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog