Sorry Guys and Gals but I couldn't stay quiet over this.

1.If you move Data files when in 'off line' mode it should have been communicated to your stakeholders that you were doing it and "Code red" irrelevant. “Some one deleted/misplaced a data/log file when SQL Server was offline and the database can not start because of missing file” just makes me angry where is your change control and why didn’t you backup the Database before taking it off line.

2.“SQL Server could not access or place an exclusive lock on the data or log file while coming online. Typically I experience this when SQL Server is shared with some other tool” WTF. What other tool should be accessing your Datafiles apart from SQL Server and related services. And you have Anti-virus checking your data files, heaven help us all !!

3.Database is in suspect because of a corrupted transaction. “The root cause of this issue is most likely SQL server abruptly went down/restarted in the middle of a transaction and while coming back” Now unless the undo and redo phase of the SQL server recovery fails you have bigger problems than a dodgy transaction and I hope you have a better backup strategy..

And the comment regarding the use of DBCC commands “I recommend trying all other possible options including calling Microsoft Support before executing below steps.” Read books online. Have you tried ringing microsoft support ??

4.I happen to agree on the probable cause, in theory, in practice anything this important to cause a “Code Red” would be on a highly available server or other enrolled in one of several scenarios the prevent this from occurring.

And as foot note: Following, or at least recognizing, best practices and change control this type of situation should be a rare occurance. I have been doing this for a Long, Long time and suspect databases are RARE !