How to Delete Maintenance Plan in SQL 2005
Step 1:
DELETE FROM MSDB..SYSMAINTPLAN_PLANS
Step 2:
DELETE FROM SYSMAINTPLAN_LOG
Step 3:
DELETE FROM SYSMAINTPLAN_SUBPLANS
Step 4:
DELETE FROM SYSMAINTPLAN_PLANS
Highest IO Queries in SQL Server
SELECT TOP 50
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],
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)
as query_text,
qt.dbid,
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
ORDER BY [Avg IO] DESC
SQL Server Performance Counters Script
–============================================
–== PERFORMANCE COUNTERS
–== AND
–== PERFORMANCE ANALYSIS
–============================================
select ‘–where b.CounterName = ”’ + CounterName + ”” from CounterDetails
–copy and paste result int oquery below
GO
select
a.CounterDateTime,
a.CounterValue,
b.CounterName,
b.InstanceName
from CounterData a inner join CounterDetails b
on a.CounterID = b.CounterID
–where b.CounterName = ‘Buffer cache hit ratio’
–where b.CounterName = ‘Page life expectancy’
–where b.CounterName = ‘Full Scans/sec’
–where b.CounterName = ‘Active Temp Tables’
–where b.CounterName = [...]
SQL Server assemblies
sp_msforeachdb ‘use [?]; SELECT * FROM sys.assemblies’
sp_msforeachdb ‘use [?]; SELECT * FROM sys.assembly_files’
Analyze Locks and Locking in SQL Server 2000
SELECT
locktype = CASE
WHEN master..syslocks.type = 1 THEN ‘Exclusive table lock’
WHEN master..syslocks.type = 2 THEN ‘Shared table lock’
WHEN master..syslocks.type = 3 THEN ‘Exclusive intent lock’
WHEN master..syslocks.type = 4 THEN ‘Shared intent lock’
WHEN master..syslocks.type = 5 THEN ‘Exclusive page lock’
WHEN master..syslocks.type = 6 THEN ‘Shared page lock’
WHEN master..syslocks.type = 7 THEN ‘Update page lock’
WHEN master..syslocks.type = 8 [...]
How to monitor blocking in SQL Server 2000 and 2005 using sp_blocker_pss08 script
sp_blocker_pss08 is a stored procedure useful to perform blocking analysis.
http://support.microsoft.com/kb/271509/
Kill Blocked Processes - SPIDs
Useful script to kill all blocked process.
select * from sysprocesses where blocked > 0 and spid <> blocked
declare @cmd nvarchar(800)
declare @spid int
AGAIN:
if exists (select null from sysprocesses where blocked > 0 and spid <> blocked)
begin
set @cmd = ”
set @spid = ”
select @spid = max(blocked) from sysprocesses where blocked > 0 and spid <> blocked
select @cmd [...]
