Red Gate Long Running Query

Monitor, diagnose and receive alerts to problems across your SQL Servers.

Red Gate Long Running Query

Postby lrobbins » Thu Oct 31, 2013 1:16 pm

I have a notification of a long running query for a database on one of my servers. When I run a sp_who2 active, the query is still active. Below is the query and it seems to be from Red Gate. When I run this code in a query window, it doesn't take that long to execute. Why is this query running for a long time on this one database?

DECLARE @endtime DATETIME; SET @endtime = GETDATE();
DECLARE @starttime DATETIME; SET @starttime = DATEADD(hh, -1, @endtime);
IF OBJECT_ID('tempdb..#LogEntries') IS NOT NULL DROP TABLE #LogEntries;
CREATE TABLE #LogEntries ( LogDate DATETIME , ProcessInfo VARCHAR(1000) , LogMessage TEXT ); INSERT INTO #LogEntries
EXEC sys.xp_readerrorlog 0, 1, N'Login', N'failed', @starttime, @endtime;
SELECT COUNT(*) FROM #LogEntries; DROP TABLE #LogEntries;
Posts: 5
Joined: Thu Jan 03, 2013 9:31 pm
Location: United States

Postby Brian Donahue » Mon Nov 04, 2013 11:15 am

It's trying to read the error log from SQL Server to find out how many login failures there were. The run time of the query depends on how long the error log is and how quick the network is. You can try changing the SQL Server setting that controls how often SQL Server recycles its' error log so there is less to be read.
Brian Donahue
Posts: 6590
Joined: Mon Aug 23, 2004 9:48 am

Return to SQL Monitor Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests