Can SQL send me an email alert when log files are getting large?

Thanks,
JM

You can set up an alert that fires when the % used space in a log goes above
a certain threshold.  Unfortunately, you'd need to set up one such alert per
database.

Can SQL send me an email alert when log files are getting large?

Thanks,
JM

How to?

Show quoteHide quote

What version of SQL Server?

Show quoteHide quote

I'd be interested in SQL2000 (SP3)

Show quoteHide quote

Enterprise Manager, Management, SQL Server Agent, right-click Alerts, New Alert.

Specify a performance condition alert with the desired perf mon counter.

And first you create your operator and make sure that SQL Mail is functioning properly.

Show quoteHide quote

OK, but that doesn't seem to work when trying to measure a threshold. Say
when the transaction log reaches N% of total allocated size.

That's the kind of thing I'm looking for.

Thanks,
JM

Show quoteHide quote

Did you specify *performance condition alert*? I'm not talking about the regular event alerts...

Show quoteHide quote

Here's a sample script:


-- Script generated on 3/3/2006 8:48 PM
-- By: BMCI04\Administrator
-- Server: BMCI04\SQL2000

BEGIN TRANSACTION
  DECLARE @JobID BINARY(16)
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name =
N'[Uncategorized (Local)]') < 1
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id
  FROM   msdb.dbo.sysjobs
  WHERE (name = N'Backup Northwind Log')
  IF (@JobID IS NOT NULL)
  BEGIN
  -- Check if the job is a multi-server job
  IF (EXISTS (SELECT  *
              FROM    msdb.dbo.sysjobservers
              WHERE   (job_id = @JobID) AND (server_id <> 0)))
  BEGIN
    -- There is, so abort the script
    RAISERROR (N'Unable to import job ''Backup Northwind Log'' since there
is already a multi-server job with this name.', 16, 1)
    GOTO QuitWithRollback
  END
  ELSE
    -- Delete the [local] job
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Backup Northwind Log'
    SELECT @JobID = NULL
  END

BEGIN

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = N'Backup Northwind Log', @owner_login_name = N'sa', @description
= N'No description available.', @category_name = N'[Uncategorized (Local)]',
@enabled = 1, @notify_level_email = 0, @notify_level_page = 0,
@notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
1, @step_name = N'Backup', @command = N'backup log Northwing to disk =
''C:\Temp\Northwind.trn''', @database_name = N'master', @server = N'',
@database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0,
@flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name =
N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0,
@on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
@start_step_id = 1

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
@server_name = N'(local)'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO   EndSave
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:




IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Backup
Northwind Log'))
---- Delete the alert with the same name.
  EXECUTE msdb.dbo.sp_delete_alert @name = N'Backup Northwind Log'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'Backup Northwind Log', @message_id =
0, @severity = 0, @enabled = 1, @delay_between_responses = 60,
@performance_condition = N'MSSQL$SQL2000:Databases|Log File(s) Used Size
(KB)|Northwind|>|25', @include_event_description_in = 5, @job_name =
N'Backup Northwind Log', @category_name = N'[Uncategorized]'
END


OK, but that doesn't seem to work when trying to measure a threshold. Say
when the transaction log reaches N% of total allocated size.

That's the kind of thing I'm looking for.

Thanks,
JM

Show quoteHide quote

SQL Server 2005: Error 14258...SQL Server Agent is starting
Tools with/for Express
Error 14258: ... SQL ServerAgent is starting
Just how to navigate SQL Server?
Diagram to Image