September 20, 2011 at 10:04 am
Once more with feeling...
GilaMonster (9/13/2011)
If you have regular log backups and the log fills, it's probably filling for some reason other than needing a log backup. Like active transaction, active backup, replication or one of the other reasons. If that's the case, running a log backup will do absolutely nothing to fix the problem.It's like having a car that won't start and going to buy petrol without checking why it won't start. Might help, but probably won't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2011 at 10:38 am
take everyones advice and just set up regular log backups, that should be your starting point and is a must.
If you want belt and braces and really feel the need to backup on log filling leverage the dbcc sqlperf(logspace) command to track how full your logs are.
Output the results of this into a table and determine from that which log backups you want to run. starter for 10 -
-- use this to track log space over time. use statements commented out to set up environment
-- if using where clause run in context of database to track
set nocount on
--either run from sqlagent or uncomment while loop with delay
--while 1=1
--begin
declare @dbname sysname
SET @dbName = DB_NAME()
-- Create a temporary table to store the DBCC SQLPERF results.
CREATE TABLE #tempLogSpace (dbName sysname,
LogSize real,
LogSpacePctUsed real,
stat int)
-- Execute the DBCC SQLPERF statement and insert the results into
-- the temporary table.
INSERT INTO #tempLogSpace EXEC ('DBCC SQLPERF (LOGSPACE)')
-- Output the log size and used log space (in MB).
insert into dbaresources..logsize SELECT dbname,CAST (CONVERT (decimal (8,3),
ROUND(LogSize,3)) AS varchar(20)) + ' MB',
--CAST (CONVERT (decimal (8,3),
-- ROUND (LogSize * LogSpacePctUsed / 100.0,3)) AS varchar(20)) +
-- ' MB', getdate()
convert(varchar(20),LogSpacePctUsed), getdate()
FROM #tempLogSpace
--WHERE LOWER (RTRIM(LTRIM(dbName))) = LOWER (RTRIM(LTRIM(@dbName)))
-- Get rid of the temporary table.
DROP TABLE #tempLogSpace
--if getdate() > '2009-03-11 12:39:00' -- use this to set a stop for loop if required
--break
--waitfor delay '00:00:05'
--end
/*
create table dbaresources..logsize (dbName sysname,
LogSize varchar(20),
LogSpaceUsed varchar(20),
sizedatetaken datetime)
drop table dbaresources..logsize
select * from dbaresources..logsize
truncate table dbaresources..logsize
*/
---------------------------------------------------------------------
August 3, 2012 at 11:15 am
I created this job the other day to deploy to our servers. It will create an agent task scheduled once an hour to see if any transaction log has grown larger than 10GB. If it does it will email the operator and create an application error in the event log. You could modify this pretty easily to automate backing up the log or doing a manual shrink. We have regular issues with logfile overgrowth when someone runs any sort of backup manually (or something else that takes over the last checkpoint that DPM was aware of), and DPM stops backing up the transaction logs. I've also seen service broker be the cause of logfile over growth.
Here's the fast way to shrink your logfiles...
USE MASTER ;
ALTER DATABASE DBNAME SET RECOVERY Simple ;
USE DBNAME ;
GO
Declare @fID varchar ( 255)
Set @fID = ( SELECT top 1 name FROM sys.database_files Where type_desc ='LOG' Order by Size desc)
DBCC SHRINKFILE (@fID , 50 )
GO
ALTER DATABASE DBNAME SET RECOVERY Full ;
GO
and here's the agent task that monitors your logfile space.
USE [msdb]
GO
Declare @AdminUserName varchar(255)
Declare @Operator varchar(255)
Declare @EmailNotify bit
Set @AdminUserName = N'A SYSADMIN USER NAME'
set @Operator = N'YOUR OPERATOR NAME'
Set @EmailNotify = 1
/****** Object: Job [Transaction Logfile Size Monitoring] Script Date: 08/02/2012 15:39:11 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/02/2012 15:39:11 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
Declare @NotifyLevel int
If @EmailNotify = 1 Begin
set @NotifyLevel =2 end
else begin
set @NotifyLevel=0
set @Operator=null
end
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Transaction Logfile Size Monitoring',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=@NotifyLevel,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=@AdminUserName,
@notify_email_operator_name=@Operator, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Monitor Logfile Size] Script Date: 08/02/2012 15:39:11 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Monitor Logfile Size',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'Declare @LogSize int;
Declare @LogName varchar(255);
Declare @LogFile varchar(2000);
select Top 1 @LogName=name,@LogFile=physical_name,@LogSize = convert(float,size) * 8192 / (1024 * 1024) from sys.master_files
where type_desc = ''LOG'' Order by size desc
IF (@LogSize > 10240)
BEGIN
Declare @AlertString varchar(5000)
set @AlertString = @LogName + '' : '' + @LogFile + '' has reached a size of '' + Convert(varchar(20),@LogSize) + '' MB.''
RAISERROR(@AlertString, 18,1) with LOG
END
ELSE
BEGIN
RAISERROR(''Transaction Log monitoring complete'', 0, 1)
END',
@database_name=N'master',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Hourly Logfile Check',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20120802,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'18435d90-4ca0-4c7a-a8a9-c81f347ba32c'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply