Recover Log Shipping Secondary DB from status Suspect

Sometimes your Log Shipping Secondary database will be go into "Suspect" mode, due to various reasons including log shipping Restore Job failed, log backup file corrupted, and transaction log backup files grown too large due to maintenance task on primary database such as index rebuild. You can recover the secondary database by restoring a recently Full/Differential database Backup WITH NORECOVERY option. Below are the steps:

1. Make a Full/Differential database Backup from the Primary database;
2. Copy this database backup to the Secondary server;
3. Restore the Secondary database by this backup WITH NORECOVERY;
4. Start the Log Shipping Restore Job on Secondary server;
5. Check the Job History of the Log Shipping Restore Job, you can see it skips all the log backup files with LSN before the database backup you just restored;
   
6. If the Log Shipping Copy Job is still running, eventually the Restore Job will find the log backup files with LSN after the database backup;
   
7. At last the secondary database catch up and resumes.

Checking Log Shipping Performance

Below SQL script can be used to check the performance of Log Shipping, including the log backup size and time of delay between the primary database and secondary database.

DECLARE @dbname sysname, @days int
SET @dbname = 'SalonWebDB'
SET @days = -14 --previous number of days, script will default to 30
SELECT
 rsh.destination_database_name AS [Database],
 CASE WHEN rsh.restore_type = 'D' THEN 'Database'
  WHEN rsh.restore_type = 'F' THEN 'File'
  WHEN rsh.restore_type = 'G' THEN 'Filegroup'
  WHEN rsh.restore_type = 'I' THEN 'Differential'
  WHEN rsh.restore_type = 'L' THEN 'Log'
  WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
  WHEN rsh.restore_type = 'R' THEN 'Revert'
  ELSE rsh.restore_type
 END AS [Restore Type],
 rsh.restore_date AS [Restore Date],
 bmf.physical_device_name AS [Restored From],
 bs.backup_start_date,
 bs.backup_finish_date,
 CAST(bs.backup_size / 1024.0 / 1024.0 AS decimal(19, 2)) AS [Backup Size MB],
 DATEDIFF(second, bs.backup_finish_date, rsh.restore_date) AS [Delay in sec.]
FROM msdb.dbo.restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
AND rsh.restore_type = 'L'    -- Log
ORDER BY rsh.restore_history_id

Sample result:

Using the query result, you can plot a graph to see the trends of size and delay.

Offload Readonly query to Log Shipping Secondary server

You can reduce the load on your primary/production server by using a Log Shipping secondary server for read-only query. To do this, the secondary database must be in STANDBY mode. You will not be able to run queries if the database is in NORECOVERY mode. When you configure log shipping secondary server using SSMS, on the "Restore Transaction Log" tab, choose the "Standby mode" option. You can also decide whether to disconnect users from the secondary database while the restore operation is underway.

After the log shipping secondary server setup is completed, you can check that the secondary database is in standby/readonly mode:

Now you are able to run readonly query on the secondary database:

If you try modifying data on the secondary database, it will be fail:

If you did NOT enabled the "Disconnect users in the database when restoring backups" option, then the Restore Job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database.

After all the connections to the secondary database are disconnected, the restore job will be resumed.

Any data modifications in the primary database will be transferred to the secondary database.

Trick: in order to prevent the readonly query blocks the restore job, you can specify the connection to USE another database, e.g. master, and query the secondary database tables using three-part-name.

Check the Progress of Shrink Database

You can use SSMS GUI or DBCC SHRINK command to shrink the size of the data and log files in a specified database. By the way, both the SSMS GUI and DBCC command do NOT tell you the progress of it. You can consult the sys.dm_exec_requests DMV's percent_complete column to check the progress as demonstrated below:

SELECT percent_complete, start_time, [status], command, estimated_completion_time, cpu_time, total_elapsed_time FROM sys.dm_exec_requests WHERE percent_complete > 0
 
You should look for DbccFilesCompact in the command column.

View Running SQL Statement, inside current processing Batches and Stored Procedures

Below SQL script can be used to view the current running (running, in runnable queue, rolling-back, and waiting for resources like locks) SQL statement, inside every current processing batches and stored procedures. Noted that the [text255] column represents the whole batch/stored procedure, but only shows the first 255 characters. Also, stored procedures are represented as "CREATE PROCEDURE" statement, it's just the definition, does NOT mean it's creating it.
This script is very useful for checking the SQL statement being stuck inside a batch and stored procedure.

SELECT
CASE WHEN (SELECT COUNT(*) FROM sys.sysprocesses WHERE spid = r.spid) > 1 THEN 'Multithread' ELSE '' END AS Multithread,
LEFT(t.[text], 255) AS [text255],
SUBSTRING(
t.[text],
(r.stmt_start / 2) + 1,
((
CASE
r.stmt_end
WHEN -1
THEN DATALENGTH(t.[text])
ELSE r.stmt_end
END - r.stmt_start) / 2) + 1) AS stmt,
DB_NAME(t.dbid) AS ObjectDB,
OBJECT_NAME(t.objectid, t.dbid) AS Object,
r.spid, r.ecid, r.blocked, r.waittime, r.lastwaittype, r.waitresource, DB_NAME(r.dbid) AS connectDB, r.cpu, r.physical_io, r.memusage, r.login_time,
r.last_batch, r.open_tran, r.status, r.hostname, r.program_name, r.loginame, r.cmd, r.net_library, r.login_time, r.stmt_start, r.stmt_end
FROM sys.sysprocesses AS r
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.status IN ('runnable', 'suspended', 'running', 'rollback', 'pending', 'spinloop')
ORDER BY spid, ecid

Fixing SQL Server Login SID by mapping it to Database User

When you restore a database from another server, especially for setting Availability Group, Database Mirroring, and Log Shipping, the client application may not able to use database and getting login failed for user after failover. It's due to a well known problem called "SID mapping" between the SQL Server Login and the Database User. If you recreate a SQL Server login (ie: not a Windows one), by default you get a new security ID (SID), even though you have the same user name and password. But the SID in the Database User is brought from the original (primary) database, which only linked to the SID of the corresponding SQL Server Login in the original (primary) server.

In order to fix this problem permanently, you should create the SQL Server Logins in secondary server by explicitly specifying the SID, e.g.

1. In primary database, get the SID of the database user:
USE LoginTest;
GO
SELECT sid FROM sysusers WHERE name = 'GregTest';

2. In the secondary server, after you restored the database into it, you can take a look in the user mapping and see that the Database User is NOT linked to any Login:
USE LoginTest
GO
EXEC sp_change_users_login 'Report';

3. You should fix it by creating a new SQL Server Login in the secondary server with the same SID:
CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd', SID = 0x2261C43EFD53F240AA989A8FB9E084DC

4. Check again the SID mapping, you should see no missing anymore:

Checking Stored Procedure Performance Statistics

We can use the sys.dm_exec_procedure_stats DMV to check the last execution time, elapsed time, and other performance figures of stored procedures exist in the plan cache. Below is the SQL statement:

SELECT
DB_NAME(database_id) AS DB,
OBJECT_NAME(object_id, database_id)AS [OBJECT],
*
FROM sys.dm_exec_procedure_stats