Script to Monitor Mirroring

Script to Monitor Mirroring

This script will provide all database which is mirrored with mirroring mode, mirroring status and partner information.


SELECT Sd.Name              AS Databasename,
       Mirroring_state_desc AS Status,
       CASE
         WHEN Mirroring_safety_level = 1 THEN 'High Performance'
         WHEN Mirroring_safety_level = 2 THEN 'High Safety'
         ELSE NULL
       END                  AS Mirroringmod,
       Mirroring_role_desc,
       Mirroring_partner_instance
FROM   MSDB.SYS.Database_mirroring Sdm
       JOIN MASTER.SYS.Sysdatabases Sd
         ON Sdm.Database_id = Sd.Dbid
WHERE  Mirroring_guid IS NOT NULL

Regards,
Nirav Gajjar

Script to get Replication Latency

Script to get Replication Latency.

I have created monitoring script to get latency information for replication and status of replication health.

I have used “SP_REPLMONITORHELPSUBSCRIPTION ” under Distribution database.

It gives you same information which you can get from Replication Monitor –> Subscription Wath List.

In SQL Server 2012 you just need to pass value only for @Publication_type.

If you are using older version of SQL Server then you need to pass @Publication_type and @Publisher parameter, so I have created script for both different versions.

DECLARE @Srvname VARCHAR(100)
DECLARE @Pub_db VARCHAR(100)
DECLARE @Pubname VARCHAR(100)

CREATE TABLE #REPLMONITOR
 (
 Status INT NULL,
 Warning INT NULL,
 Subscriber SYSNAME NULL,
 Subscriber_db SYSNAME NULL,
 Publisher_db SYSNAME NULL,
 Publication SYSNAME NULL,
 Publication_type INT NULL,
 Subtype INT NULL,
 Latency INT NULL,
 Latencythreshold INT NULL,
 Agentnotrunning INT NULL,
 Agentnotrunningthreshold INT NULL,
 Timetoexpiration INT NULL,
 Expirationthreshold INT NULL,
 Last_distsync DATETIME,
 Distribution_agentname SYSNAME NULL,
 Mergeagentname SYSNAME NULL,
 Mergesubscriptionfriendlyname SYSNAME NULL,
 Mergeagentlocation SYSNAME NULL,
 Mergeconnectiontype INT NULL,
 Mergeperformance INT NULL,
 Mergerunspeed FLOAT,
 Mergerunduration INT NULL,
 Monitorranking INT NULL,
 Distributionagentjobid BINARY(16),
 Mergeagentjobid BINARY(16),
 Distributionagentid INT NULL,
 Distributionagentprofileid INT NULL,
 Mergeagentid INT NULL,
 Mergeagentprofileid INT NULL,
 Logreaderagentname VARCHAR(100),
 Publisher VARCHAR(20)
 )

INSERT INTO #REPLMONITOR
SELECT A.*
FROM OPENROWSET('MSDASQL',
 'DRIVER={SQL Server}; SERVER=InstanceName; UID=sa; PWD=Password',
 'SET FMTONLY OFF EXEC DISTRIBUTION.DBO.SP_REPLMONITORHELPSUBSCRIPTION @Publication_type = 0') AS A;

SELECT CASE Status
 WHEN 1 THEN 'Started'
 WHEN 2 THEN 'Succeeded'
 WHEN 3 THEN 'In Profress'
 WHEN 4 THEN 'Idle'
 WHEN 5 THEN 'Retrying'
 WHEN 6 THEN 'Failed'
 END AS Status,
 Publication,
 Publisher_db Subscriber_db,
 Subscriber_db,
 CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, Latency
 ), 6),
 5, 0, ':'), 3, 0,
 ':'), 108) AS Latency,
 CASE Monitorranking
 WHEN 60 THEN 'Error'
 WHEN 56 THEN 'Warning: performance critical'
 WHEN 52 THEN 'Warning: expiring soon or expired'
 WHEN 50 THEN 'Warning: subscription uninitialized'
 WHEN 40 THEN 'Retrying failed command'
 WHEN 30 THEN 'Not running (success)'
 WHEN 20 THEN 'Running (starting, running, or idle)'
 END AS Healthcheck
