When you need to Run Stored Procedure in a JOB

if object_id(N'usp_DefragIndexes') <>0
drop proc usp_DefragIndexes
go

CREATE PROC usp_DefragIndexes @databasename SYSNAME = null,
@maxfrag DECIMAL

as

-- @maxfrag = 10.0
BEGIN

IF @databasename is null
BEGIN
RETURN;
END

DECLARE @SQL nvarchar(4000)
SET @SQL = 'USE '+ @databasename +'
SET NOCOUNT ON
.......;'

DECLARE @Params nvarchar(4000)
SET @Params = N'
@databasename SYSNAME ,
@maxfrag DECIMAL'

EXECUTE sp_executesql @SQL,
@Params,
@databasename =@databasename ,
@maxfrag = @maxfrag
END

SQL Server 2000 Defragmentation Indexes Script --The Best

/*
** Author: Rodrigo Acosta
** Email: acosta_rodrigo@hotmail.com
**
** Script to be included in a Job that manages fragmentation
** in all indexes in the executing database.
*/


Set Nocount On

/*
** *************Error Message Definition********************
*/

/* Declare Variables that wil be used for the error message */
Declare @Error_no Int
Declare @AddMessage Varchar(1500)

/* Calculates the last error number of the message */
Set @Error_no = (Select Top 1 error From master.dbo.sysmessages Order by error Desc)

/*
First checks if the message that will be added exists.
If it doesn't, calculates the available error number
and creates the message.
*/
If Not Exists
(Select description from master.dbo.sysmessages
Where description Like ('%View C:\Frag_Info.log for more info.'))
Begin
/*
If no user define error messages were added (Error < 50001).
Creates the message with the number 50001.
*/
If @Error_no < 50001
Begin
Set @Error_no = 50001
Set @AddMessage = 'EXEC Sp_addmessage @msgnum=' + Convert(VarChar(5), @Error_no) + ',
@severity=19,
@msgtext="%s index %s on table %s is %s percent fragmented. Consider re-building the index. View C:\Frag_Info.log for more info.",
@with_log=True'
EXEC (@AddMessage)
End
Else
/*
If there is an used define error (Error > 50001). Calculates the
first available error number and creates the error message.
*/
Begin
Set @Error_no = @Error_no + 1
Set @AddMessage='EXEC Sp_addmessage @msgnum=' + Convert(VarChar(5), @Error_no) + ',
@severity=19,
@msgtext="%s index %s on table %s is %s percent fragmented. Consider re-building the index. View C:\Frag_Info.log for more info.",
@with_log=True'
EXEC (@AddMessage)
End
End

/*
** *************Alert Definition********************
*/

/*
** If the Alerts Does not exists, it is created to response to the Error message
** created before.
*/
If Not Exists
(Select Name From msdb.dbo.sysalerts Where name = 'Fragmentation above 50% in user index.')
Begin
EXECUTE msdb.dbo.sp_add_alert @name = 'Fragmentation above 50% in user index.',
@message_id = @Error_no,
@Severity = 0,
@Enabled = 1,
@delay_between_responses = 0,
@category_name = '[Uncategorized]'
End


/*
** *************Selecting Sysindexes columns********************
*/


/* Creates a Temp Table to hold the results from DBCC ShowContig */

/* Creates a Temp Table to show the results from DBCC ShowContig */
Create Table #SaveResults
(ObjectName Sysname,
ObjectId int,
IndexName Sysname,
Indexid int,
[Level] int,
Pages Int,
[Rows] Int,
MinimumRecordSize Int,
MaximumRecordSize Int,
AverageRecordSize Int,
ForwardedRecords Int,
Extents Int,
ExtentSwitches Int,
AverageFreeBytes Int,
AveragePageDensity Int,
ScanDensity Int,
BestCount int,
ActualCount Int,
LogFragmentation Int,
ExtentFragmentation Int)

/* Creates a Temp Table to show the modified results from DBCC ShowContig */
Create Table ##ShowResults
(TableName sysname,
IndexName sysname,
IndexType Varchar (12),
[%Frag] Int,
Defrag VarChar (3),
Pages Int,
AvgFreeBytes Int)

/*
Declares a cursor and variables that holds the table and index Type (Clustered or Nonclustered)
and the FillFactor (original) from user tables with indexes.
*/
Declare @TableName sysname
Declare @IndexName sysname
Declare vCursor Cursor For
Select Object_name (i.id) As 'TableName',
i.name As 'IndexName'
From Sysindexes i Inner Join sysobjects o
On i.id=o.id
Where (Indid = 1 Or Indid Between 2 And 250) And xtype = 'U'

Declare @cmdDBCC Varchar (200)


Open vCursor
Fetch Next From vCursor Into @TableName, @IndexName
While @@Fetch_Status = 0
Begin
Set @cmdDBCC = 'DBCC ShowContig (' + @TableName + ', ' + @IndexName +
') With TableResults'
Insert #SaveResults
Exec (@cmdDBCC)
/* Fills the #SaveResults Table with all the results of the execution*/
Fetch Next From vCursor Into @TableName, @IndexName
End

/* Close the cursor because it don't use it anymore */
Close vCursor
Deallocate Vcursor

/* Declare variables to save info from the #SaveResults */
Declare @ObjectName Sysname
Declare @IndexName2 Sysname
Declare @ScanDensity Int
Declare @IndexType Varchar(13)
Declare @Pages Int
Declare @AverageFreeBytes Int
Declare @Rows Int
Declare @Defrag Char(3)
Declare vCursor2 Cursor For
Select ObjectName, IndexName, ScanDensity,
[IndexType]=
Case
When IndexId = 1 Then 'Clustered'
Else 'NonClustered'
End
,
Pages, [Rows], AverageFreeBytes
From #SaveResults
Open vCursor2
Fetch Next From vCursor2 Into @ObjectName, @IndexName2, @ScanDensity, @IndexType,
@Pages, @Rows, @AverageFreeBytes

While @@Fetch_Status = 0
Begin
/* If fragmentation is above 50 % Then fire the error */
If @ScanDensity < 50
Begin
/* Builts the raise error sentence */
Declare @Raise Varchar(150)
Set @Defrag = 'Yes'
Set @Raise = 'Raiserror (' + Convert(Varchar(10),@Error_no) +
', 18, 1,' + '''' + @IndexType + '''' + ',' + '''' + @IndexName2+ '''' + ',' + '''' +
@ObjectName + '''' + ',' + '''' + Convert(Varchar(20),@ScanDensity) + '''' + ')'
EXEC (@Raise)
End
/* If not just alter the variable to indicate no defragmentation is needed*/
Else
Begin
Set @Defrag = 'No'
End

/* Insert the results into the temp table created to show the results */
Insert ##ShowResults
Values
(@ObjectName, @IndexName2, @IndexType,
@ScanDensity, @Defrag, @Pages,
@AverageFreeBytes)

Fetch Next From vCursor2 Into @ObjectName, @IndexName2, @ScanDensity, @IndexType,
@Pages, @Rows, @AverageFreeBytes
End

Close vCursor2
Deallocate vCursor2

Drop Table #SaveResults

/*
** *************Log File generation********************
*/
Declare @cmd Varchar(1000)
Set @cmd=' EXEC master.dbo.xp_cmdShell ' + '''' +
'OSQL -E -q"Set Nocount On Select Substring(@@ServerName,1,20) as [Executed On Server:] Select Substring(db_name(),1,20) ' +
'AS [Executed On Database:] Select Getdate() as [LogFile generated with the ' +
'results of fragmentation in all user Indexes. Date:] Select ' +
'SubString(TableName,1,20) as TableName,IndexType, Substring(IndexName,1,20) ' +
'as IndexName, [%Frag] as [%Defrag] , Defrag As [Need Defrag.?] from ##ShowResults" -oC:\Frag_Info.log' + ''''
EXEC (@cmd)
Drop Table ##ShowResults
Drop Table #SaveResults

