Long Running Queries Alert

use master
go

/*
exec sp_long_running_queries @longduration = 15 , @sendalertemail = 1
*/
ALTER procedure sp_long_running_queries
(
@longduration int = 15 -- minutes
, @sendalertemail int = 0
)
as
BEGIN

CREATE TABLE #_tmp_whoisactive
( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[CPU] varchar(30) NULL,[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,[reads] varchar(30) NULL,[writes] varchar(30) NULL,[physical_reads] varchar(30) NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL)

exec sp_WhoIsActive @destination_table = '#_tmp_whoisactive'

--select datediff(mi,start_time,collection_time) , * from #_tmp_whoisactive where datediff(mi,start_time,collection_time) > @longduration

if @sendalertemail = 1 and exists ( select * from #_tmp_whoisactive where datediff(mi,start_time,collection_time) > @longduration)
begin

DECLARE @tableHTML  NVARCHAR(MAX) , @subjectstring nvarchar(200);

SET @tableHTML =
N'

Long Runing Queries on '+@@SERVERNAME+' executing over '+convert(nvarchar(10),@longduration)+' minutes

' +
N'
' +
N'
' +
N'
' +
N'
' +
CAST ( ( SELECT td = [dd hh:mm:ss.mss],       '',
td = session_id, '',
td = login_name, '',
td = database_name, '',
td = host_name, '',
td = Status
 FROM #_tmp_whoisactive
 where datediff(mi,start_time,collection_time) > @longduration
 FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'

Time Elapsed	Session	Login Name	Database	Executing Host	Status

' ;

--print @tableHTML
 
set @subjectstring = 'Long Running Queries over '+convert(nvarchar(10),@longduration)+' min on SQL Server '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'DLProductionDBA@cornerstoneondemand.com;jferris@csod.com',
--@recipients ='palexander@csod.com',
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subjectstring;
 
end

drop table #_tmp_whoisactive

END
GO

If Primary Key Exists

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'user_career_pref_date' AND TABLE_SCHEMA ='dbo'

if not exists(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'user_career_pref_date' AND TABLE_SCHEMA ='dbo' )

begin

ALTER TABLE [dbo].[user_career_pref_date]

    ADD CONSTRAINT [PK_user_career_pref_date] PRIMARY KEY CLUSTERED ([user_id] ASC, [question_id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

end

Proc to show expensive queries : sp_expensive_queries

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_expensive_queries]
as
BEGIN


SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

SELECT TOP 10
[Total Logical Reads] = qs.total_logical_reads,
[Last Logical Reads] = qs.last_logical_reads,
[Total Logical Writes] = qs.total_logical_writes,
[Last Logical Writes] = qs.last_logical_writes,
[Execution count] = qs.execution_count,
[Individual Query] = SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
[Parent Query] = qt.text,
DatabaseName = DB_NAME(qt.dbid),
[Total Worker Time] = qs.total_worker_time,
[Last Worker Time] = qs.last_worker_time,
[Total Elapsed Time Seconds] = qs.total_elapsed_time/1000000 ,
[Last Elapsed Time Seconds] = qs.last_elapsed_time/1000000 ,
[Last Execution Time] = qs.last_execution_time,
[Query Plan] = qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC

SELECT TOP 10
[Total Reads] = SUM(total_logical_reads)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;

END

Automate Monitoring SQL Server Error Logs with Email Alerts

Automate Monitoring SQL Server Error Logs with Email Alerts

Get Alerts for Specific SQL Server Login Failed Events

Get Alerts for Specific SQL Server Login Failed Events

Script to configure SQL database Mail

USE master;
GO
exec sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
exec sp_configure 'Database Mail XPs','1'
RECONFIGURE WITH OVERRIDE;
GO

use msdb
go
exec sysmail_start_sp

/*
exec msdb.dbo.sysmail_delete_profile_sp @profile_name = 'default' ;
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'default' ;
*/  
 
DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
   @display_name NVARCHAR(128);

-- Profile name. Replace with the name for your profile
        SET @profile_name = 'default';

-- Account information. Replace with the information for your account.

SET @account_name = 'default';
SET @SMTP_servername = '10.11.150.25';
SET @email_address = REPLACE(@@servername,'-','')+'@csod.com';
        SET @display_name = REPLACE(@@servername,'-','');


-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
  RAISERROR('The specified Database Mail profile () already exists.', 16, 1);
  GOTO done;
END;

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
 RAISERROR('The specified Database Mail account () already exists.', 16, 1) ;
 GOTO done;
END;

-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;

DECLARE @rv INT;

-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
    @account_name = @account_name,
    @email_address = @email_address,
    @display_name = @display_name,
    @mailserver_name = @SMTP_servername;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail account ().', 16, 1) ;
    GOTO done;
END

-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
    @profile_name = @profile_name ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail profile ().', 16, 1);
ROLLBACK TRANSACTION;
    GOTO done;
END;

-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @profile_name,
    @account_name = @account_name,
    @sequence_number = 1 ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to associate the speficied profile with the specified account ().', 16, 1) ;
ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'public',
    @profile_name = 'default',
    @is_default = 1 ;
GO


EXEC msdb.dbo.sp_send_dbmail @recipients = 'palexander@csod.com', @subject='test', @body='test', @body_format = 'TEXT'

/*

http://technet.microsoft.com/en-us/library/ms177580(v=sql.90).aspx

*/

sp_job_steps_history : Job History for Steps

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[sp_job_steps_history]    Script Date: 02/08/2012 08:13:52 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_job_steps_history]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_job_steps_history]
GO


/*
--all failed steps
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = 0 , @stepid = null

--all failed specific steps
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = 0 , @stepid = 15

--outcome of the specific step for all jobs all dates
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = null , @stepid = 15

--outcome of the specific step for all jobs specific date
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = null , @stepid = 15

--all failed steps for given day
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = 0 , @stepid = null

--all failed steps for given day specific step
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = 0 , @stepid = 13

--all failed steps for given job specific date
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = 20120208, @runstatus = 0 , @stepid = null

--all failed steps for given job all dates
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = null, @runstatus = 0 , @stepid = null

--specific job status for given dae
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = 20120208, @runstatus = null , @stepid = null

*/
CREATE PROCEDURE dbo.sp_job_steps_history
(
@jobname nvarchar(256) = null
,@rundate int = null
,@runstatus int = 0
,@stepid int = null
)
as
BEGIN
/*
@runstatus:

0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
*/

/*
set @jobname = null
set @rundate = null
set @runstatus = 3
*/

SELECT j.name JobName,h.step_id, h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
where (j.name like @jobname or @jobname is null)
and (h.run_date = @rundate or @rundate is null)
and (h.run_status = @runstatus or @runstatus is null)
and (h.step_id = @stepid or @stepid is null)
ORDER BY j.name, h.run_date, h.run_time

END
GO

What's the Point of Using VARCHAR(n) Anymore?

What's the Point of Using VARCHAR(n) Anymore?

SQL Orphan users

select u.name , 'exec sp_revokedbaccess '''+u.name+''''
from master..syslogins l right join
    sysusers u on l.sid = u.sid
    where l.sid is null and issqlrole <> 1 and isapprole <> 1  
    and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
    and u.name <> 'system_function_schema')
   

Win 2008 Run As

Create a batch file that has the following on SQL server:

cd\
cd C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE
runas /user:csodmgmt\palexander ssms