8
Just because you can does not mean you should. Please do not apply the changes below. This answer is only to show you why you should not change system tables or system procedures and what can go wrong.
msdb.dbo.sp_add_operator
When adding the operator with the ssms gui, msdb.dbo.sp_add_operator is called behind the scenes.
The email address parameter used ...
7
I have managed to find one solution to this, although it's not as neat as I would have preferred. I've created a second job that checks the last run date on my target job. This is scheduled to run daily, a couple of hours after the target job should have run.
If not exists
(
SELECT 'x'
FROM msdb.dbo.SysJobServers S
INNER JOIN msdb.dbo....
6
Probably the error 1205 is not set up to be logged in ERRORLOG. Look it up in sys.messages:
SELECT *
FROM sys.messages
WHERE message_id = 1205;
If the column is_event_logged is set to 0, change it this way:
EXEC master.sys.sp_altermessage
@message_id = 1205,
@parameter = 'WITH_LOG',
@parameter_value = 'true';
The documentation for ...
5
You can use convert (date, GETDATE()) to get just the 'date' part:
select GETDATE(), convert (date, GETDATE())
----------------------- ----------
2012-11-16 08:58:20.750 2012-11-16
And if you're only ever going to be comparing 'date' parts, it might be worth changing the datatype in your table to DATE, to save having to convert each time you query it.
...
5
From reading the BOL article on sp_add_alert
[ @severity = ] severity - The severity level (from 1 through 25) that defines the alert. Any SQL Server message stored in the sysmessages table sent to the Microsoft Windows application log with the indicated severity causes the alert to be sent. severity is int, with a default of 0. If message_id is used to ...
4
1) If you are using Oracle 11g or later:
Starting from 11g Oracle, was been introduced a fixed table, X$DBGALERTEXT, from which you can retrieve all the data what alert.log does.
Here is a simple query that retrieves all errors which occured today, like you want.
SET linesize 200 pagesize 200
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head ...
4
I think we might need to dip into sys.dm_os_ring_buffers and sys.dm_os_sys_info to get the CPU utilization data.
Please find below the script of Glenn Berry
-- Recent CPU Utilization History (SQL 2008/2008 R2 Only)
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(144) SQLProcessUtilization AS [...
4
Polymon from codeplex is ideal for my uses. I'm monitoring not just server availability, but sql agent jobs success, a host of perfmon data, SQL locking/blocking, db and filesystem freespace, and a whole lot more.
It has a very nice Powershell interface, so any Powershell script you can imagine, it will run on the schedule you specify and then alert/notify/...
4
Please refer to Create a User-Defined Event
By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts.
So your message should be
EXEC sp_addmessage @msgnum = 50005,
@severity = 1,...
4
Severity 25 is a 'catch-all' fatal system error.
A severity 25 error is a fatal system error. I have heard that
severity 25 is more or less a catch-all for miscellaneous fatal
errors. I have only seen this error when related to failed upgrades:
something prevents one of the upgrade scripts from running, and a
severity 25 error is thrown
http://...
4
No, THROW is missing some RAISERROR functionality, including WITH LOG. I blogged about this back when SQL Server 2012 was still in beta, and no changes have been made since:
SQL Server v.Next (Denali) : Exploring THROW
However, you don't need to use sp_addmessage to send RAISERROR messages to the log, unless you need a custom message number outside of ...
4
Please look at the three main queries in the following answer (of mine):
SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type
The first query looks at currently running queries.
The second two queries (labeled "Query #1" and "Query #2") work in combination to find high-cost queries over a period of time.
To get alerts, look into ...
4
One option would be to handle some of this logic inside your code running in the job... this works if you are doing things in batches, or if you are using a cursor or something similar.
Procedure to be Executed by Job
--create some_stored_procedure as
declare @procStartDate datetime = getdate()
declare @threshold int = 2
--doing something in batches, ...
4
I wrote Blocking Detector which uses event notification. You can find it here
Below is how it works :
This script will create
- a blocking detection alert
- table to hold blocking information
- modify the 'blocked process threshold' sp_configure option to 5 mins (300 sec)
- create a sql agent job that will fire in resonse to the alert to ...
3
To answer your direct question, I'd probably go about that with a dedicated SQL Server Agent Job to toggling the alert enabled status with the duration of that time.
To disable that alert:
use msdb;
go
exec dbo.sp_update_alert @name = 'YourAlertName', @enabled = 0;
go
And then 4 hours later (either as a separately scheduled job, or with just a WAITFOR ...
3
It's a good idea to create SQL alerts in the SQL agent for all errors of severity 16 through to 25. Also create 1 SQL alert for each of these error IDs 823 824 825.
I would create an operator that points to an AD group (probably DBA or ITsupport) so that a team can be alerted or you can drop users in and out of that group when you are away so that errors ...
3
If you are looking for free why not try SQL Server Performance Dashboard? The link below is pretty detailed on how to set it up and get it running:
http://www.sql-server-performance.com/2007/bm-performance-dashboard-2005/
Like Shawn said though, there aren't that many free ones available but it is pretty easy to roll your own using SQL/CLR and SSRS. If it'...
3
I am only able to get SQL Server 2012 to fire an alert based upon the "Workload Group Stats -> CPU Usage %" if I enable a dummy Resource Governor classifier function that classifies all connections into the default Resource Governor group.
I used the following classifier function, with the associated code to enable that function:
USE master;
GO
CREATE ...
2
I would suggest that they are equally important on physical or VMs.
suggest you to create all critical alerts as highlighted by Glenn Berry.
2
This is no error. It is just a log file which was too small and has auto growth enabled. It means that while there are transactions running the sql server process has to expand the log file. Expanding the log file is a single threaded process and every transaction which has to write something in the transaction log has to wait. And no: no IFI - you have to ...
2
This is telling you that a or several transaction logs are growing repeatedly, which means it is set to FULL or BULK LOGGED recovery mode with no or insufficient transaction log backups, or someone has written a really bad or long query that is keeping the transaction log active.
You should look for all FULL recovery model databases on that server and when ...
2
Oracle provides the following information for this error message:
ORA-00376 file string cannot be read at this time
Cause: An attempt was made to read from a file that is not readable. Most likely the file is offline.
Action: Check the state of the file. Bring it online.
Their logic for not including this error message into the alert log is ...
2
You could use DBMS_SYSTEM.KSDWRT to write the error to the alert log when you catch the relevant exception.
Then you can configure Enterprise Manager to alert on that error code. You've two options here:
Modify the regex for the existing error log analysis to cover this error code. This would be much easier, but might be a little inflexible.
Write a Metric ...
2
WORKAROUND/SOLUTION
Loopback check can be removed by adding a registry entry as follows:
Edit the registry using regedit. (Start –> Run > Regedit )
Navigate to: HKLM\System\CurrentControlSet\Control\LSA
Add a DWORD value called “DisableLoopbackCheck”
Set this value to 1
2
Here is a script that I use. I don't remember the source, but it is handy, as it will show you the growth events.
DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);
-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(...
2
msdb.dbo.sp_add_alert calls msdb.dbo.sp_add_alert_internal, which in turn writes values into the msdb.dbo.sysalerts table.
The sysalerts table insert looks like this:
INSERT INTO msdb.dbo.sysalerts
(name,
event_source,
event_category_id,
event_id,
message_id,
severity,
enabled,
...
2
As it turns out, AOAG event ID #35266 quite regularly occurs 2 or 3 times as a node is coming up or down. So long as what you do when a 35266 is detected isn't toggle-like in nature, you should be OK. It works fine in my case because what I do when I detect a 35266 is to (depending on the text in the event) either enable or disable specific jobs. So if I ...
2
I find I receive more help and the help is more detailed when I'm very specific in my questions. I liked the detail of the tagging and the questions, it lets me know roughly what you would want and for what flavor of SQL. I would recommend to place the version of SQL and what you have tried or looked for and what did not make sense in the future, this way we ...
Only top voted, non community-wiki answers of a minimum length are eligible