sp_BlockedProcesses

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BlockedProcesses] Script Date: 12/16/2008 11:37:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_BlockedProcesses
-- Description Returns list of blocked processes and buffers for blocked and blocking processes
-- Input None
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
ALTER PROC [dbo].[sp_BlockedProcesses]
AS
SET NOCOUNT ON
DECLARE @Blocked int
DECLARE @BlockedBy int
DECLARE @SqlStr varchar(1000)
CREATE TABLE #TmpSysprocesses
(BlockedSPID smallint,
BlockedBuffer nvarchar(255) null,
BlockingSPID smallint,
BlockingBuffer nvarchar(255) null,
waitresource nchar(256),
dbid smallint,
BlockedHostname nchar(128),
BlockedProgram_name nchar(128),
BlockedCmd nchar(16),
BlockedLoginame nchar(128),
BlockingHostname nchar(128),
BlockingProgram_name nchar(128),
BlockingCmd nchar(16),
BlockingLoginame nchar(128))
INSERT INTO #TmpSysprocesses
SELECT blocked.spid 'BlockedSPID', null 'BlockedBuffer', blocked.blocked 'BlockingSPID',
null 'BlockingBuffer', blocked.waitresource, blocked.dbid,
blocked.hostname 'BlockedHostname', blocked.program_name 'BlockedProgram_name',
blocked.cmd 'BlockedCmd', blocked.loginame 'BlockedLoginame',
Blocking.hostname 'BlockingHostname', Blocking.program_name 'BlockingProgram_name',
Blocking.cmd 'BlockingCmd', Blocking.loginame 'BlockingLoginame'
FROM master..sysprocesses blocked
JOIN master..sysprocesses blocking ON blocking.SPID = blocked.blocked
WHERE blocked.Blocked > 0

CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE Processes CURSOR FOR
SELECT BlockedSPID, BlockingSPID FROM #TmpSysprocesses
OPEN Processes
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@Blocked))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockedBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@BlockedBy))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockingBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
END
SELECT * FROM #TmpSysprocesses

sp_ActiveProcesses

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_ActiveProcesses] Script Date: 12/16/2008 11:35:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_ActiveProcesses
-- Description Returns list of active processes and their buffer contents (what they execute)
-- A process is considered as active if it has some changes of cpu time consumed or number
-- of io operation in specified period.
-- Input (optional) @Delay - Time interval to catch activity
-- Output Result set with active processes
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
ALTER PROC [dbo].[sp_ActiveProcesses]
@Delay smallint = 5
AS
SET NOCOUNT ON
IF @Delay > 59
SET @Delay = 59
IF @Delay < 1
SET @Delay = 1
print @Delay
DECLARE @SPID int
DECLARE @DelayClock char(8)
DECLARE @SqlStr varchar(1000)
DECLARE @Internal_Value int
SET @DelayClock = '00:00:'+LTRIM(STR(@Delay))
CREATE TABLE #TmpSysprocesses
(EventTime datetime,
FragmentDuration int,
spid smallint,
cpu int,
physical_io int,
cpuUpdate int null,
physical_ioUpdate int null,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint,
dbname sysname null,
IsActive bit null,
SPIDBuffer nvarchar(255) null)
INSERT INTO #TmpSysprocesses
SELECT getdate(), null 'FragmentDuration', spid, SUM(cpu), SUM(physical_io), null, null, MAX(hostname), MAX(program_name),
MAX(loginame), MAX(dbid), null, null, null
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
WAITFOR DELAY @DelayClock
CREATE TABLE #TmpSysprocesses2
(EventTime datetime,
spid smallint,
cpu int,
physical_io int,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint)
INSERT INTO #TmpSysprocesses2
SELECT getdate(), spid, SUM(cpu), SUM(physical_io), MAX(hostname), MAX(program_name), MAX(loginame), MAX(dbid)
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0

UPDATE #TmpSysprocesses SET FragmentDuration = DATEDIFF(ms,t.EventTime,t2.EventTime), IsActive = 1,
cpuUpdate = t2.cpu - t.cpu,
physical_ioUpdate = t2.physical_io - t.physical_io, dbname = d.name
FROM #TmpSysprocesses t
JOIN #TmpSysprocesses2 t2 ON t2.spid = t.spid
and t2.hostname = t.hostname
and t2.loginame = t.loginame
and (t2.cpu <> t.cpu or t2.physical_io <> t.physical_io)
JOIN sysdatabases d ON d.dbid = t.dbid
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE ActiveProcesses CURSOR FOR
SELECT spid FROM #TmpSysprocesses WHERE IsActive = 1
OPEN ActiveProcesses
FETCH NEXT FROM ActiveProcesses INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@SPID))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET SPIDBuffer = EventInfo
FROM #tmpBuffer
WHERE spid = @SPID
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM ActiveProcesses INTO @SPID
END
DEALLOCATE ActiveProcesses
CREATE TABLE #xp_msver (
[Index] int,
[Name] varchar(1000) null,
Internal_Value int null,
Character_value varchar(1000) null)
INSERT INTO #xp_msver EXEC master..xp_msver 'ProcessorCount'
SELECT @Internal_Value = Internal_Value FROM #xp_msver
WHERE [Name] = 'ProcessorCount'
SELECT spid 'ProcessId',
cpu 'TotalCPU',
cpuUpdate 'CPU_ConsumedInTheTimeFragment',
physical_io 'TotalPhysical_IO',
physical_ioUpdate 'Physical_IO_InTheTimeFragment',
LEFT(hostname,12) 'Hostname',
LEFT(program_name,30) 'ApplicationName', LEFT(loginame,30) 'NT_LoginName', dbname 'DatabaseName', SPIDBuffer
FROM #TmpSysprocesses s WHERE IsActive = 1
SELECT MAX(FragmentDuration) 'TheFragmentDuration', @Internal_Value 'NumberOfCPUs',
SUM(cpuUpdate) 'SUM CPU_ConsumedInTheTimeFragment',
SUM(physical_ioUpdate) 'SUM Physical_IO_InTheTimeFragment'
FROM #TmpSysprocesses s WHERE IsActive = 1
DROP TABLE #xp_msver

sp_ListConnectionsOK

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_ListConnectionsOK] Script Date: 12/16/2008 11:31:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ListConnectionsOK]
@DBUltra bit = 0,
@PCUltra bit = 0,
@DBIntra varchar(8000) = NULL,
@DBExtra varchar(8000) = NULL,
@PCIntra varchar(100) = NULL,
@PCExtra varchar(100) = NULL,
@DBTrain char(1) = NULL,
@PCTrain varchar(2000) = NULL
AS

SET NOCOUNT ON

DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int

SET @Status = 0

DECLARE @Task varchar(4000)

DECLARE @Work varchar(2000)

DECLARE @Host varchar(200)

DECLARE @SPID smallint

DECLARE @SPOT smallint

SET @SPOT = CASE WHEN CHARINDEX('2000 - 8',@@VERSION,1) > 0 THEN 50 ELSE 12 END

SET @Work = REPLACE(@PCTrain,CHAR(94),CHAR(39))

IF @PCTrain IS NULL

BEGIN

SELECT P.spid AS [ID]
, RTRIM(P.hostname) AS [Client]
, RTRIM(P.loginame) AS [Login]
, RTRIM(P.program_name) AS [Application]
, RTRIM(O.name) AS [Database]
, CONVERT(varchar(20),P.login_time,20) AS [Connection]
FROM master.dbo.sysprocesses AS P
JOIN master.dbo.sysdatabases AS O
ON P.dbid = O.dbid
LEFT JOIN master.dbo.sysprocesses AS Z
ON P.spid = Z.blocked
WHERE P.spid > @SPOT
ORDER BY [Client],[Login],[Application],[Database],[Connection]

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

END




IF @Status = 1 OR @Status NOT BETWEEN 0 AND 50000 RAISERROR ('Windows error [%d]',16,1,@Status)

SET NOCOUNT OFF

RETURN (@Status)

exec sp_ListConnectionsOK