SQL SERVER 2000 LOGS

Question

Post reply

SQL SERVER 2000 LOGS

Tom Reis

SSC Journeyman

Points: 89
More actions
May 17, 2005 at 9:25 am

#163591

Is there a way in SQL 2000 to monitor database logs and then send email when they reach 70 % full. Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736198 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736198

May 17, 2005 at 11:50 am

#559336

They don't get full. They grow with disk space.

noeld SSC Guru Points: 96590 More actions · Answer 2

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'SQLServeratabases|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

sa24 SSCrazy Eights Points: 8027 More actions · Answer 3

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.

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 4

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

Louis_See1 Ten Centuries Points: 1100 More actions · Answer 5

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'SQLServeratabases|Percent Log Used|Databasename|>|80',

@include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

Kumaran Govender Ten Centuries Points: 1089 More actions · Answer 6

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.

Michelle Cook SSCrazy Points: 2119 More actions · Answer 7

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

DBA-640728 SSChampion Points: 12898 More actions · Answer 8

hello with your script i get the error:

Procedure or function 'sp_dumplog' expects parameter '@dmpdevice', which was not supplied.