Sql-server – “lock request time out period exceeded” Error When Trying to See DB Hierarchies

If you look in the ERRORLOG file you'll probably see that the database is in the process of rolling commands forward or backward. Once that process is done the database will come back online. All you can do at this point is wait.

DO NOT restart the SQL Server instance again. All you'll do is cause the SQL Server to start this process over again.

However long the query ran for, you can expect the crash recovery process (that's what is running at the moment) to take about that long to complete.

Using mirroring alone won't be sufficient since the mirrored secondary is not available for querying. You have to create and maintain snapshots, which can be annoying.

Your options are, in no particular order:

• Mirroring with snapshots : effective but has management overhead.
• Backup and restore : Are you restoring from full backups rather than differential or logs? If so, you may be able to reduce the time spent on restores.
• Log shipping : management overhead, database is unavailable when log backups are being applied
• Replication : challenging from a management perspective, not really intended for syncing full databases
• Report from transactional database - This option is all too often discarded over unsubstantiated performance concerns. Those concerns can also be mitigated somewhat through the use of Resource Governor, snapshot isolation, etc. This is an underutilized option, IMO.
• ETL with SSIS or a similar tool. You would have to do your own schema changes, however.
• Availability Groups, if you can upgrade to 2012 (I assume you're on 2008 R2). This is really the best option available these days.
• Idera makes a tool called Virtual Database that allows you to mount and query a backup file. It's an interesting option in some cases.

It's difficult to make a clear recommendation without knowing a lot more information about your environment. I've used most of these methods to varying degrees of success. Note that most places end up building out more robust data marts / data warehouses for reporting and analytics, so you'll probably end up with the ETL route one day.

Oh, and make sure you have licensed the secondary server ;)