Finding most used alerts and monitors to override

I had a requirement from one of the teams that uses SCOM monitoring – the requirement was to change alerting level on scom alerts from critical to warning for all servers that were not on production for BizTalk management pack. Now there are several rules and monitors related to this management pack – or in general any one management pack – there is no quick and easy way to override all of them. To do so manually would be very cumbersome and time consuming. Instead – a better way to approach this would be to find rules and monitors that generate the most alerts and override them instead. This would create the override for most relevant rules and monitors – the others can be overridden as and when they happen. The query I used to find most used rules and monitors is as below. I wanted the BizTalk management pack – this can be substituted with any other as necessary.

 

Use OperationsManagerDW

SELECT

alt.AlertName,

COUNT(*) AS AlertRepeated

FROM Alert.vAlertResolutionState AS ars INNER JOIN

Alert.vAlertDetail AS adt ON ars.AlertGuid=adt.AlertGuid INNER JOIN

Alert.vAlert AS alt ON ars.AlertGuid=alt.AlertGuid INNER JOIN

vManagedEntity ON alt.ManagedEntityRowId=vManagedEntity.ManagedEntityRowId

WHERE

alt.AlertName like ‘%biztalk%’

GROUP BY alt.AlertName

ORDER BY count(*) desc

Advertisements

What it took to stump the optimizer

Sometime ago I was working with a Microsoft support technician on a ticket related to some database issues on SCOM. The technician wanted to run a stored procedure to clear out some of the backlog we were seeing with a batch job.

When he ran the procedure (via Query Analyser) – we encountered an error as below. Basically an error that is wee bit rare, the optimizer telling us that it is stumped and the query is too big for it to process. The technician moved on to other ways of resolving the issue as fixing this problem involved touching some procedures that came with the product and was beyond the scope of our call. But after we were done, I was intrigued to find out what caused the optimizer to give up.scomoptimiser

I went to Line 177 of the specified stored procedure and found a statement that looked pretty small on the outside:

DELETE dbo.[CategoryOverride]

FROM dbo.[CategoryOverride] CO

JOIN #BaseManagedEntitiesToPurge BMETP

ON CO.[InstanceContext] = BMETP.[BaseManagedEntityId]

The table CategoryOverride had about 300 rows..and  on checking what went into the temp table – it wasn’t all that large either, only around 100 rows. Looking at the estimated plan from the optimizer gave me nothing – a perfectly ordinary plan with an equijoin on a clustered index. So what was causing the problem?

I looked to see if the table CategoryOverride had any triggers on it. None. Then I went on to looking at keys. The table had one foreign key defined on it.

scomo1

I decided to script this key and see what it showed me.

ALTER TABLE [dbo].[CategoryOverride] WITH CHECK ADD CONSTRAINT [FK_CategoryOverride_ManagementPack] FOREIGN KEY([ManagementPackId])

REFERENCES [dbo].[ManagementPack] ([ManagementPackId])

ON DELETE CASCADE

GO

There was my culprit – an ON DELETE CASCADE. So many other tables is this touching? A quick look at the dependancies on this table showed me no less than 14. For one record to be deleted – there were dependancies on 14 tables. I picked one record on the main table and counted how many dependant records were there to clean up. It was about 6000. So the delete statement was far from a benign straightforward delete – it had a huge amount of data to clean up and the optimizer could not generate a plan across 14 tables some of them having more cascade deletes on them.

Moral of the story – never assume the simplicity of a statement based on looking at it, or what the query optimizer tells you. The truth may be much deeper. If I had the choice of fixing it I would remove the cascade delete and go on to cleaning up data step-by-step in each of the dependant tables.

Tail of the log has not been backed up

This is the second post in my attempt to blog one little thing every day. Today I ran into this message when trying to restore a database over an existing database. I forgot to check the box that says ‘overwrite existing database’ and got this rather informative error. I love this error message since it tells exactly what the user should do

‘The tail of the log of the database <mydatabase< has not been backed up – Meaning there are transactions in the log that need to be backed up.

‘USE BACKUPLOG WITH NORECOVERY’ if you want to backup the log and it contains work you don’t want to lose. ‘- It tells exactly what should user do in case he needs the older copy.

‘USE WITH REPLACE OR WITH STOPAT CLAUSE OF THE RESTORE STATEMENT TO JUST OVERWRITE THE CONTENTS OF THE LOG’ – in other words if am using the GUI I better check the box to tell sQL Server to overwrite the existing database, if that is what I intend to do.

This message tells me what is wrong and also evaluates the two possibilities – I want to keep or discard the old copy of the database, and tells me what to do in both situations. It is an excellent example of user friendly error messages.

blogpost

 

 

T-SQL Tuesday #025 – Invitation to Share Your Tricks

This is my first response to a TSQL Tuesday.

There are lot of things in TSQL that can be done to make DBA’s life easier. We have a large collection of scripts. These are some I have used over the years at multiple jobs – there are several more but I don’t particularly want to write a very long post. Also a disclaimer that I did not write all of them.  I wrote some,  I tweaked some I found on sites like SQLServerCentral.com that have come in very handy. I apologize ahead for not offering credit since I have not kept track of the authors.

–Find the last server start date(2005)
SELECT crdate AS Datetime_Started FROM sysdatabases WHERE name = ‘tempdb’
 
1 –Find Blank passwords
SELECT name FROM sys.syslogins WHERE pwdcompare(””, password) = 1

2 –Find members of a role (for example, public)
SELECT a.[name] + ‘ ‘ + v.[name] + ‘ ON ‘ + QuoteName(oo.[name]) + ‘.’ + QuoteName(o.[name]) + ‘ TO ‘ + QuoteName(u.[name])
FROM dbo.sysprotects AS p JOIN master.dbo.spt_values AS a
ON (a.number = p.protecttype AND ‘T’ = a.type) JOIN master.dbo.spt_values
AS v ON (v.number = p.action AND ‘T’ = v.type) JOIN dbo.sysobjects AS o ON (o.id = p.id)
JOIN dbo.sysusers AS oo ON (oo.uid = o.uid) JOIN dbo.sysusers AS u ON (u.uid = p.uid) WHERE ‘public’ = u.name

3 –Find number of times a stored procedure was used since last reboot
SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = ‘proc’
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid),
   OBJECT_NAME(objectid,st.dbid)
 order by max(cp.usecounts)

4 –Stick in anything to be changed across all dbs (just used sp_dboption with read only here)
SET NOCOUNT ON

— Get the name of all databases
DECLARE UserDatabases CURSOR FOR

SELECT name FROM master..sysdatabases
   where name not in (‘master’,’tempdb’, ‘model’, ‘msdb’, ‘distribution’)

— Open Cursor
OPEN UserDatabases

— Define variables needed
DECLARE @DBase NVARCHAR(128)
DECLARE @COMMAND NVARCHAR(128)

— Get First database
FETCH NEXT FROM USerDatabases INTO @DBase

— Process until no more databases
WHILE (@@FETCH_STATUS = 0)
BEGIN

 set @command =’master..sp_dboption @dbname=”’ + @dbase +
                ”’,@optname=”Read ONLY”, @optvalue=”True”’

— Print command to be processed
  print @command

— Process Command
  exec (@command)

— Get next database
  FETCH NEXT FROM UserDatabases INTO @DBase

END

— Close and Deallocate Cursor
CLOSE AllDatabases
DEALLOCATE AllDatabases

5 —-My favorite, lists job calender

USE MSDB

DECLARE

@JOB_ID VARCHAR(200),

@SCHED_ID VARCHAR(200),

@FREQ_TYPE INT,

@FREQ_INTERVAL INT,

@FREQ_SUBDAY_TYPE INT,

@FREQ_SUBDAY_INTERVAL INT,

