Hot answers tagged

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

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 ...


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 ...


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 ...


4

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 ...


3

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 ...


3

I would start with this list of errors, most of the login errors I believe are around 18400+. The only other two I think I would add would be: 18452: Login failed. The login is from an untrusted domain and cannot be used with Windows Authentication.%.*ls If you are prone to disable logins I would include this as well: 18470: Login failed for ...


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

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

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 ...


2

Well if you are wanting pretty charts and all, there are not to many free ones that I know of that will automagically do all that you ask. In regards to PBM (Policy Based Management), you can run checks against SQL 2000, 2005, and 20008 instances. So I am not sure where it comes up short for you. With your primary requirements, most of that could be done ...


2

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 ...


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

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 ...


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

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

You can get an idea of what backups have been taken, and when, by looking at the result of this query: DECLARE @DBName SYSNAME; SET @DBName = DB_NAME(); -- modify these as you desire. SET @DBName = NULL; -- comment this line if you want to limit the displayed history SELECT DatabaseName = bs.database_name , BackupStartDate = bs.backup_start_date , ...


1

Adding "with log" to your raiserror statement will make the alert fire. RAISERROR (50005, -- Message id. 16, -- Severity, 1, -- State, N'My custom message') WITH LOG; you will need sysadmin or alter trace to use with log(https://msdn.microsoft.com/en-us/library/ms178592.aspx)


1

Regarding the status of job run, that's the correct steps. However, its recommended you configure the alerts for any database corruption, HW/SW failures or any other cause for an outage. Read here to configure the same. In order to test, create a TEST job and purposefully make it fail and see if you receive the failure notification email or not.


1

You can use SQL Server Change Data Capture or Change Tracking features to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. To get the changed records for a table and information about these changes, use the CHANGETABLE(CHANGES…) function. ...


1

There are three ways to find who is connected to a database in sqlserver. First one: Use the SQL SERVER Management Studio -- Management -- Activity Monitor This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc. Second One: Use the built in ...


1

The easiest way to setup an alert is to base it off the error number that occurs for failed backups. That would be error 3041. To setup the Alert USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N'Failed Backup Alert', @message_id=3041, @severity=0, @enabled=1, @delay_between_responses=0, ...


1

Note: This is for Microsoft SQL Server. For testing purpose, I downloaded a corrupt database backup file from SQLSkills.com Below is a quick script that will help you (obviously you can adapt the logic to do it dynamically for all your databases and send email in a nice HTML format) : begin try BACKUP DATABASE broken TO ...


1

I would recommend any error that decodes to a SQL Syntax error. #113 (unterminated comment) and #208 (unknown object name) are particularly ominous.


1

I have had similar issues to this and every time it was caused by a cloned machine where the SQL components had not been renamed (@@SERVERNAME, Database Mail SMTP, and so on). I would check that possibility first. If the LSAlert job is stopped you should not be receiving mails any more.


1

If you are using Oracle 11g or later, consider using the ADRCI utility instead.


1

One approach is to use sp_help_log_shipping_alert_job to find the log shipping alert job ID and then use sp_update_job to suspend the job during maintenance. Enable it back when maintenance is done. I don't know whether the job would not immediately send an alert upon being enabled back though.


1

By recompiling/committing the Alert, the double emails have stopped. It must have been a bad commit initially.



Only top voted, non community-wiki answers of a minimum length are eligible