SQL Server 2008 database is not coming online after restore.. Pleae need help asap
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
go
reconfigure
go
exec sp_configure 'max degree of parallelism', 1
go
reconfigure
go


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