Mini Heart attack moment..Transaction log full then mirror busy

--// During index maintenance

The statement has been terminated.
Msg 9002, Level 17, State 5, Line 2
The transaction log for database 'VeryBIGDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

log_reuse_wait_desc: database_mirroring

Quick what to do what to do.....

ALTER DATBASE [VeryBIGDB] SET PARTNER OFF

BACKUP LOG [VeryBIGDB] TO DISK='Wherever.hbc'
Lucky for me I have a job already configured and part of log shipping strategy, so I just ran the job.

SHRINK LOG Files

Deep breathes WUSAaaaaaa.

But now....

:-(

Msg 1404, Level 16, State 5, Line 1
The command failed because the database mirror is busy. Reissue the command later.

C'mon, give me a frickenfarackenchikenmonkey break!!!!

Google said, sorry just restart the mirror from scratch.

MAN-UP.

Gotta go, running dark now.

UPDATE:1:26AM
Miraculously the mirrored DB stopped complaining about being busy.

Shot of adrenealine when i saw that.... DING DING DING... ROUND 3, FIGHT


Dug through the Log Shipping backups for said DB, with cmdline nugget:
C:\>dir /b L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2*

C:\>dir /b L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110328_*

Add a dash of Excel, cut-n-paste, and a nifty formula:
="RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\" & A1 & "' WITH NORECOVERY,STATS=10;"
=
RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2000_LOG.hbc' WITH NORECOVERY,STATS=10;
RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2015_LOG.hbc' WITH NORECOVERY,STATS=10;
RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2030_LOG.hbc' WITH NORECOVERY,STATS=10;
...........
RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110328_0115_LOG.hbc' WITH NORECOVERY,STATS=10;

......
Ran it with error cause not all were needed, too early to apply to the database....

BUT then:
10 percent processed.
20 percent processed.
30 percent processed.
......
....
;-(( Sniff sniff, tears of joy...

Resync mirror looking very promising... knock on wood...

Comments :

1
Bob Mihada said...
on 

RESTORE LOG 97.05813