Monitoring a data import - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Monitoring a data import

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-04-13 : 06:30:20

Hello

I have a data import within one of our sage systems that runs overnight that takes several hours to complete. The backend database is SQL server 2005.

I have been asked to monitor the amount of time it takes to complete the import, Is there any way of doing this within SQL server or would it be a Windows event that I would need to monitor.

I have already looked at Activity Monitoring but can only see an event start time and not a completion time.

I cannot find a way of doing this within the application itself.

Can this be achieved within SQL server?

Thanks

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2010-04-15 : 05:00:43

How is the job scheduled? Is there any job history in the scheduler?
Is there a log file, with the start and end times for each run?

You could use SQL Server Profiler. Or check with the vendor.

Kristen
Test

22859 Posts

Posted - 2010-04-15 : 05:39:21

"I have a data import within one of our sage systems that runs overnight that takes several hours to complete"

Sounds to me (assuming we are not talking about 100s-of-millions-of-rows) that the improt method is inefficient and needs writing differently.

I presume you do actually know that it takes hours - in which case IMHO it needs fixing, rather than working out exactly how long it takes!

If you describe the process here then I expect folk will have suggestions as to how it can be improved.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 06:20:49

You could use this to get the duration of the job(s) to check the SQL part of it.

As Kristen said though: if it is taking hours then you are doing something wrong.


SELECT
	sj.[name] AS [Job Name]
	, CASE
		WHEN sj.[enabled] = 1 THEN 'Normal'
		WHEN sj.[enabled] = 0 THEN 'DISABLED'
		WHEN sj.[enabled] IS NULL THEN 'NO JOB SET UP!'
	END AS [Job Status]
	, ISNULL(CONVERT(CHAR(11), sja.[start], 106), '') AS [Last Run Date]
	, ISNULL(CONVERT(CHAR(5), sja.[start], 108), '') AS [Last Run Time]
	, ISNULL(STUFF(CONVERT(CHAR(8), DATEADD(SS, sja.[duration] % 86400, 0), 8), 1, 2, CAST(sja.[duration] / 3600 AS VARCHAR(12))), '') AS [Duration]
FROM
	msdb.dbo.sysJobs sj

	LEFT JOIN (
		SELECT
			[job_Id] AS [jobID]
			, [start_execution_date] AS [start]
			, [stop_execution_date] AS [stop]
			, DATEDIFF(SS, [start_execution_date] , [stop_execution_date]) AS [duration]
			, [last_executed_step_Id] AS [lastStep]
			, ROW_NUMBER() OVER (PARTITION BY [job_ID] ORDER BY [start_execution_date] DESC) AS [pos]
		FROM
			msdb.dbo.sysJobActivity
		)
		sja ON sja.[jobId] = sj.[job_Id] AND sja.[pos] = 1

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources