Cannot attach database
-
Hi, I'm new to SQL 2005 and have a bit of a problem.
A database filled its' transaction log, to the extent that here was no space left on the drive.
Attempts were made to truncate the log, but somehow this resulted in the db disappearing fro the management studio.
I have tried to reattach the db but this results in an error " the transaction log is full" and the suggestion to look in the log_reuse_wait_desc column in sys.databases. But, there is no entry in the sys.databases for this particular db.
Oh, to really put a shine on it, there are no backups available.
I have tried to attach a single file, but this also fails.
I've found a suggestion to;
create a database of equal size to the one you're trying to attach
shutdown the server
swap in the old mdf file
bring up the server and let the database attempt to be recovered and then go into suspect mode
put the database into emergency mode with ALTER DATABASE
run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair
I'm fine with all of the above apart from swap in the old mdf file, what does this mean and how do I do it?
Any help gratefully received
-
I have cracked it!
Solution was;
create a new database [db name_2] (with new data and log files)
shutdown sql server
swap the mdf file (db name_2.mdf) for (db name.mdf)
bring up the server and let the database attempt to be recovered and then go into suspect mode
ALTER DATABASE db name_2 SET EMERGENCY;
ALTER DATABASE db name_2 SET single_User;
DBCC CHECKDB (db name_2, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE db name_2 SET ONLINE;
sp_renamedb @dbname = 'db name_2' , @newname ='db name'
ALTER DATABASE db nameSET multi_user;
-
For next time it happens (or for anyone else reading)
Detach the database. Move the transaction log to a drive that does have some space. Reattach the database. You can't backup the log when the log's that full. SQL needs a little bit of space in the log file to do the backup, prior to truncating inactive log entries. Same with a backup with truncate_only, same with a switch to simple recovery.
Just be aware that by discarding the transaction log and doing a repair with data loss, you now potentially have a database with logical or transactional inconsistencies and you have potentially lost data. There may be foreign key relationships that are no longer valid, etc, etc. Check the DB carefully.
Oh, and set up a backup routine......;)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It is great that you solve the problem! There is something I don't really understand yet. I thought you said that you did not have any more room on the disk for the log file. How did you manage to create the new database?
-
Great tip Gila!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply