SQL SERVER 2000 LOGS
-
Is there a way in SQL 2000 to monitor database logs and then send email when they reach 70 % full. Thanks.
-
They don't get full. They grow with disk space.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Just create an Alert for SQL Performance Condition like:
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'YourDatabase Log > 70%'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'YourDatabase Log > 70%'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'YourDatabase Log > 70%', @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 60, @performance_condition = N'SQLServer
atabases|Percent Log Used|YourDatabase|>|70', @include_event_description_in = 5, @category_name = N'[Uncategorized]'END
and make sure you setup the email or page to an operator!
hth
* Noel -
You can recycle the logs periodically by using sp_cycle_errorlog. But as Steve said, there is no way to restrict them to a certain size.
-
You can restrict the size of Transaction Log. Noeld answer is correct . Just to add you can use @job_id parameter to run a particular job if the event happens. Example to backup the log (with or with truncate_only).
Note: I am assuming you have less than 99 database
Kindest Regards,Amit Lohia
-
EXECUTE msdb.dbo.sp_add_alert @name = N'TransactionLOG Databasename 70 %',
@message_id = 0, @severity = 0, @enabled = 1,
@delay_between_responses = 60,
@performance_condition = N'SQLServer
atabases|Percent Log Used|Databasename|>|80',@include_event_description_in = 5, @category_name = N'[Uncategorized]'
END
-
Mate,
You can use Perfmon, Select:
Databases: Percentage Log used,
you can set this against individual or all.
Also try sp_spaceused it might hlp.
-
You could also use a stored procedure like the one below, with changes to fit your situation and schedule it to run periodically.
create procedure dbo.sp_dumplog
@dmpdevice varchar(80)
as
declare @db varchar( 40)
declare @name varchar(40)
declare @dstat char(180)
declare @seq varchar(14)
declare @logmsg varchar(120)
declare @mail_subj varchar(50)
declare @mail_body varchar(240)
declare @dmpname char(40)
declare @error int
declare @maxid int
declare @minid int
create table #dbrcvmode (
DBN char(40),
rcvmode varchar(20)
)
declare @cur1 table (name varchar(50), tid int identity(1,1))
insert into @cur1 (name)
select name from master.dbo.sysdatabases
select @maxid = max(tid), @minid = min(tid) from @cur1
While (@minid <= @maxid)
begin
select @db = name from @cur1 where tid = @minid
insert into #dbrcvmode (DBN, rcvmode)
select @db, CONVERT( varchar(20), databasepropertyex(@db, 'Recovery'))
set @minid = @minid + 1
end
CREATE TABLE #logspace (
DBName varchar( 100),
LogSize float,
PrcntUsed float,
status int
)
insert into #logspace
exec ('dbcc sqlperf(logspace)')
select @name= ' '
select @dstat=' '
declare @cur2 table (name varchar(50), tid int identity(1,1))
insert into @cur2 (name)
select a.DBName from
#logspace a, #dbrcvmode b
where a.DBName not in ('model','master','msdb','tempdb')
and a.DBName = b.DBN and b.rcvmode = 'FULL'
and a.PrcntUsed > 70
select @maxid = max(tid), @minid = min(tid) from @cur2
While (@minid <= @maxid)
begin
select @name = name from @cur2 where tid = @minid
set @mail_subj =( 'Transaction Log dump results for database ' + @name)
set @logmsg =( 'Log is over 70 percent full. Dumping the log for database ' + @name)
select @seq = convert(char(8),getdate(), 112) +
right('0' + convert(varchar(2),datepart(hour,getdate())),2) +
right('0' + convert(varchar(2),datepart(minute,getdate())),2)
set @dstat =('backup log ' + rtrim(@name)+ ' to disk=''' +rtrim(@dmpdevice)+ '\' + rtrim(@name)+ '_tlog_' + rtrim(@seq) + '.TRN''')
exec (@dstat)
select @error = @@error
if @error = 0
begin
set @mail_body=( rtrim(@logmsg) + ' - log dump successful!!')
execute dbo.sp_send_smtpmail
@From = 'e-mail address',
@To = 'e-mail address',
@Subject = @mail_subj,
@Body = @mail_body
end
else
begin
set @mail_body=( rtrim(@logmsg) + ' - log dump FAILED, check SQL Server and event logs for details!!')
execute dbo.sp_send_smtpmail
@From = 'e-mail address',
@To = 'e-mail address',
@Subject = @mail_subj,
@Body = @mail_body
end
set @minid = @minid + 1
/* Dump Transaction Logs */
end
drop table #dbrcvmode
drop table #logspace
GO
-
hello with your script i get the error:
Procedure or function 'sp_dumplog' expects parameter '@dmpdevice', which was not supplied.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply