We have a problem.
A database was set to use 2 log files, and one of them somehow grew to a bloated 32GB, with a 400MB database! The recovery model was set to full, which will not be used again.
Now, whenever we start SQL Server a system process places an exclusive lock on the database is question and never lets go of it. It doesn't seem to be doing anything but it never lets go, so nothing can be done with the database.
We suspect the massive log file has something to do with the problem.
We want to somehow remove the log files, but can't do a damn thing because of the lock.
We have tried renaming the log files, then detaching the database and reattaching, but sp_attach_db and sp_attach_single_file_db won't recreate log files on databases that use more than one.
We have tried creating a "dummy", empty database with the same name and log files, then moving in the original MDF, but it error out saying we can't use log files from another database.
Does anyone have any suggestions?
No, we don't have backups of any of the log files, only the mdf. This is going to be corrected, but we need to get this thing going!
Originally posted by nigelrivett
What did MicroSoft say?
I guess that under the circumstances they suggested using
sp_attach_single_file_db. But use if anyone is planning on using this command have a look at BOL first.
BTW using full recovery mode is ok as long as you create a job that backs up the transaction log on a regular basis. One can easily set this up in enterprise manager -> sql server agent -> right click on datbase maintence plans -> new etc etc you can even tell it to write over trans backups older than a set period of time say 24 hours.
Making a really good maintaince plain withing sql server is not brain surgery it just takes a little effort.