Link to home
Create AccountLog in
Avatar of Jahwilz
Jahwilz

asked on

SQL database attach not progressing

I'm trying to attach a an MS SQL database using the management studio 2008, however it has been executing for several hours now with no signs of progress.  Checking disk I/O, there was substantial activity at the start of the process but now there are no such signs.  Activity monitor shows a runnable process with the CREATE command but again CPU time/Disk I/O suggests nothing is happening.

I'm sure this is because there are issues with the database as the server was shutdown abruptly after a powercut.

So suggestions welcome of what is actually going on?  How long would you think an attach takes for a database of around ~5GB.  The other thing to mention is that checking the log file, it's at a massive ~350GB at present(don't ask me why, the server has not been setup to truncate correctly).  I understand SQL maybe checking the integrity of  log file and this could take a long time but the fact that I know there was an abrupt shutdown is making me question if the Attach job is actually running?

Any other information let me know...

Thanks
Avatar of jonnidip
jonnidip
Flag of Italy image

You may see the attach as "freezed", but you should get a notification of the progress in the windows eventlog.
Avatar of Jahwilz
Jahwilz

ASKER

The windows event log is not showing any progress for SQL.  Nor does the SQL server logs.  I cannot even see any mention of an 'attach' or 'restore' command?

There is enough disk space.
Avatar of Jahwilz

ASKER

However I just picked up on this error which somehow I missed earlier:

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.

This clearly applies in my case so, what next.  Can the database still be recovered with the MDF/NDF and the LDF that is present.  Although the log maybe corrupt as suggested.
you may try to attach the DB without log. it should be recreated by the system, by using "FOR ATTACH_REBUILD_LOG" .
you will, however, lose any uncommitted transaction.
I don't know if this will work as you expect. this method should work if the DB has been shut down gracefully, but it is not your case...
Avatar of Jahwilz

ASKER

Thank you for you suggestion.  I'v attempted this but receive the same message.  "The log cannot be rebuilt..." as you state because the server was not shutdown gracefully.

Now I have located backups but I'm having another issue with this.  When I select to restore a database and select my .bak file, management studio hangs for a few minutes then I get

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The backup or restore was aborted. (Microsoft SQL Server, Error: -2)

Any other suggestions on attaching the database or why the backups are giving me this error?
SOLUTION
Avatar of jonnidip
jonnidip
Flag of Italy image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Jahwilz

ASKER

As suggested I scripted the restore to a test database with simple command

RESTORE DATABASE MyDatabase FROM DISK='c:\backup.bak'

