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

Get List of Stopped SQL Services from central server with WMI

Get List of Stopped SQL Services from central server with WMI.
As a DBA we have to monitor many things. To monitor all SQL Server Serives wotking properly works fine we have to 
check status of services periodically.
We can do that it manually or automatically, Manually - By using Services.msc, Automatically - By using scripting and programming.
I have created a WMI Script which is very helpfull to DBA Monitoring Task.

Benifits of using WMI.
1) WMI gives detail information without affecting performance.
2) WMI is applicable to handle from central server, you can get information from all remote server without any overhead.
For this you just need to create Wnidows user which have WMI Permission. For Permission please see http://nirav.extreme-advice.com/2013/05/15/get-disk-space-usage-from-central-server-with-wmi/
3) Automatically sends mail for which service is stopped.
4) WMI script has a provision to start stopped services automatically.

Get List of Stopped SQL Services from central server with WMI.
Step1 : Open notepad and save below code with "list of stopped services.vbs"
Dim strComputer
Dim objWMIService, objItem, colItems
Dim strDriveType,txt,flg
Dim arrylist(4)

arrylist(0) = ("Server1")
arrylist(1) = ("Server2")
arrylist(2) = ("Server3")
arrylist(3) = ("Server4")
arrylist(4) = ("Server5")

txt = "<table cellspacing='0' cellpadding='0' border='0' bordercolor='black' id='shell' colspace ='2'>"
txt = txt & "<tr bgcolor='#DDDDDD' height=30>"
txt = txt & "<th width='50' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Server</th>"
txt = txt & "<th width='100' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Service</th>"
txt = txt & "<th width='50' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Status</th>"
txt = txt & "</tr>"

For Each strComputer in arrylist
On Error Resume Next

Set objWMIService = Nothing
Set colItems = Nothing
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Service Where DisplayName like'%sql%' and AcceptStop = False")
    If Err.Number = 0 Then
	    For Each objItem in colItems
            txt = txt & "<tr Bordercolor='#000000'>"
            txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strComputer &"</Font></td>"
            txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& objItem.DisplayName &"</Font></td>"
            txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& objItem.State &"</Font></td>"
            txt = txt & "</tr>"
            Set objWMIService = Nothing
            Set colItems = Nothing
        Next
    End If
Next
txt = txt & "</table>"

    Set objMessage = CreateObject("CDO.Message")
    objMessage.Subject = "List of stopped sql services"
    objMessage.From = "Alerts@Sender.com"
    objMessage.To = "niravgajjar@Receiver.com"
    objMessage.htmlbody = txt
    objMessage.Send

Step2 : Create one Schedule task and call this VBS file periodically like Daily ones or twice as need of process. You will get mail like below.

Server Service Status
Server1 SQL Active Directory Helper Service Stopped
Server2 SQL Active Directory Helper Service Stopped
Server2 SQL Server Agent (MSSQLSERVER) Stopped
Server3 SQL Active Directory Helper Service Stopped
Server3 SQL Server Agent (SQL2K8R2EXP) Stopped
Server4 SQL Server Distributed Replay Client Stopped
Server4 SQL Server Distributed Replay Controller Stopped
Server4 SQL Server Agent (SQL2K12EXPR) Stopped
Server5 SQL Server FullText Search (MSSQLSERVER) Stopped
Server5 SQL Server Active Directory Helper Stopped
 Step3 : This is an additional step to make changes in script.
if you need all stopped services then change query.
Select * from Win32_Service Where AcceptStop = False

if you need all SQL services then change query.

Select * from Win32_Service Where DisplayName like'%sql%'

if you need all services.

Select * from Win32_Service

Get Disk space usage from central server with WMI

Get Disk space usage from central server with WMI.

As a DBA job we have to check server health regularly. sometime that happens when you are working on multiple servers and its very difficult to monitor health of all servers. I faced the same issue last week. My application abnormally going down and i am unable to connect database, while i had tried to connect that particular server i found there is only 20KB free space in data drive.

It happens sometime that you don’t know how fast drive is filling and adversity occurs due to low disk space.
I have created one wmi application that observes disk space and mail us on your defined email id’s. This application will handles all server’s Disk usage from central server. For that you need to create one common user with WMI permission.
Please follow the below steps to manage disk space usage from all servers.

Step1 : All of your server must be enable for remote WMI connection.

Click on Start –> Run –> WMIMGMT.MSC

WMI1

Just right click on WMI Control ans select property.

On “WMI Control Property” go to “Security” tab and select “Root” click on Security button which will pop one more window for “Security for root”.

On “Security for Root” window mark on Remote enable for your “WMIUser” or “Everyone”.

WMI2

Step2 : Open Notepad and copy below WMI Code to notepad and save with “DiskUsageWithWMi.csv”.

Sub SendMail(Sender, Recipient, Subject, Message)
   Set objMessage = CreateObject("CDO.Message")
   objMessage.Subject = Subject
   objMessage.From = Sender
   objMessage.To = Recipient
   objMessage.htmlbody = Message
   objMessage.Send
End Sub

Dim strComputer
Dim objWMIService, objItem, colItems
Dim strDriveType,txt
Dim arrylist(4)
Dim pctFreeSpace,strFreeSpace,strusedSpace,strDiskSize

arrylist(0) = ("Server1")
arrylist(0) = ("Server2")
arrylist(0) = ("Server3")
arrylist(0) = ("Server4")
arrylist(0) = ("Server5")

txt = "<table cellspacing='1' cellpadding='0' border='0' bordercolor='black' id='shell'>"
txt = txt & "<tr bgcolor='#DDDDDD' height=30>"
txt = txt & "<th width='50' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Drive</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Size(GB)</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Used(GB)</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Free(GB)</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Free(%)</th>"
txt = txt & "</tr>"

For Each strComputer in arrylist
'On Error Resume Next

   Set objWMIService = Nothing
   Set colItems = Nothing

   Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
   Set colItems = objWMIService.ExecQuery("Select * from Win32_LogicalDisk WHERE DriveType=3 or DriveType=4")

   txt = txt & "<tr bgcolor='#DDDDDD'Bordercolor='#000000'><td colspan='5' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strComputer & "</font></td></tr>"

   For Each objItem in colItems

      pctFreeSpace = FormatNumber(((objItem.FreeSpace / objItem.Size) * 1000)/10,2)
      strDiskSize = FormatNumber(objItem.Size /1073741824,3)
      strFreeSpace = FormatNumber(objItem.FreeSpace /1073741824,3)
      strUsedSpace = objItem.Size-objItem.FreeSpace
      strUsedSpace = FormatNumber(strUsedSpace/1073741824,3)
      IF pctFreeSpace <= 10 Then
         txt = txt & "<tr bgcolor='#9C3333' Bordercolor='#000000'>"
      Else
         txt = txt & "<tr Bordercolor='#000000'>"
      End If
      txt = txt & "<td style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>" & objItem.Name & "</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strDiskSize &"</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strUsedSpace &"</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strFreeSpace &"</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& pctFreeSpace &"</Font></td>"
      txt = txt & "</tr>"
      pctFreeSpace = 0
      strDiskSize = 0
      strFreeSpace = 0
      strUsedSpace = 0
      strUsedSpace = 0
      Set objWMIService = Nothing
      Set colItems = Nothing
   Next
Next
txt = txt & "</table>"
SendMail "emailid@sender.com","emailid.reciever.com", strComputer & "Disk Space Information was executed at " & Now(), txt

Step3 : You can create a schedule task which calls this VBS file daily on your required duration like every hour or every 3 hour or daily one time etc.

This code will send you a message like this.

Drive Size(GB) Used(GB) Free(GB) Free(%)
Server1
C: 465.150 213.078 252.073 54.19
D: 465.247 340.871 124.376 26.73
E: 465.247 425.474 39.773 8.55
F: 465.247 328.124 137.123 29.47
L: 169.853 125.357 44.496 26.20
P: 749.997 664.429 85.568 11.41
T: 169.991 72.388 97.603 57.42
Server2
C: 20.407 15.690 4.717 23.12
E: 54.052 35.063 18.988 35.13
F: 465.759 279.674 186.085 39.95
Server3
C: 22.399 20.217 2.182 9.74
D: 67.750 37.477 30.273 44.68
E: 45.281 37.560 7.721 17.05
G: 67.748 53.881 13.868 20.47
H: 272.243 244.077 28.166 10.35
W: 5,527.029 4,654.987 872.042 15.78
Server4
C: 20.216 18.012 2.204 10.90
E: 115.243 58.476 56.767 49.26

Some of them showing in red color which means that drive has lower than 10 % disk space.

Regards,

Nirav Gajjar

Posted in WMI

Log Shipping with SQL Server 2012

Log Shipping with SQL Server 2012.

SQL Server provides many ways for disaster recovery and log shipping is one of them.

You can configure log shipping in three topology.

1). Configure log shipping on one instance with different database name.

2). Configure log shipping on different instance in same server.

3). Configure log shipping on different server.

Log shipping engine just restore only transaction logs. the procedure is to restore one full backup of original database with no recovery and one transaction backup with also norecovery mode.

Please follow the below steps to configure Log shipping on your machine.

Step1 :  Creat sample database.

Use Master
Go
Create Database MyDatabase

Step2 : You must have your database full recovery mode.

Use Master
Go
ALTER DATABASE [MyDatabase] SET RECOVERY FULL WITH NO_WAIT

Step3 : Take one full backup and one transaction backup of your primary database.

Use Master
Go
Backup Database MyDatabase to Disk = 'D:\MyDatabase_Full.bak'
Backup log MyDatabase to Disk = 'D:\MyDatabase_Tran.trn'

Step4 : Restore full backup and then transactiosnal baackup.

Use Master
Go
RESTORE DATABASE [MyDatabase_LogShipping] FROM  DISK = N'D:\MyDatabase_Full.bak' WITH  FILE = 1,
MOVE N'MyDatabase' TO N'C:\SQL Server\Data\MyDatabase_LogShiiping.mdf',
MOVE N'MyDatabase_log' TO N'C:\SQL Server\Data\MyDatabase_LogShipping_log.ldf',
NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE Log [MyDatabase_LogShipping] FROM  DISK = N'D:\MyDatabase_Tran.trn'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

Step5 : Now right click on MyDatabase and click on property and go to “Option” pane.
Check on “Enable this as a priamary database in a log shipping configuration”
and click on “Backup Setting” button.
LogShipping1

Step6 : Please create one sharing folder with full rights and that path in Network path to backup folder.

There is a default schedule of taking backup every 15 minutes, you can change it.

LogShipping2

Click “OK” to complete transaction log backup setting window.

Step7 : Click on “ADD” button for Secondary databases in Log shipping property.

Click on connect to connect your log shipping server and database and move to Copy files tab.

LogShipping3

Step8 : Create one folder to copy files from primary server with full permission.LogShipping4

Step9 : Please select your log shipping database where you want to restore your log on Secondary database.

There is two option to restore backup.

1).  No recovery mode – you can’t use log shipped database in this mode, but it gives better performance.

2) Standby mode – you can use log shipped database for only select statement, you can’t change data you can just read data. log shipping is stopped working when any user reading data from log shipping database, so this mode will slow down the performance.

LogShipping5

Click “OK” button to complete your log shipping.

Step10 : To Recovery Log shipping database.

Use Master
Go
Restore database MyDatabase_logshipping with Recovery

now, log shipping database is ready to use.

Regards,

Nirav Gajjar

Schedule SSIS Package Without Deploying

Schedule SSIS Package Without Deploying.

You can schedule your package before you completely deploy it. you just need to build your SSIS Package and you will get executable “.dtsx” file.

Path for “.dtsx” file is different according to SQL Server versions.

For SQL Serve 2008 :  “your package path \bin\*.dtsx”

For SQL Serve 2012 :  “your package path \bin\Development\*.dtsx”

There are two ways to schedule SSIS Package if you have created package through “File System”.

1) SQL Server Agent Job.

You need to create new job in your SQL Server.

Step1 : Connect your local sql server –> expand SQL Server Agent –> right click on job and click on new job.

SSIS1

Step2 : Give “Call Package” to your job name.

SSIS2

Step3 : Click on “New” button on Steps tab.

SSIS3

Step4 : On New Job Step – Select SQL Server Integration Package in TYPE and File System in Package Source and give “.dtsx” file path.

SSIS4

Step5 : Schedule your package as you convenient time.

SSIS5

2) Windows Schedule Task.

You can’t call “.dtsx” file directly from Schedule task, you must need to create batch file which calls “.dtsx” file and letter you can create schedule task for that batch file.

Step1 : Create batch file with following code.


"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /FILE "D:\Work\SSIS\Nirav's Diary\Nirav's Diary\bin\Development\GetSpaceInfoWithWMI.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI

save it to “D:\Work\SSIS\BatchFiles\GetSpaceInfoWithWMI.bat”

Step2 : Create Windows Schedule Task.

Click on Start button — > Run –> and Type — > control schedtasks and click ok.

Step3 : Click on Task Scheduler Library on left pane of Task Scheduler, it will show a window to create new task.

Now on the right side of Task Scheduler –> click on Create Task under Actions pane. It will open one pop menu to create new task.

Give schedule name  and select option “Run whether user is logged in or not”.

SSIS6

Step4 :  Select Trigger tab from Create Task window –> click on new to schedule time.

SSIS7

Step5 : Select Action tab from Create Task window –> click on browse to map file path.

SSIS8

Click on “OK” button to complete it. windows will ask for user name and password.

Please apply administrator user and password or any other user which have a permission to execute schedule task.

Regards,

Nirav Gajjar