SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tommy Bollhofer / Shrink Log File to Target Percentage / Latest Posts

RE: Shrink Log File to Target Percentage

Tommy Bollhofer — Mon, 21 Sep 2009 13:30:15 GMT

[quote][b]Tommy Bollhofer (9/21/2009)[/b][hr][quote][b]Tommy Bollhofer (9/20/2009)[/b][hr][quote][b]dlucas-615847 (9/19/2009)[/b][hr]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 107Incorrect syntax near ' '.Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111Incorrect syntax near '#results'.I'm not sure why.[/quote]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 :)[/quote]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![/quote]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!

RE: Shrink Log File to Target Percentage

Tommy Bollhofer — Mon, 21 Sep 2009 12:00:27 GMT

[quote][b]Tommy Bollhofer (9/20/2009)[/b][hr][quote][b]dlucas-615847 (9/19/2009)[/b][hr]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 107Incorrect syntax near ' '.Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111Incorrect syntax near '#results'.I'm not sure why.[/quote]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 :)[/quote]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!

RE: Shrink Log File to Target Percentage

Tommy Bollhofer — Sun, 20 Sep 2009 11:07:37 GMT

[quote][b]dlucas-615847 (9/19/2009)[/b][hr]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 107Incorrect syntax near ' '.Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111Incorrect syntax near '#results'.I'm not sure why.[/quote]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 :)

RE: Shrink Log File to Target Percentage

dlucas-615847 — Sat, 19 Sep 2009 15:30:11 GMT

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 107Incorrect syntax near ' '.Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111Incorrect syntax near '#results'.I'm not sure why.

RE: Shrink Log File to Target Percentage

Tommy Bollhofer — Fri, 18 Sep 2009 07:54:14 GMT

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

RE: Shrink Log File to Target Percentage

Tommy Bollhofer — Tue, 01 Sep 2009 06:26:51 GMT

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!

RE: Shrink Log File to Target Percentage

hannesm — Tue, 01 Sep 2009 03:37:37 GMT

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 ??? [code]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[/code]:-)

Shrink Log File to Target Percentage

Tommy Bollhofer — Mon, 31 Aug 2009 07:02:15 GMT

Comments posted to this topic are about the item [B]Shrink Log File to Target Percentage[/B]