FROM #REPLMONITOR

DROP TABLE #REPLMONITOR

Query For SQL Server 2005 and 2008.

DECLARE @Srvname VARCHAR(100)
DECLARE @Pub_db VARCHAR(100)
DECLARE @Pubname VARCHAR(100)

CREATE TABLE #REPLMONITOR
  (
     Status                        INT NULL,
     Warning                       INT NULL,
     Subscriber                    SYSNAME NULL,
     Subscriber_db                 SYSNAME NULL,
     Publisher_db                  SYSNAME NULL,
     Publication                   SYSNAME NULL,
     Publication_type              INT NULL,
     Subtype                       INT NULL,
     Latency                       INT NULL,
     Latencythreshold              INT NULL,
     Agentnotrunning               INT NULL,
     Agentnotrunningthreshold      INT NULL,
     Timetoexpiration              INT NULL,
     Expirationthreshold           INT NULL,
     Last_distsync                 DATETIME,
     Distribution_agentname        SYSNAME NULL,
     Mergeagentname                SYSNAME NULL,
     Mergesubscriptionfriendlyname SYSNAME NULL,
     Mergeagentlocation            SYSNAME NULL,
     Mergeconnectiontype           INT NULL,
     Mergeperformance              INT NULL,
     Mergerunspeed                 FLOAT,
     Mergerunduration              INT NULL,
     Monitorranking                INT NULL,
     Distributionagentjobid        BINARY(16),
     Mergeagentjobid               BINARY(16),
     Distributionagentid           INT NULL,
     Distributionagentprofileid    INT NULL,
     Mergeagentid                  INT NULL,
     Mergeagentprofileid           INT NULL,
     Logreaderagentname            VARCHAR(100)
  )

INSERT INTO #REPLMONITOR
SELECT A.*
FROM   OPENROWSET('MSDASQL',
       'DRIVER={SQL Server}; SERVER=InstanceName; UID=sa; PWD=Password',
' set fmtonly off EXEC distribution.dbo.sp_replmonitorhelpsubscription @publication_type = 0, @Publisher = ''ServerName'''
) AS A;

SELECT CASE Status
         WHEN 1 THEN 'Started'
         WHEN 2 THEN 'Succeeded'
         WHEN 3 THEN 'In Profress'
         WHEN 4 THEN 'Idle'
         WHEN 5 THEN 'Retrying'
         WHEN 6 THEN 'Failed'
       END                                                AS Status,
       Publication,
       Publisher_db                                       Subscriber_db,
       Subscriber_db,
       CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, Latency
                                             ), 6),
                                 5, 0, ':'), 3, 0,
                                               ':'), 108) AS Latency,
       CASE Monitorranking
         WHEN 60 THEN 'Error'
         WHEN 56 THEN 'Warning: performance critical'
         WHEN 52 THEN 'Warning: expiring soon or expired'
         WHEN 50 THEN 'Warning: subscription uninitialized'
         WHEN 40 THEN 'Retrying failed command'
         WHEN 30 THEN 'Not running (success)'
         WHEN 20 THEN 'Running (starting, running, or idle)'
       END                                                AS Healthcheck
FROM   #REPLMONITOR

DROP TABLE #REPLMONITOR

Regards,
Nirav Gajjar

Script to get replication Undistributed Commands

Script to get replication Undistributed Commands

Script to Monitor Replication by checking last refresh time and pending commands

SELECT ( CASE
           WHEN Mdh.Runstatus = '1' THEN 'Start'
           WHEN Mdh.Runstatus = '2' THEN 'Succeed'
           WHEN Mdh.Runstatus = '3' THEN 'InProgress'
           WHEN Mdh.Runstatus = '4' THEN 'Idle'
           WHEN Mdh.Runstatus = '5' THEN 'Retry'
           WHEN Mdh.Runstatus = '6' THEN 'Fail'
         END )                                 [Run status],
       Mda.Subscriber_db                       [Subscriber db],
       Mda.Publication                         [Pub name],
       Mdh.[Time]                              [Lastsynchronized],
       Und.Undelivcmdsindistdb                 [Undistcom],
       Mdh.Comments                            [Comments],
       DATEDIFF(SECOND, Mdh.[Time], GETDATE()) Lastrefresh
FROM   DISTRIBUTION.DBO.Msdistribution_agents Mda
       LEFT JOIN DISTRIBUTION.DBO.Msdistribution_history Mdh
              ON Mdh.Agent_id = Mda.Id
       JOIN (SELECT S.AGENT_ID,
                    Maxagentvalue.[Time],
                    SUM(CASE
                          WHEN Xact_seqno > Maxagentvalue.Maxseq THEN 1
                          ELSE 0
                        END) AS Undelivcmdsindistdb
             FROM   DISTRIBUTION.DBO.Msrepl_commands T (NOLOCK)
                    JOIN DISTRIBUTION.DBO.Mssubscriptions AS S (NOLOCK)
                      ON ( T.ARTICLE_ID = S.ARTICLE_ID
                           AND T.PUBLISHER_DATABASE_ID = S.PUBLISHER_DATABASE_ID
                         )
                    JOIN (SELECT Hist.AGENT_ID,
                                 MAX(Hist.[TIME]) AS [Time],
                                 H.Maxseq
                          FROM   DISTRIBUTION.DBO.Msdistribution_history Hist (
                                 NOLOCK)
                                 JOIN (SELECT Agent_id,
                                              ISNULL(MAX(Xact_seqno), 0x0) AS
                                              Maxseq
                                       FROM
                                 DISTRIBUTION.DBO.Msdistribution_history (
                                 NOLOCK)
                                       GROUP  BY Agent_id) AS H
                                   ON ( Hist.AGENT_ID = H.Agent_id
                                        AND H.Maxseq = Hist.XACT_SEQNO )
                          GROUP  BY Hist.AGENT_ID,
                                    H.Maxseq) AS Maxagentvalue
                      ON Maxagentvalue.Agent_id = S.AGENT_ID
             GROUP  BY S.AGENT_ID,
                       Maxagentvalue.[Time]) Und
         ON Mda.Id = Und.Agent_id
            AND Und.[Time] = Mdh.[Time]
ORDER  BY Mdh.[Time] DESC


Regards,

Nirav Gajjar

Query to get Database size and table size

Query to get Database size and table size.

Query to get Database size in GB.


SELECT
 D.NAME,
 Cast((SUM(MF.SIZE) * 8.00 /1024.00/1024.00)as Numeric(18,2)) AS [SIZE (In GB)]
FROM SYS.MASTER_FILES MF
INNER JOIN SYS.DATABASES D
 ON D.DATABASE_ID = MF.DATABASE_ID
 WHERE D.DATABASE_ID > 4
 GROUP BY D.NAME
 ORDER BY [SIZE (In GB)] DESC

Query to get Table size in GB with no of rows.


SELECT SCHEMA_NAME(T.Schema_id) + '.' + T.Name
 AS
 Tablename,
 SUM(
		CASE
			WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
			ELSE 0
		END
	   )
 AS Rowcounts,
 CAST(( SUM(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(
 18, 3)) AS
 Totalspacegb
FROM SYS.Tables T
 INNER JOIN SYS.Indexes I
 ON T.Object_id = I.Object_id
 INNER JOIN SYS.Partitions P
 ON I.Object_id = P.Object_id
 AND I.Index_id = P.Index_id
 INNER JOIN SYS.Allocation_units A
 ON P.Partition_id = A.Container_id
 INNER JOIN SYS.Filegroups Fg
 ON I.Data_space_id = Fg.Data_space_id
 INNER JOIN SYS.Database_files Df
 ON Df.Data_space_id = Fg.Data_space_id
WHERE T.Is_ms_shipped = 0
 AND I.Object_id > 255
GROUP BY SCHEMA_NAME(T.Schema_id) + '.' + T.Name
ORDER BY Totalspacegb DESC

Query to get Service Broker Queue size in GB with no of rows.

Select OBJECT_NAME(IT.parent_object_id),SUM(
 CASE
 WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
 ELSE 0
 END
 )
 AS Rowcounts,CAST(( SUM(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC( 18, 3)) AS Totalspacegb
From sys.partitions P
Inner Join sys.internal_tables IT On IT.OBJECT_ID = P.OBJECT_ID
INNER JOIN SYS.Allocation_units A
ON P.PARTITION_ID = A.Container_id
Where P.index_id In (1, 0)
Group By OBJECT_NAME(IT.parent_object_id)
Order by 3 DESC

Regards,
Nirav Gajjar

Query to get Open Transaction

Query to get Open Transaction.

Query to monitor open transactions in your sql server instance.

If you are using SQL Server 2012 use open_transaction_count column instead of enlist_count.

Query to get open transactions by host. remove the comment if you want open transactions just from ssms window.

SELECT
SUM(Tst.Enlist_count) Noofopentran,
Host_name
FROM   SYS.Dm_tran_session_transactions Tst
INNER JOIN SYS.Dm_exec_sessions Es
ON Tst.Session_id = Es.Session_id
--WHERE program_name = 'Microsoft SQL Server Management Studio - Query'
GROUP  BY Host_name

List Open transactions in details.


SELECT
Tst.Session_id,
Tst.Enlist_count,
Host_name,
Program_name,
Text
FROM   SYS.Dm_tran_session_transactions Tst
INNER JOIN SYS.Dm_exec_connections Ec
ON Tst.Session_id = Ec.Session_id
INNER JOIN SYS.Dm_exec_sessions Es
ON Tst.Session_id = Es.Session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Ec.Most_recent_sql_handle)
--where program_name = 'Microsoft SQL Server Management Studio - Query'

Regards,
Nirav Gajjar

SQL Server long running and blocking queries

SQL Server long running and blocking queries

This query helps you to get all running queries including user queries and SQL Server engine queries.

with duration, so you can examine which query running long.

You can see BlockingWith field which shows you query is blocking with another process.

I have ordered query by blocking first so you can get all blocking query at first and you can take necessary actions.

SELECT
DATEDIFF(MI, Start_time, GETDATE()) AS Mins,
'USE ' + DB_NAME(Database_id) + '; SELECT object_name(' + CAST(Objectid AS VARCHAR) + ')' AS Objectname,
SUBSTRING(St.Text, ( Statement_start_offset / 2 ) + 1, ( ( CASE Statement_end_offset WHEN -1 THEN DATALENGTH(St.Text) ELSE Statement_end_offset END -   Statement_start_offset ) / 2 ) + 1) AS Statement_text,
DB_NAME(Database_id) AS Dbname,
Session_id,
Blocking_session_id AS Blockingwith,
Status,
Command
FROM SYS.Dm_exec_requests R
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Sql_handle) AS St
ORDER BY Blockingwith DESC, Mins DESC

This query show you only user queries. any queries running by SQL Server machine will be filtered.

Here are few more things to observe.

CPU field which shows the CPU usage, Reads, Writes, Host_Name which shows query running from which machine, Program_Name Which shows from which program the query executed.

SELECT
R.Cpu_time AS Cpu,
DATEDIFF(MINUTE, Last_request_start_time, GETDATE()) AS Mins,
S.Host_name AS Host,
LEFT(Client_interface_name, 15) AS Client,
S.Session_id AS Sessionid,
Blocking_session_id AS Blocking,
'USE ' + DB_NAME(Database_id) + '; SELECT object_name(' + CAST(Objectid AS VARCHAR) + ')' AS Objectname,
SUBSTRING(St.Text, ( Statement_start_offset / 2 ) + 1, ABS(( ( CASE Statement_end_offset WHEN -1 THEN DATALENGTH(St.Text) ELSE Statement_end_offset END - Statement_start_offset ) / 2 ) + 1)) AS Statement_text,
DB_NAME(Database_id) AS Dbname,
Blocking_session_id AS Blockingwith,
R.Reads,
R.Writes,
S.Session_id,
S.[Program_name],
S.Login_name,
S.Status,
S.Last_request_start_time,
R.Logical_reads
FROM SYS.Dm_exec_requests R
INNER JOIN SYS.Dm_exec_sessions S ON S.Session_id = R.Session_id AND S.Is_user_process = 1
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Sql_handle) AS St
ORDER BY Blocking DESC, Mins DESC

Regards,
Nirav Gajjar