@FREQ_RELATIVE_INTERVAL INT,

@FREQ_RECURRENCE_FACTOR INT,

@ACTIVE_START_DATE INT,

@SCHEDULE VARCHAR(1000),

@SCHEDULE_DAY VARCHAR(200),

@START_TIME VARCHAR(10),

@END_TIME VARCHAR(10)

CREATE TABLE #SCHEDULES

(JOB_ID VARCHAR(200),

SCHED_ID VARCHAR(200),

JOB_NAME SYSNAME,

[STATUS] INT,

SCHEDULED INT NULL,

schedule VARCHAR(1000) NULL,

FREQ_TYPE INT NULL,

FREQ_INTERVAL INT NULL,

FREQ_SUBDAY_TYPE INT NULL,

FREQ_SUBDAY_INTERVAL INT NULL,

FREQ_RELATIVE_INTERVAL INT NULL,

FREQ_RECURRENCE_FACTOR INT NULL,

ACTIVE_START_DATE INT NULL,

ACTIVE_END_DATE INT NULL,

ACTIVE_START_TIME INT NULL,

ACTIVE_END_TIME INT NULL,

DATE_CREATED DATETIME NULL)

INSERT INTO #SCHEDULES (

job_id,

sched_id ,

job_name ,

[status] ,

Scheduled ,

schedule ,

freq_type,

freq_interval,

freq_subday_type,

freq_subday_interval,

freq_relative_interval,

freq_recurrence_factor,

active_start_date,

active_end_date,

active_start_time,

active_end_time,

date_created)

SELECT

j.job_id,

sched.schedule_id,

j.name ,

j.enabled,

sched.enabled,

NULL,

sched.freq_type,

sched.freq_interval,

sched.freq_subday_type,

sched.freq_subday_interval,

sched.freq_relative_interval,

sched.freq_recurrence_factor,

sched.active_start_date,

sched.active_end_date,

sched.active_start_time,

sched.active_end_time,

j.date_created

FROM sysjobs j

inner join

sysjobschedules s

ON j.job_id=s.job_id

INNER JOIN dbo.sysschedules sched

ON s.schedule_id = sched.schedule_id

WHILE 1=1

BEGIN

SET @SCHEDULE = ”

IF (SELECT COUNT(*) FROM #SCHEDULES WHERE scheduled=1 and schedule is null) = 0

BREAK

ELSE

BEGIN

SELECT

@job_id=job_id,

@sched_id=sched_id,

@freq_type=freq_type,

@Freq_Interval=freq_interval,

@freq_subday_type=freq_subday_type,

@freq_subday_interval=freq_subday_interval,

@freq_relative_interval=freq_relative_interval,

@freq_recurrence_factor=freq_recurrence_factor,

@active_start_date = active_start_date,

@start_time =

CASE

WHEN

LEFT(active_start_time, 2) IN (22, 23) AND len(active_start_time) = 6

THEN

convert(varchar(2), left(active_start_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ P.M’

WHEN

left(active_start_time, 2) = (12) AND len(active_start_time) = 6

THEN

cast(LEFT(active_start_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_start_time, 2) BETWEEN 13 AND 24 AND len(active_start_time) = 6

THEN

convert(varchar(2), left(active_start_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_start_time, 2) IN (10, 11) AND len(active_start_time) = 6

THEN

cast(LEFT(active_start_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ A.M.’

WHEN

active_start_time = 0

THEN

’12:00 A.M.’

WHEN

LEN(active_start_time) = 4

THEN

’12:’ + convert(varchar(2), left(active_start_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_start_time) = 3

THEN

’12:0′ + convert(varchar(2), left(active_start_time, 1) ) + ‘ A.M.’

WHEN

LEN(active_start_time) = 2

THEN

’12:00:’ + convert(varchar(2), left(active_start_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_start_time) = 1

THEN

’12:00:0′ + convert(varchar(2), left(active_start_time, 2) ) + ‘ A.M.’

ELSE

cast(LEFT(active_start_time,1) as char(1))

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),2, 2) + ‘ A.M.’

END,

@END_TIME= CASE

WHEN

left(active_end_time, 2) IN (22, 23) AND len(active_end_time) = 6

THEN

convert(varchar(2), left(active_end_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ P.M’

WHEN

left(active_end_time, 2) = (12) AND len(active_end_time) = 6

THEN

cast(LEFT(active_end_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_end_time, 2) BETWEEN 13 AND 24 AND len(active_end_time) = 6

THEN

convert(varchar(2), left(active_end_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_end_time, 2) IN (10, 11) AND len(active_end_time) = 6

THEN

cast(LEFT(active_end_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ A.M.’

WHEN

active_end_time = 0

THEN

’12:00 A.M.’

WHEN

LEN(active_end_time) = 4

THEN

’12:’ + convert(varchar(2), left(active_end_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_end_time) = 3

THEN

’12:0′ + convert(varchar(2), left(active_end_time, 1) ) + ‘ A.M.’

WHEN

LEN(active_end_time) = 2

THEN

’12:00:’ + convert(varchar(2), left(active_end_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_end_time) = 1

THEN

’12:00:0′ + convert(varchar(2), left(active_end_time, 2) ) + ‘ A.M.’

ELSE

cast(LEFT(active_end_time,1) as char(1))

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),2, 2) + ‘ A.M.’

END

FROM #SCHEDULES

WHERE schedule is null

AND scheduled=1

 
IF EXISTS(SELECT @freq_type WHERE @freq_type in (1,64))

BEGIN

SELECT @SCHEDULE = CASE @freq_type

WHEN 1 THEN ‘Occurs Once, On ‘+cast(@active_start_date as varchar(8))+’, At ‘+@start_time

WHEN 64 THEN ‘Occurs When SQL Server Agent Starts’

END

END

ELSE

BEGIN

IF @freq_type=4

BEGIN

SELECT @SCHEDULE = ‘Occurs Every ‘+cast(@freq_interval as varchar(10))+’ Day(s)’

END

IF @freq_type=8

BEGIN

SELECT @SCHEDULE = ‘Occurs Every ‘+cast(@freq_recurrence_factor as varchar(3))+’ Week(s)’

SELECT @schedule_day=”

IF (SELECT (convert(int,(@freq_interval/1)) % 2)) = 1

select @schedule_day = @schedule_day+’Sun’

IF (SELECT (convert(int,(@freq_interval/2)) % 2)) = 1

select @schedule_day = @schedule_day+’Mon’

IF (SELECT (convert(int,(@freq_interval/4)) % 2)) = 1

select @schedule_day = @schedule_day+’Tue’

IF (SELECT (convert(int,(@freq_interval/8)) % 2)) = 1

select @schedule_day = @schedule_day+’Wed’

IF (SELECT (convert(int,(@freq_interval/16)) % 2)) = 1

select @schedule_day = @schedule_day+’Thu’

IF (SELECT (convert(int,(@freq_interval/32)) % 2)) = 1

select @schedule_day = @schedule_day+’Fri’

IF (SELECT (convert(int,(@freq_interval/64)) % 2)) = 1

select @schedule_day = @schedule_day+’Sat’

SELECT @SCHEDULE = @SCHEDULE+’, On ‘+@schedule_day

END

IF @freq_type=16

BEGIN

SELECT @SCHEDULE = ‘Occurs Every ‘+cast(@freq_recurrence_factor as varchar(3))+’ Month(s) on Day ‘+cast(@freq_interval as varchar(3))+’ of that Month’

END

IF @freq_type=32

BEGIN

SELECT @SCHEDULE = CASE @freq_relative_interval

WHEN 1 THEN ‘First’

WHEN 2 THEN ‘Second’

WHEN 4 THEN ‘Third’

WHEN 8 THEN ‘Fourth’

WHEN 16 THEN ‘Last’

ELSE ‘Not Applicable’

END

SELECT @SCHEDULE =

CASE @freq_interval

WHEN 1 THEN ‘Occurs Every ‘+@SCHEDULE+’ Sunday of the Month’

WHEN 2 THEN ‘Occurs Every ‘+@SCHEDULE+’ Monday of the Month’

WHEN 3 THEN ‘Occurs Every ‘+@SCHEDULE+’ Tueday of the Month’

WHEN 4 THEN ‘Occurs Every ‘+@SCHEDULE+’ Wednesday of the Month’

WHEN 5 THEN ‘Occurs Every ‘+@SCHEDULE+’ Thursday of the Month’

WHEN 6 THEN ‘Occurs Every ‘+@SCHEDULE+’ Friday of the Month’

WHEN 7 THEN ‘Occurs Every ‘+@SCHEDULE+’ Saturday of the Month’

WHEN 8 THEN ‘Occurs Every ‘+@SCHEDULE+’ Day of the Month’

WHEN 9 THEN ‘Occurs Every ‘+@SCHEDULE+’ Weekday of the Month’

WHEN 10 THEN ‘Occurs Every ‘+@SCHEDULE+’ Weekend Day of the Month’

END

END

SELECT @SCHEDULE =

CASE @freq_subday_type

WHEN 1 THEN @SCHEDULE+’, At ‘+@start_time

WHEN 2 THEN @SCHEDULE+’, every ‘+cast(@freq_subday_interval as varchar(3))+’ Second(s) Between ‘+@start_time+’ and ‘+@END_TIME

WHEN 4 THEN @SCHEDULE+’, every ‘+cast(@freq_subday_interval as varchar(3))+’ Minute(s) Between ‘+@start_time+’ and ‘+@END_TIME

WHEN 8 THEN @SCHEDULE+’, every ‘+cast(@freq_subday_interval as varchar(3))+’ Hour(s) Between ‘+@start_time+’ and ‘+@END_TIME

END

END

END

UPDATE #SCHEDULES

SET schedule=@SCHEDULE

WHERE job_id=@job_id

AND sched_id=@sched_Id

END

SELECT job_name ,

[status] = CASE STATUS

WHEN 1 THEN ‘ENABLED’

WHEN 0 THEN ‘DISABLED’

ELSE ‘ ‘

END,

scheduled= case scheduled

when 1 then ‘Yes’

when 0 then ‘No’

else ‘ ‘

end,

schedule as ‘Frequency’ ,

convert(datetime, convert(varchar,active_start_date, 101)) AS schedule_start_date,

convert(datetime, convert(varchar,active_end_date, 101)) AS schedule_end_date,

date_created

FROM #schedules

WHERE scheduled=1

ORDER BY job_name

DROP TABLE #schedules

I hope you all find something useful. And thank you to Allen White for hosting.

SQL Server 2012 – Changes to set up

I plan to write a series of posts while learning more on SQL Server 2012. The changes to set up as I observed while installing SQL Server 2012 RC0 are :

1 Pre requisites include Powershell 2.0 and .NET Framework 3.5
2 If you have Windows 7/Windows Server 8/Office 2010 it expects Service Pack 1 to be installed (does not even proceed until pre requisites if not).

The install is a slipstream process – by that all the necessary pre requisites are part of the media, there is no need to go to separate sites to download/install.
3 There is no reboot required upon install (I only tested it as standalone, with all other programs closed – this may not be true in a cluster install).

4 32 bit install is still supported.
5 Support for Itanium appears to have been removed.

Acknowledgement:

I was inspired to learn more and write by Aaron Bertrand’s presentation on New Features at the PASS conference 2011(http://sqlblog.com/blogs/aaron_bertrand/archive/2011/08/07/sql-server-v-next-denali-changes-to-system-objects-in-ctp3.aspx)