Activity overview
Latest activity by dylanfinney
Repository IP Address Issue
Hello,
I'm having a strange issue where I cannot connect to my running repository. The service is running and I installed the client on the machine and verified that I could connect locally, howev...
Found something interesting.
Started a trace on one of the servers that encounters the long running queries from SQL Response. The trace captured a statement and some errors being returned.
Here's the code:
exec sp_reset_connection
DBCC TRACEON WITH NO_INFOMSGS
CREATE TABLE #dbccstat
(
TraceFlag INT,
Status INT,
)
INSERT INTO #dbccstat EXEC('DBCC TRACESTATUS(1204) WITH NO_INFOMSGS')
DECLARE @status INT
SELECT @status = Status FROM #dbccstat
IF @status = 0
BEGIN
DBCC TRACEON( 1204, -1 ) WITH NO_INFOMSGS;
END
DROP TABLE #dbccstat
The first error is barking that it can't find sp_reset_connection. The second error appears to be the same but I would expect an error on the insert because the table definition does not match the output of the DBCC command. / comments
Found something interesting.
Started a trace on one of the servers that encounters the long running queries from SQL Response. The trace captured a statement and some errors being returned.
Here's...
Sorry it took almost an entire day to get one to come through with a code snippit. The query that this fragment belongs to took over 15 seconds to run. (@table int,@index int)select GETDATE(); DBCC SHOWCONTIG ( @table, @index ) WITH TABLERESULTS, FAST
/ comments
Sorry it took almost an entire day to get one to come through with a code snippit. The query that this fragment belongs to took over 15 seconds to run.(@table int,@index int)select GETDATE...
dlkj wrote:
Hi Dylan,
I take it from the forum thread that you are regularly seeing two types of long running queries that are coming from SQL Response
One relating to xp_readerrorlog - this has now been resolved by truncating the error log
One relating to DBCC TRACESTATUS - which is still occuring
The error log reading was due to extremely large log files. I had been logging all connections (successful and failed) and one of my 3rd party applications is log in/out happy so the log files grew to be very large. I cycled the logs and that problem has ceased.
I ran the queries that you provided, had to change it to execute (code below, missing columns in temp table). It was under a second on each of the servers that I see the problem.
I have several more instances of long running query alerts coming from SQL Response, however I can't get a query fragment from any of the alerts! [image]
I will keep looking at them today and post another query fragment once I've found an alert with the data. CREATE TABLE #dbccstat
(
TraceFlag INT,
Status INT,
GLOBAL INT,
Session INT
)
INSERT INTO #dbccstat EXEC('DBCC TRACESTATUS(1204) WITH NO_INFOMSGS')
DECLARE @status INT
SELECT @status = Status FROM #dbccstat
IF @status = 0
BEGIN
DBCC TRACEON( 1204, -1 ) WITH NO_INFOMSGS;
END
DROP TABLE #dbccstat
/ comments
dlkj wrote:
Hi Dylan,
I take it from the forum thread that you are regularly seeing two types of long running queries that are coming from SQL Response
One relating to xp_readerrorlog - this has ...
Any thoughts on this? / comments
Any thoughts on this?
david connell wrote:
Hiya,
Out of interest, do you have a lot of SQL job history too? This may also be worth trimming down to a more managable size.
Regards David
Across the servers that I was seeing the behavior the job history is what I would consider reasonable. We've kept the job history log to less than 1000 rows and no more than 100 rows per job. / comments
david connell wrote:
Hiya,
Out of interest, do you have a lot of SQL job history too? This may also be worth trimming down to a more managable size.
Regards David
Across the servers that I was ...
david connell wrote:
Regarding the error log I would use sp_cycle_errorlog to reduce it back down again.
Checkout http://msdn.microsoft.com/en-us/library/ms182512.aspx for more information on that.
Regarding the trace flags, not too sure, need to investigate further. I'll ask DKJ to look into it.
Regards
David
Thanks for that, I did cycle the log file after I found that application. Helped considerably with the xp_readerrorlog problem. The other still remains however. Let me know if there is any additional information that I can provide you. / comments
david connell wrote:
Regarding the error log I would use sp_cycle_errorlog to reduce it back down again.
Checkout http://msdn.microsoft.com/en-us/library/ms182512.aspx for more information on tha...
Oops, found the problem (at least on xp_readerrorlog). I've got an application that's log in/out happy. My current SQL Server log is almost 500k records in length. / comments
Oops, found the problem (at least on xp_readerrorlog). I've got an application that's log in/out happy. My current SQL Server log is almost 500k records in length.
Here's another one. Latest fragment. DBCC TRACEON WITH NO_INFOMSGS
CREATE TABLE #dbccstat
(
TraceFlag INT,
Status INT,
)
INSERT INTO #dbccstat EXEC('DBCC TRACESTATUS(1204) WITH NO_INFOMSGS')
DECLARE @status INT
SELECT @status = Status FROM #dbccstat
IF @status = 0
BEGIN
DBCC
/ comments
Here's another one. Latest fragment.DBCC TRACEON WITH NO_INFOMSGS
CREATE TABLE #dbccstat
(
TraceFlag INT,
Status INT,
)
INSERT INTO #dbccstat EXEC('DBCC TRACESTATUS(1204) WI...
david connell wrote:
Hiya
Do you know which are the long running queries?
Out of interest how much job history do you have?
Regards
David
xp_readerrorlog (thought that may indicate more of a SQL Server bottleneck issue than application) is the typical one that I see when the query fragment is available, however there are lots of other warnings being thrown without that fragment data so I'm not sure. I could turn on tracing if that will help.
I've been using SQL Response for a little over a month now, it's been a consistent problem. / comments
david connell wrote:
Hiya
Do you know which are the long running queries?
Out of interest how much job history do you have?
Regards
David
xp_readerrorlog (thought that may indicate more of a SQ...