Archive

Archive for March, 2011

SQL Server Connection Encryption

March 17, 2011 Leave a comment

 

DBA struggles a lot to justify an Auditor that SQL Server Connections are encrypted. To prove the same I use my favorite tool Net Monitor to justify the same, but how SQL Server Encrypt it.

SQL Server always encrypts network packets associated with logging in. If no certificate has been provisioned on the server when it starts up, SQL Server generates a self-signed certificate which is used to encrypt login packets

To get more information check this out : http://msdn.microsoft.com/en-us/library/ms131691.aspx


Categories: SQL Server Encryption

Send Jobs Status Report using DB Mail as HTML

March 7, 2011 1 comment

 

It is really a herculean task to have a consolidated report for all the jobs status for one or more server. My much time is saved by this report

Steps to Create Report

Configure Database Mail Profile, which will be used to send mail.

  1. Create Table for Job History

    USE [msdb]

    GO

    /****** Object: Table [dbo].[JobHistory] ******/

    SET
    ANSI_NULLS
    ON

    GO

    SET
    QUOTED_IDENTIFIER
    ON

    GO

    SET
    ANSI_PADDING
    ON

    GO

    CREATE
    TABLE [dbo].[ServerJobHistory](

    [Job_ID] [uniqueidentifier] NOT
    NULL,

    [Job_Name] [sysname] NOT
    NULL,

    [Run_Date] [int] NOT
    NULL,

    [Run_Time] [int] NOT
    NULL,

    [Run_Duration] [int] NOT
    NULL,

    [Job_Outcome] [varchar](100)
    NOT
    NULL,

    [Step_Outcome] [varchar](250)
    NOT
    NULL

    )
    ON [PRIMARY]

    GO

    SET
    ANSI_PADDING
    OFF

    GO

  2. Create Stored Proc to pull Job Information from msdb Job Table into above created Table

     

USE [msdb]

GO

/****** Object: StoredProcedure [dbo].[sp_LoadJobHistory] Script Date: 03/07/2011 17:48:23 ******/

SET
ANSI_NULLS
ON

GO

SET
QUOTED_IDENTIFIER
ON

GO

ALTER
PROCEDURE [dbo].[sp_LoadJobHistory]

AS

    BEGIN

        SET
NOCOUNT
ON

        DECLARE @JobID UNIQUEIDENTIFIER

        DECLARE @JobName SYSNAME

        DECLARE @RunDate INT

        DECLARE @RunTime INT

        DECLARE @RunDuration INT

        DECLARE @RunStatus INT

        DECLARE @FailedSteps INT

        DECLARE @JobOutcome VARCHAR(100)

        DECLARE @StepOutcome VARCHAR(250)

        DECLARE JobHistoryCursor CURSOR
LOCAL
READ_ONLY
FOR

        SELECT J.job_id,

            J.[name],

            JH.[run_date],

            JH.[run_time],

            ((JH.[run_duration]/10000)*3600)
+
(((JH.[run_duration]/100)



(JH.[run_duration]/10000)
* 100)
* 60)
+


(JH.[run_duration]%100) [run_duration (sec)],

            JH.[run_status]

        FROM dbo.sysjobhistory JH

            JOIN dbo.sysjobs J

            ON J.job_id = JH.job_id

        WHERE JH.[job_id] IN
(

            SELECT [job_id] FROM dbo.sysjobs

        )

        AND
CONVERT(VARCHAR(10), JH.[run_date])
+
CONVERT(VARCHAR(10), JH.[run_time])
NOT
IN

        (

            SELECT
CONVERT(VARCHAR(10), IJH.[run_date])
+
CONVERT(VARCHAR(10), IJH.[run_time])

            FROM dbo.ServerJobHistory IJH

            WHERE IJH.Job_ID = JH.[job_id]

        )

        AND JH.[step_id] = 0

        OPEN JobHistoryCursor

        FETCH
NEXT
FROM JobHistoryCursor INTO @JobID, @JobName, @RunDate, @RunTime, @RunDuration, @RunStatus

        WHILE
@@FETCH_STATUS
= 0

        BEGIN

            SELECT @FailedSteps =
COUNT(*)

            FROM dbo.sysjobhistory H

            WHERE H.job_id = @JobID

            AND H.run_date = @RunDate

            AND H.run_time/100 = @RunTime/100

            AND H.step_id > 0

            AND H.run_status = 0

            IF (@RunStatus = 0)

                SELECT @JobOutcome =
‘Failed’, @StepOutcome = ‘One or more steps failed’

            ELSE
IF (@RunStatus = 1 AND @FailedSteps > 0)

                SELECT @JobOutcome =
‘Succeeded (with errors)’, @StepOutcome = ‘One or more steps failed’

            ELSE
IF (@RunStatus = 1 AND @FailedSteps = 0)

                SELECT @JobOutcome =
‘Succeeded’, @StepOutcome = ‘All steps succeeded’

            ELSE
IF (@RunStatus = 3)

                SELECT @JobOutcome =
‘Cancelled by user’, @StepOutcome =

            ELSE

                SELECT @JobOutcome =
‘Unknown or Retrying’, @StepOutcome = ‘Please check job history’

            INSERT
INTO dbo.ServerJobHistory
(Job_ID, Job_Name, Run_Date, Run_Time, Run_Duration, Job_Outcome, St
ep_Outcome)

                VALUES (@JobID, @JobName, @RunDate, @RunTime, @RunDuration, @JobOutcome, @StepOutcome)

            FETCH
NEXT
FROM JobHistoryCursor INTO @JobID, @JobName, @RunDate, @RunTime, @RunDuration, @RunStatus

        END

        CLOSE JobHistoryCursor

        DEALLOCATE JobHistoryCursor

    END

 

 

 

  1. Create Procedure to send Job Status in Mail using DB Mail
