Archive

Archive for the ‘SQL Scripts’ Category

Rebuild Database Indexes

November 11, 2014 Leave a comment

The below script will rebuild all indexes with in a database

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = ‘BASE TABLE’ and table_name not in (‘sysdiagrams’)

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Change Database Ownership

November 11, 2014 Leave a comment

The most common error dba do after restoring the database is they forget to change the database ownership.

Its really important to have the database ownership set to a user which will always be available to the SQL Server Instance

Script to change the database ownership for all the database on a instance

EXEC sp_MSforeachdb ‘EXEC [?]..sp_changedbowner ”sa”’

Change database ownership for single database

use <database_name>
go
sp_changedbowner ‘sa’

Using PowerShell

image



param([string] $Sqlserver) 

if (-not (Get-Module SQLPS)) 
{Import-Module SQLPS -WarningAction SilentlyContinue -DisableNameChecking}

foreach($_sqlinst in $Sqlserver.Split(','))
    {
        if ($_sqlinst -match "\\")
            {
                Write-Host "SQL Server Instance : "$_sqlinst
            cd c:\
                $_pspath="SQLSERVER:\SQL\" + $_sqlinst + "\Databases"    
                cd $_pspath -WarningAction SilentlyContinue
                DIR |Where-Object {$_.Status -eq "Normal"}| foreach-object {$_.SetOwner('sa'); $_.Refresh()}
            }
        else
            {
                Write-Host "SQL Server Instance : "$_sqlinst
            cd c:\
                $_pspath="SQLSERVER:\SQL\" + $_sqlinst + "\default" +"\Databases"
                cd $_pspath -WarningAction SilentlyContinue
                DIR |Where-Object {$_.Status -eq "Normal"}| foreach-object {$_.SetOwner('sa'); $_.Refresh()}
            }            
    }

sp_whoisactive cutomization

November 9, 2014 Leave a comment

We all mostly know about WhoIsActive custom stored procedure which give a full view of running sessions and much more based on parameter values.

Attached is the WhoIsActive_1 code in which I have done a bit of customization to have little more information

 

How to execute the Stored Procedure?

Create the procedure in master database.Execute the procedure from Query Analyser as below.

Code Sample :
use master
go
dbo.sp_WhoIsActive1 @get_plans=1, @get_transaction_info =1,@find_block_leaders=1,@get_locks=1

Attachment :WhoIsActive_1_Code

Categories: SQL Scripts, Uncategorized

Convert Varbinary to Varchar data type

November 27, 2012 Leave a comment

 

Covert varbinary data type to Varchar

Script:

 

SELECT
thread_address,CONVERT(VARCHAR(MAX),thread_address,1) thread_address_converted
FROM
sys.dm_os_threads

 

 

 

 

Categories: SQL Scripts

Reading Error Log (Parameterized)

November 15, 2012 Leave a comment

Read specific date, time and text error log. This help while troubleshooting issue on a highly transaction SQL Server.

SET DATEFORMAT DMY

DECLARE @StartDateTime Varchar(30)=’15/11/2012 23:40:00′
DECLARE @EndDateTime Varchar(30)=’29/11/2012 23:59:00′
DECLARE @SearchString nvarchar(MAX)=”

DECLARE @ReadCurrentSQLErrorLog TABLE
(LogDate DATETIME,
ProcessInfo VARCHAR(20),
Text VARCHAR(500)
)

INSERT INTO @ReadCurrentSQLErrorLog
EXEC master.dbo.xp_readerrorlog 0

SELECT * FROM @ReadCurrentSQLErrorLog
WHERE LogDate >= CONVERT(datetime,@StartDateTime)
AND LogDate < CONVERT(datetime,@EndDateTime)
AND text like ‘%’+@SearchString+’%’
ORDER BY LogDate DESC

You can also use
xp_readerrorlog 0, 1,NULL, NULL, ’12-11-2012 15:40:00′, ’12-11-2012 23:40:00′, N’asc’

Categories: SQL Scripts

Get Installed SQL Server Instance on a Server

September 13, 2011 Leave a comment

 

Developed the script to get installed SQL Server Instance on a Server. To run this script user should have admin privileges or minimum permission to read OS registry on the server.

DECLARE @Registry_Value VARCHAR(1000)

DECLARE @SQL_In AS
TABLE (Id NVARCHAR(MAX),InsName NVARCHAR(25))

 

INSERT
INTO @SQL_In(Id,InsName)

EXECUTE
xp_regread
‘HKEY_LOCAL_MACHINE’,‘SOFTWARE\Microsoft\Microsoft SQL Server’,‘InstalledInstances’,

@Registry_Value OUTPUT

 

SELECT
@@ServerName [Server Name],InsName [Installed SQL Instance] FROM @SQL_In

 

Categories: SQL Scripts

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