email notificaiton when log files are getting large

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.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Joe Murphy" <spam@.spamthis.com> wrote in message
news:OdCtSbHPGHA.1832@.TK2MSFTNGP11.phx.gbl...
Can SQL send me an email alert when log files are getting large?
Thanks,
JM
|||How to?
_______________________________
http://boston.craigslist.org/about/b.../62974620.html
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uPDMueHPGHA.536@.TK2MSFTNGP09.phx.gbl...
> 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@.spamthis.com> wrote in message
> news:OdCtSbHPGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Can SQL send me an email alert when log files are getting large?
> Thanks,
> JM
>
|||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@.spamthis.com> wrote in message news:%23soU6wHPGHA.3728@.tk2msftngp13.phx.gbl...
> How to?
> --
> _______________________________
> http://boston.craigslist.org/about/b.../62974620.html
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uPDMueHPGHA.536@.TK2MSFTNGP09.phx.gbl...
>
|||I'd be interested in SQL2000 (SP3)
Regards,
Hank Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23cPC6SIPGHA.3984@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> 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@.spamthis.com> wrote in message
> news:%23soU6wHPGHA.3728@.tk2msftngp13.phx.gbl...
|||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@.aol.com> wrote in message news:%23Y6DHDSPGHA.3408@.TK2MSFTNGP12.phx.gbl...
> I'd be interested in SQL2000 (SP3)
> --
> Regards,
> Hank Arnold
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23cPC6SIPGHA.3984@.TK2MSFTNGP14.phx.gbl...
>
|||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/b.../62974620.html
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OPnG5UVPGHA.3264@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> 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@.aol.com> wrote in message
> news:%23Y6DHDSPGHA.3408@.TK2MSFTNGP12.phx.gbl...
|||Did you specify *performance condition alert*? I'm not talking about the regular event alerts...
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@.spamthis.com> wrote in message news:%23Nw4iLuPGHA.3848@.TK2MSFTNGP12.phx.gbl...
> 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/b.../62974620.html
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OPnG5UVPGHA.3264@.TK2MSFTNGP11.phx.gbl...
>
|||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@.spamthis.com> wrote in message
news:%23Nw4iLuPGHA.3848@.TK2MSFTNGP12.phx.gbl...
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/b.../62974620.html
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OPnG5UVPGHA.3264@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> 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@.aol.com> wrote in message
> news:%23Y6DHDSPGHA.3408@.TK2MSFTNGP12.phx.gbl...