March 20, 2014 at 1:15 pm
Every month we take dirty snapshots from our storage ( PURE Flash storage and our regular san) of our database instances. We then mount those luns to another server so we test scripts against our production databases (we have over 15000 databases to restore thats why we do it from the snapshot). I have always just attached the files and was able to create each database. I was doing this on Windows Server 2008R2 and sql server 2012. This month we are now working with Windows Server 2012 and Sql Server 2012 and I am running into errors.
File activation failure. The physical file name "N:\sqllog\KareoAudit_log.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'KareoAudit'. CREATE DATABASE is aborted.
I never had this issue before. Is anyone else having this issue or no what to do to fix it? Any help would be greatly appreciated. I am extremely frustrated.
March 20, 2014 at 4:31 pm
Rebuilding the log file requires that no open transactions exist in the database. Try deleting the log file and attaching the database without the log. It might not work, but it's worth a shot.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 20, 2014 at 11:27 pm
The other possibilities I can see is the missing of the logfile which might have created recently. It could be on local drive?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 21, 2014 at 4:18 am
Grant Fritchey (3/20/2014)
Try deleting the log file and attaching the database without the log. It might not work, but it's worth a shot.
It won't work. Deleting the log requires that SQL be able to rebuilt it, which requires no open transactions.
pamozer, the entire approach here is prone to failure. If you're happy with a process which will sometimes work and sometimes fail, then carry on using this. If you want something to work every time, you need to either completely quiesce the databases prior to taking the snapshot or look for some other approach (restoring backups, etc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2014 at 8:18 am
I have taken the DB offline and back online with some success in this situation, just a thought.
March 21, 2014 at 4:01 pm
I have been doing this for a couple of years now and have never had this problem. Backups and restores are a possibility but when you have over 15000 databases to restore its a bit limiting in terms of time. I am wondering why all of a sudden i am running into this issue when it never happened before. But I guess that doesn't necessarily matter if it won't work going forward.
March 21, 2014 at 4:07 pm
If the snapshot tool is capable of hooking into SQL and quiescing the IOs, the approach will work. It could that on windows 2008 it was doing that and now it's not. Nor familiar with the tool you mention. Maybe check with the vendor.
If the IOs aren't quiesced, then it depends which of the log or data is snapped first and whether there are any writes during the process as to whether the DB will attach or not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply