Re: email notificaiton when log files are getting large
- From: "Tom Moreau" <tom@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 3 Mar 2006 20:48:42 -0500
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
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Joe Murphy" <spam@xxxxxxxxxxxx> wrote in message
news:%23Nw4iLuPGHA.3848@xxxxxxxxxxxxxxxxxxxxxxx
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
--
_______________________________
http://boston.craigslist.org/about/best/nyc/62974620.html
"Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> wrote in
message news:OPnG5UVPGHA.3264@xxxxxxxxxxxxxxxxxxxxxxx
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.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hank Arnold" <rasilon@xxxxxxx> wrote in message
news:%23Y6DHDSPGHA.3408@xxxxxxxxxxxxxxxxxxxxxxx
I'd be interested in SQL2000 (SP3)
--
Regards,
Hank Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> wrote
in message news:%23cPC6SIPGHA.3984@xxxxxxxxxxxxxxxxxxxxxxx
What version of SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Murphy" <spam@xxxxxxxxxxxx> wrote in message
news:%23soU6wHPGHA.3728@xxxxxxxxxxxxxxxxxxxxxxx
How to?
--
_______________________________
http://boston.craigslist.org/about/best/nyc/62974620.html
"Tom Moreau" <tom@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:uPDMueHPGHA.536@xxxxxxxxxxxxxxxxxxxxxxx
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.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Joe Murphy" <spam@xxxxxxxxxxxx> wrote in message
news:OdCtSbHPGHA.1832@xxxxxxxxxxxxxxxxxxxxxxx
Can SQL send me an email alert when log files are getting large?
Thanks,
JM
.
- References:
- Re: email notificaiton when log files are getting large
- From: Hank Arnold
- Re: email notificaiton when log files are getting large
- From: Tibor Karaszi
- Re: email notificaiton when log files are getting large
- From: Joe Murphy
- Re: email notificaiton when log files are getting large
- Prev by Date: Re: CheckPoint causing long SP durations
- Next by Date: Can SSIS packages target SQL 2000 DBs?
- Previous by thread: Re: email notificaiton when log files are getting large
- Next by thread: Re: SQL Mail
- Index(es):
Relevant Pages
|
