/*** This is a script to watch the T-log and make sure it dows not fill up.  The technique
     is to run DBCC SQLPERF(logspace) to a table.  This function calculates the % used for
     all T-logs on a server by database.  The second part of the script reads the logspace
     table (the output from DBBC SQLPERF) and checks to see if the logspace % is over 
     60 percent.  If so, it dumps the log for that database to a disk device.

     Schedule sp_logstd to run every X minutes and it will find if you are filling
     your log.  This does not relieve you from setting an alert on 1105 error to do a 
     DUMP TRAN xxx WITH NO_LOG.   ***/

/********************************************************************************************/

/**** This first part just creates a table.  You can create it anywhere.  I created it in
      Master.  You can use a temp table if you want to script the whole thing in a job. ***/

use master
go

/****** Object:  Table dbo.logspace    Script Date: 2/5/98 11:51:37 AM ******/
if exists (select * from sysobjects where id = object_id('dbo.logspace') and sysstat & 0xf = 3)
	drop table dbo.logspace
GO

/****** Object:  Table dbo.logspace    Script Date: 2/5/98 11:51:37 AM ******/
CREATE TABLE dbo.logspace (
	dbname char (30) NOT NULL ,
	logsize float NOT NULL ,
	logspaceused float NOT NULL ,
	status tinyint NOT NULL 
)
GO

/********************************************************************************************/

/*** This is the first part of the  script.  It loads the results into a table  ****/

if exists (select * from sysobjects where id = object_id('dbo.sp_log_dbccsqlperf') and sysstat & 0xf = 4)
	drop procedure dbo.sp_log_dbccsqlperf
GO

create proc sp_log_dbccsqlperf as

insert logspace
 exec ('dbcc sqlperf(logspace)')

GO

/********************************************************************************************/

/*** This proc is the second part.  It gets the output from the DBBC and tests for 
     a percent full.  I truncate the table after it is done so it does not get too big. ***/ 

if exists (select * from sysobjects where id = object_id('dbo.sp_logstd') and sysstat & 0xf = 4)
	drop procedure dbo.sp_logstd
GO

CREATE PROCEDURE sp_logstd AS
declare @name char(30)


insert jobs..logspace
 exec ('dbcc sqlperf(logspace)')


select @name= ' '

while @name is not null
begin
	select @name=(select min(dbname) from jobs..logspace 
			where logspaceused >60
			and dbname>@name
			and dbname not in(master,tempdb,pubs))
	
	if @name is not null 
	begin
	select 'Log is over 60 percent full. Dumping the log for database ' + @name
	EXEC ('dump tran ' + @name + ' to test with noinit')
	end 
	
end

truncate table jobs..logspace
GO