How can we help you today? How can we help you today?
cjlotz
Activity overview Badges (0) Posts (13) Comments (19)

Activity overview

Latest activity by cjlotz

We can't see the detailed time values in the SQL Monitor log entries, but the alert is raised at the same time (up to minutes) than the entries in the SQL trace files. All other details in the SQL trace matches the SQL Monitor log entry, but as illustrated, SQL Monitor shows considerably less content than the SQL Server trace logs. / comments
  1. Community
  2. SQL Monitor Previous Versions
  3. Troubleshooting Deadlocks
We can't see the detailed time values in the SQL Monitor log entries, but the alert is raised at the same time (up to minutes) than the entries in the SQL trace files. All other details in the SQL...
0 votes
After some more digging I came to the same deadlock that was logged using the previous version of SQL Monitor. In the old version the log gave us the correct information to point out the error. Old Version Log: SQL query fragment: Language Event: INSERT INTO astTasks(Id,Version,LastModifiedOn,LastModifiedByUserId,Code,Description,AlternativeDescription,SiteId,IsActive,Sequ New Version Log: SQL query fragment: Language Event: (@Id int,@Version int,@LastModifiedOn datetime,@LastModifiedByUserId int,code nvarchar(50),@Description nvarchar(255),@AlternativeDescription nvarchar(255),@SiteId int,@IsActive bit,@SequenceNumber smallint,@DevelopmentStatus int,@LastConfirmedOn datetim You'll notice that in the new version it starts off with the parameters, thus not giving us the table that it occurs on. With regard to the fragment. I've switched on tracing on SQL Server 2008, by issuing the following command: DBCC TRACEON (1222, -1) In SQL Monitor I get this fragment: SQL query fragment: Language Event: SELECT T.Id, RUA.Id AS RollupAssetId, RUA.Code, RUA.Description FROM astAssetTree AT INNER JOIN astTasks T ON T.AssetId = AT.AssetId INNER JOIN astAssets A ON A.Id = T.AssetId INNER JOIN If I look in the SQL Server Error Log I get the following: 2011-01-11 12:10:22.27 spid4s Node:2 2011-01-11 12:10:22.27 spid4s PAGE: 8:1:367896 CleanCnt:2 Mode:S Flags: 0x3 2011-01-11 12:10:22.27 spid4s Grant List 1: 2011-01-11 12:10:22.27 spid4s Owner:0x00000000B1ACB700 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x000000019D09F5F0 2011-01-11 12:10:22.27 spid4s SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1 2011-01-11 12:10:22.27 spid4s Input Buf: Language Event: SELECT T.Id, RUA.Id AS RollupAssetId, RUA.Code, RUA.Description FROM astAssetTree AT INNER JOIN astTasks T ON T.AssetId = AT.AssetId INNER JOIN astAssets A ON A.Id = T.AssetId INNER JOIN 2011-01-11 12:10:22.27 spid4s Requested by: 2011-01-11 12:10:22.27 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000127C4E8F0 Mode: IX SPID:55 BatchID:2 ECID:0 TaskProxy:(0x00000001AA68C538) Value:0xdd1a3fc0 Cost:(0/70416) 2011-01-11 12:10:22.27 spid4s 2011-01-11 12:10:22.27 spid4s Victim Resource Owner: 2011-01-11 12:10:22.27 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000019D09F5B0 Mode: S SPID:63 BatchID:2 ECID:0 TaskProxy:(0x00000001561A0538) Value:0xb3138dc0 Cost:(0/0) 2011-01-11 12:10:22.27 spid21s deadlock-list 2011-01-11 12:10:22.27 spid21s deadlock victim=process3e89708 2011-01-11 12:10:22.27 spid21s process-list 2011-01-11 12:10:22.27 spid21s process id=process3e89708 taskpriority=0 logused=0 waitresource=PAGE: 8:1:314426 waittime=3859 ownerId=2081590891 transactionname=SELECT lasttranstarted=2011-01-11T12:10:16.950 XDES=0x19d09f5b0 lockMode=S schedulerid=2 kpid=3820 status=suspended spid=63 sbid=2 ecid=0 priority=0 trancount=0 lastbatchstarted=2011-01-11T12:10:16.950 lastbatchcompleted=2011-01-11T12:10:16.950 clientapp=.Net SqlClient Data Provider hostname=PRODS-HARM hostpid=4604 loginname=OnKey5 isolationlevel=read committed (2) xactid=2081590891 currentdb=8 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 2011-01-11 12:10:22.27 spid21s executionStack 2011-01-11 12:10:22.27 spid21s frame procname=adhoc line=1 sqlhandle=0x0200000020ac2b241e4e25d6f5dfaabe4012c10170109e6f 2011-01-11 12:10:22.27 spid21s SELECT T.Id, RUA.Id AS RollupAssetId, RUA.Code, RUA.Description 2011-01-11 12:10:22.27 spid21s FROM astAssetTree AT 2011-01-11 12:10:22.27 spid21s INNER JOIN astTasks T ON T.AssetId = AT.AssetId 2011-01-11 12:10:22.27 spid21s INNER JOIN astAssets A ON A.Id = T.AssetId 2011-01-11 12:10:22.27 spid21s INNER JOIN stdSites SITE ON SITE.Id = A.SiteId 2011-01-11 12:10:22.27 spid21s INNER JOIN astTaskScenarios S ON (S.ParentId = T.Id AND S.ScenarioId = A.ScenarioId) 2011-01-11 12:10:22.27 spid21s LEFT OUTER JOIN vw_schIntervals I ON I.Id = S.IntervalId 2011-01-11 12:10:22.27 spid21s LEFT OUTER JOIN astComponents C ON C.Id = T.ComponentId 2011-01-11 12:10:22.27 spid21s LEFT OUTER JOIN astMeters M ON M.Id = T.MeterId 2011-01-11 12:10:22.27 spid21s LEFT OUTER JOIN astEvents E ON E.Id = T.EventId 2011-01-11 12:10:22.27 spid21s JOIN astAssetTree RUAT ON (T.AssetId = RUAT.AssetId) 2011-01-11 12:10:22.27 spid21s JOIN astAssets RUA ON ((RUAT.ParentId = RUA.Id) AND (T.RollUpPointId = RUA.RollUpPointId)) 2011-01-11 12:10:22.27 spid21s JOIN astTaskIntervalTypes TIT ON T.IntervalTypeId = TIT.Id 2011-01-11 12:10:22.27 spid21s JOIN usrUserRights UR ON ((A.SiteId = UR.SiteId) AND (UR.UserId = 1012) AND ( 2011-01-11 12:10:22.27 spid21s inputbuf 2011-01-11 12:10:22.27 spid21s SELECT T.Id, RUA.Id AS RollupAssetId, RUA.Code, RUA.Description 2011-01-11 12:10:22.27 spid21s FROM astAssetTree AT 2011-01-11 12:10:22.27 spid21s INNER JOIN astTasks T ON T.AssetId = AT.AssetId 2011-01-11 12:10:22.27 spid21s INNER JOIN astAssets A ON A.Id = T.AssetId 2011-01-11 12:10:22.27 spid21s INNER JOIN stdSites SITE ON SITE.Id = A.SiteId 2011-01-11 12:10:22.27 spid21s INNER JOIN astTaskScenarios S ON (S.ParentId = T.Id AND S.ScenarioId = A.ScenarioId) 2011-01-11 12:10:22.27 spid21s LEFT OUTER JOIN vw_schIntervals I ON I.Id = S.IntervalId 2011-01-11 12:10:22.27 spid21s LEFT OUTER JOIN astComponents C ON C.Id = T.ComponentId 2011-01-11 12:10:22.27 spid21s LEFT OUTER JOIN astMeters M ON M.Id = T.MeterId 2011-01-11 12:10:22.27 spid21s LEFT OUTER JOIN astEvents E ON E.Id = T.EventId 2011-01-11 12:10:22.27 spid21s JOIN astAssetTree RUAT ON (T.AssetId = RUAT.AssetId) 2011-01-11 12:10:22.27 spid21s JOIN astAssets RUA ON ((RUAT.ParentId = RUA.Id) AND (T.RollUpPointId = RUA.RollUpPointId)) 2011-01-11 12:10:22.27 spid21s JOIN astTaskIntervalTypes TIT ON T.IntervalTypeId = TIT.Id 2011-01-11 12:10:22.27 spid21s JOIN usrUserRights UR ON ((A.SiteId = UR.SiteId) AND (UR.UserId = 1012) AND 2011-01-11 12:10:22.27 spid21s process id=process3e88e08 taskpriority=0 logused=70416 waitresource=PAGE: 8:1:367896 waittime=4763 ownerId=2081593817 transactionname=user_transaction lasttranstarted=2011-01-11T12:10:17.043 XDES=0x127c4e8f0 lockMode=IX schedulerid=2 kpid=1680 status=suspended spid=55 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-01-11T12:10:17.507 lastbatchcompleted=2011-01-11T12:10:17.507 clientapp=.Net SqlClient Data Provider hostname=PRODS-HARM hostpid=4604 loginname=OnKey5 isolationlevel=repeatable read (3) xactid=2081593817 currentdb=1 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 2011-01-11 12:10:22.27 spid21s executionStack You'll notice that it gives me the fragment that SQL Monitor prints out, but further down it gives me a bigger fragment of the query. In Summary: 1. The old version of SQL Monitor gave us better information about the query that was involved in the deadlock. For inserts it gave us the first part of the query, where the table name is. In the new version it starts off with the Parameters. 2. Even though SQL Server prints out more information in the error log, this information is not available in SQL Monitor. It would be great to have it there too. 3. I've also confirmed that going to "SQL processes/Profiler trace" and looking for the SPID in question, I could not find the queries in the fragment anywhere in the list of statements for that SPID. / comments
  1. Community
  2. SQL Monitor Previous Versions
  3. Troubleshooting Deadlocks
After some more digging I came to the same deadlock that was logged using the previous version of SQL Monitor. In the old version the log gave us the correct information to point out the error. Old...
0 votes
Chris, Just to confirm - does SQL Server itself only send through the partial query fragment or is this a SQL Monitor issue? Thx / comments
  1. Community
  2. SQL Monitor Previous Versions
  3. Troubleshooting Deadlocks
Chris, Just to confirm - does SQL Server itself only send through the partial query fragment or is this a SQL Monitor issue? Thx
0 votes
Thanks Robin Do you have a public issue tracker where we can track progress on issues using the issue number? / comments
  1. Community
  2. SQL Monitor Previous Versions
  3. Feature Request: Custom Performance Counters
Thanks Robin Do you have a public issue tracker where we can track progress on issues using the issue number?
0 votes
Feature Request: Custom Performance Counters
  1. Community
  2. SQL Monitor Previous Versions
Hi Can you please add the ability to the Analysis functionality to allow the end-user to choose additional performance counters that SQL Monitor should monitor. The set of database and general mac...
2 followers 3 comments 0 votes
The issue is related to the account permissions for the identity under which the Application Pool for SQL monitor is running in IIS. When I change this a domain account with Administrator rights the issue disappears. However, ideally I don't want to run the App Pool using an administrator account. I have verified that the previous ApplicationPoolIdentity has the necessary rights on the SQL Monitor folder using icacls as described in your knowledge base. For now the site is running, but I still want to not use the Admin account. Thx / comments
  1. Community
  2. SQL Monitor Previous Versions
  3. Upgrade to 2.1 - Cannot connect to Base Monitor Service
The issue is related to the account permissions for the identity under which the Application Pool for SQL monitor is running in IIS. When I change this a domain account with Administrator rights t...
0 votes
Troubleshooting Deadlocks
  1. Community
  2. SQL Monitor Previous Versions
Hi I'm having trouble trying to pinpoint a deadlock issue raised by SQL Monitor. SQL Monitor only displays fragments of the queries that are deadlocked and I'm unable to locate the specific querie...
2 followers 6 comments 0 votes
Upgrade to 2.1 - Cannot connect to Base Monitor Service
  1. Community
  2. SQL Monitor Previous Versions
Hi After upgrading to 2.1 SQL Monitor cannot connect to the Base Monitor service. It is giving me a The credentials supplied to the package were not recognized error. I've gone through all the do...
3 followers 3 comments 0 votes
Thanks Andrew. Rough estimate on v6.1 - 1 month/2 month timeframe? I need to report back to management. Thx / comments
  1. Community
  2. ANTS Performance Profiler Previous Versions
  3. SQL, File I/O not working via command line profiling
Thanks Andrew. Rough estimate on v6.1 - 1 month/2 month timeframe? I need to report back to management. Thx
0 votes
Andrew Just to verify that I understand you correctly - you are saying that the command line profiling should generate the SQL/File I/O stats as long as we run it in an elevated command prompt with administrator rights? I'm running without UAC and it is still not generating the stats. Thanks / comments
  1. Community
  2. ANTS Performance Profiler Previous Versions
  3. SQL, File I/O not working via command line profiling
Andrew Just to verify that I understand you correctly - you are saying that the command line profiling should generate the SQL/File I/O stats as long as we run it in an elevated command prompt with...
0 votes
Total activity
43
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
11