MS SQL Full Disk, transaction log does not shrink
Posted on 2009-04-28
I've a very strange problem.
The situation on monday morning:
Database-server with database-file of +/- 48 GB. Transaction log is +/- 38 GB. Recovery mode is full, nightly backup.
One of the tables includes 7 million records.
I wanted to delete 5 million records to clean up some disk space and make the database faster again.
After two hours of running the delete-query, the server returned the error "transactionlog is full" because disk is full. De transactionlog was grown to more than 45 GB.
The database turned itself into "in recovery" modus. After some minutes, the database was online again, but disk is still full.
I've done a shrink of the database but nothing happens! (transactionlog still too large).
After that, I've done a backup because I've read the transaction log will become smaller after a backup.
No result, still big transaction log and database.
I've shrinked another database on the server, which gave me 5GB workspace on the disk again. Now I've deleted some 100.000 records per 5.000 records, with a shrink between every +/- 100.000 records. Result: database is some 100MB's smaller, but transaction log keeps big (or even larger).
After some 100.000 records, the transaction log explodes again and disk is full again.
- Why doesn't shrink the transaction log after a "shrink" or "backup"?
- How do I delete my 5 million of records without any problem? (to make disk space again free)
- Is it a good idea to convert the recovery model from "full" to "simple"? Will the transactionlog of 43GB be deleted, or be a lot smaller?
- There are a lot of indexes on the table. Is het required to rebuild indexes before transaction log will become smaller?
Any advice will be appriciated!
Thanks a lot!