InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 16:14:35 GMT20http://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]krn045 (1/21/2013)[/b][hr]Hi..We are using SCOM 2007 to monitor SQL Server 2008 instances. I have checked the blocked sessions alert details in SCOM console as well as in OperationsManager database. I was not able to find the head_blocker (or lead blocker spid) details when more than 2 processes are involved in blocking.Eg: 1. SPID 87 is blocked SPID 85 (Details available, lead blocker is 85) 2. SPID 87 is blocked by SPID 90 SPID 90 is blocked by SPID 85 (Details available for SPID 87 and 90, here SPID 85 is blocker. There are no details available like hostname, logintime, cmd etc for SPID 85)I am not sure if this is the limitation in this product or something we should configure to capture the information when there are more than 2 spids involved in blocking.[/quote]Sometimes the blocking session is not doing anything, and there is therefore not much info on it (that info would be coming from sys.dm_exec_requests). A good example of this would be a user-initiated session in which the user has opened a web page, and that has started a transaction that won't commit until the page is exited (code defect). In that scenario a SQL statement in the transaction could be blocking others indefinitely, even though there is no work being done.Wed, 06 Feb 2013 18:37:36 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxHi..We are using SCOM 2007 to monitor SQL Server 2008 instances. I have checked the blocked sessions alert details in SCOM console as well as in OperationsManager database. I was not able to find the head_blocker (or lead blocker spid) details when more than 2 processes are involved in blocking.Eg: 1. SPID 87 is blocked SPID 85 (Details available, lead blocker is 85) 2. SPID 87 is blocked by SPID 90 SPID 90 is blocked by SPID 85 (Details available for SPID 87 and 90, here SPID 85 is blocker. There are no details available like hostname, logintime, cmd etc for SPID 85)I am not sure if this is the limitation in this product or something we should configure to capture the information when there are more than 2 spids involved in blocking.Mon, 21 Jan 2013 21:43:33 GMTkrn045http://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]JKG (8/31/2011)[/b][hr]Great article - very helpful!!![/quote]Thank you :)Thu, 01 Sep 2011 08:02:57 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxGreat article - very helpful!!!Wed, 31 Aug 2011 00:43:46 GMTJKGhttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]fannonland2 (3/3/2011)[/b][hr]ok, changed that line back to iWaitInMinutes = 1iAlertFreq = 1sAnalysisInstance = "server\instance"sAnalysisDB = "dbName"and the other line is now....sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=SCOMADMIN;PWD=mysupercomplexpassword"But I have yet to receive a single notification. I made sure I created a new subscription based on Sql Servers, too. Anything I can check on the SQL side to see if it is even collecting anything yet?[/quote]Try simulating blocking by running the following in SSMS:In 1st window; run this in a test database of your choice with a table of your choice:[code="sql"]USE dbName;set nocount onbegin tranwhile 1=1 update tblName set test ='abcd' -- --rollback tran[/code]In a 2nd window of SSMS:[code="sql"]--2nd window:USE dbName;SELECT * FROM tblName [/code]That will establish a blocking condition where the 2nd query is blocked indefinitely by the 1st.Now on a 3rd SSMS window run the following.Run the attached query after a minute, do you see any blocking? (this is the same query running in the .vbs script for SQL2005/2008 servers).BTW, I hope you imported the custom management pack into SCOM... :-)Thu, 03 Mar 2011 15:31:25 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxok, changed that line back to iWaitInMinutes = 1iAlertFreq = 1sAnalysisInstance = "server\instance"sAnalysisDB = "dbName"and the other line is now....sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=SCOMADMIN;PWD=mysupercomplexpassword"But I have yet to receive a single notification. I made sure I created a new subscription based on Sql Servers, too. Anything I can check on the SQL side to see if it is even collecting anything yet?Thu, 03 Mar 2011 14:36:42 GMTfannonland2http://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]fannonland2 (3/2/2011)[/b][hr]I appreciate you taking the time to talk me through this. I am not a DBA by any stretch but I am trying to convince the DBA's that scom can do this and obviously they aren't interested in helping me. So, I do this on my own. So I have gotten to the point where I created a new database called, "Analysis" on the scom server. This sits right alongside the OperationsmangerDW and ReportServer databases. I ran the scripts, it created tables, I copied and pasted the PRC files and ran those. Had a little problem because I hadn't created a database role called "ROLE_UID". So I did and made it a dbowner. Now, I am going back to change the scripts in SCOM.I get this...sAnalysisInstance = "SCOMP001\SCOMSQL"sAnalysisDB = "Analysis"But I don't get this....sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"What do I need to change in this line???[/quote]sAnalysisInstance and sAnalysisDB take their values from the variable definitions so you don't have to worry about changing those.UID: this is a server login and database user on Analysis db that has execute permissions on stored procedure SCOM_InsertBlockingInfo.This procedure is installed on Analysis db. See below code excerpt to get an idea of what I am talking about:[quote]sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"cnAnalysisInstance.Open sConnStringSet cmd = CreateObject("ADODB.Command")Set cmd.ActiveConnection = cnAnalysisInstancecmd.CommandText = "[dbo].[SCOM_InsertBlockingInfo]"cmd.CommandType = 4 'adCmdStoredProc[/quote]In my case I have a login, ssrs, mapped to a database role, ROLE_ssrs, in the Analysis db.ROLE_ssrs has EXECUTE permissions on procedures SCOM_InsertBlockingInfo and SCOM_InsertBlockingInfo_2000.Then the line you were asking about could read like this:[quote]sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=ssrs;PWD=insert-complex-passwd"[/quote]Wed, 02 Mar 2011 14:37:28 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxI appreciate you taking the time to talk me through this. I am not a DBA by any stretch but I am trying to convince the DBA's that scom can do this and obviously they aren't interested in helping me. So, I do this on my own. So I have gotten to the point where I created a new database called, "Analysis" on the scom server. This sits right alongside the OperationsmangerDW and ReportServer databases. I ran the scripts, it created tables, I copied and pasted the PRC files and ran those. Had a little problem because I hadn't created a database role called "ROLE_UID". So I did and made it a dbowner. Now, I am going back to change the scripts in SCOM.I get this...sAnalysisInstance = "SCOMP001\SCOMSQL"sAnalysisDB = "Analysis"But I don't get this....sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"What do I need to change in this line???Wed, 02 Mar 2011 12:44:54 GMTfannonland2http://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]fannonland2 (3/2/2011)[/b][hr]ok, so it sounds like I need to go into SQL management Studio, create a new database called "Analysis" and then run the blocking tables.sql against that new database. Does that sound right?And then when I need to customize it it will be......sAnalysisInstance = "scomserver\scomsql"sAnalysisDB = "analysis"......sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"...[/quote]Yes, that sounds right. Basically, you need to specify a place where you will be storing all the info collected by the custom rules.Wed, 02 Mar 2011 11:29:21 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxok, so it sounds like I need to go into SQL management Studio, create a new database called "Analysis" and then run the blocking tables.sql against that new database. Does that sound right?And then when I need to customize it it will be......sAnalysisInstance = "scomserver\scomsql"sAnalysisDB = "analysis"......sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"...Wed, 02 Mar 2011 11:21:55 GMTfannonland2http://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]fannonland2 (3/2/2011)[/b][hr]Hmm, then I guess I am misreading your instructions. It says I need to run scripts scom blocking.sql. I do that on the scom server? And then I need to edit the rules to reflect settings in my environment. I am looking at my SCOM SQL database and I don't see a database that says "Analysis". [/quote]Yes, sorry the instructions on ReadMe.txt file are not very clear.The Analysis db could be any centralized database in your environment that is collecting historical blocking information.It is not the SCOM database, but a database created by you to collect the custom info generated by the rules here.If you already have such a db in your environment for collecting other such info, you can use the same db for this implementation.You may need to edit the name of the database from "Analysis" to whatever the name of your db is in a few places though.Does that make sense?Wed, 02 Mar 2011 11:16:31 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxHmm, then I guess I am misreading your instructions. It says I need to run scripts scom blocking.sql. I do that on the scom server? And then I need to edit the rules to reflect settings in my environment. I am looking at my SCOM SQL database and I don't see a database that says "Analysis". Wed, 02 Mar 2011 10:33:24 GMTfannonland2http://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]fannonland2 (2/28/2011)[/b][hr]Just trying to understand what you are doing here with your scripts. I am trying to stop my IT department from buying Quest Tools or Foglight and hoping SCOM can do the job. We currently have 10 SQL servers ranging form 2000 to 2008 and each has multiple databases. Do I have to edit each script and create a new rule for every single server? Or can I just make one rule for 2000, 2005, 2008 and it will find all my SQL servers and run the alert?[/quote]Yes, in fact this is the power SCOM gives you. You create a rule for SQL 2000 and one for SQL 2005/8 (it should be able to accomodate both of these versions). Then SCOM will propagate these scripts to all servers on which a SCOM agent has been deployed and that will be it.The more servers you have in your environment, the larger the benefit of the centralized management SCOM provides.Actually, to be precise, there are a couple of rules for each SQL version, if I remember correctly: one for detecting the condition and one for generating the alert.But it is the same idea.Mon, 28 Feb 2011 14:21:27 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxJust trying to understand what you are doing here with your scripts. I am trying to stop my IT department from buying Quest Tools or Foglight and hoping SCOM can do the job. We currently have 10 SQL servers ranging form 2000 to 2008 and each has multiple databases. Do I have to edit each script and create a new rule for every single server? Or can I just make one rule for 2000, 2005, 2008 and it will find all my SQL servers and run the alert?Mon, 28 Feb 2011 14:15:24 GMTfannonland2http://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxStill working through my problem but I did have some updates I thought I would share. So far a couple of things going on here.1) first off the script I used in the rule is slightly modified from what you posted on this article. However bottom line was that it was failing on the part where it logs to the event log.[b]So I changed this:[/b]If bWriteLOG = True Then oAPI.LogScriptEvent("SQLBlocks",999,EVT_WARNING,sAlertDescription)Else oAPI.LogScriptEvent("SQLBlocks",998,EVT_INFO,sAlertDescription)End If[b]To this:[/b]If bWriteLOG = True Then [b]CALL[/b] oAPI.LogScriptEvent("SQLBlocks",999,EVT_WARNING,sAlertDescription)Else [b]CALL[/b] oAPI.LogScriptEvent("SQLBlocks",998,EVT_INFO,sAlertDescription)End IfThis article helped http://msdn.microsoft.com/en-us/library/bb437630.aspx2) The other issue is that on Windows 2008 servers the events are logging to the Operations Manager Event log. On my 2003 servers it goes to Application Event. Log. However My monitor is looking at the App log not Ops Manager. So I'm going to create 2 Monitors unless anyone can think of another way around this?Tue, 21 Dec 2010 15:19:21 GMTKeith Meschahttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxGood point let me check a system with SQL 2008 Non R2 and see what it's doing.Thu, 09 Dec 2010 09:36:34 GMTKeith Meschahttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]Keith Mescha (12/9/2010)[/b][hr]I have this solution partly implemented. I have recently upgraded quite a few servers to Windows 2008 R2 and SQL 2008 R2 and just found out that the vb script from within SCOM is not writing to the event log. Just started digging but figured I would ping this forum to see if anyone has noticed this behavior.If you have appreciate any direction. When I find the cause I'll be sure to post back.[/quote]Unfortunately, I don't have an answer for this at the moment.There may be some changes in the way WMI interfaces with the new O/S and SQL versions that are breaking the scripts.Incidentally, we have also upgraded to Windows 2008 R2, and we are still getting the blocking alerts.However, we have only upgraded one SQL instance to SQL 2008 R2 as well as Windows 2008 R2, and we may be having the same issue as you.So the issue may be with SQL 2008 R2.If I come up with something new on this, I will post it here.Parenthetically, see this link for a memory-leak issue with WMI and Windows 2008 R2:[url]http://support.microsoft.com/kb/977357[/url]Thu, 09 Dec 2010 09:34:08 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]Srinivas-490730 (11/5/2010)[/b][hr]Marios.Thanks for posting the MP and the relevant scripts.. It helped me a lot.I also want to know if we can modify these scripts to alert on long running queries and locking queries.If so, what is the SQL script that I have to use?Please advise.Thanks.Srini.[/quote]Sorry for the long delay in responding.For SQL scripts that monitor locks, see this excellent link:[url]http://www.simple-talk.com/sql/database-administration/investigating-transactions-using-dynamic-management-objects/[/url]You will need to customize the SCOM objects to reference these scripts accordingly.For long-running queries, you may want to use something like this:[code="sql"]--http://www.sql-server-performance.com/articles/per/tsql_statement_performance_p1.aspxSELECT creation_time , last_execution_time , total_physical_reads, total_logical_reads , total_logical_writes, execution_count , total_worker_time, total_elapsed_time , total_elapsed_time / execution_count avg_elapsed_time, DatabaseName = DB_NAME(st.dbid), SUBSTRING(st.text, (qs.statement_start_offset/2) + 1 ,(( CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text , qp.query_planFROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY total_elapsed_time / execution_count DESC;[/code]Thu, 09 Dec 2010 09:20:12 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]Keith Mescha (12/9/2010)[/b][hr]I have this solution partly implemented. I have recently upgraded quite a few servers to Windows 2008 R2 and SQL 2008 R2 and just found out that the vb script from within SCOM is not writing to the event log. Just started digging but figured I would ping this forum to see if anyone has noticed this behavior.If you have appreciate any direction. When I find the cause I'll be sure to post back.[/quote]I don't have a specific answer for you, but, since there's probably something wrong with either the script or the parameters, you need to debug to determine what's what. [url=http://scarydba.com/2008/05/06/scom-vbscript-testing-the-answer/]Here's a link[/url] to how I figured out debugging. Hopefully that helps.Thu, 09 Dec 2010 08:37:37 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxI have this solution partly implemented. I have recently upgraded quite a few servers to Windows 2008 R2 and SQL 2008 R2 and just found out that the vb script from within SCOM is not writing to the event log. Just started digging but figured I would ping this forum to see if anyone has noticed this behavior.If you have appreciate any direction. When I find the cause I'll be sure to post back.Thu, 09 Dec 2010 08:29:32 GMTKeith Meschahttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxMarios.Thanks for posting the MP and the relevant scripts.. It helped me a lot.I also want to know if we can modify these scripts to alert on long running queries and locking queries.If so, what is the SQL script that I have to use?Please advise.Thanks.Srini.Fri, 05 Nov 2010 12:10:27 GMTSrinivas-490730http://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxThank you, I'm glad it was helpful.Mon, 12 Jul 2010 07:58:09 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxMarios,Very nice work. I just implemented this at work. It was an instant hit with our DBA teams.Thanks,Fri, 09 Jul 2010 09:56:38 GMTpeter_siderishttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxJesse can you point to a reference on how alert suppression works?Tue, 23 Mar 2010 11:51:44 GMTKeith Meschahttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxHow do i import the 2 .rpc files?Thu, 22 Oct 2009 01:50:39 GMTf.ademihttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]marc.schmieder (5/28/2009)[/b][hr]Marios,We tried to run the script form the command line and he get an error. Are we supposed to use the computer name as a parameter on the cmd line? Ex. Cscript SQLBlocking.vbs SERVERNAME? Also, we are no long seeing the event being created either. So all in all we've seen the data written to the central db once on 5/26 and we have seen the alert occur 2 times, but never when we expect it to. We have set the all parameters to 1min so we should be seening something after this 2-3 mins or so. At this point we don't know what else to check. Any additional advice would be appreciated.[/quote]The parameter should be the server/instance in question, eg. [b]myServerName/myInstanceName[/b].What is the error and where in the script does it occur?Also, is this the SQL-2005 or the SQL-2000 script?Thu, 28 May 2009 13:34:18 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxMarios,We tried to run the script form the command line and he get an error. Are we supposed to use the computer name as a parameter on the cmd line? Ex. Cscript SQLBlocking.vbs SERVERNAME? Also, we are no long seeing the event being created either. So all in all we've seen the data written to the central db once on 5/26 and we have seen the alert occur 2 times, but never when we expect it to. We have set the all parameters to 1min so we should be seening something after this 2-3 mins or so. At this point we don't know what else to check. Any additional advice would be appreciated.Thu, 28 May 2009 13:17:38 GMTmarc.schmiederhttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]marc.schmieder (5/27/2009)[/b][hr]When does data get added to the central DB? We have this setup and we saw data get added once, but it hasn't worked since then. The script is running and creating the event in the Application log, but we haven't seen data appended to the central Db? Any help would be appreciated.[/quote]If the event is being generated in the App Log, it's possible that the procedure entering the data in the analysis db is failing. But then, the fact that an event is being created means that the procedure does return a true value for bWriteLOG:[code] If cmd.Parameters("@WriteLog").Value = True Then bWriteLOG = TrueEnd If[/code]So I'm actually puzzled why this is happening. I would suggest that you run the vbscript directly on your server to dig deeper.Lower these 2 parameters in the script to 1 minute to ease testing:[code]iWaitInMinutes = 10iAlertFreq = 10[/code]Run the blocking-condition script indicated in the article on the server instance in question. After about a minute, run the vbscript from the command line.Check whether anything has been added to your db. If not, troubleshoot through the script by adding msgbox debug lines close to the call to the stored procedure.Sorry, I can't think of any reason offhand why this would not work. Please post any more info that you may have available.Thu, 28 May 2009 07:29:22 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxWhen does data get added to the central DB? We have this setup and we saw data get added once, but it hasn't worked since then. The script is running and creating the event in the Application log, but we haven't seen data appended to the central Db? Any help would be appreciated.Wed, 27 May 2009 14:46:54 GMTmarc.schmiederhttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]Harold Buckner (2/2/2009)[/b][hr]MariosI was doing some more thinking on this script....and thought......Currently the alert is generated from the managed server and the computer name in the event is the managed server. This works in most cases but here is my issue:I have a SQL Cluster with 9 instances on it that can move from one node to another. So it would be best for me if the event stated the SQL Virtual Server name so I can better define my notifications.Currently I send all notification to someone for this SQL instance and if I have the SQL Blocks event that are from the physical node I have to set up new notifications for it and they will end up getting alerts from other SQL instances.See my issue?So I was thinking and trying to make the event compter state the SQL Server and the only way I could do this is let SQL create the alert from with in the SQL Blocking Statement. It was painful to write but I finally got it. The only bad issue I see it that whomever creates the alert needs to be a sysadmin but this will work in our environment.I got the script complete so if there is anyone in the same shoes as I, feel free to PM me and I can sent it to you or post here.Thanks[/quote]Thank you, I haven't come across this situation yet, but it's only a matter of time.I will keep your solution in mind...Mon, 02 Feb 2009 08:50:10 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxMariosI was doing some more thinking on this script....and thought......Currently the alert is generated from the managed server and the computer name in the event is the managed server. This works in most cases but here is my issue:I have a SQL Cluster with 9 instances on it that can move from one node to another. So it would be best for me if the event stated the SQL Virtual Server name so I can better define my notifications.Currently I send all notification to someone for this SQL instance and if I have the SQL Blocks event that are from the physical node I have to set up new notifications for it and they will end up getting alerts from other SQL instances.See my issue?So I was thinking and trying to make the event compter state the SQL Server and the only way I could do this is let SQL create the alert from with in the SQL Blocking Statement. It was painful to write but I finally got it. The only bad issue I see it that whomever creates the alert needs to be a sysadmin but this will work in our environment.I got the script complete so if there is anyone in the same shoes as I, feel free to PM me and I can sent it to you or post here.ThanksMon, 02 Feb 2009 08:39:56 GMTHarold Bucknerhttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxHere is an edit I did to the Custom_SQL2005_Blocking.vbs added the DBName to the alert descriptionadded the Blocking Login Name to alert descriptionadded SQL Text to alert descriptionremoved waittime to alert descriptionThis does not write to the analysis db and you can you can use supression to supress the alerts by description since I removed the waittime. Attached Below.Thu, 29 Jan 2009 10:30:50 GMTHarold Bucknerhttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxI think I am seeing the issue now then. I get the blocking SPID but not the LOGIN from the Blocking SPID. I verified the #blocks table is populated with the Blocked Login in both the NT_User_Login and the Login_name and the alert always have the blocked SPID Login. We have the blocking spid in the #blocked table, I think we would just have to join another table to get the blocking information. I'll work on it today and see what I come up with.Thu, 29 Jan 2009 06:25:22 GMTHarold Bucknerhttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]Harold Buckner (1/28/2009)[/b][hr]How hard of a change would it be to add the blocking User info. For example we have the blocking spid and the blocked spid and we have the blocked login. Could we also add the blocking login?Thanks to all that worked on the scripts.[/quote]The blocking login is returned as well. Here is the script for sql 2005 (I'm highlighting the relevant sections):[code]-- Script to return blocking and blocked spids with wait times > 10 min-- SQL 2005SET NOCOUNT ON;CREATE TABLE #blocks( SPID SMALLINT, BlockingSPID SMALLINT, WaitTime BIGINT, LastWaitType VARCHAR(32), WaitResource VARCHAR(32), DBName VARCHAR(100), CPU_Time INT, Total_Elapsed_Time INT, Physical_IO BIGINT, Logical_Reads BIGINT, Memory_Usage INT, Login_Time DATETIME, [Status] VARCHAR(30), HostName VARCHAR(128), ProgramName VARCHAR(128), Command VARCHAR(16), NT_User_Name VARCHAR(128), Login_Name VARCHAR(128), SQL_Text VARCHAR(1000));INSERT INTO #blocksSELECT R.session_id , R.blocking_session_id , R.wait_time , R.last_wait_type , R.wait_resource , DB_NAME(R.database_id) , R.cpu_time , R.total_elapsed_time , R.reads + R.writes , R.logical_reads , S.memory_usage , S.login_time , R.status , S.[host_name] , S.[program_name] , R.command , S.nt_user_name , [b]S.login_name [/b], LEFT(H.text, 1000) FROM sys.dm_exec_requests RINNER JOIN sys.dm_exec_sessions SON R.session_id = S.session_idCROSS APPLY sys.dm_exec_sql_text(R.sql_handle) HWHERE ( (R.blocking_session_id <> 0 AND R.wait_time > 600000) -- 10 min OR (R.blocking_session_id = 0 AND R.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 )) )ORDER BY R.blocking_session_id;IF EXISTS (SELECT * FROM #blocks WHERE BlockingSPID > 0)BEGIN SELECT * FROM #blocks WHERE BlockingSPID > 0 [b]OR SPID IN (SELECT BlockingSPID FROM #blocks WHERE BlockingSPID > 0);[/b]ENDELSEBEGIN TRUNCATE TABLE #blocks; SELECT * FROM #blocks;ENDDROP TABLE #blocks;[/code]Thu, 29 Jan 2009 03:54:16 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]Nilesh Patel (1/28/2009)[/b][hr]I was able to find answer on how to set alert for specific monitor. It needs to be done using script as mentioned in http://blogs.msdn.com/jakuboleksy/archive/2007/01/18/notification-subscriptions.aspxWe did not feel comfortable with this approach, so decided to create different groups based on alert requirements and accept the fact that we will get alerts for all monitors.[/quote]Thank you for this info. Yes, there is no straightforward way to specify which monitor(s) to get alerts on.Thu, 29 Jan 2009 03:44:24 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxHow hard of a change would it be to add the blocking User info. For example we have the blocking spid and the blocked spid and we have the blocked login. Could we also add the blocking login?Thanks to all that worked on the scripts.Wed, 28 Jan 2009 14:35:24 GMTHarold Bucknerhttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxI was able to find answer on how to set alert for specific monitor. It needs to be done using script as mentioned in http://blogs.msdn.com/jakuboleksy/archive/2007/01/18/notification-subscriptions.aspxWe did not feel comfortable with this approach, so decided to create different groups based on alert requirements and accept the fact that we will get alerts for all monitors.Wed, 28 Jan 2009 11:25:32 GMTNilesh Patel-258792http://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxHello,I am back again with more questions about notifications. I have created operator and would like to get email (notification) when following conditin occurs within group- SQL Server instance goes down- Trans log free space (%) alert is generated.I started creating notification using 'My Notifications' and selected 'SQL 2005 DB Engine' class. However, in 'Alert Criteria', I see bunch of categories but cannot decide which ones to pick. Any suggestions?TIAnileshThu, 15 Jan 2009 08:37:39 GMTNilesh Patel-258792http://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspx[quote][b]jesse.roberts (1/14/2009)[/b][hr]Hey mariosJust a followup the alert supression is working perfectly.Jesse[/quote]Nice! I'm going to have to make use of this at some point - some of our users are starting to complain about the multiple alerts... ;)Wed, 14 Jan 2009 12:30:33 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic584420-1399-1.aspxHey mariosJust a followup the alert supression is working perfectly.JesseWed, 14 Jan 2009 12:28:01 GMTjesse.roberts