Would appreciate any ideas on this one please.
This is a DEV database. I'm the only one working on it, but I had several connections open (from QA and probably some, not-yet-died, from Web APP connections).
I screwed up, so I took a full backup (just in case I needed it) and then set about restoring to point-in-time. I started with FULL Backup (from Sunday) and then last night's DIFF then TLogs since.
First off I did:
ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
it hung. I don't think that has ever happened to me before [when using Rollback Immediate]. I left it running and tried to change each QA windows to connect to MASTER instead. No difference so I did a ROLLBACK in each window/session "just in case". None of them found anything to roll back.
I cancelled the ALTER DATABASE (which took a minute or two). I tried it again, same thing. Not entirely sure what I did next, but I think I may have done a SET SINGLE_USER WITH ROLLBACK IMMEDIATE instead or the OFFLINE command. Anyway, the next attempt with SET OFFLINE WITH ROLLBACK IMMEDIATE worked (and responded immediately).
I ran the restore from Full Backup. That went through 10%, 20% ... to 100% in reasonable time for the DB size (a couple of minutes) and then sat there at 100%
after a bit I ran an sp_WhoIsActive a few times, I got this:
Content_switches = 46,254 (not changing)
physical_io = 21,685 (not changing)
pecent_complete = 100.0000 (not changing)
Login time = 2016-02-10 14:31:24.057 (not changing)
Collection_time = 2016-02-10 14:56:38.680
I ran it again after a bit:
(the other figures had not changed)
then the Restore progressed to the "Processed 358352 pages ..." display ... and, again, just sat there, sp_WhoIsActive displayed:
Content_switches = 48,331
physical_io = 23,979
Collection_time = 2016-02-10 15:02:52.970
I forgot to record the elapsed time of the restore. The FULL backup file was 492MB
I then started the DIFF Restore. That is, now, sitting there stuck on 100% - its been running 12 minutes. XXX dsplays:
Content_switches = 1,322
physical_io = 1,072
pecent_complete = 100.0000
Login time = 2016-02-10 14:31:24.057
Collection_time = 2016-02-10 15:17:11.297
The DIFF restore finished with duration 16m55s. Restore of the TLog backups was not particularly speedy, although not disastrously slow like the FULL and DIFF. Most of the TLog backups would be "empty" as no DEV activity during the night, for example! (Pretty much all the TLog backup files were 391KB, they are made every 15 minutes, I restored 78 of them and it took 9 minutes ...)
RESTORE DATABASE MyDatabase WITH RECOVERY
only took a couple of seconds
I didn't check, but I think it is unlikely that there has been any database file extension since the Sunday backup (its a DEV database, rarely any massive query processing etc.)
I don't think that the Backup files have any/many transactions included (the filesizes are too small for there to be anything significant)
When I checked Server CPU during FULL backup restore it was showing 1%-8% over the duration of the displayed history. No spikes above 8%
Memory was flat at 27.8GB (it said 3.1GB available, Task Manager suggested that SQL was using 25GB)
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )