Red Gate Long Running Query

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Red Gate Long Running Query

Postby lrobbins » Thu Oct 31, 2013 2: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;
lrobbins
 
Posts: 5
Joined: Thu Jan 03, 2013 10:31 pm
Location: United States

Postby Brian Donahue » Mon Nov 04, 2013 12:15 pm

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: 6669
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 1 guest