February 12, 2013 at 2:02 am
Hi Vinay,
the trick is to right-click on the results and select "Copy with Headers".
That copies the column names too.
May 9, 2013 at 10:11 am
This script is great!!! It's something I've wanted but even beginning it was way over my head. I assume you are using conditional formatting on the cell color in Excel? Brilliant!
Instead of the previous 24 hours, do you have anything that looks at what's sheduled and coming up?
Thanks!
--@ The Wiz, when I run your script, it does not give each time increment, only the records where the job ran. So when I put it in Excel, I don't get the graph I get when the orig. script is pasted in with (all 0s and 1s).
May 10, 2013 at 3:09 am
Hi Lisa!
If you really want to see all the time-slots, not just the "interesting ones" (where 1 or more jobs are running),
then grab the now-fixed script from above and SET @ShowJobCollisionsOnly = -1
I took out the no-jobs-running timeslots to cut down on the data the script returns, to make it easier for people to scan the "important" bits, but I guess should have left the option to display them in there for people who wanted it 🙂
May 24, 2013 at 4:43 am
Hello,
thank you, great skript.
i changed time(0) and cast to convert(DATETIME
then it worked for me (sql 2005)
regards
June 14, 2013 at 3:13 am
This is a fantastic script but where i work i have 2 server still running SQL Server 2000. I have tried and tried to get the information to display the same way but just cant seem to get it to work.
Can anyone help me out and get the information to display the same way as it does with the original script?
June 14, 2013 at 3:37 am
I tried getting the job names to pivot somehow in SQL 2000 (as we have a legacy server running it still...), but I gave up after a while too.
The closest I got to the original "pivoted" data was posted a bit further up in this thread: http://www.sqlservercentral.com/Forums/FindPost1414190.aspx.
A google search for SQL 2000 pivot brings up a few contenders for a solution.
Best of luck, and if anyone manages to solve it, please post the code here for others to use!
June 19, 2013 at 4:36 am
Hi guys
Thought i would post again as i have managed to get ths to work for SQL Server 2000.
I know its porbably not the tidyest or most slick way of doing it but it works as the original script does. If anyone can edit and provide the code for a more slick example then i am open to suggestion.
set nocount on
declare @Minutes table (DT datetime)
declare @JobNames table (JobName varchar(255))
declare @dt datetime
declare @StartDT datetime
declare @EndDT datetime
declare @resolution int
declare @RemoveNonactiveJobs int
declare @IgnoreDisabledJobs int
--***************************************************************************************
-- Set variables
--***************************************************************************************
set @StartDT = getdate() - 1
set @EndDT = getdate()
set @resolution = 1 -- Enter the Resolution in minutes
set @RemoveNonactiveJobs = 1
set @IgnoreDisabledJobs = 1
--***************************************************************************************
-- Pre-run cleanup (just in case)
--***************************************************************************************
IF OBJECT_ID('tempdb..#Timeline') IS NOT NULL DROP TABLE #Timeline;
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..#Pivot') IS NOT NULL DROP TABLE #Pivot;
IF OBJECT_ID('tempdb..#tblTLine') IS NOT NULL DROP TABLE #tblTLine;
IF OBJECT_ID('tempdb..#tblTasks') IS NOT NULL DROP TABLE #tblTasks;
--***************************************************************************************
-- Make a Jobname table
--***************************************************************************************
insert into @JobNames (JobName)
selectreplace(name, ' ', '.') --Replace spaces (they are invalid in XML based pivot lower in the code)
frommsdb.dbo.sysjobs
whereenabled = @IgnoreDisabledJobs
--WHERE NAME = '<dbname>'
--WHERE NAME like '%<partial dbname>%'
--***************************************************************************************
-- Genereate a Datetime table between StartDT and EndDT with x minute Resolution
--***************************************************************************************
set @dt = @StartDT
WHILE @dt < @EndDT
begin
insert into @Minutes (DT) values(@DT)
set @dt= dateadd(mi, @resolution, @dt)
end
--***************************************************************************************
-- Create a timeline table by crossjoining the Datetime and Jobnames tables
--***************************************************************************************
select DT, JobName, 0 as Active
into #Timeline
from @Minutes cross join @JobNames
--***************************************************************************************
-- Create the Job Runtime information table
--***************************************************************************************
selectreplace(name, ' ', '.') as name --Replace spaces (they are invalid in XML based pivot lower in the code)
--,step_id
--,step_name
,CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120) as SDT
,dateadd(s,
((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 ,
CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120)
) as EDT
--,CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS Duration
,((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 DurationSeconds
into#JobRuntime
FROMmsdb.dbo.sysjobs job
left JOIN msdb.dbo.sysjobhistory his
ON his.job_id = job.job_id
whereCONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120) between @StartDT and @EndDT
andjob.name not in ('Database Mirroring Monitor Job', '<dbname>')
andstep_id = 0 -- step_id = 0 is the entrite job, step_id > 0 is actual step number
--and((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 > 1 -- Ignore trivial runtimes
order by SDT
--***************************************************************************************
-- Update the Timeline based on the the Job Runtime information table
--***************************************************************************************
update#Timeline
setActive = 1
from#Timeline inner join #JobRuntime
onJobName = Name
and(
SDT between dt and dateadd(mi, @resolution - 1, DT) -- Start point (added for Resolution support)
or
EDT between dt and dateadd(mi, @resolution, DT) -- End point (added for Resolution support)
or
DT between SDT and EDT
)
--***************************************************************************************
-- Delete all jobs from the Timeline that that had no activity
--***************************************************************************************
if @RemoveNonactiveJobs = 1
delete
from#Timeline
whereJobName in(selectJobname
from#Timeline
group by Jobname
havingsum(active) = 0 )
--***************************************************************************************
-- Build and Pivot the Timeline table
--***************************************************************************************
create table #Pivot (DT varchar(250) null, Name varchar(250) null, Active int null)
-- col1 = row, col2 = column, col3 = data
insertinto #Pivot
selectconvert(varchar(250), DT, 120), JobName, Active
from#Timeline
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @SQL nvarchar(4000)
DECLARE @TaskName nvarchar(100)
SET NOCOUNT ON
CREATE TABLE #tblTLine (
[DT] varchar(200)
)
CREATE TABLE #tblTasks (
[Tasks] varchar(200)
)
INSERT INTO #tblTasks (
[Tasks]
)
select DISTINCT
Name
from #Pivot
INSERT INTO #tblTLine (
[DT]
)
select DISTINCT
[DT]
from #Pivot
ORDER BY DT
--WHERE Active = 1
-- Build Table
DECLARE cur CURSOR FOR
select DISTINCT
[Tasks]
from #tblTasks
OPEN cur
FETCH NEXT FROM cur INTO @TaskName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER TABLE #tblTLine ADD [' + @TaskName + '] nchar(1) NULL'
EXEC (@SQL)
SET @SQL = ''
SET @SQL = 'UPDATE #tblTLine SET [' + @TaskName + '] = ''0'''
EXEC (@SQL)
FETCH NEXT FROM cur INTO @TaskName
END
CLOSE cur
DEALLOCATE cur
-- Update Table
DECLARE @SQLUpdate nvarchar(4000)
DECLARE @Time nvarchar(100)
DECLARE @Name nvarchar(100)
DECLARE @Active nchar(1)
DECLARE curUpdate CURSOR FOR
SELECT
[DT],
[Name],
[Active]
FROM #Pivot
WHERE Active = 1
OPEN curUpdate
FETCH NEXT FROM curUpdate INTO @Time, @Name, @Active
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLUpdate = 'UPDATE #tblTLine SET [' + @Name + '] = ''1'' WHERE [DT] = ''' + @Time + ''''
EXEC (@SQLUpdate)
FETCH NEXT FROM curUpdate INTO @Time, @Name, @Active
END
CLOSE curUpdate
DEALLOCATE curUpdate
SET NOCOUNT OFF
--***************************************************************************************
-- Output the Timeline table
--***************************************************************************************
SELECT * FROM #tblTLine
--***************************************************************************************
-- Cleanup
--***************************************************************************************
IF OBJECT_ID('tempdb..#Timeline') IS NOT NULL DROP TABLE #Timeline;
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..#Pivot') IS NOT NULL DROP TABLE #Pivot;
IF OBJECT_ID('tempdb..#tblTLine') IS NOT NULL DROP TABLE #tblTLine;
IF OBJECT_ID('tempdb..#tblTasks') IS NOT NULL DROP TABLE #tblTasks;
June 19, 2013 at 6:14 am
Thanks ryanwooster1980 😀
It will be much appreciated by the poor sods that still have to maintain SQL2000 databases.
I for one still have 2 old boxes that were virtualized because the hardware was getting really unreliable.
June 25, 2013 at 9:43 am
yeah I have 2 PRODUCTION db's still running this version of SQL server...I have further altered the script to make it a dynamic SP and have set the spreadsheet up to be refreshable so i just pass the server in and hey presto, easy real time checking 🙂
May 12, 2014 at 6:16 pm
Love this script and the excel template. Thanks.:-)
I have modified the line below by adding @@servername. This gives me a clearer picture on which SQL instance the report was run.
from
SELECT col1 as DT,' + @cols + N'
to
SELECT col1 as '+@@SERVERNAME+'_DT,' + @cols + N'
September 17, 2015 at 5:33 am
Very Nice with some useful tweaks in the thread.
Thanks
September 17, 2015 at 6:44 am
This looks like it's going to be fun. Thanks.
September 17, 2015 at 7:03 am
Can I recommend that you add a SQL tab to the spreadsheet and include the notes with it?
412-977-3526 call/text
September 17, 2015 at 1:22 pm
I love it.... and coincidentally I was working in the same problem but from a Powershell angle. Now I don't have to.
BTW... why do you have this:
your your WHERE statement:
andjob.name not in ('Database Mirroring Monitor Job', '<dbname>')
Is that left over from testing?
September 17, 2015 at 1:24 pm
argg... that's supposed to be <dbname>
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply