I have restored a 1TB database and now it shows the restore completed 100% but the database is not accessible.

sp_who2 has a SPID 50 performing this restore but current command its showing is 'CREATE INDEX' and this SPID is not completing from quite long time.

When I verified through sp_whoisactive this spid is trying to create index on a table of 12gb size but the % complete shows as 100%...

Now for 2 hours the DB has not been accessible and this spid is not finishing. Main thing to note is this restore happens daily and used to work fine from ages.

Any help ides would be much appreciated. I already tried taking SQL Server services offline and removed the underlying db files and start fresh restore but still it got stuck in same way....

  • A RESTORE command does not create indexes. Probably, your restore script does that. Find out why. – usr May 12 '14 at 12:16
  • hey, actually when i run sp_who2 'active'.... the stuck SPID of restore command is running the command 'CREATE INDEX'.... and we have this restore job set up and running from quite a while.... – Vinay May 12 '14 at 12:43
  • How do you know that the restore command is creating an index? I'd say that a CREATE INDEX command is creating an index. – usr May 12 '14 at 13:29
  • i agree 100% with you... but that is wat i see when i run sp_who2 – Vinay May 13 '14 at 4:38
  • And I ask you: How did you make sure that the restore script is actually in RESTORE right now and not in a CREATE INDEX that might also be in the script? I do not doubt that an index is being created. I doubt that the script does what you think it does. – usr May 13 '14 at 8:59

I know this is an old question but will leave this here for anyone else who comes looking. Ran into this problem today and was able to get it going.

This is difficult to believe but disabling parallelism got it online in my case.

I never would have thought to even try that, only I was watching the sysprocesses for that DB and was seeing the parallelism split / cxpacket wait behavior… the wait types would change like it was working on something. Thinking maybe maxdop=1 would help whatever this was by not splitting up, changed it and the DB immediately came online.

exec sp_configure 'show advanced options', 1
exec sp_configure 'max degree of parallelism', 1

Other info:

  • Mine was also SQL 2008 database is 176GB

  • Also saw (at some point) a system spid (under 51) showing "Create Index" Runs change data capture in production (not sure that had anything to do with it)

  • Was restoring copy to a dev environment as part of a regular data refresh from production.

  • Disk space was very tight on the destination server it had just enough room for the data and log files to restore, but was under 10 meg free at that point. I think the lack of space may have helped cause the problem? I was able to free up some space before the maxdop change, but the database was still not usable until the maxdop setting was changed. sys.databases showed the state as 'online' the database looked usable in every way but any attempt to use it would time out

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy