SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),5,2) 'Start Date Time'
From time to time, history info on our scheduled jobs (SQL Server 7.0) is missing. When I try to view job history, I get a popup saying "There is no history information for this job". Sometimes the job does run, however, sometimes it doesn't... The problem seems to happen randomly on random scheduled jobs. Any tips on how to get back the consistency of info we used to enjoy in 6.5?
Also, is there a way to specify how many job runs the history should display. It only goes as far as 5 or so, I wonder if it can capture more "runs"?
Please email back the answer to lpinskaya@firstam.com - it would be GREATLY appreciated.
To monitor the status of replication in SQL 2000 we insert the resuts of sp_MShelp_replication_status into a temp table and review the status. In SQL 2005 this stored procedure has changed and now does it's own insert into execute command. Since insert execute commands can not be nested we must find another way to programically monitor replication using TSQL. We could call sys.sp_replmonitorhelppublisherhelper but we can't seem to find it in the distribution database. It must be hidden somehow. Any ideas?
I am trying get the status field from the list of jobs in management. I have a activex script that queries system tables to find out if a job has failed and sends the report via e-mail.
Currently I use this query to get the names of jobs failed... SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0 ORDER BY name
I need to add some criteria around the status field to get the appropriate results.
I am a Junior DBA and i have to checks the various jobs on different servers.Please help me with a T-SQL way by which i can check the Job status through a Query.
now this bat file only start the job, does not wait for it completion or send us the success or failure message.Now i want to check the value of LastRunStatus for this job using BAT file?? Is is possible to fetch the LastRunStatus of sql job using bat file.However i have achieved the same using vbscript that starts a job using vbscript start method and then looping through al the jobs at server and displaying the LastRunStatus. However i wanted to achieve this uisng bat file only.
I am a newbie to tsql. I am trying to find if a job has failed, if so I only want to pickup the lastest job. I want to see name,message and run date. I know I need to find the max run_date and run_time for each job_id. I know that I need to join the sysjobhistory table and the sysjobs tables. I also know that I need status not equal to one. I have tried thousands of different things and nothing seems to work. If I ever get this to work then I only want to look at jobs in the last 24 hours. I am not going to include any of my job since I have tried so many things and I have been all over the map.
I have two tables t_DTM_DATA_STAGING around 2 million records t_DTM_DATA around 251 million records
The below SQL statement looks for records in the t_DTM_DATA_STAGING table that are not in the t_DTM_DATA table and adds them to a 3rd table. (t_DTM_DATA_STAGING2)
This statement has been running fine for weeks, but now it seems to get hung every day. I ran sp_Who2 and it says the status is runnable. I let it run for around 5 or 6 hours the other day to see if it will finish but it didn't. This SQL job is step 3 in a 6 step SQLAgent job that usually finishes in 30 to 45 minutes.
I'm not sure how to troubleshoot this problem. No other jobs are running at the time this job runs.
Could this SQL statement be written a better way?
Thanks for any help anyone can provide.
Jerid
SET QUOTED_IDENTIFIER ON
INSERT INTO [DTM].[dbo].[t_DTM_DATA_STAGING2] ([CP],,[MAJ],[MINR],[LOCN],[DPT],[YEAR],[PD],[WK],[TRDT],[SYSTEM],[AMOUNT],[DESCRIPTION],[GROUP],[VENDOR] ,[INVOICE],[IDAT],[PO_NUMBER],[DDAT],[RCV#],[RDAT],[RSP],[EXPLANATION],[UPLOAD_DATE],[UPLOAD_USER],[UPLOAD_NAME] ,[RELEASE_DATE],[RELEASE_USER],[RELEASE_NAME],[TRTM]) SELECT t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT, t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT, t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT, t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP, t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME, t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM FROM t_DTM_DATA_STAGING LEFT OUTER JOIN t_DTM_DATA AS t_DTM_DATA_1 ON t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM AND t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT AND t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD AND t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR AND t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT AND t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN AND t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR AND t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ AND t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO AND t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP WHERE (t_DTM_DATA_1.CP IS NULL)
I am trying to find out CPU utilization from the history using process.%processor time. I am having dual core CPU with 2 numa nodes each having 16 logical cpus bind to it.
how to calculate the CPU utilization using perfmon.I tried to use SQL query which gives CPU history using SQL DMV, but I am unable to get the exact value. Because in between I have used the same querry to capture my CPU usage on the run day, the value on run day and the query which iam tryting to pull out is different. I am using the same query to pull the history data with providing the date.
-- Get CPU Utilization History (SQL Server 2008 and above)
DECLARE @ts BIGINT SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info); SELECT SQLProcessUtilization AS [SQLServer_Process_CPU_Utilization], SystemIdle AS [System_Idle_Process], 100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization],
We have development and user acceptance (UA) servers. When I start a job on the development server, on the management studio, Start Jobs window and Job activity windows indicate "Executing" until the end of the job and finish with "success" or "failure"
But on the UA server, second after I start a job, Start Jobs window comes up with "success" or failure" and Job activity monitor says "idle" but Job continues to log without any error message and updates the tables. So these two windows are not reliable at all. I have to add that I have only job operator rights on the UA server.
T-SQL for checking which jobs are currently running ? And what happen if index rebuild job overlap with shrink log file job Is database wont allow to insert or update for other application
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
I need to know if there is any way to extract all our jobs/dts packages where the 'sa' password is being used in either the job steps or any stored proc the sa pwd harcoded in it.
I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.
can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple. I have used sql server 2000 and i am new in sql server 2005.
What is the best way of reliably closing a cursor inside a BEGIN CATCH block? The simple problem statement is: I want to check if the cursor is open, then close it. I'm trying to use CURSOR_STATUS function and it seems to return a -3 (both when the cursor is open and not open.). Is this a bug or am I missing something?
I have removed all of my code and only provided what is necessary to repro the problem.
I would like to create a query to find what user owns the job. It probably is in the master db, but I wouldn't know where to begin other than that. Telling me how to either change the job owner or create a job through t-sql would also help. Thanks -Kyle
Hi, This is the situation: There are around 300 jobs on the server. The server shutsdown abnormally and is not functional for let's say 2 hours. After the server is back on, I need to find what jobs had to start during the server was down. Is there a way to get this list? I tried to write an SQL script that tells me the needed information, but the job schedule sheme is so complex that it would take too much time in developing such SQL script. Maybe anybody has a better solution, or has already made a similar script? ThankYou.
I try to find out how many jobs where run in parallel on my server in an interval of time. For example: between 1:00 AM and 2:00 AM there were MAX 66 jobs that run in parallel and MIN 4 jobs. I am not sure if I can find this info out from a system view or I need to play with sysjobhistory view.
I was wondering if there was a SQL query I could run that would list all the current jobs in a SQL 2005 database? I would like to check to ensure a job exists before I call it, but was having trouble finding a way to determine if it existed.
I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name. Some tables have slight differences in terms of data types or Data type lenght.
For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.
I am using SQL Server 2005. And the Two Servers are Linked Servers
What Script can i use to accomplish this task. Thanks
When I give job Id in filter of this query this will give job status of "Success" but actually my job is currently in executing stage. I want to get all jobs that are currently in executing status.
Use msdb go select distinct j.Name as "Job Name", --j.job_id, case j.enabled when 1 then 'Enable' when 0 then 'Disable'
Help. Several people have told me to connect to SSIS from SSMS, but I don't know how. Can someone PLEASE tell me how to connect to SQL Server Integration Services from Management Studio?
Does anyone know where to find a simple sample showing you how to use asp.net 2.0 with the login control to direct you to another aspx page using SQL 2000 as the database on a hosted server? I have been beating my head against the wall trying to figure this out and would be very appreciative if anyone knows a place out there that shows this? I can find many sites that show you how to do this with SQL 2005, but not SQL 2000. Thanks - Chris
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1 -- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2 -- PK and Index select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005] I need a sql whihc will return the same result for sql server 2000
