Shrink Log File to Target Percentage

Question

Post reply

Shrink Log File to Target Percentage

Tommy Bollhofer

SSChampion

Points: 14940
More actions
August 31, 2009 at 7:02 am

#214468

Comments posted to this topic are about the item Shrink Log File to Target Percentage
Tommy
Follow @sqlscribe

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

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

hannesm Valued Member Points: 66 More actions · Answer 1

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 More actions · Answer 2

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

Follow @sqlscribe

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 3

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

Tommy

Follow @sqlscribe

dlucas-615847 SSC Enthusiast Points: 132 More actions · Answer 4

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 More actions · Answer 5

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

Follow @sqlscribe

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 6

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

Follow @sqlscribe

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 7

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!

Tommy

Follow @sqlscribe