Has anyone written routines to analyze sysjobhistory? I'm looking for a tool/routine to analyze jobs for failures, trends (such as constantly increasing run times) and other information. It's not as straighforward as I had originally hoped it would be.
I am specifically prohibited from using any 3rd-party tools (too expensive per mgmt).
I would be grateful for any input/insight.
Regards,
hmscott
Hi
I'm trying to use the sysjobhistory table to find out if a job is already running.
I'm using :
select sjh.run_status
from msdb.dbo.sysjobhistory sjh,
msdb.dbo.sysjobs_view sj
where sj.job_id=sjh.job_id
and sjh.run_status=4
and sj.name='Full Backup'
Even though I know the Full Backup job is running, I never get any records returned.
I don't get anything returned using sp_help_jobhistory either.
Anybody got any ideas?
Thanks
Andy
Currently all of my scheduled tasks are completing successfully (I can see the results in output logs I create) but that aren't being written to the sysjobhistory table. Any ideas?
View 1 Replies View RelatedI see a lot of posts saying that to check if a job is currentlyexecuting one needs to look at run_status for the job in thesysjobhistory table in msdb catalog. I also see the possible valuesof that column in the help files. What gets me is that even when Iknow my job is executing (according to MMC), no record in thesysjobhistory has the run_status of 4 (In Progress). Why is that? Isthere a server-wide setting that needs to be enabled?
View 2 Replies View RelatedHi,
Can any one suggest me how to retrieve most recent job from msdb..sysjobhistory table?
I want to supply the job name which has more than 1 steps. Step 1 or more is already completed ( success/failure) and in the last step I am trying to retrieve sysjobhistory.messages(success/failure) stored in the sysjobhistory table for the steps already executed.
I want the records related with last/current job executed.
Thanks.
I am setting up a monitor to alert me if an SQL job has failed in the "last 20 minutes". This should run 24 hours a day, 7 days a week. My query looks something like this.
select * from TALMAIN.msdb.dbo.sysjobhistory where job_id = '7139D5D1-CD88-46E8-8324-5D5A0D8D3A27' and run_status <> 1 and
DATEPART(YYYY,GETDATE()) = substring(convert(char(8),run_date),1,4)and
DATEPART(MM,GETDATE()) = substring(convert(char(8),run_date),5,2) and
DATEPART(DD,GETDATE()) = substring(convert(char(8),run_date),7,2)and DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)) <= 20.
The run_date and run_time columns in msdb..sysjobhistory are stored as integers. Tried a couple of things, but I am unable to convert both of them to datetime data type. The last conditions in the above logic hold true for only "2 digit" hour and minute values.
DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)).
What about time values like 00:05 AM and single digit time values like 1:00 AM and 9:05 AM, for example?. I pasted some sample run_date and run_time values from sysjobhistory below.
run_date run_time
2006122821510 -- 02:15:10 AM (how to get the minute count?)
2006122821510 -- 02:15:10 AM (same as above)
20061227233014 -- 23:30:14 PM (this is strt forward)
20061227233014 -- 23:30:14 PM (same as above)
200612273016 -- 00:30:16 AM (how to get minute count?)
200612273015 -- 00:30:15 AM (how to get minute count?)
Is there a simpler logic to achieve this? Hope I was clear, else let me know. Please advise. Thank you.
Hi knights,
Pls tell me how to analyzing the db log. I'd appreciate your help!
Well my question is how do i analyze db growth per day. is there a tool i can use or a method. I mean i do take a look at the task view and the files but per day it doesnt move in MB wich is weird since this is a warehouse and their are nightly loads to it inserting maybe 30000 record a night on avg.
Any help would be grately aprreciated.
Hi All,
I am going over the output of a Profiler trace and I've found that the duration for many occurrences of EventClass 15 (Logout) is several seconds, up to a maximum of 20 seconds. That seems excessive just to complete a logout, so my question is, does the duration figure reflect only the time to complete the logout operation or does it include the total time that the connection has been active for?
Thanks in advance
Lempster
sql server was using a lot of the cpu so I ran a trace.
Now i see the results and there are lines with high cpu number with textdata NULL and AppicationName Internet Information Services
how can i figure out what this is?
I have to duplicate a bunch of reports that were produced by a VB6 app. Now I'm using C#, ASP.net and Crystal Reports via VS2003. Each executes a stored procedure in SQL Server (before and now).
For each report I have a Stored Procedure and a View. The View's SQL code is identical to the Stored Procedure except that the two input parameters (startDate and endDate) are removed because Views don't allow parameters.
Some of the reports work perfectly right off the bat. But others are timing out. My initial test of the timing out is to just display the View. If it fails then I know that the the report engine will fail too.
So now I'm trying out the SQL Query Analyzer tool to execute the code in one of the Views. It's now at 29 minutes and still going - at least it hasn't timed out!
My question is this: Is there a way I could examine what's going on with the query to see why it's taking so long?
Robert Werner
Hi guys,
We have 2 databases: DataBaseSource and DatabaseDestination. We need to truncate all the data in DatabaseDestination and put all the data from DataBaseSource into DatabaseDestination.
What is the best way to do that, we have a lot of data?
And what is the best way if we also need to keep a trace of what happened in case we wanna go back and see what happened.
Also , pls, if we use DTS, is it possible that if someone wants to see what the DTS does, is it possible to read the DTS? I mean if I give a dts to sompeone, a new DBA guy in 2 years for example, how can he know what a certain DTS does? I mean does SQL 2005 put the DTS packaege scripts somewhere or is there a friendly way to know what a DTS dsoes exactly?
Also the trace to see if something went bad is important for us?
Sory if i didn t express myself well enough, and thanks a lot for your help.
Rachid.
For one of our SQL server 2005 Ent edition 64 bit SP4 which has transnational replication set up and used for heavy reporting, i was trying to counter out the performance of slow running queries which basically runs and get suspended and most often are seeing waiting:So i tried to analyse the wait stats and come up with below stats where ASYNC_NETWORK_IO dominated for a collection of two weeks data.
View 15 Replies View RelatedI am trying to setup a means to analyze our servers using SQL Server 2005 Data Mining. I will be collecting all the logs from the Event Log of each server and store them in a SQL Server database for analysis.
Has anybody done this before? Can you give some pointers? I would appreciate your assistance.
For one of our database we have an issue where its log file got increased rapidly last week on Fri and Sat. The database is on SQL server 2008 R2 with compatibility level at 80. Please see below log grow events :
First, we thought Index maintenance like Re-index and update stats could have been the reason, but when check the schedule that job ran on 16th using below code:
USE ABC
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GOEXEC sp_updatestatsGO
I know above is OLD fashioned, but we believe that should not be the major cause here? How can i determine what happened on 14th and 15th which cause the event to trigger and log file bumps to 80 and 70 GB both days.
I need to discover the actual order in which locks are acquired on a table during a query.
This with a goal of analyzing the lock order of queries against the same table to prevent deadlocks.
I'm using SQL Server 2008 R2.
From Management Studio I execute:
begin transaction
<my query>
exec sp_lock
rollback transaction
In the output I see interesting information about which locks are acquired, but:
- are this locks ordered by the time they're acquired? That is, can I be sure that lock at row n is acquired before lock at row n+1?
- if not, how can I get this information?
Hello,
I have successfully installed two instances SQL Server 2005 Standard Ed. 64. and integration services (for the MSSQLSERVER instance).
When I try to install SP2 x64 everything exept the Database services of the MSSQLSERVER instance was updated well.
Here is the part of the inst.summary:
Time: 12/25/2007 13:09:26.420
KB Number: KB921896
Machine: xxxxxxx
OS Version: Professional (Build 6000)
Package Language: 1031 (DEU)
Package Platform: x64
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No
**********************************************************************************
Prerequisites Check & Status
SQLSupport: Passed
**********************************************************************************
Products Detected Language Level Patch Level Platform Edition
Unterstützungsdateien für das Setup DEU 9.2.3042 x64
Datenbankdienste (AUTODESKVAULT) DEU SP2 2005.090.3042.00 x64 STANDARD
Datenbankdienste (MSSQLSERVER) DEU SP2 2005.090.3042.00 x64 STANDARD
Integration Services DEU SP2 9.00.3042.00 x64 STANDARD
SQL Server Native Client DEU 9.00.3042.00 x64
Clientkomponenten DEU SP2 9.2.3042 x64 STANDARD
SQLXML4 DEU 9.00.3042.00 x64
Abwärtskompatibilität DEU 8.05.2004 x64
Microsoft SQL Server VSS Writer DEU 9.00.3042.00 x64
----------------------------------------------------------------------------------
Product : Datenbankdienste (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final) :
Status : Fehler
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29504
Error Description : MSP Error: 29504 SQL Server-Setupfehler beim Analysieren von SQL-Skript 'c:ProgrammeMicrosoft SQL ServerMSSQL.1MSSQLInstallsysdbupg.sql'. Fehlercode: Zugriff verweigert
. Beheben Sie das Problem, und führen Sie dann das SQL Server-Setup erneut aus, um den Vorgang fortzusetzen.
----------------------------------------------------------------------------------
Any ideas ? Olaf