Matt Crowley (11/6/2013)

---

GilaMonster (11/6/2013)

---

Matt Crowley (11/6/2013)

---

just keep in mind that a rebuild of the clustered index will involve rebuilds of the non-clustered indexes as well, so you may want to...

Restoring or recovering?

A DB will go restoring if someone runs BACKUP LOG ... WITH NORECOVERY or runs a restore over the database and specifies NORECOVERY. In either case running RESTORE...

Matt Crowley (11/6/2013)

---

just keep in mind that a rebuild of the clustered index will involve rebuilds of the non-clustered indexes as well, so you may want to wait for the...

Heaps do appear in sys.dm_db_index_physical_stats, but since you can't really rebuild them there's not much point.

Good point. Should have said CREATE ... WITH DROP_EXISTING

Next time don't restart SQL while there's a large transaction rolling back.

Please post table definitions and execution plan. Also consider getting rid of those nolocks, they're not go-faster options they can result in incorrect results.

Hope no one needs to use that database for the next several hours...

As I said, if you restart, the rollback will continue with the database unavailable. It'll be unavailable until...

No, it won't because there's no free space in the...

I'll second the 'rebuild indexes onto another filegroup' option. If you just rebuild them in the filegroup they're currently in, you'll probably end up with a lot of empty space...

Blocked simply means waiting for a lock that some other process has. Not a deadlock, not fatal. It's not something you can tell from just an update statement, identifying that...

Wait for it to finish or stop it and wait for it to rollback. That's pretty much it.

Or drop the database and restore from backups.

Have you considered enabling 'optimise for adhoc workloads'?

Low PLE != plan cache memory pressure. The balance between the plan cache and the data cache is complex, but they are two separate caches each with their own aging...

So yes.

See whether you have memory pressure, see whether you have cache memory pressure before you spend lots...