USE [msdb]

GO

SET
ANSI_NULLS
ON

GO

SET
QUOTED_IDENTIFIER
ON

GO

ALTER
PROCEDURE [dbo].[DBA_sp_JobHistoryMailer]

AS

BEGIN

    DECLARE @tableHTML NVARCHAR(MAX)

    SET @tableHTML =

        N'<html><body><Font Face=”Verdana” Size=4><b>Server Job History Report</b></Font><p>’
+

        N'<Font Face=”Verdana” Size=2>Report generated on: ‘
+
CONVERT(VARCHAR(11),
GETDATE(), 113)
+
‘</Font><br>’
+

        N'<Font Face=”Verdana” Size=2>This report shows yesterday”s history only.</Font><p>’
+

        N'<table border=”1″width=”100%”><tr bgcolor=”Black”>’
+

        N'<td><Font Face=”Verdana” Size=2 Color=”White”><b>Job Name</b></Font></td>’
+

        N'<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Date</b></Font></td>’
+

        N'<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Time</b></Font></td>’
+

        N'<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Duration (sec)</b></Font></td>’
+

        N'<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Status</b></Font></td>’
+

        cast
((SELECT

                td =
‘<Font Face=”Verdana” Size=2 Color=”Black”>’
+
CONVERT(VARCHAR(250), Job_Name)
+
‘</Font>’,,

                td =
‘<Font Face=”Verdana” Size=2 Color=”Black”>’
+
CONVERT(VARCHAR(250), Run_Date)
+
‘</Font>’,,

                td =
‘<Font Face=”Verdana” Size=2 Color=”Black”>’
+
CONVERT(VARCHAR(250), Run_Time)
+
‘</Font>’,,

                td =
‘<Font Face=”Verdana” Size=2 Color=”Black”>’
+
CONVERT(VARCHAR(250), Run_Duration)
+
‘</Font>’,,

                Case Job_Outcome

                    WHEN
‘Succeeded’
THEN
‘<Font Face=”Verdana” Size=2 Color=”Green”>’
+
CONVERT(VARCHAR(250), Job_Outcome)
+
‘</Font>’

                    WHEN
‘Failed’
THEN
‘<Font Face=”Verdana” Size=2 Color=”Red”>’
+
CONVERT(VARCHAR(250), Job_Outcome)
+
‘</Font>’

                    ELSE
‘<Font Face=”Verdana” Size=2 Color=”Orange”>’
+
CONVERT(VARCHAR(250), Job_Outcome)
+
‘</Font>’

                END
as td

                     FROM dbo.ServerJobHistory

         WHERE Run_Date =
REPLACE(CONVERT(VARCHAR(10),
DATEADD(dd, 0, GETDATE()), 121),
‘-‘,)

         ORDER
BY Job_Name

     FOR
XML
PATH(‘tr’),
TYPE
)
AS
NVARCHAR(MAX)
)
+
N'</table></body></html>’

     SET @tableHtml =
REPLACE(REPLACE(@tableHtml,‘&gt;’,‘>’),‘&lt;’,‘<‘)

    SELECT @tableHtml

    IF (ISNULL(@tableHtml,
‘NULL’))
!= ‘NULL’

        BEGIN

            EXEC msdb.dbo.sp_send_dbmail
@profile_name =
‘DBProfileName’,

                @recipients=‘xyz@xyz.com’,

                @subject=‘Job History Report’,

                @body=@tableHTML,

                @body_format=‘HTML’

        END

END

 

 

 

Report Looks as below

 

Categories: SQL Scripts

How to Check SQL Server is facing Memory Pressure using windows Performance Monitor

March 2, 2011 3 comments

 

It is really a good experience to figure out memory change which could be possibly memory pressure SQL Server is facing. I will demonstrate how to figure out memory pressure in SQL Server using Windows Perfmon.

Before I start demonstration on figuring out memory pressure, DBA need to have deep understanding of memory Configuration with SQL Server with Standalone instance and Multiple Instance on the same Machine. Don’t ignore memory configuration for the Operating System (OS).

System can face serious problem if enough memory is not given to the Operating System.

Example: System Shutdown due to not enough memory (unexpected shutdown) which lead to Production hamper.

 

Perfmon Counter to Capture Memory Utilization

SQL Server: Memory Manager: Total Server Memory

SQL Server: Memory Manager: Target Server Memory

Process: Working Set à Instance àSqlservr

Memory: System Cache Resident Bytes

Memory: % Committed Bytes in Use

Memory: Available Mbytes

 

Process: Working Set. This is the current size of the memory area that the process is utilizing for code, threads, and data. The size of the working set will grow and shrink as the VMM can permit. When memory is becoming scarce the working sets of the applications will be trimmed. When memory is plentiful the working sets are allowed to grow. Larger working sets mean more code and data in memory making the overall performance of the applications increase. However, a large working set that does not shrink appropriately is usually an indication of a memory leak.

If you see the values of working set being trimmed it indicates a Memory Pressure or Leak. Look into SQL Server Error Log, SQL server Agent Error Log, In case u did not find anything, check for Jobs if they fail withl Memory Issues (Out of Memory).

 

Externally:

Physical memory (RAM) running low (Memory Trimmed). This causes the system to trim working sets of currently running processes, which can result in overall slowdown.

 

SQL Server detects this condition and, depending on the configuration, it might reduce the commit target of the buffer pool and start clearing internal caches.

Internally:

SQL Server detects high memory consumption internally, causing redistribution of memory between internal components.

 

Internal memory pressure may be a result of:

  • Responding to the external memory pressure (SQL Server sets lower memory usage caps).
  • Changed memory settings (for example, max server memory).
  • Changes in memory distribution of internal components.
Categories: Memory