GilaMonster (9/9/2010)
No one mentioned defragging the drive. Log fragmentation is internal in the log file - lots and lots of very small VLFs within the transaction log. A very large number of these VLFs slow down database recovery.
Refer to the links that ALZDBA posted.
We're talking about two different types of fragmentation - this was posted by the OP, bottom of 1st page:
ioana-477197 (9/8/2010)
- The hard drive on which the database files were stored performed very badly lately (it is an flash USB drive) and was short on free space; our DBA decided to defragment it, but before that he detached the the database
- After hard drive fragmentation was over, he attached back the DB
Yes, log fragmentation probably caused the slow recovery of the other DBs, but it sounds like a disk error corrupted one DB, preventing its recovery at all. My point was this disk error was possibly caused by the disk defrag done on the flash drive with the DBs offline:
ioana-477197 (9/8/2010)
Problem partially solved. Here is what we did:
- determined which was the blocking recovering database
- stopped SQL Services
- renamed mdf and log files for the database above
- restarted SQL Server
- all current databases recovered successfully in a couple of minutes
- deleted the database with the issues and created a restore based on the latest backup (lucky, there were no data loses)