Tag Info

New answers tagged

2

If you're sure the user in question has View Server State (and it looks like in your screenshot he does). Then there are a number of reasons previously put into an msdn blog. Ranging from: Performance Objects and counters set-up during the SQL Server installation failed. A mixture of 64 and 32 bit platforms. Registry permissions have been skewed To ...


3

This is what I use to monitor sleeping SPIDs causing blocking issues: SELECT ,s.session_id ,s.status ,s.login_time ,s.host_name ,s.program_name ,s.host_process_id ,s.original_login_name ,s.last_request_end_time ,CAST(t.text AS nvarchar(4000)) AS [text] FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_connections AS ...


2

You can use Adam Machanic's SP_WHOISACTIVE to get the details tracked. One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval. This usage is documented. Please see on how to collect ...


2

There should be no real performance problems related to using the system objects to build your T-SQL. Consider also that building your dynamic T-SQL is probably a much lighter load on your server than actually running the T-SQL statements that you generate. There are also INFORMATION_SCHEMA views, but these only partially cover the system objects. I ...


5

In SQL Server 2005 and SQL Server 2008 there was no documented way. So undocumented command xp_regread was used to get the result DECLARE @sn NVARCHAR(128); EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT', 'ObjectName', @sn OUTPUT; SELECT @sn; Since SQL Server 2008R2 SP1 we have a ...



Top 50 recent answers are included