Hello,
I have a critical job.
When i take the view job history of that job here it has a colum named as 'duration'. It gives the exact duration of each step it has taken in its last run.
But i have the same job in test environment.
But when i take take the history here i dont see any duration column. how can i add that column.
Thank you
ichayan
I am trying to convert the @runtime to hh/mm/ss format as it's currently printing milliseconds.
Declare @runtime varchar(100)
Select @runtime = sum(run_duration)
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
where j.name = 'Accounts_From_App03'
and run_date = CONVERT(varchar(8), getdate(), 112)
Declare @Body varchar(100)
Set @Body = 'The Job completed successfully. Duration:' + @runtime
exec master.dbo.sp_SQLNotify 'App02@email.com', 'team@email.com', 'Scheduled Job Success' ,@Body
Hi
I have a full database backup scheduled at 2AM in the morning
everyday and If I see that job status in Sql Server logs
current activity , it shows me that database has been backed
up at 2AM....But the problem is when I go to jobs and try to
do a view job history it doesn't give me the details of the
job and I didn't clear the job history..Can anyone tell me the
reason why it doesn't show me the job history.
Thanks
RAGHU
How can I allow non SA accounts access to view completed Job History of certain jobs.
Development application owners may receive notification of a Production SQL job failing and have no way to see step "details" of the failed job.
I received this error while trying to view a job's history while using SSMS.
syntax error (ll DB Maint'']) (Microsoft.SqlServer.SmoEnum)
anyone see anything like this? I dropped a database yesterday that was included in the maintenance plan, but forgot to take it out of the plan itself.
Hello, I browsed the internet for an answer but all I can
find is the mention of a third party tool called log explorer
that cost a grand for a single license ... no thank you,
but basically what i want to do is be able to open the .ldf file
that is created to log transactions when you first create a database.
Or if there is another method to view transaction logs then please mention it.
I tried DBCC Log('database') which did not provide me with much information
which I can use.
Thanks in advance,
Sharp_At_C
Hi guys,
acutally i have setup a Disaster Recovery plan for my database.. i m taking a full back once in a week,. i dont' know when i right click on the job and trying to check the view history option to check when was last backup has been taken, it's showing nothing..but when i check on acutall location the backup was taken there.. i don't know y it's not writing any info in view history table.. or is it clear once in a week and i cann't see that...
Can any one tell's me about this...
I have the following SQL query
SELECT
[Req_ID]
,[Service_Name]
,[Req_Started_Date]
,[Task_Name]
,[Task_Status]
,[Performer_Full_Name]
[code]....
Which works fine, but what I need to calculate the total duration of a request based on the duration of the tasks completed in the request based on Req_ID. I would like to use the CASE statement I have to determine the SLA_Mins for each task and add them together to get total request SLA_Mins.
Below is the create table schema and data
GO
/****** Object: Table [dbo].[MidrangeOtherSourceControl] Script Date: 06/03/2015 18:13:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MidrangeOtherSourceControl](
[Req_ID] [float] NULL,
[Service_Name] [nvarchar](255) NULL,
[code]....
I want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.
how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?
Hi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below.
I've recently been given responsibility for a pair of clustered Windows 2003 servers running SQL Server 2005. Looking through SQL Server Management Studio, I found the following three maintenance plans:
- System Maintenance Plan (all system databases)
- Plan 1 (1 database)
- Plan 2 (4 databases).
All three maintenace plans had the following elements configured, in the following order:
- Check Database Integrity
- Backup Database (Full)
- Shrink Database
- Update statistics
- Clean Up History
with the exception that Plan 1 doesn't have a 'Shrink Database' task (because, I'm told, its data is pretty static), but none had a Maintenance Cleanup Task, so I added one to each plan specifying 14 days of old backups to be kept in all cases except Plan 1, where I limited it to 2 days, the database being over 7 GB in size. All links between tasks in all plans are AND constraints, value 'Completion'. All .BAKs are written to tape by Symantec Backup Exec as part of the daily backup.
Two questions from this:
1. Since .BAKs are written to separate folders for each database, do I need, for example, four Maintenance Cleanup Tasks for the Plans covering four databases - one for each folder/database? I can't see a way of specifying that a Maintenance Cleanup Task should apply to multiple databases or search subfolders based on database name, so I assume I need four consecutive Maintenance Cleanup Tasks within a single plan? Is this the way to do it?
2.The data displayed when I right-click a maintenance plan and choose View History shows:
date and time run
plan name
task name
duration
log type
log source
Since I added a Maintenance Cleanup Task to each plan, the only task shown in the Task Name column is Cleanup History (set to 'Older than 7 days'), and the duration is 00:00:00, where previously all tasks were shown and the overall duration was over a minute.
However, the .BAK files are being created (size looks OK), the application log contains entries suggesting the backups and other tasks completed successfully, and if I expand the SQL Server Logs node and view the current file I can see entries which give the same 'success' messages (presumably this log is the source of the 'success' messages in the application log?).
Why does there appear to be an anomaly between the 'View History' display and the SQL Server Log and application log? Should I be worried, or is what I can see in the SQL Server Logs and application log sufficient grounds for a good night's sleep?
Thanks,
Ed
Hi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below.
I've recently been given responsibility for a pair of clustered Windows 2003 servers running SQL Server 2005. Looking through SQL Server Management Studio, I found the following three maintenance plans:
- System Maintenance Plan (all system databases)
- Plan 1 (1 database)
- Plan 2 (4 databases).
All three maintenace plans had the following elements configured, in the following order:
- Check Database Integrity
- Backup Database (Full)
- Shrink Database
- Update statistics
- Clean Up History
with the exception that Plan 1 doesn't have a 'Shrink Database' task (because, I'm told, its data is pretty static), but none had a Maintenance Cleanup Task, so I added one to each plan specifying 14 days of old backups to be kept in all cases except Plan 1, where I limited it to 2 days, the database being over 7 GB in size. All links between tasks in all plans are AND constraints, value 'Completion'. All .BAKs are written to tape by Symantec Backup Exec as part of the daily backup.
Two questions from this:
1. Since .BAKs are written to separate folders for each database, do I need, for example, four Maintenance Cleanup Tasks for the Plans covering four databases - one for each folder/database? I can't see a way of specifying that a Maintenance Cleanup Task should apply to multiple databases or search subfolders based on database name, so I assume I need four consecutive Maintenance Cleanup Tasks within a single plan? Is this the way to do it?
2.The data displayed when I right-click a maintenance plan and choose View History shows:
date and time run
plan name
task name
duration
log type
log source
Since I added a Maintenance Cleanup Task to each plan, the only task shown in the Task Name column is Cleanup History (set to 'Older than 7 days'), and the duration is 00:00:00, where previously all tasks were shown and the overall duration was over a minute.
However, the .BAK files are being created (size looks OK), the application log contains entries suggesting the backups and other tasks completed successfully, and if I expand the SQL Server Logs node and view the current file I can see entries which give the same 'success' messages (presumably this log is the source of the 'success' messages in the application log?).
Why does there appear to be an anomaly between the 'View History' display and the SQL Server Log and application log? Should I be worried, or is what I can see in the SQL Server Logs and application log sufficient grounds for a good night's sleep?
Thanks,
Ed
Hi all
I have a table, and contains one coloum with timestamp data type,
so i can retrieve timestamp coloum data by executing following query
select CONVERT (int, timestamp_coloum ) from MyTable
Query result as follow
4003
4017
4036
4041... etc......
I just want to know is this result ( out put vale after converted timestamp to INT ) is unique for entire Database ???
Thanks
IndikaD (Virtusa cop)
If monitoring for duration with sql profiler, what does the number represent ie 2733906 is it milliseconds, thousandths, looked in BOL no clear definition
View 2 Replies View RelatedCan someone please tell me what unit of measurement the Duration column is in when running SQL Profiler? I assume milleseconds, but am not sure.
I'm trying to filter on this and want to be sure I'm not missing anything.
Thanks!
I found this nifty code on stackoverflow that works well but I'm trying to send the results to a text file and the column lengths are huge. I used CAST for the first line and it worked great but I can't seem to make it work with duration. Here's the original code:
SELECT
j.name,
h.run_status,
durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
' ','0'),4,0,':'),7,0,':'),
[code]....
how to run a CAST on DURATION?
I am trying to get a query that will allow me to report the time taken to complete a certain training module.
The database itself does not have a duration field so I am tring to get the duration by using MIN and MAX. I can get the timing for when the module was opened and the time for the last mouse click on it, from this I need to be able to calculate the time taken to complete.
Query I am using to get the basic info comes from 3 tables so I have only attached the relevent output. Query used is as follow:
SELECT *
FROM PPS_SCOS, PPS_TRANSCRIPTS, PPS_TRANSCRIPT_DETAILS, PPS_PRINCIPALS
WHERE PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND PPS_TRANSCRIPTS.TRANSCRIPT_ID = PPS_TRANSCRIPT_DETAILS.TRANSCRIPT_ID
AND PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_SCOS.NAME LIKE 'MTM-106 The Dangers of Smoking'
AND PPS_PRINCIPALS.NAME LIKE 'Nigel Cordiner'
AND PPS_TRANSCRIPTS.TICKET NOT LIKE 'l-%'
ORDER BY PPS_TRANSCRIPT_DETAILS.DATE_CREATED
Output:
pps_scospps_scospps_transcript_detailspps_principalspps_principals
SCO_IDNAME DATE_CREATED PRINCIPAL_ID NAME
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:4016287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:5716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:1416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:4716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:2116287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:4416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:21:2616287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:22:1316287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:24:5516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:1216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:26:4916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:0216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:4316287Nigel Cordiner
Have added the column heading and the tables the output comes from.
Relatively new to SQL so any help would be greatly received.
I have a query that displays number of hours duration in a jobs by months matrix. Here is a simplified version of my tables.
CREATE TABLE jobhead (
jobnum varchar (14) NULL,
jobcompletiondate datetime NULL
)
CREATE TABLE labordtl (
jobnum varchar (14) NULL ,
clockindate datetime NULL ,
clockintime decimal(6, 2) NULL ,
)
Here's an abbreviated query.
SELECT h.JobNum,
(CASE WHEN MONTH(h.JobCompletionDate) = 1 THEN datediff(day,MIN(l.ClockInDate),MAX(l.clockInDate)) ELSE 0 END) AS JAN,
(CASE WHEN MONTH(h.JobCompletionDate) = 2 THEN datediff(day,MIN(l.ClockInDate),MAX(l.clockInDate)) ELSE 0 END) AS FEB,
...
FROM JobHead h
INNER JOIN LaborDtl l ON h.JobNum=l.JobNum
WHERE JobCompletionDate>='20070101' AND JobCompletionDate <'20080101'
AND l.ClockInTime<>0
GROUP BYh.JobNum
,h.JobCompletionDate
The query shows, for each job, the month in which the job completed, and the number of hours it took to complete. I€™m calculating the number of days€™ duration by doing a datediff between the oldest and newest clockindates. I need to ignore adjustment transactions in the labordtl table €“ these rows are easily identified as they have clockintime values of 0. So far, so good. Now here€™s my problem.
There are some jobs which have only one €śreal€? labor transaction €“ this could happen if the job only took one day to complete. Other labor transactions may exist for that job, but let's say they are adjustments which we can ignore -- the date they were entered should not extend the duration of the job. In this situation, my datediff between the oldest valid transaction and the newest, returns 0. I don€™t have to count hours between clockintime and clockouttime. The rule is simply that if there is only one "real" labor transaction, I need to count this as a 1 day job.
I thought a nested CASE statement or expression might be the way to go but I didn't make any real progress.
Any ideas to solve this problem would be appreciated.
Hi!
Does anyone know how to query for the job duration of all jobs in a server?
Thanks!
I'm developing a web app that displays the running packages and the total elapsed time. I'm calling GetRunningPackages() method and using the ExecutionDuration property of the returned package. The duration seems to be only for the currently executing container and not the entire package. Is there a way to get the duration of the entire package? Thanks.
View 2 Replies View RelatedHi all, I want to find working duration between two datetimes in c#.i'm using following code... DateTime starttime = Convert.ToDateTime(Session["StartTime"]); DateTime endtime = DateTime.Now; TimeSpan duration = endtime - starttime; DateTime period = new DateTime(duration.Ticks); i want to store this duration in database through stored procedure, i've give datetime datatype to duration but it is giving error in conversion of TimeSpan to DateTime..Please help... Thanks
View 1 Replies View RelatedSQL 6.5 - run duration 6-7 hours
SQL 7.0 - run duration 12-13 hours
175+ columns with total record size=570
4.2M records with tablesize 2.5G
It's a simple 'select into...' with some embedded logic from a work table with all char fields into the actual table converting char fields into various data types (int, datetime, real, etc.) Here is a sample of the code:
SELECT
LoanNum=CASE
WHEN ISNUMERIC(ACCT#)=1 THEN CONVERT(int,ACCT#)
ELSE NULL
END,
PaidToDt=CASE
WHEN PAIDDT = '0001-01-01' THEN NULL
WHEN ISDATE(PAIDDT)=1 AND SUBSTRING(PAIDDT,1,2) = '19' THEN CONVERT(smalldatetime,PAIDDT)
WHEN ISDATE(PAIDDT)=1 AND SUBSTRING(PAIDDT,1,2) = '20' AND SUBSTRING(PAIDDT,3,2) < '79' THEN CONVERT(smalldatetime,PAIDDT)
ELSE NULL
END,
.
.
.
INTO db.owner.tablename
FROM db.owner.wrktablename (NOLOCK)
Hearing complaints from users about speed on db server (I have almost no control on design) it just has to work. Ran profiler looking for all sql statements over 4000 millsec and in one hour returned over 715 tsql statements. Over 300 of these were over 10000 milliseconds. THis is on an 8 way Dell with 8 gig of RAM. Looking for opinions, how bad does this look compared to other servers you are taking care of? Cache hit ratio is at 99 % and system queue length still under 1, but this does not look good.
View 2 Replies View RelatedHello, everyone:
There is a trigger to monitor the modification on a table, and it turn on. For a special duration, I need to turn off this trigger to modify the table. And then turn on the trigger again.
Any help will be appreciated.
Thanks
ZYT
I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:
Ticket_No (int)
Machine_No (int)
Description (char)
Start_Time (datetime)
End_Time (datetime)
I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example:
If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.
Can anyone help me get started in tackling this problem or provide any examples?
Is there anyway to tell how long this will run for -- or how far it has got? I have a large database that has just had most of the data removed. The command has been running for 8 hours and I have just stopped it to let something else run quickly. Any way of telling how much longer it will take?
View 7 Replies View RelatedIn my example, i have a table as below:
Time|Machine
2013-08-05 10:12:00 |M1
2013-08-05 10:15:00 |M1
2013-08-05 10:20:00 |M1
2013-08-05 10:11:00 |M2
2013-08-05 10:15:00 |M2
2013-08-05 10:16:00 |M2
2013-08-05 10:17:00 |M2
2013-08-06 11:12:00 |M1
2013-08-05 11:42:00 |M1
2013-08-05 12:02:00 |M1
I want to have a report for every machine by time that will show how long each machine worked. The result is something like below:
M1|8 (minutes)
M2|6
M1|50
/*
This SP has 2 functions.
a) if @method='duration' gives the average run duration in minutes for successful jobs
b) if @method='failures' displays failures/cancels/still executing jobs
It defaults to today's date. Specify @xdate for a different date
-- Louis Nguyen
*/
CREATE PROCEDURE UtilityJobsHistory
(
@method varchar(100)='duration'
,@xdate datetime=null
)
AS
set nocount on
set transaction isolation level read uncommitted
if @method='duration' begin
select @xdate=isnull(@xdate,getdate())
/*run_duration is in HHMMSS format; drop SS*/
/*run_staus: 1 complete 2 retry*/
/*step_id: 0 is final job outcome*/
/*run_date: yyyymmdd format*/
/*today's performance*/
select a.name,minutes=avg((b.run_duration / 100)/100*60 + (b.run_duration / 100)%100)
into #today
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
where run_status in ('1','2') and step_id=0 and run_date =convert(varchar,@xdate,112)
group by a.name
/*7 day average performance*/
/*populate #D with dates in yyyymmdd format*/
create table #D (run_date varchar(50))
declare @idate datetime set @idate=@xdate
while @idate>dateadd(day,-7,@xdate) begin
insert into #D
select run_date=convert(varchar,@idate,112)
select @idate=dateadd(day,-1,@idate)
end
/*Avg7Days*/
select a.name,minutes=avg((b.run_duration / 100)/100*60 + (b.run_duration / 100)%100)
into #avg7Days
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
join #D as c
on b.run_date = c.run_date
where run_status in ('1','2') and step_id=0
group by a.name
/*output*/
select name=cast(a.name as varchar(35)),OneDayAvg=a.minutes,SevenDayAvg=b.minutes
from #today as a
join #avg7days as b
on a.name=b.name
order by a.name
return end
if @method='failures' begin
select @xdate=isnull(@xdate,getdate())
select status=case run_status when 0 then 'FAILED' when 3 then 'CANCELED' when 4 then 'EXECUTING' end
,name=cast(a.name as varchar(35)),step_name
,time=replace(convert(varchar,@xdate,107),' ','')+' '+right('0000'+cast(b.run_time/100 as varchar),4)
,b.message
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
where run_status in ('0','3','4') and run_date=convert(varchar,@xdate,112)
order by run_status,a.name
return end
Sarfaraz writes "I have captured SQL Profiler data. I was reviewing top running CPU intensive SQL statements. The Duration (in seconds) 1.39, 1.09, 0.16 and CPU (in seconds) 0.97, 0.95, 0.16 respectively for some SQL statements. How do I know what is the normal baseline for duration and CPU in order to determine the CPU intensive SQL statements.
Secondly same question for long running procedure duration 0.14, 0.11. What is the normal baseline here. Is this normal or too long.
Thanks,
Sarfaraz"
Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process.
I have a table that consists of: Order#, Processes, Time_In, Time_Out.
Order# can be 1, 2, 3, 4, 5.
While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping.
Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process.
For each process we will have recorded time when the order# comes in and when it finishes with each process.
I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.
I.E.:
Order# | Process | Time_In | Time_out
1 | Receiving | 2007-12-1 10:00:00.000 | 2007-12-1 10:10:00.000
1 | Incoming Q.A. | 2007-12-1 10:40:00.000 | 2007-12-1 11:42:00.000
1 | Visual Check | 2007-12-2 08:10:00.000 | 2007-12-2 11:00:00.000
1 | Shipping | 2007-12-2 11:20:00.000 | 2007-12-2 11:52:00.000
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx
Please help.
Thanks in advance.
Hi!
I'm using RDA (Remote Data Access) to pull 20 tables to my Pocket PC. It took quite a long time so I ran a trace to see what happened. Everything looks fine except for when it runs:
exec [mydb]..sp_primary_keys_rowset N'Person',NULL
The duration is: 18446744073!!!
A couple of more tables has this enomous duration others have about 5000 which seems more normal.
Any clues?
/Magnus
Jesus saves. But Gretzky slaps in the rebound.
i want to get the execution duration of the ssis package and insert into one table,so i used a variable--"duration" and specified it's expression is getdate(). I think that only i can get the start value of the varible and the finish value,then i can know the duration.but whether i can get these two values? or better way i should try?
thanks
Hi Experts,
We have a VLDB ( few table with above 200 million records ). This database is used for performance testing by simulating for 150 users and executing all necessary functional flows.
When I examined the profiler results , I could see some very high values as shown below in the duration column for many events.
1521729
3462142
1624325
3211255
1248276
3903998
Does it mean that that SP or the T-Sql statement is taking this much time in milliseconds to execute and give the output ?
Any help would be greatly appreciated.
Thanks & Regards,
DBLearner
Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process.
I have a table that consists of: Order#, Processes, Time_In, Time_Out.
Order# can be 1, 2, 3, 4, 5.
While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping.
Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process.
For each process we will have recorded time when the order# comes in and when it finishes with each process.
I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.
I.E.:
Order# | Process | Time_In | Time_out
1 | Receiving | 2007-12-1 10:00:00.000 | 2007-12-1 10:10:00.000
1 | Incoming Q.A. | 2007-12-1 10:40:00.000 | 2007-12-1 11:42:00.000
1 | Visual Check | 2007-12-2 08:10:00.000 | 2007-12-2 11:00:00.000
1 | Shipping | 2007-12-2 11:20:00.000 | 2007-12-2 11:52:00.000
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx
Please help.
Thanks in advance.