Shrink Log File to Target Percentage

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    Comments posted to this topic are about the item Shrink Log File to Target Percentage

  • hannesm

    Valued Member

    Points: 66

    This seems to be a great piece of work....

    One question though - it seems as if there is a line missing after the last checkpoint ???

    ????if (select cast(databasepropertyex(db_name(), 'recovery') as varchar(10))) = 'FULL'

    ????begin

    ???? set @backup_file_name = upper(db_name()) + '_Log_' + replace(replace(replace(convert(varchar(50), getdate(), 120), '-', ''), ' ', ''), ':', '') + '.lbk'

    ???? set @sql = 'backup log ' + @database_name + ' to disk = ''' + @backup_log_file_path + @backup_file_name + ''' '

    ???? exec sp_executesql @sql

    ???? set @sql = 'checkpoint'

    ????end

    🙂

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    This is an artifact of copy and paste 🙂 You can safely comment out the "set @sql = 'checkpoint'" after the backup. It won't do any harm in however I'll be sure to post a new revision without it - good catch!

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    Just FYI - procedure has been updated on SSC to account for checkpoint (previous post).

  • dlucas-615847

    SSC Enthusiast

    Points: 132

    When I try to creat this stored procedure, I get two syntax errors:

    Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 107

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111

    Incorrect syntax near '#results'.

    I'm not sure why.

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    dlucas-615847 (9/19/2009)


    When I try to creat this stored procedure, I get two syntax errors:

    Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 107

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111

    Incorrect syntax near '#results'.

    I'm not sure why.

    I can't seem to reproduce it but check the quotes ('). I've run into some problems with the WYSWYG editor on SSC when uploading scripts. You can e-mail me at tbollhofer2@gmail.com if you continue to have problems and I'll send you a copy. I've been running this in production for some time now.

    Thanks 🙂

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    Tommy Bollhofer (9/20/2009)


    dlucas-615847 (9/19/2009)


    When I try to creat this stored procedure, I get two syntax errors:

    Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 107

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111

    Incorrect syntax near '#results'.

    I'm not sure why.

    I can't seem to reproduce it but check the quotes ('). I've run into some problems with the WYSWYG editor on SSC when uploading scripts. You can e-mail me at tbollhofer2@gmail.com if you continue to have problems and I'll send you a copy. I've been running this in production for some time now.

    Thanks 🙂

    Update: I can reproduce this by copying the T-SQL from SSC so I'm assuming this is related to the WYSWYG editor. I just uploaded a new version which should post later this afternoon or sometime tomorrow morning. If this continues to be a problem I will reach out to Steve Jones to see if we can add a resource section so that the procedure can be downloaded directly.

    In the interim, please feel free to e-mail me at tbollhofer2@gmail.com and I'll e-mail you a copy.

    Thanks!

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    Tommy Bollhofer (9/21/2009)


    Tommy Bollhofer (9/20/2009)


    dlucas-615847 (9/19/2009)


    When I try to creat this stored procedure, I get two syntax errors:

    Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 107

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111

    Incorrect syntax near '#results'.

    I'm not sure why.

    I can't seem to reproduce it but check the quotes ('). I've run into some problems with the WYSWYG editor on SSC when uploading scripts. You can e-mail me at tbollhofer2@gmail.com if you continue to have problems and I'll send you a copy. I've been running this in production for some time now.

    Thanks 🙂

    Update: I can reproduce this by copying the T-SQL from SSC so I'm assuming this is related to the WYSWYG editor. I just uploaded a new version which should post later this afternoon or sometime tomorrow morning. If this continues to be a problem I will reach out to Steve Jones to see if we can add a resource section so that the procedure can be downloaded directly.

    In the interim, please feel free to e-mail me at tbollhofer2@gmail.com and I'll e-mail you a copy.

    Thanks!

    The new version has been posted to SSC. I've tested and verified everything is working. To avoid any formatting issues; i.e. UNICODE, etc., I would recommend copying and pasting the code into SSMS.

    Thanks!

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • dlucas-615847

    SSC Enthusiast

    Points: 132

    That did the trick. Thanks.

Viewing 10 posts - 1 through 10 (of 10 total)

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