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

Deploy SSIS Package in SQL Server 2012

Deploy SSIS Package in SQL Server 2012.

SQL Server 2012 added new feature of Integration Service Catalog, which makes SSIS package deployment task very easy.

To get advantage of this feature you must have sql server integration service installed on your server.

Follow the steps to deploy package.

Step1 : Create Integration Service catalog.

DeploySSISPackage1

Step2 : Enable CLR Integration and provide encryption password.

DeploySSISPackage2

Step3 : You will find SSISDB catalog will created. Creating catalog automatically creates database “SSISDB” to your instance.

DeploySSISPackage3

Step4 : Now go to your package right click on your project and select “Deploy”.

DeploySSISPackage4

Step5 : Selection will opens deployment window click on “next”.

DeploySSISPackage5

Step6 : Provide destination server detail to server name.

DeploySSISPackage6

Step7 :  Click on “Browse” to select Path:

DeploySSISPackage7

Create New folder with your desired name.

DeploySSISPackage8

Step8 : Click on “Next”.

DeploySSISPackage9

Step9 : Check the summary and click on “Deploy”.

DeploySSISPackage10

Step10 :  Click on “Close”.

DeploySSISPackage11

Step11 : Check your deployed package under Integration service in SSMS.

DeploySSISPackage12

Step12 : To Schedule this package create new job with name “Deployment Testing”.

DeploySSISPackage13

–> Select “SQL Server Integration Services Package’ in Type.

–> Select “SSIS Catalog” in Package Source and provide server connection details.

DeploySSISPackage14

–> Select your package path.

DeploySSISPackage15

–> Schedule the package as your desired time.

DeploySSISPackage16

 

Regards,

Nirav Gajjar

XML Data in where clause

XML Data in where clause
XML Data in Document Types.
Step1 : Create Table XMLData.

	Use Adventureworks2012
	Go
	Create Table XMLData
	(
		ID Int Identity(1,1),
		XMLResult XML,
		CreateDate DateTime
	)

Step2 : Insert XML Document Types Data.

	Insert Into XMLData Values('<Databases> <Name>master</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2003-04-08T09:13:36.390</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>tempdb</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-27T16:13:43.170</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>model</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2003-04-08T09:13:36.390</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>msdb</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2012-02-10T21:02:17.770</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>ReportServer$SQL</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-16T20:33:13.300</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>ReportServer$SQLTempDB</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-16T20:33:13.583</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>temp</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-22T21:16:22.997</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>Adventureworks2012</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-27T22:12:48.527</create_date> </Databases>',getdate())

Step3 : Where clause for XML Data Type.

	Select * from XMLData
	 Where XMLResult.value('(/Databases/Name)[1]','varchar(20)') = 'master'

XML Data in Element Types.
Step1 : Truncate XML Data table and insert some element type XML data in the same table.

	Truncate Table XMLData
	Insert Into XMLData Values('<root><Databases name="master" recovery_model="3" state_desc="ONLINE" create_date="2003-04-08T09:13:36.390" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="tempdb" recovery_model="3" state_desc="ONLINE" create_date="2013-05-29T17:09:36.863" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="model" recovery_model="1" state_desc="ONLINE" create_date="2003-04-08T09:13:36.390" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="msdb" recovery_model="3" state_desc="ONLINE" create_date="2012-02-10T21:02:17.770" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="ReportServer$SQL" recovery_model="1" state_desc="ONLINE" create_date="2013-05-16T20:33:13.300" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="ReportServer$SQLTempDB" recovery_model="3" state_desc="ONLINE" create_date="2013-05-16T20:33:13.583" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="temp" recovery_model="1" state_desc="ONLINE" create_date="2013-05-22T21:16:22.997" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="Adventureworks2012" recovery_model="1" state_desc="ONLINE" create_date="2013-05-27T22:12:48.527" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="SSISDB" recovery_model="1" state_desc="ONLINE" create_date="2013-05-29T17:07:05.090" /></root>',getdate())

Step2 : Where clause for XML Data Type.

	Select * from XMLData Where XMLResult.exist('(/root/Databases[@name="master"])') = 1
	Select * from XMLData Where XMLResult.value('(/root/Databases/@name)[1]','varchar(20)') = 'master'

Regards,
Nirav Gajjar

SQL Server connection in WMI Script

SQL Server connection in WMI Script.

VB Script provides composition of WMI and SQL Server. You can connect SQL Server and store WMI informations in SQL Server. Before reading this blog you already know that WMI is also works like central communication machine for all network servers. To know more read http://nirav.extreme-advice.com/2013/05/15/get-disk-space-usage-from-central-server-with-wmi/.

I have created one example for to collect windows event log information from Win32_NTLogEvent and store the information into SQL Server table. This task is helpfull to monitor event log error.

you need to create event log table in your sql server instance.


USE [AdventureWorks2012]
GO

/****** Object:  Table [dbo].[Win32_Event_Log]    Script Date: 27-05-2012 12:13:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Win32_Event_Log](
[Category] [bigint] NULL,
[CategoryString] [varchar](200) NULL,
[ComputerName] [varchar](20) NULL,
[EventCode] [bigint] NULL,
[EventIdentifier] [bigint] NULL,
[EventType] [bigint] NULL,
[Logfile] [varchar](200) NULL,
[Message] [varchar](max) NULL,
[RecordNumber] [bigint] NULL,
[SourceName] [varchar](20) NULL,
[TimeGenerated] [varchar](100) NULL,
[TimeWritten] [varchar](100) NULL,
[Type] [varchar](20) NULL,
[user] [varchar](20) NULL
) ON [PRIMARY]

GO

Now open Notepad paste below code in your notepad and save with “WMI_Event_Log.vbs”.


strComputer = "."
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colScheduledJobs = objWMIService.ExecQuery ("Select * from Win32_NTLogEvent where Logfile='System'")
flg = 0
Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=localmachinename ;Initial Catalog=AdventureWorks2012;user id ='sa';password='password'"

Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn

For Each objJob in colScheduledJobs

'WMIDateStringToTimegenerator = CDate(Mid(objJob.TimeGenerated, 5, 2) & "/" & _
'     Mid(objJob.TimeGenerated, 7, 2) & "/" & Left(objJob.TimeGenerated, 4) _
'         & " " & Mid (objJob.TimeGenerated, 9, 2) & ":" & _
'             Mid(objJob.TimeGenerated, 11, 2) & ":" & Mid(objJob.TimeGenerated, _
'                 13, 2))
'WMIDateStringToTimeWr = CDate(Mid(objJob.TimeWritten, 5, 2) & "/" & _
'     Mid(objJob.TimeWritten, 7, 2) & "/" & Left(objJob.TimeWritten, 4) _
'         & " " & Mid (objJob.TimeWritten, 9, 2) & ":" & _

'             Mid(objJob.TimeWritten, 11, 2) & ":" & Mid(objJob.TimeWritten, _
'                 13, 2))

myCommand.CommandText = "Insert into Win32_Event_Log values(" & objJob.Category & ",'" & objJob.CategoryString & "','" & objJob.ComputerName & "'," & objJob.EventCode & "," & objJob.EventIdentifier & "," & objJob.EventType & ",'" & objJob.Logfile & "','" & objJob.Message & "'," & objJob.RecordNumber & ",'" & objJob.SourceName & "','" & objJob.TimeGenerated & "','" & objJob.TimeWritten & "','" & objJob.Type & "','" & objJob.User & "')"
myCommand.Execute
Next
myConn.Close

Execute this file periodically by schedule task and get information as your need.

Regards,
Nirav Gajjar

Monitor performance counter with WMI and SQL Server

Monitor performance counter with WMI and SQL Server.

WMI is very important to use as central monitoring task. I have created performance monitoring which checks Disk Queue Legth, Disk Read/Sec, Disk Write/Sec, CPU Usage, Memory Usage, Disk Read Bytes/Sec, Disk Write Bytes/Sec, ASP Request/Sec. Create a table “PerformanceCounter” to store all information.


USE [Adventureworks2012]
GO

/****** Object: Table [dbo].[PerformanceCounter] Script Date: 05/27/2013 12:35:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PerformanceCounter](
 [ServerName] [varchar](20) NULL,
 [CounterName] [varchar](100) NULL,
 [CounterValue] [varchar](100) NULL,
 [CreatedDate] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PerformanceCounter] ADD DEFAULT (getdate()) FOR [CreatedDate]
GO

Open Notepad and save below code with “PerformanceCount.vbs”.

Option Explicit
 On Error Goto 0
 Dim strSrv, strQuery
 Dim arrylist(9)
 arrylist(0) = ("Server1")
 arrylist(1) = ("Server2")
 arrylist(2) = ("Server3")
 arrylist(3) = ("Server4")
 arrylist(4) = ("Server5")
 arrylist(5) = ("Server6")
 arrylist(6) = ("Server7")
 arrylist(7) = ("Server8")
 arrylist(8) = ("Server9")
 arrylist(9) = ("Server10")

Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=localmachinename ;Initial Catalog=AdventureWorks2012;user id ='sa';password='password'"
 Dim myConn, myCommand

For Each strSrv in arrylist
 On Error Resume Next
 Call GetDiskQueueLegth(StrSrv)
 Call GetDiskReadperSec(StrSrv)
 Call GetDiskWritesPerSec(StrSrv)
 Call GetCPUUsage(StrSrv)
 Call GetMemory(strSrv)
 Call GetDiskWriteBytesPerSec(strSrv)
 Call GetDiskReadBytesPerSec(strSrv)
 Call GetRequestPerSec(strSrv)
 Next

Function GetMemory(strSrv)
 'On Error Resume Next
 Dim objWMIService, Item, Proc
 strQuery = "select Name,Pagelifeexpectancy from Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager"
 Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
 Set Item = objWMIService.ExecQuery(strQuery,,48)
 For Each Proc In Item
 Dim myConn,myCommand
 'wscript.echo Proc.Name
 'wscript.echo Proc.Pagelifeexpectancy
 If Proc.Pagelifeexpectancy < 300 Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','Memory PLE','" & Proc.Pagelifeexpectancy & "',getdate())"
 myCommand.Execute
 myConn.Close
 End If
 Next
 End Function

Function GetCPUUsage(strSrv)
 Dim objWMIService,objRefresher,colItems,Proc,CPUUsage,Cnt
 Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strSrv & "\root\cimv2")
 set objRefresher = CreateObject("WbemScripting.SWbemRefresher")
 Set colItems = objRefresher.AddEnum (objWMIService, "Win32_Processor").objectSet
 objRefresher.Refresh
 CPUUsage = 0
 Cnt = 0
 For Each Proc in colItems
 If Proc.LoadPercentage Then
 CPUUsage = CPUUsage + Proc.LoadPercentage
 'objRefresher.Refresh
 Cnt = Cnt + 1
 End If
 Next
 If Cnt > 0 and Cint(CPUUsage/cnt) > 90 Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','CPU Usage','" & Cint(CPUUsage/cnt) & "',getdate())"
 myCommand.Execute
 myConn.Close
 End If
 End Function

Function GetDiskQueueLegth(strSrv)
 Dim objWMIService, Item, Proc

strQuery = "select Name,AvgDiskQueueLength from win32_perfformatteddata_perfdisk_physicaldisk"
 Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
 Set Item = objWMIService.ExecQuery(strQuery,,48)
 For Each Proc In Item
 Dim myConn,myCommand
 'wscript.echo Proc.Name
 'wscript.echo Proc.AvgDiskQueueLength
 If Proc.Name = "_Total" and Proc.AvgDiskQueueLength > 2 Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','I/O Queue','" & Proc.AvgDiskQueueLength & "',getdate())"
 myCommand.Execute
 myConn.Close
 End If
 Next
 End Function

Function GetDiskWritesPerSec(strSrv)
 Dim objWMIService,objRefresher,colItems,Proc
 Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strSrv & "\root\cimv2")
 set objRefresher = CreateObject("WbemScripting.SWbemRefresher")
 Set colItems = objRefresher.AddEnum _
 (objWMIService, "Win32_PerfFormattedData_PerfDisk_PhysicalDisk").objectSet
 objRefresher.Refresh
 For Each Proc in colItems
 If Proc.name = "_Total" and Proc.DiskWritesPerSec Then
 IF Cint(1000/Proc.DiskWritesPerSec) > 15 Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','MS/Write','" & Cint(1000/Proc.DiskWritesPerSec) & "',getdate())"
 myCommand.Execute
 myConn.Close
 objRefresher.Refresh
 End If
 End If
 Next
 End Function

Function GetDiskReadperSec(strSrv)
 Dim objWMIService,objRefresher,colItems,Proc
 Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strSrv & "\root\cimv2")
 set objRefresher = CreateObject("WbemScripting.SWbemRefresher")
 Set colItems = objRefresher.AddEnum _
 (objWMIService, "Win32_PerfFormattedData_PerfDisk_PhysicalDisk").objectSet
 objRefresher.Refresh
 For Each Proc in colItems
 If Proc.name = "_Total" and Proc.DiskReadsPerSec Then
 If Cint(1000/Proc.DiskReadsPerSec) > 15 Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','MS/Read','" & Cint(1000/Proc.DiskReadsPerSec) & "',getdate())"
 myCommand.Execute
 myConn.Close
 objRefresher.Refresh
 End If
 End If
 Next
 End Function

Function GetDiskReadBytesPerSec(strSrv)
 Dim objWMIService, Item, Proc

strQuery = "select Name,DiskReadBytesPerSec from win32_perfformatteddata_perfdisk_physicaldisk"
 Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
 Set Item = objWMIService.ExecQuery(strQuery,,48)
 For Each Proc In Item
 Dim myConn,myCommand
 'wscript.echo Proc.Name
 'wscript.echo Proc.AvgDiskQueueLength
 If Proc.Name = "_Total" Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','RGB/Sec','" & Round((Proc.DiskReadBytesPerSec/1024/1024/1024),3) & "',getdate())"
 myCommand.Execute
 myConn.Close
 End If
 Next
 End Function

Function GetDiskWriteBytesPerSec(strSrv)
 Dim objWMIService, Item, Proc

strQuery = "select Name,DiskWriteBytesPerSec from win32_perfformatteddata_perfdisk_physicaldisk"
 Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
 Set Item = objWMIService.ExecQuery(strQuery,,48)
 For Each Proc In Item
 Dim myConn,myCommand
 'wscript.echo Proc.Name
 'wscript.echo Proc.AvgDiskQueueLength
 If Proc.Name = "_Total" Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','WGB/Sec','" & Round((Proc.DiskWriteBytesPerSec/1024/1024/1024),3) & "',getdate())"
 myCommand.Execute
 myConn.Close
 End If
 Next
 End Function

Function GetRequestPerSec(strSrv)
 'On Error Resume Next
 Dim objWMIService, Item, Proc
 strQuery = "select Name,RequestsPerSec from Win32_PerfFormattedData_ASPNET_ASPNETApplications"
 Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
 Set Item = objWMIService.ExecQuery(strQuery,,48)
 For Each Proc In Item
 Dim myConn,myCommand
 If Proc.Name = "_Total" and (strSrv = "Server9" or strSrv = "Server14")Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','Req/Sec','" & Proc.RequestsPerSec & "',getdate())"
 myCommand.Execute
 myConn.Close
 End IF
 Next
 End Function

Regards,
Nirav Gajjar

Change file location of Master Database

Change file location of Master Database.

To change file location of other database is very easy and it can be done in three steps.
Step1 Detach that database.
Step2 Cut from Old location and paste mdf and ldf files to new location.
Step3 Attach database with new location.

To change file location of master database is more tricky task, because you must need to restart the sql server service and need parameter changes in SQL Server Service property.

Please follow the steps to change database file location of master database.
Step1 : Check for the current path of master database files to copy from that path.

Select * FROM SYS.sysaltfiles where dbid = DB_ID('master')

Step2 : Script to change file location.

USE master;
GO
ALTER DATABASE master
MODIFY FILE (NAME = master, FILENAME = 'D:\Data\master.mdf');
GO
ALTER DATABASE master
MODIFY FILE (NAME = mastlog, FILENAME = 'D:\Log\mastlog.ldf');
GO

Step3 : Change SQL Server service parameter.
–> Goto SQL Server Configuration Manager –> Select you SQL instance(By Default MSSQLSERVER).
–> Right click on service and goto property.
–> Goto startup parameter tab and select “-dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf”
–> change it by “-dD:\DATA\master.mdf” and click on update.
–> Now Select “-lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf”
–> change it by “-lD:\Log\mastlog.ldf”
ChangeMasterFile1

Step4 : Stop your sql server instance copy mdf and ldf file from base location to new location and restart the service.

Step5 : If you get error like “The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.”.

it means you don’t have rights on new locations.
ChangeMasterFile2

To solve this give a full permission to that folder.

–> Go to D:\Data –> Right click on Data Folder and go to Security tab.

–>Click on ADD button and add everyone user rights.

ChangeMasterFile3

 

 

Regards,

Nirav Gajjar