Dynamics AX 2009 does have some issues with SQL Server locks and quite often we are finding that people cannot access journal lines due to someone else blocking them. This then just hangs the client and it becomes unresponsive. Here is a quick method of finding the offender. The spid in SQL can be traced back to the user in Dynamics AX 2009 on the online users screen which can be accessed from "Administration". Each user that is working will have a SPID or sometimes several attached to his/her user name. Now you can call the lead blocker as you have a name and ask them to shut down AX.
If you do not have a tool such as Spotlight then create a view on the Master database that will show you the blockers. Here is the code for the view:
Create View vSysProcesses as
select p.spid, case when (p.blocked <> 0) then left(convert(varchar, b.spid) + ' - ' + case when (b.nt_username = '') then rtrim(b.loginame) else rtrim(b.nt_username) end -- UserName, + ' on ' + rtrim(b.hostname) + ', using ' + rtrim(b.program_name), 80) else null end as [BlockedBy], case when (p.nt_username = '') then left(p.loginame, 15) else left(p.nt_username, 15) end as UserName, left(p.hostname, 15) as HostName, left(p.program_name, 35) as Program, left(isnull(d.name, ''), 20) as DB, left(p.cmd, 25) as Command, left(p.status, 15) as Status, p.login_time as LogInTime, p.last_batch as LastBatchTime, left(p.lastwaittype, 20) as [LastWaitType], p.Open_Tran as [OpenTran], p.cpu as [CPU], p.physical_io as [PhysicalIO], p.[memusage] as [MemUsage], p.dbid as [dbId], (((1.0 + p.cpu) / 8500.0) * ((1.0 + p.physical_io) / 1100.0) * ((1.0 + p.[memusage]) / 38.0)) / (datediff(ss, p.login_time, GetDate())) as ResourceUsageFactorfrom master.dbo.sysprocesses pjoin master.dbo.sysdatabases don p.dbid = d.dbidleft join master.dbo.sysprocesses b -- blocking on p.blocked = b.spidwhere p.spid <> @@spid and p.Status <> 'background'
Once you have created the view run this against the view and you will find the blockers:
use master
select * from dbo.vSysProcesses
order by blockedby desc
- Paul Steynberg
2 comments:
have you tried to copy+paste that sql? :-/
Try this:
SELECT p.spid,
CASE
WHEN
(
p.blocked <> 0
)
THEN LEFT(CONVERT(VARCHAR, b.spid) + ' - ' +
CASE
WHEN
(
b.nt_username = ''
)
THEN RTRIM(b.loginame)
ELSE RTRIM(b.nt_username)
END --UserName,
+ ' on ' + RTRIM(b.hostname) + ', using ' + RTRIM(b.program_name), 80)
ELSE NULL
END AS [BlockedBy],
CASE
WHEN
(
p.nt_username = ''
)
THEN LEFT(p.loginame, 15)
ELSE LEFT(p.nt_username, 15)
END AS UserName ,
LEFT(p.hostname, 15) AS HostName ,
LEFT(p.program_name, 35) AS Program ,
LEFT(ISNULL(d.name, ''), 20) AS DB ,
LEFT(p.cmd, 25) AS Command ,
LEFT(p.status, 15) AS Status ,
p.login_time AS LogInTime ,
p.last_batch AS LastBatchTime ,
LEFT(p.lastwaittype, 20) AS [LastWaitType],
p.Open_Tran AS [OpenTran] ,
p.cpu AS [CPU] ,
p.physical_io AS [PhysicalIO] ,
p.[memusage] AS [MemUsage] ,
p.dbid AS [dbId] ,
(((1.0 + p.cpu) / 8500.0) * ((1.0 + p.physical_io) / 1100.0) * ((1.0 + p.[memusage]) / 38.0)) / (DATEDIFF(ss, p.login_time, GETDATE())) AS ResourceUsageFactor
FROM master.dbo.sysprocesses p
JOIN master.dbo.sysdatabases d
ON p.dbid = d.dbid
LEFT JOIN master.dbo.sysprocesses b -- blocking
ON p.blocked = b.spid
WHERE p.spid <> @@spid
AND p.Status <> 'background'
Post a Comment