1

We have a database that needs 40% free in the log files at all times or we get an alert. I cannot find a way to set growth when a limit is reached.

Of course we can set autogrowth to a certain limit but then we would have to go into it weekly to add 40% based on last growth.

Any ideas? SQL 2012

  • 6
    Why do you have this alert? I would very much question who/what is enforcing this. – LowlyDBA Apr 24 '18 at 20:44
  • 1
    This alert doesn’t make sense to me. Imagine trying to keep your dishwasher at 40% empty at all times. – Aaron Bertrand Apr 24 '18 at 21:41
  • These types of alerts are commonplace in large organizations where server monitoring is enforced by departments who have zero knowledge of how database systems actually operate. Our servers are monitored for this type of thing all over the place; we (DBA group) has no control over it. – Max Vernon Apr 28 '18 at 14:41
1

I agree with Brent about this being not a fantastic idea; I prefer to manage log file sizes manually with intention.

Having said that, you can use SQL Server Agent alerts to accomplish what you want.

DECLARE @jobid uniqueidentifier;
DECLARE @cmd nvarchar(max);
SET @cmd = N'DECLARE @cmd nvarchar(max);
DECLARE @size int;
DECLARE @newSize int;
SET @size = (SELECT size * 8192E0 / 1048576 
             FROM sys.master_files mf 
             WHERE mf.name = ''templog'');
SET @newSize = @size + (@size * 0.40);
SET @cmd = N''ALTER DATABASE tempdb 
              MODIFY FILE (name = ''''templog''''
                 , SIZE = '' + CONVERT(nvarchar(max), @newSize) + N''MB'''');'';
EXEC master.sys.sp_executesql @cmd;';

IF EXISTS (SELECT 1 
             FROM msdb.dbo.sysjobs sj 
             WHERE sj.name = N'tempdb log size increase')
BEGIN
    EXEC msdb.dbo.sp_delete_job @job_name = N'tempdb log size increase';
END
EXEC msdb.dbo.sp_add_job @job_name = N'tempdb log size increase'
    , @enabled = 1
    , @description = N'increases the size of tempdb log file by 40%'
    , @start_step_id = 1
    , @owner_login_name = 'sa'
    , @job_id = @jobid OUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @jobid
    , @step_id = 1
    , @step_name = N'increase log size'
    , @subsystem = N'TSQL'
    , @command = @cmd;

EXEC msdb.dbo.sp_add_jobserver @job_id = @jobid, @server_name = N'(local)';

IF EXISTS (SELECT 1 
             FROM msdb.dbo.sysalerts sa 
             WHERE sa.name = N'tempdb 80pct Tx Log Alert')
BEGIN
    EXEC msdb.dbo.sp_delete_alert @name = N'tempdb 80pct Tx Log Alert';
END
EXEC msdb.dbo.sp_add_alert @name = N'tempdb 80pct Tx Log Alert' 
        , @message_id = 0 
        , @severity = 0 
        , @enabled = 1 
        , @delay_between_responses = 300 
        , @include_event_description_in = 7 
        , @category_name = N'[Uncategorized]' 
        , @performance_condition = N'MSSQL$MV2016:Databases|Percent Log Used|tempdb|>|60' 
        , @job_id = @jobid;

You'll need to modify the code above to reflect the name of the server in the @performance_condition parameter. If your server is a named instance, you'll need to change MV2016 to the name of your instance. If you're using an unnamed instance then @performance_condition would simply be:

@performance_condition = N'MSSQL:Databases|Percent Log Used|tempdb|>|60' 

This alert will fire whenever the tempdb log file crosses 60% full. When the alert fires, it automatically runs a job which increases the size of the tempdb log by 40%.

5

SQL Server doesn't have this functionality built in, but you could start with this DBA.se question:

Query to report disk space allocation and used space

Then based on the output, grow the log files appropriately.

I'll be honest though: I'm decent with T-SQL, and it'd take me longer to write that than it would to just change the threshold in my monitoring software so that it's more appropriate. Because here's the problem: if you start growing your log files like crazy, next thing you know, your monitoring software is going to be complaining about having less than 40% free space on your drives, hahaha.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.