How to Write Errors into SQL Server Log

The following T-SQL writes errors into SQL log when there are databases have not been backed up in last 12 hours:

IF EXISTS (
    SELECT bs.database_name,  MAX(bs.backup_finish_date) AS LatestDatabaseBackupDate
    FROM  msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
    INNER JOIN master.sys.databases d ON d.name = bs.database_name
    WHERE bs.type = 'D' and d.name <> 'tempdb' and d.state = 0 and d.source_database_id is null
    GROUP BY bs.database_name HAVING DATEDIFF(HH, MAX(bs.backup_finish_date), GETDATE()) > 12
) RAISERROR(N'On TESTSERVER\TESTINST one or more databases have not been backed up in last 12 hours, please check..', 17, 1) with log

Check the log as below:

ALso we can check spid 55 details:

select * from sys.sysprocesses where spid =55;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.