SQL Server DBA - T-SQL Queries to find SQL Server Cluster Nodes and Shared Drives

T-SQL Queries to find SQL Server Cluster Nodes and Shared Drives

Find name of the Node on which SQL Server Instance is Currently running

 SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName] 

If the server is not cluster, then the above query returns the Host Name of the Server.

Find SQL Server Cluster Nodes
Using Function

SELECT * FROM fn_virtualservernodes() 

Find SQL Server Cluster Shared Drives
Using Function

SELECT * FROM fn_servershareddrives() 

SQL Server DBA - Send e-mail notification when a database is not online

Checking the databases availability is one of the major task of a DBA which has to be performed  at regular intervals to make sure that the critical live databases are always online and fix them immediately if they are not online. This can be done in "n" number of ways and here is one such method.
The method that i am going to discuss below makes use of "Database Mail" option to send e-mails to the intended recipients when the process finds the status of the database which is not online.

After the "Database Mail" Option is configured all you have to do is to create a job with the below code and schedule it to run every five minutes or every one minute depending on the criticality of the databases that you are going to monitor.

Use this Code if you wish to receive the e-mail in HTML format:

if(select count(*) from sys.databases where state_desc<>'Online')>0

Begin

DECLARE @table  NVARCHAR(MAX) ;

SET @table =

    N'<H1>Offline Databases Report</H1>' +

    N'<table border="1">' +

    N'<tr><th>Database Name</th><th>Database Status</th></tr>' +

    CAST ( ( Select td=name, '',td=state_desc from sys.databases where state_desc<>'Online'

              FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) )    +

    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @profile_name='ProfileName', --Change to your Profile Name

      @recipients='email@domain.com;email1@domain.com', --Put the email address of those who want to receive the e-mail

    @subject = 'Offline Databases Report',

    @body = @table,

    @body_format = 'HTML' ;

END

Else Print 'All Databases are Online'

 

Use this Code if you wish to receive the e-mail in Plain Text format:

 

if(select count(*) from sys.databases where state_desc<>'Online')>0

Begin

EXEC msdb.dbo.sp_send_dbmail @profile_name='ProfileName', --Change to your Profile Name

      @recipients='email@domain.com;email1@domain.com', --Put the email address of those who want to receive the e-mail

    @subject = 'Offline Databases Report',

    @Query='Select rtrim(ltrim(name)) as DatabaseName,rtrim(ltrim(state_desc)) as CurrentDatabaseStatus from sys.databases where state_desc<>''Online'''

    

END

Else Print 'All Databases are Online'

 

SQL DBA : SQL 2005 - Move C2 Trace file from one Location to another Location

Move C2 Trace file from one Location to another Location :-

Create a Job With the Option of “ActiveX Script” (Select VB Script)

dim TracePath         ‘Declaring Variable for Assigning the Source path

dim FName              ‘Declaring Variable for Assigning the File Name

TracePath="G:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" ‘Source path of Trace File

   Set fso = CreateObject("Scripting.FileSystemObject")  ‘Object Creation for File system 

   Set folder = fso.GetFolder(Left(TracePath,len(TracePath)-1)) ‘Object Creation for Folder Access 

   Set files = folder.Files                                     ‘Object Creation For File Access

i=0

 For each fileIdx In files                     ‘Loop For Accessing the Files in the Source Folder

 FName = fileIdx.Name

              if right(FName ,4) = ".trc" then  ‘Checking the file Extension of the Trace file

                 Redim Preserve allFiles(i) 

                 Redim Preserve allTimeStamps(i)

                 allFiles(i)=fileIdx.Name       ‘Storing a Filename in a Array

                 allTimeStamps(i)=fileIdx.DateLastModified ‘Storing the Datemodified value of file

                      i=i+1

              end if

 Next

               'Now sort as per Modified TimeStamp  

 for i =  lbound(allTimeStamps) to ubound(allTimeStamps)

       for j =  lbound(allTimeStamps) to ubound(allTimeStamps)

              if allTimeStamps(i)<allTimeStamps(j) then

                     tt=allTimeStamps(i)

                     allTimeStamps(i)=allTimeStamps(j)

                      allTimeStamps(j)=tt

                      tt=allFiles(i)

                     allFiles(i)=allFiles(j)

                     allFiles(j)=tt

              end if

       next

 next

for i =  lbound(allFiles) to ubound(allFiles) -1        ' -1 so that we leave the latest file

       fso.movefile TracePath&allFiles(i) ,"P:\Audit\"  ‘Move the trace to Destination Path

Next

set fso = Nothing     ‘Clearing the Created Objects

set folder = Nothing

Set files = Nothing

Finally you can create a Step for Success and failure status mail for Mail Alert