How to query currently running SQL Server Agent jobs

Today I went on a quest to discover a way to list all currently running SQL Server Agent jobs. As every other quest, this one also started with Google-ing.

Within 2 minutes I found this great post by Brent Ozar on SQLServerPedia. Why is this post so great? It is so great because Brent figured

Continue reading […] How to query currently running SQL Server Agent jobs […]

 

Audit startup procedures in SQL Server

Here is a script which will list all enabled or disabled startup procedures in SQL Server.

?View Code TSQLuse [master]
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
DECLARE @procedureID INT
CREATE TABLE #procedureList(objectname varchar(100),startup_status varchar(50))
DECLARE procedure_cursor CURSOR FOR SELECT id
FROM master.dbo.sysobjects WHERE TYPE IN (’P’,’X’)
OPEN procedure_cursor
FETCH NEXT FROM procedure_cursor INTO @procedureID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #procedureList
SELECT OBJECT_NAME(@procedureID)
,CASE Objectproperty(@procedureID,’ExecIsStartup’)
WHEN 1
then ‘Enabled’
else ‘Disabled’
end
FETCH NEXT

Continue reading […] Audit startup procedures in SQL Server […]

 

sp_MSForEachDB Tutorial

First things first – a few words on Microsoft undocumented procedures (and tools): it is this simple – USE THEM WITH CAUTION. There are several out there, and they are all great. If you compare sp_who (documented) with sp_who2 (UN-documented) you will see the advantages.

In the case of sp_MSForEachDB (UN-documented), there are several warnings I

Continue reading […] sp_MSForEachDB Tutorial […]