It was restoring or looking like it was restoring all fine and quickly the MDF and LDF was created(with the sizes I'd expect) but coming back to the server 5 hours later, it is still restoring.  However now, like before, again there is no CPU or disk I/O but the process is still saying executing.
Avatar of Anthony Perkins
Do yourself a favor and never use the GUI for these type of functions, at least if you value your data.  Enter the attach/restore commands from the query window.  If your Transaction Log is that big (350 GB) I would expect it to take an hour or two.  It may be attempting to rollback all the changes it had before it was shutdown.  What you really should be doing is "seeing" what SQL Server is doing using the DMVs, this will tell you the percent completed.  Alternatively, you can set a parameter in the RESTORE command to view the progress.

This would be a good time to contemplate the importance of database maintenance:

1.

If you are using the default FULL Recovery Model and are not prepared to backup the Transaction Log (as evidenced by their size) perhaps you should use Simple Recovery Model, instead.

2.

Backups are useless if you have not tested them.  In other words doing a backup may make you feel better, but the only way you can know that you have accomplished anything is if you test it by  doing a RESTORE.
Avatar of Jahwilz

ASKER

Thank you for your response.

I ran the restore command again but this time with "WITH RECOVERY".  I'm still seeing the same issue where 7 hours later the query is still executing.

From the DMV query I ran, it says the restore is 100% complete with 'time to go' = 0 yet the database is still in"(restoring...)" mode and offline.

I cannot see any relevant information in the SQL error log or windows event logs, is there somewhere else I should be looking?

I fully take on board your comments(and agree with you) but it's a server I've been asked to look at, for which I was not involved in it's commissioning.

Other suggestions and approaches?
Please post the syntax of the RESTORE command you used.  Did you use the STATS clause?  Have you tried the RESTORE VERIFYONLY  command to make sure the backup set is valid.
Avatar of Jahwilz

ASKER

acperkins, just an update.

I've managed to reattach the MFD and LDF and it's now back in "Recovering" mode.  This is how it was post server shutdown.  I've left it again and will leave it for 24+ hours to see the outcome.  I cannot see that any significant process is running but either way I will let it run.  From your experience, how long would a recovery process take for such a scenario when the log file is 350GB?  

As to your question.  On a test server, I've got a simple restore process running using:

RESTORE DATABASE MyDatabase FROM DISK='c:\backup.bak' WITH RECOVERY

I was going to add the STATS option but it kept giving me a syntax error, so I started it without it.  

I've run another query now as suggested and will let you know the outcome but I suspect this verify may not complete in a timely manner.
RESTORE VERIFYONLY FROM DISK = 'C:\backup.bak'

By all means please correct any commands I'm using or suggest alternative ones.

Thanks

p.s. The live server is SQL 2008 SP2(not R2), it's not had SP3 applied either.
From your experience, how long would a recovery process take for such a scenario when the log file is 350GB?  
It is impossible to know for sure and I have never had a Transaction Log grow that big.  However when I have had a database in that state I have usually inspected the SQL Server Error log, this should show the progress.
By all means please correct any commands I'm using or suggest alternative ones.
I believe the correct syntax is:
RESTORE DATABASE MyDatabase FROM DISK='c:\backup.bak' WITH RECOVERY, STATS
Avatar of Jahwilz

ASKER

After leaving the live server to continue the recovery process over the weekend, I returned to find it online again.  It looks like it completed it's recovery process taking approx. 34 hours(due to the large log I guess.  The strange thing checking the logs, I can now see reference to the recovery process where it was not present before.  It looks like it only put a record in the log at a certain point.  This is the first relevant message starting at 80%

Recovery of database 'Database' (1) is 80%% complete (approximately 17 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

Unfortunately, I had to restart the SQL service to enable remote connection(which was disabled during maintenance).  Doing so has put the database back in Recovery mode.  I can only assume it will take another 34 hours which I will wait out and then try to do a full backup and test.

I ran the RESTORE VERIFYONLY on a test server and it said the backup set was valid.  However the restore job that has been running on my test server for 34+ hours has not completed so not sure what's wrong with the backup.  I've run another instance of the backup with the STATS = 1 command and it's not displayed anything an hour later.

1.

Does this mean this database will now go into recovery mode everytime the SQL instance is restarted?

2.

Once the live DB is out of recovery mode, what can I expect?  Will the attached DB have data up to the point of failure?  Once I'm happy with the DB, can I safely truncate the log file, after doing a full backup?
As suggested before by #acperkins, you would like to set your db in simple recovery mode, rather than full.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Jahwilz

ASKER

To summarise the case for the next person that stumbles upon such issues:

If your database is already "in recovering mode", let it complete.  Depending on your transaction log size, this may take many many hours.  35ish in my instance.

If your database is not attaching, there is a 'hack' to reattach the MDF/LDF at SQL Skills.  If you manage to reattach the database, then once again let it go into recover mode and let it finish.

Once I was at this stage, I followed acperkins suggestions.  To make the maintenance for that point in time more manageable and minimise downtime any further, I changed the recovery model to simple and then used DBCC SHRINKFILE to reduce the transaction log.

I made multiple backups through this process to test I have a working set to go back to.  However it was only once I was in simple recovery mode and shrunk the log, that my full backup set was restoring correctly.

I also ran DB integrity checks again and no errors were found.  At this point took another backup of a full working database.

Now I'm in control of this server, I will be making a business decision if we want to go back to full recovery model or staying with the simple mode.

As suggested by acperkins, plan ahead and test your back up sets.

acperkins & jonnidip thank you for the information.