Databases don't 'just go into restoring mode'. If the DB is RESTORING (not RECOVERING, that's different), then...

Fail over to your DR server.

You do have a DR server, given that the system is so critical that downtime will cause the loss of millions of rupees?

Someone's either having the worst day, or is asking interview questions.

Database goes into Restoring mode

Performance tuning it so critical can any one plz figure it out urgent

How...

Indexes don't have a MAXDOP.

What you can do, when rebuilding an index, is specify the maxdop for that rebuild statement, but it's not persistent, it doesn't get stored anywhere,...

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

http://www.amazon.com/Server-Query-Performance-Tuning-Experts/dp/1430242035/

If it's critically urgent, hire someone to help you.

You've either had the worse day possible or are asking interview question.

Is this the same DB you posted the Suspect database thread about? If so, I answered there.

To recover without data loss, restore your last full backup and all log backups since that backup. Since the DB is so critical, I assume you must have those backups.

Why does it matter how the data is stored in the full backup?

Full backup is just that, a complete copy of the entire database. Diff backup contains the changed extents...

You can. It will force every single query to recompile, increasing CPU usage and slowing everything down, but you can run it if you really want.

Or someone has taken a tail-log backup. (BACKUP LOG ...WITH NORECOVERY)

Check the log backup jobs, make sure someone hasn't mistakenly checked the 'backup tail of the log' option'

Pros: None

Cons: It's a known sysadmin account, so a hacker target

Because I first saw it the day I wrote that. Hence it couldn't possibly have scared me any time prior to that.

How big are the indexes in question? How many pages?

Have you tried looking in the default trace?

Stefan Krzywicki (9/17/2014)

---

SQLRNNR (9/17/2014)

---

Roy Ernest (9/17/2014)

---

I got someone asking if Cursor will work fast on memory based objects.

It felt like the OP would upgrade to SQL 2014 so that...