Thursday, September 20, 2012
List of inactive users in a DB
SELECT u.*, l.*
FROM sysusers U
left outer join master..syslogins L on U.sid = L.sid
where l.sid is null and u.altuid is null and u.status <> 4
and u.name not in ('sys','guest','INFORMATION_SCHEMA')
order by u.name
Wednesday, September 19, 2012
Monday, September 17, 2012
Recompile Stuck Procs : sp_recompile_stuck_procs
USE [master]
GO
/*
exec sp_recompile_stuck_procs 2
*/
CREATE PROCEDURE [dbo].[sp_recompile_stuck_procs]
(
@maxcount int = 10
)
AS
BEGIN
SET NOCOUNT ON;
declare @datestr nvarchar(100), @sql nvarchar(4000), @tblname nvarchar(100)
--declare @maxcount int = 10
select @datestr = CONVERT(nvarchar(10),getdate(), 112)+'_'+CONVERT(nvarchar(10),datepart(hour,getdate()))+'_'+CONVERT(nvarchar(10),datepart(minute,getdate()))+'_'+CONVERT(nvarchar(10),datepart(SECOND,getdate()))
--select @datestr
set @tblname = '__drop_who3_collection_1_minute_'+@datestr
set @sql = 'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@tblname+']'') AND type in (N''U''))
create table '+@tblname+'
(SPID bigint,Status varchar(100),Login varchar(100),Host varchar(100),BlkBy bigint, TempDBWait varchar(100),DBName varchar(100),CommandType varchar(100),
SQLStatement varchar(max),ObjectName varchar(100),ElapsedMS bigint,CPUTime bigint,IOReads bigint,IOWrites bigint,LastWaitType varchar(100),
StartTime datetime,Protocol varchar(100),ConnectionWrites bigint,ConnectionReads bigint,ClientAddress varchar(100),Authentication varchar(100), Collectiontime datetime)
'
--print @sql
exec sp_executesql @sql
set @sql = 'insert into '+@tblname+'
exec sp_who3 '
--print @sql
exec sp_executesql @sql
set @sql = 'select dbname, objectname, count(*) as cc into '+@tblname+'_loop from '+@tblname+' where objectname is not null group by dbname, objectname having count(*) > '+CONVERT(varchar, @maxcount)
--print @sql
exec sp_executesql @sql
--set @sql = 'select * from '+@tblname
----print @sql
--exec sp_executesql @sql
--set @sql = 'select * from '+@tblname+'_loop'
----print @sql
--exec sp_executesql @sql
---------------------------------------------------------
--declare @tblname nvarchar(100) = '__drop_who3_collection_1_minute_20120917_13_37_16' , @sql nvarchar(1000)
declare @tbl table (db nvarchar(100) , sp nvarchar(100), cc int)
declare @db nvarchar(100) , @sp nvarchar(100)
set @sql = 'select dbname, objectname, cc From '+@tblname+'_loop'
insert into @tbl
exec sp_executesql @sql
declare tmp_cur cursor for
select db, sp From @tbl
open tmp_cur
Fetch next from tmp_cur into @db, @sp
while @@fetch_status = 0
begin
--print @db
--print @sp
set @sql = 'use ' +@db +'
exec sp_recompile '+replace(@sp, 'dbo.','')
print @sql
--exec sp_executesql @sql
Fetch next from tmp_cur into @db, @sp
print '---'
end
close tmp_cur
deallocate tmp_cur
-----------------------------------
if (select COUNT(*) from @tbl) > 0
begin
DECLARE @tableHTML NVARCHAR(MAX) ='...' , @subjectstring nvarchar(200)='...'
SET @tableHTML =
N'
N'' +
N' ' +
N' ' +
CAST ( ( SELECT td = db, '',
td = sp, '',
td = cc
FROM @tbl
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
' ;
print @tableHTML
set @subjectstring = 'Alert : Proc Re-compile on SQL Server '+@@SERVERNAME
print @subjectstring
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients ='patrick@csod.com',
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subjectstring;
end
---------------------------------------
set @sql = 'drop table '+@tblname+'_loop'
--print @sql
exec sp_executesql @sql
set @sql = 'drop table '+@tblname
--print @sql
exec sp_executesql @sql
END
GO
/*
exec sp_recompile_stuck_procs 2
*/
CREATE PROCEDURE [dbo].[sp_recompile_stuck_procs]
(
@maxcount int = 10
)
AS
BEGIN
SET NOCOUNT ON;
declare @datestr nvarchar(100), @sql nvarchar(4000), @tblname nvarchar(100)
--declare @maxcount int = 10
select @datestr = CONVERT(nvarchar(10),getdate(), 112)+'_'+CONVERT(nvarchar(10),datepart(hour,getdate()))+'_'+CONVERT(nvarchar(10),datepart(minute,getdate()))+'_'+CONVERT(nvarchar(10),datepart(SECOND,getdate()))
--select @datestr
set @tblname = '__drop_who3_collection_1_minute_'+@datestr
set @sql = 'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@tblname+']'') AND type in (N''U''))
create table '+@tblname+'
(SPID bigint,Status varchar(100),Login varchar(100),Host varchar(100),BlkBy bigint, TempDBWait varchar(100),DBName varchar(100),CommandType varchar(100),
SQLStatement varchar(max),ObjectName varchar(100),ElapsedMS bigint,CPUTime bigint,IOReads bigint,IOWrites bigint,LastWaitType varchar(100),
StartTime datetime,Protocol varchar(100),ConnectionWrites bigint,ConnectionReads bigint,ClientAddress varchar(100),Authentication varchar(100), Collectiontime datetime)
'
--print @sql
exec sp_executesql @sql
set @sql = 'insert into '+@tblname+'
exec sp_who3 '
--print @sql
exec sp_executesql @sql
set @sql = 'select dbname, objectname, count(*) as cc into '+@tblname+'_loop from '+@tblname+' where objectname is not null group by dbname, objectname having count(*) > '+CONVERT(varchar, @maxcount)
--print @sql
exec sp_executesql @sql
--set @sql = 'select * from '+@tblname
----print @sql
--exec sp_executesql @sql
--set @sql = 'select * from '+@tblname+'_loop'
----print @sql
--exec sp_executesql @sql
---------------------------------------------------------
--declare @tblname nvarchar(100) = '__drop_who3_collection_1_minute_20120917_13_37_16' , @sql nvarchar(1000)
declare @tbl table (db nvarchar(100) , sp nvarchar(100), cc int)
declare @db nvarchar(100) , @sp nvarchar(100)
set @sql = 'select dbname, objectname, cc From '+@tblname+'_loop'
insert into @tbl
exec sp_executesql @sql
declare tmp_cur cursor for
select db, sp From @tbl
open tmp_cur
Fetch next from tmp_cur into @db, @sp
while @@fetch_status = 0
begin
--print @db
--print @sp
set @sql = 'use ' +@db +'
exec sp_recompile '+replace(@sp, 'dbo.','')
print @sql
--exec sp_executesql @sql
Fetch next from tmp_cur into @db, @sp
print '---'
end
close tmp_cur
deallocate tmp_cur
-----------------------------------
if (select COUNT(*) from @tbl) > 0
begin
DECLARE @tableHTML NVARCHAR(MAX) ='...' , @subjectstring nvarchar(200)='...'
SET @tableHTML =
N'
Proc Re-compile on '+@@SERVERNAME+'
' +N'' +
N' ' +
N' ' +
CAST ( ( SELECT td = db, '',
td = sp, '',
td = cc
FROM @tbl
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
Database | Proc | Counts |
---|
print @tableHTML
set @subjectstring = 'Alert : Proc Re-compile on SQL Server '+@@SERVERNAME
print @subjectstring
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients ='patrick@csod.com',
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subjectstring;
end
---------------------------------------
set @sql = 'drop table '+@tblname+'_loop'
--print @sql
exec sp_executesql @sql
set @sql = 'drop table '+@tblname
--print @sql
exec sp_executesql @sql
END
Friday, September 14, 2012
Friday, September 07, 2012
Reindex Online tables - maintenance_re_index
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
maintenance_re_index
*/
ALTER Procedure maintenance_re_index
(
@MaxFragmentation int = 10 ,
@TrivialPageCount int = 100 ,
@RebuildThreshold int = 40
)
as
BEGIN
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @HasBlobColumn int;
--DECLARE @MaxFragmentation int;
--DECLARE @TrivialPageCount int;
--DECLARE @RebuildThreshold int;
---- Tuning constants
--SET @MaxFragmentation = 10 --Change this value to adjust the threshold for fragmentation
--SET @RebuildThreshold = 30 --Change this value to adjust the break point for defrag/rebuild
--SET @TrivialPageCount = 1000 --Change this value to adjust the size threshold
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
if(OBJECT_ID('tempdb..#work_to_do')>1)
DROP TABLE #work_to_do;
SELECT distinct
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @MaxFragmentation
AND index_id > 0 -- cannot defrag a heap
AND page_count > @TrivialPageCount -- ignore trivial sized indexes
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT distinct WTD.*
FROM #work_to_do WTD
INNER JOIN sys.indexes I ON I.object_id = WTD.objectid
WHERE I.is_disabled = 0 AND I.is_hypothetical = 0;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SET @HasBlobColumn = 0 -- reinitialize
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- Check for BLOB columns
IF @indexid = 1 -- only check here for clustered indexes ANY blob column on the table counts
SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
FROM sys.objects SO
inner join sys.columns SC ON SO.Object_id = SC.object_id
inner join sys.types ST ON SC.system_type_id = ST.system_type_id AND
(
ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
OR
ST.name IN ('text', 'ntext', 'image', 'xml')
)
WHERE SO.Object_ID = @objectID
ELSE -- nonclustered. Only need to check if indexed column is a BLOB
SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
FROM sys.objects SO
INNER JOIN sys.index_columns SIC ON SO.Object_ID = SIC.object_id
INNER JOIN sys.Indexes SI ON SO.Object_ID = SI.Object_ID AND SIC.index_id = SI.index_id
INNER JOIN sys.columns SC ON SO.Object_id = SC.object_id AND SIC.Column_id = SC.column_id
INNER JOIN sys.types ST ON SC.system_type_id = ST.system_type_id AND
(
ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
OR
ST.name IN ('text', 'ntext', 'image', 'xml')
)
WHERE SO.Object_ID = @objectID
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname;
IF @frag > @RebuildThreshold
BEGIN
SET @command = @command + N' REBUILD'
IF @HasBlobColumn = 1
SET @command = @command + N' WITH( SORT_IN_TEMPDB = ON) '
ELSE
SET @command = @command + N' WITH( ONLINE = ON, SORT_IN_TEMPDB = ON) '
END
ELSE
SET @command = @command + N' REORGANIZE'
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
PRINT N'Executing: ' + @command +' Has Blob = ' + convert(nvarchar(2),@HasBlobColumn);
--PRINT @command
--print 'print '''+@command +''''
--print 'Go'
EXEC (@command)
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
END
/*
maintenance_re_index
*/
ALTER Procedure maintenance_re_index
(
@MaxFragmentation int = 10 ,
@TrivialPageCount int = 100 ,
@RebuildThreshold int = 40
)
as
BEGIN
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @HasBlobColumn int;
--DECLARE @MaxFragmentation int;
--DECLARE @TrivialPageCount int;
--DECLARE @RebuildThreshold int;
---- Tuning constants
--SET @MaxFragmentation = 10 --Change this value to adjust the threshold for fragmentation
--SET @RebuildThreshold = 30 --Change this value to adjust the break point for defrag/rebuild
--SET @TrivialPageCount = 1000 --Change this value to adjust the size threshold
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
if(OBJECT_ID('tempdb..#work_to_do')>1)
DROP TABLE #work_to_do;
SELECT distinct
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @MaxFragmentation
AND index_id > 0 -- cannot defrag a heap
AND page_count > @TrivialPageCount -- ignore trivial sized indexes
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT distinct WTD.*
FROM #work_to_do WTD
INNER JOIN sys.indexes I ON I.object_id = WTD.objectid
WHERE I.is_disabled = 0 AND I.is_hypothetical = 0;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SET @HasBlobColumn = 0 -- reinitialize
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- Check for BLOB columns
IF @indexid = 1 -- only check here for clustered indexes ANY blob column on the table counts
SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
FROM sys.objects SO
inner join sys.columns SC ON SO.Object_id = SC.object_id
inner join sys.types ST ON SC.system_type_id = ST.system_type_id AND
(
ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
OR
ST.name IN ('text', 'ntext', 'image', 'xml')
)
WHERE SO.Object_ID = @objectID
ELSE -- nonclustered. Only need to check if indexed column is a BLOB
SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
FROM sys.objects SO
INNER JOIN sys.index_columns SIC ON SO.Object_ID = SIC.object_id
INNER JOIN sys.Indexes SI ON SO.Object_ID = SI.Object_ID AND SIC.index_id = SI.index_id
INNER JOIN sys.columns SC ON SO.Object_id = SC.object_id AND SIC.Column_id = SC.column_id
INNER JOIN sys.types ST ON SC.system_type_id = ST.system_type_id AND
(
ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
OR
ST.name IN ('text', 'ntext', 'image', 'xml')
)
WHERE SO.Object_ID = @objectID
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname;
IF @frag > @RebuildThreshold
BEGIN
SET @command = @command + N' REBUILD'
IF @HasBlobColumn = 1
SET @command = @command + N' WITH( SORT_IN_TEMPDB = ON) '
ELSE
SET @command = @command + N' WITH( ONLINE = ON, SORT_IN_TEMPDB = ON) '
END
ELSE
SET @command = @command + N' REORGANIZE'
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
PRINT N'Executing: ' + @command +' Has Blob = ' + convert(nvarchar(2),@HasBlobColumn);
--PRINT @command
--print 'print '''+@command +''''
--print 'Go'
EXEC (@command)
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
END
Friday, August 31, 2012
parent child relation hierachy script
with cte as
(
select 1 as lvl, SpaceShuttleID , ParentSpaceShuttleID from SpaceShuttle where SpaceShuttleID = 200
union all
select cte.lvl + 1, b.SpaceShuttleID , b.ParentSpaceShuttleID from SpaceShuttle b join cte on cte.SpaceShuttleID = b.ParentSpaceShuttleID
)
select * from cte order by lvl;
Friday, August 17, 2012
modify sysjobs trigger to send alert
USE [msdb]
GO
/****** Object: Trigger [dbo].[trig_sysjobs_insert_update] Script Date: 08/17/2012 08:56:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[trig_sysjobs_insert_update]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
-- Disallow the insert or update if the originating_server_id isn't in sysoriginatingservers_view.
IF (EXISTS (SELECT *
FROM inserted
WHERE inserted.originating_server_id NOT IN
(SELECT v.originating_server_id
FROM sysoriginatingservers_view AS v)))
BEGIN
RAISERROR(14379, -1, -1, 'dbo.sysjobs')
ROLLBACK TRANSACTION
RETURN
END
else
begin
Declare @Body1 nvarchar(max)
Select @Body1 = 'SQL Server Job Name: ' + name + ' modified on '+convert(varchar, date_modified,9) from inserted
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'palexander@csod.com',
@body = @Body1,
@subject = 'SQL Server Job Modification Alert'
end
END
GO
/****** Object: Trigger [dbo].[trig_sysjobs_insert_update] Script Date: 08/17/2012 08:56:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[trig_sysjobs_insert_update]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
-- Disallow the insert or update if the originating_server_id isn't in sysoriginatingservers_view.
IF (EXISTS (SELECT *
FROM inserted
WHERE inserted.originating_server_id NOT IN
(SELECT v.originating_server_id
FROM sysoriginatingservers_view AS v)))
BEGIN
RAISERROR(14379, -1, -1, 'dbo.sysjobs')
ROLLBACK TRANSACTION
RETURN
END
else
begin
Declare @Body1 nvarchar(max)
Select @Body1 = 'SQL Server Job Name: ' + name + ' modified on '+convert(varchar, date_modified,9) from inserted
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'palexander@csod.com',
@body = @Body1,
@subject = 'SQL Server Job Modification Alert'
end
END
Thursday, August 16, 2012
SQL job queries
--JOB INFO
select *
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
inner join sysschedules c on b.schedule_id = c.schedule_id
--where a.name like '%SoapFeedJobs%'
where a.name like '%CES Daily Maintenance - VLINE%'
--JOB HISTORY
select *
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'
--JOB HISTORY with outcome result
select
Case isnull(h.run_status,'') WHEN '' THEN 'Unknown' WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
*
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'
select *
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
inner join sysschedules c on b.schedule_id = c.schedule_id
--where a.name like '%SoapFeedJobs%'
where a.name like '%CES Daily Maintenance - VLINE%'
--JOB HISTORY
select *
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'
--JOB HISTORY with outcome result
select
Case isnull(h.run_status,'') WHEN '' THEN 'Unknown' WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
*
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'
Wednesday, August 15, 2012
lists all the default constraints and the default values for the user tables in the database
SELECT
b.name AS TABLE_NAME,
d.name AS COLUMN_NAME,
a.name AS CONSTRAINT_NAME,
c.text AS DEFAULT_VALUEFROM sys.sysobjects a INNER JOIN
(SELECT name, id
FROM sys.sysobjects
WHERE xtype = 'U') b on (a.parent_obj = b.id)
INNER JOIN sys.syscomments c ON (a.id = c.id)
INNER JOIN sys.syscolumns d ON (d.cdefault = a.id)
WHERE a.xtype = 'D'
ORDER BY b.name, a.name
function to get time for next job
use msdb
go
/*
select dbo.fn_next_job_time(15, null, null)
select dbo.fn_next_job_time(7, null, null)
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -','Weekend Sat')
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -',null)
select dbo.fn_next_job_time(4, 'CES Weekly Maintenance -',null)
*/
CREATE FUNCTION fn_next_job_time
(
@interval int = 10,
@jobname nvarchar(100) = null ,
@schedulename nvarchar(100) = null
)
RETURNS int
AS
BEGIN
set @jobname = @jobname + '%'
set @schedulename = @schedulename + '%'
declare @curr_hr int, @curr_min int, @curr_sec int, @next_hr int, @next_min int, @next_sec int
declare @nexttime int
--set @nexttime = 10004
--set @nexttime = 206
--set @nexttime = 5506
--set @nexttime = 230847
--set @nexttime = 145712
--set @nexttime = 41942
--set @nexttime = 225847
--set @nexttime = 235847
select top 1 @nexttime = c.active_start_time
--a.schedule_id, b.name, c.active_start_time,c.name, *
from sysjobschedules a
inner join sysjobs b on a.job_id = b.job_id
inner join sysschedules c on a.schedule_id = c.schedule_id
where
(b.name like @jobname or @jobname is null)
and
( c.name like @schedulename or @schedulename is null)
order by c.active_start_time desc
select @curr_hr = right(convert(nvarchar(10), @nexttime/10000),2) , @curr_min = right(convert(nvarchar(10), @nexttime/100),2), @curr_sec = right(convert(nvarchar(10), @nexttime),2)
set @next_sec = @curr_sec
set @next_min = (@curr_min + @interval)%60
set @next_hr = @curr_hr + (@curr_min + @interval)/ 60
set @next_hr = case when @next_hr >= 24 then 0 else @next_hr end
--select @nexttime, @curr_hr , @curr_min , @curr_sec
--select @nexttime, @next_hr , @next_min , @next_sec
select @nexttime = convert(int,
(case when @next_hr < 10 then '0'+CONVERT(nvarchar(10),@next_hr ) else convert(nvarchar(10),@next_hr ) end) +
(case when @next_min < 10 then '0'+CONVERT(nvarchar(10),@next_min ) else convert(nvarchar(10),@next_min ) end) +
(case when @next_sec < 10 then '0'+CONVERT(nvarchar(10),@next_sec) else convert(nvarchar(10),@next_sec) end)
)
RETURN @nexttime
END
GO
go
/*
select dbo.fn_next_job_time(15, null, null)
select dbo.fn_next_job_time(7, null, null)
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -','Weekend Sat')
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -',null)
select dbo.fn_next_job_time(4, 'CES Weekly Maintenance -',null)
*/
CREATE FUNCTION fn_next_job_time
(
@interval int = 10,
@jobname nvarchar(100) = null ,
@schedulename nvarchar(100) = null
)
RETURNS int
AS
BEGIN
set @jobname = @jobname + '%'
set @schedulename = @schedulename + '%'
declare @curr_hr int, @curr_min int, @curr_sec int, @next_hr int, @next_min int, @next_sec int
declare @nexttime int
--set @nexttime = 10004
--set @nexttime = 206
--set @nexttime = 5506
--set @nexttime = 230847
--set @nexttime = 145712
--set @nexttime = 41942
--set @nexttime = 225847
--set @nexttime = 235847
select top 1 @nexttime = c.active_start_time
--a.schedule_id, b.name, c.active_start_time,c.name, *
from sysjobschedules a
inner join sysjobs b on a.job_id = b.job_id
inner join sysschedules c on a.schedule_id = c.schedule_id
where
(b.name like @jobname or @jobname is null)
and
( c.name like @schedulename or @schedulename is null)
order by c.active_start_time desc
select @curr_hr = right(convert(nvarchar(10), @nexttime/10000),2) , @curr_min = right(convert(nvarchar(10), @nexttime/100),2), @curr_sec = right(convert(nvarchar(10), @nexttime),2)
set @next_sec = @curr_sec
set @next_min = (@curr_min + @interval)%60
set @next_hr = @curr_hr + (@curr_min + @interval)/ 60
set @next_hr = case when @next_hr >= 24 then 0 else @next_hr end
--select @nexttime, @curr_hr , @curr_min , @curr_sec
--select @nexttime, @next_hr , @next_min , @next_sec
select @nexttime = convert(int,
(case when @next_hr < 10 then '0'+CONVERT(nvarchar(10),@next_hr ) else convert(nvarchar(10),@next_hr ) end) +
(case when @next_min < 10 then '0'+CONVERT(nvarchar(10),@next_min ) else convert(nvarchar(10),@next_min ) end) +
(case when @next_sec < 10 then '0'+CONVERT(nvarchar(10),@next_sec) else convert(nvarchar(10),@next_sec) end)
)
RETURN @nexttime
END
GO
Subscribe to:
Posts (Atom)