July 10, 2009 at 9:49 am
I have an sql server 2005 database in full recovery mode that has very high activity (which incurrs logging) that occurs at random times during the week. The rest of the time the database is doing basically nothing. This is causing us to schedule a transaction log backup every 5 minutes 24/7 thru a maintenance plan so the log doesnt fill up and throw errors. This has the result of having 90+% of the transaction logs backups being very small and generating very many of them.
I would like to reduce the number of daily tlog backups and have them only implemented when the log file size is getting to a determined threshold.
Does anyone have a suggestions on how to schedule and automatically (thru SQL Agent) run a transaction log backup when the transaction log gets to be 70% (could be 50 to 80). I understand that we will only be able to recover to a PIT of the last transaction log backup and thats OK.
Thanks for any suggestions.
James
July 10, 2009 at 10:07 am
You can create an Alert in SQL Server Agent based on the SQL Serverperformance condition that the transaction log is greater than a certain percentage. The response to that alert could be to run the transaction log backup job outside its normally scheduled time.
July 10, 2009 at 10:12 am
--create the table
create table logspace
(
dbName varchar(255),
logSize decimal(18,5),
logSpaceUsed decimal(18,5),
status int
)
-- create the procedure
create procedure logsp as
dbcc sqlperf(logspace)
--put all these into job that's scheduled to run every 5 mins or so..
truncate table logspace
insert logspace
exec logsp
Declare @sql varchar(500)
Declare @dbname varchar(500)
declare log_Cursor cursor for
select dbName from logspace where logspaceused>70
open log_cursor
fetch next from log_cursor into @dbname
while @@fetch_status0
begin
set @sql='backup log ' + @dbname + ' to disk=c:\'+@dbname+convert(varchar, getdate(),112)
print @sql
exec sp_executesql @sql
end
close log_cursor
deallocate log_cursor
July 10, 2009 at 10:14 am
I understand that this would reduce the # of files you need to restore in order to restore the database, but is it really worth it?
If you run a trace and the run the Disk Usage report in SSMS you can see how that report returns the data for space used for the log. Here it is:
declare @tran_log_space_usage table
(
database_name sysname,
log_size_mb float,
log_space_used float,
status int
) ;
insert into
@tran_log_space_usage
exec (
'DBCC SQLPERF ( LOGSPACE )'
) ;
select
1 as l1,
1 as l2,
log_size_mb as LogSizeMB,
cast(convert(float, log_space_used) as decimal(10, 1)) as SpaceUsage,
'Used' as UsageType
from
@tran_log_space_usage
where
database_name = DB_NAME()
UNION
select
1 as l1,
1 as l2,
log_size_mb,
cast(convert(float, (100 - log_space_used)) as decimal(10, 1)) as SpaceUsage,
'Unused' as UsageType
from
@tran_log_space_usage
where
database_name = DB_NAME() ;
You could then modify that to determine % space used and run your backup as needed. Here would be the idea:
Get the %free space
If % space used > n then backup log else do nothing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2009 at 10:17 am
Guess I was too slow, as usual.
Lynn has an interesting solution, but I'm not sure I'd want to count on the alert to fire a backup.
Another thought is that I would probably add code to make sure I got a backup at least every 30 minutes, but I'm a bit picky about having backups.
Are you doing any differential backups? If I had to do log backups every 5 minutes, I'd probably do several differentials during the day as well to limit the # of backups I had to apply in a recovery situation.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2009 at 10:41 am
Actually, Jack, I should have been more specific. I'd still have a regularly scheduled t-log backup process running. The Alert would just run that job outside its normal schedule should there be excessive t-log activitiy taking the t-log over a specified percentage between scheduled t-log backups.
July 10, 2009 at 10:43 am
Thanks for the responses
I currently do weekly full and daily differential backups in addition to the transaction log backups. Currently I tend to investigate the alert method with a transactional log safety net at a determined interval of time. Maybe the best of both worlds.
James
July 10, 2009 at 11:32 am
The FILEPROPERTY function provides the count of pages used in a file, which can be compared to the total number of pages in the file, available in sys.database_files. All log files are in data_space_id 0.
This allows you to determine how full files are without messing with DBCC into temp tables.
To check if a database's log is more than 70% full:
IF EXISTS(SELECT * FROM sys.database_files f
WHERE f.data_space_id = 0
GROUP BY f.data_space_id
HAVING SUM(CONVERT(int, FILEPROPERTY(f.name, 'SpaceUsed'))) / (1.0 * SUM(f.size)) > .70 )
BEGIN
-- backup the log here
END
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply