Transact SQL :: Get Duration Of Each Event 0 Which Has Other Events Before And After?
Jun 22, 2015
I have a table that holds log event records, that keep getting appended. I need to get the duration of the each event 0 which has other events before and after. How can I do this - to get event duration of event 0 and the cumulative.
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,
When you view the Extended Events "Watch Live Data" is the duration in milliseconds or microseconds? I'm assuming it's milliseconds, but if you look at the timestamp difference from start to complete it doesn't add up to the duration amount? It looks like it's just the difference between the timestamps?
I am wanting to get/filter on all queries and procs that take longer than 2 seconds to run (I'll balance real values later) but I'm not sure which Action out of the XE that I need.
I am using SQL Server 2014 and thought I had used sqlserver.sql_statement_completed.duration > 2000 in a previous version.
Thanks for setting up such a great site and forum.
Here is my problem:
I have a table like the following in SQL Server 2005:
order | taskid | main_person | temp_person | start_assign_date 1 | 3 | John | John | 2008-01-01 10:20:22 2 | 3 | John | Joe | 2008-02-05 15:20:22 3 | 3 | John | John | 2008-02-07 20:25:20 4 | 6 | Joe | Joe | 2008-01-01 10:20:22 5 | 6 | Joe | Mike | 2008-02-01 10:20:22 6 | 10 | Doug | Doug | 2008-01-01 10:20:22 7 | 7 | Russ | Russ | 2008-02-01 11:20:22 8 | 7 | Russ | Mike | 2008-02-08 12:20:22 9 | 7 | Russ | John | 2008-02-10 20:05:12
It was made to record who was in charge of a specific task at a specific time. Each task has its own main responsible person and some substitutes for that person as Temporary Persons (who did the task while main person was away). The Main Person's name is in the temp_person column when he is doing the task by himself.
I'd like to generate a report that shows: - in a specific time period - which persons were in charge of a specific task and - for how long
Something like this:
From 2008-##-## to 2008-##-## Task 3 - John - 15 days Task 3 - Joe - 5 days Task 6 - Joe - 18 days Task 6 - Mike - 2 days Task 10 - Doug - 20 days
I have some ideas to do that when there are both start and end dates for every record but I couldn't find a way to use the next assignment start date for each task, as the end date for its previous record (in that task group) to calculate the duration for that record.
I can group the tasks and users and put them in the chronological order but I can't indicate the next start date as the end date for the previous record (in specific task group) to use the date difference functions.
Hi everyone, I was wondering how do for 'Log Events' info going to Event Viewer Windows visor. Is it possible or it's just only private info for you SSIS.
I am backing up my all the databases (around 50 servers and 500 database) using scheduled jobs. I backup my transaction log files at every two hours of interval and databases on daily basis.
I have set option of "Write to Windows Application Event Log" - When job fails during the creation of job in the "Notification Tab" of Create New Job.
As per documentation SQL server automatically records some of the events to windows application event log. After succesful completion of Log backup and DB backup, event gets logged in Windows Application Event Log. And when job fails, I get two events logged in the Windows Application Event Log file, one due to my setting and other automatically by SQL Server.
Due to this Windows event log file is growing much faster and I have to clear it in every 3-4 days.
My quesiton - Is there a way such that to get only failures event logged in Windows Application event log file and any successful backup jobs should not go to Windows application event file?
Can any suggest some idea what should I do?
I am using SQL Server 2000 with SP3 on Windows 2000 Advanced server. Some of my databases are still using Version 7.0/6.5 on NT.
Hi, I am looking to implement a custom event handler that will also retain the original event arguments (in addition to several custom arguments). Specifically, I am looking to pass custom arguments into a SqlDataSourceStatusEventHandler, but also want to be able to access the Command.Parameters. I have implemented a new Event Arguments class (derived from System.EventArgs), new Event class and delegate, but do not know how to retain the SqlDataSourceEventArgs. I would really appreciate your suggestions! Thanks!
I was wondering if anyone knew of a way to disable the following, SQLISPackage start/finish events sent to windows event logs everytime a SSIS package is executed and completed.
I am trying to use xquery to get event data back from extended events. I am trying to use some sample data from Grant Fritchey but I am getting null records back. Below is the xml - I just want to retrieve a distinct list of the client_hostname and client_app_name.
WITH xEvents AS (SELECT object_name AS xEventName, CAST (event_data AS xml) AS xEventData FROM sys.fn_xe_file_target_read_file ('C:LoginTraceShared_0*.xel', NULL, NULL, NULL)) SELECT distinct top 1000 xEventName, xEventData.value('(/event/data[@action_name=''Client_APP_Name'']/value)[1]','varchar') Client_APP_Name, xEventData.value('(/event/data[@action_name=''Client_Host_Name'']/value)[1]','varchar') Client_Host_Name FROM xEvents
We know we can use the event lock_deadlock and xml_deadlock_report to capture the deadlock info, however I also want to capture the execution plans for all of the SPIDs in the deadlock graph, how to output the execution plans to the extended events trace results either ? such as if there is an action for execution plan or workaround for it ?If there is no built in action for execution plan , may I know if we can add the customized info to the extended events results file also ? Such as when the deadlock related event happens , then we can run a query to get some info ,then added the info along with other info such as sql_text, dbname etc to the events trace results file either ? The reason is if we also know the execution plans when the deadlock happens, it is useful to turning the query based on the execution plans to reduce deadlock happening .
Now I need to get average duration per day for every month. Is this possible? I have a calendar table that has every single day month year microsecond millisecond second minute and hour.
Means If ID is same for two or more than two records then difference between first row's EndDate and second row's StartDate is 1 day then we should get one record as output. How can we built this logic in T-SQL ?
Need calculating the count of events on each day. I have an event log table as shown below.
DECLARE @EventLog TABLE ( event_id int, status_id varchar(20) , dttm datetime ) INSERT INTO @EventLog SELECT '100','Inplace','01/05/2015 05:00' UNION ALL SELECT '100','Removed','03/05/2015 10:00' UNION ALL SELECT '100','Inplace','05/05/2015 10:41' UNION ALL
[Code] ....
I need an output to look like on each day how many events were in place.
E.g.: for the month on May , Event_id 100 was inplace on 1st and removed on 3rd , so on 1st may 2 event_ids (100, 103) hence 2 .
event_ids - 103 / 104 and inplace the whole month and not removed. so its calculated the count as 2 til towards teh end of the month.
So I need to count of all each event_id in place for each day in may.
We are planning to convert or change all existing Traces to Extended Events in SQL server 2012. What is the procedure to convert custom traces. We have already created some below custom traces: like this we are planning to convert for all servers.
Hello experts. I have been searching for anything about this but found very little. What are the events logged in SQL Server Error Logs aside from Successful/Failed Login, Backup/Restore/Recover database and, start/init sql server? Can we configure this to log other events, like CREATE or DBCC events for example? If so, how? Thanks a lot.
I have a log table in which alarm events are logged (a many relation to a main table) where EventId is the foreign key in the log table.
In this table all events related to a given incident (EventID) are logged, but it is specifically two eventtypes which is of interest, thats the "into alarm" represented by an integer value "0" in the column "EventTypeId" and the "restored represented by an integer value of "2". Each state of change has a timestamp of datetime.For each incident (EventId) there can be multiple "into alarm" and "restore" rows, there can also be other events which has no interests. Only the timediff between each "0" and "2" accumulated is of interest.
There's one more little twitch, it can occure that when the query is run, the "2" restored state is not yet present, the query should not return a result for that "0" into alarm when the "2" restored is not present, unless there was a complete alarm/restore session.
CREATE TABLE [dbo].[AlarmHistoryLog]( [EventId] [int] NOT NULL, [EventSeq] [int] IDENTITY(1,1) NOT NULL, [EventTypeId] [int] NOT NULL, [EventTime] [datetime] NOT NULL, [EventDescription] [nvarchar](250) NOT NULL, [Comment] [nvarchar](4000) NULL, [UserID] [int] NULL, [ReceiverID] [int] NULL ) ON [PRIMARY]
I have a query that returns material(items) that are used in an event on a certain day.
SELECT C.categoryName, count(I.itemID) AS InMission from items as I RIGHT JOIN Categories AS C on I.categoryID = C.categoryID INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID INNER JOIN Missions as M on LM.missionID = M.MissionID where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0 GROUP BY C.categoryName, C.categoryID ORDER BY C.categoryID
There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM Log SQL Server (Current - 10/25/2011 10:55:00 AM) Source spid Message Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file. The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30 WHERE targetinstance isa 'CIM_DataFile' AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message. However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup) We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers. We were having
Event Type: Failure Audit Event Source: Security Event Category: Object AccessEvent ID: 560 Date: 5/23/2007 Time: 6:27:15 AM User: domainuser Computer: MACHINENAME Description: Object Open: Object Server: SC Manager Object Type: SC_MANAGER OBJECT Object Name: ServicesActive Handle ID: - Operation ID: {0,1623975729} Process ID: 840 Image File Name: C:WINDOWSsystem32services.exe Primary User Name: MACHINE$ Primary Domain: Domain Primary Logon ID: (0x0,0x3E7) Client User Name: User Client Domain: Domain Client Logon ID: (0x0,0x6097C608) Accesses: READ_CONTROL Connect to service controller Enumerate services Query service database lock state
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month], FROM RECalendar WHERE (EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101))) AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
If monitoring for duration with sql profiler, what does the number represent ie 2733906 is it milliseconds, thousandths, looked in BOL no clear definition
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:
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.
