January 8, 2009 at 1:52 pm
Thanks Marios
The SQL 2000 script is working there was an extra _ in one of the lines but no big deal
I have to test out the SQL 2005 script still but I'm sure it will be fine. Thanks again
January 8, 2009 at 1:55 pm
jesse.roberts (1/8/2009)
Thanks MariosThe SQL 2000 script is working there was an extra _ in one of the lines but no big deal
I have to test out the SQL 2005 script still but I'm sure it will be fine. Thanks again
Any time!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 8, 2009 at 3:32 pm
Thanks to both Marios and SQLEngine... I think I am starting to understand how things are working now..
January 8, 2009 at 5:45 pm
Nilesh Patel (1/8/2009)
Thanks to both Marios and SQLEngine... I think I am starting to understand how things are working now..
There is no substitute for experimentation. The best way to learn is to be willing to try different things out (and spend a lot of time doing it).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 14, 2009 at 12:28 pm
Hey marios
Just a followup the alert supression is working perfectly.
Jesse
January 14, 2009 at 12:30 pm
jesse.roberts (1/14/2009)
Hey mariosJust a followup the alert supression is working perfectly.
Jesse
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... 😉
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 15, 2009 at 8:37 am
Hello,
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?
TIA
nilesh
January 28, 2009 at 11:25 am
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.aspx
We 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.
January 28, 2009 at 2:35 pm
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.
January 29, 2009 at 3:44 am
Nilesh Patel (1/28/2009)
I was able to find answer on how to set alert for specific monitor. It needs to be done using script as mentioned inhttp://blogs.msdn.com/jakuboleksy/archive/2007/01/18/notification-subscriptions.aspx
We 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.
Thank you for this info. Yes, there is no straightforward way to specify which monitor(s) to get alerts on.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 29, 2009 at 3:54 am
Harold Buckner (1/28/2009)
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.
The blocking login is returned as well. Here is the script for sql 2005 (I'm highlighting the relevant sections):
-- Script to return blocking and blocked spids with wait times > 10 min
-- SQL 2005
SET 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
#blocks
SELECT
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
,S.login_name
,LEFT(H.text, 1000)
FROM
sys.dm_exec_requests R
INNER JOIN
sys.dm_exec_sessions S
ON
R.session_id = S.session_id
CROSS APPLY
sys.dm_exec_sql_text(R.sql_handle) H
WHERE
(
(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
OR SPID IN (SELECT BlockingSPID FROM #blocks WHERE BlockingSPID > 0);
END
ELSE
BEGIN
TRUNCATE TABLE #blocks;
SELECT * FROM #blocks;
END
DROP TABLE #blocks;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 29, 2009 at 6:25 am
I 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.
January 29, 2009 at 10:30 am
Here is an edit I did to the Custom_SQL2005_Blocking.vbs
added the DBName to the alert description
added the Blocking Login Name to alert description
added SQL Text to alert description
removed waittime to alert description
This 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.
February 2, 2009 at 8:39 am
Marios
I 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
February 2, 2009 at 8:50 am
Harold Buckner (2/2/2009)
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
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...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 31 through 45 (of 73 total)
You must be logged in to reply to this topic. Login to reply