Shrink Database Log Files One by One

Question

Shrink Database Log Files One by One

Manie Verster

SSCertifiable

Points: 7152
More actions
March 31, 2023 at 12:00 am

#4165619

Comments posted to this topic are about the item Shrink Database Log Files One by One
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
March 31, 2023 at 10:49 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 26 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

I've just started reading your article, Manie. I don't believe you'll find many experts that would crucify anyone for using a cursor for process control. RBAR process of rows? Yeah, fair game but not for things like the subject of your article.

A lot of people will say you could have avoided the cursor by using that bloody sys.sp_MSforeachdb system stored procedure. Apparently, very few of those folks have look at that proc... it's actually a Cursor on steroids! They also don't realize that it's not the Cursor that's going to take the time here.

I do have a question for you, though... why are these database bases in the FULL Recovery Model? It sounds like you may not be taking regularly scheduled full and log file backups. If you are, then we need to make a couple of additions to your script but I don't want to take the time to write those suggestions up if you're not backing up these databases. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eitan Blumin SSC Enthusiast Points: 172 More actions · Answer 2

Hey, we got a similar script in our Madeira toolbox:

https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/Shrink%20All%20Shrinkable%20Transaction%20Logs.sql

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 3

Keep in mind, your LOG file(s) may not shrink at first attempt !!!

ref Shrink a log file

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Johan Bijnens wrote:

Keep in mind, your LOG file(s) may not shrink at first attempt !!!
ref Shrink a log file

Even there, they don't mention that you should do a CHECKPOINT first and then wait for the rocks to stop moving. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27799 More actions · Answer 5

Good article, and it does brign up the question of why a database is in full recovery model with no backups being made to keep the log file contained.

This process would be hard to execute in a Availability Group database as you first need to remove the database from the AG group before altering the recovery model.

And not to try to tear things apart but the use of nvarchar(max) is not a good habit to practice.

Those things aside, thank you for taking the time that you did in your contributions with this article.

----------------------------------------------------

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 6

indeed. Good point.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ildjarn.is.dead Ten Centuries Points: 1165 More actions · Answer 7

That's what Powershell is for.

Invoke-DBAShrink -SQLInstance Instance -AllUserDatabases -FileType Log

chuck.hamilton SSCommitted Points: 1539 More actions · Answer 8

You're a day early if this is an April Fools day joke.

Unless the DBs need FULL recovery model for something, just change them to SIMPLE. Do you need point-in-time recovery, log shipping, or always or availability groups? If not just change them to simple recovery model.

If they need point in time recovery you need to set up a regularly scheduled transaction log backup.

This reply was modified 1 year, 8 months ago by chuck.hamilton. Reason: spelling correction
This reply was modified 1 year, 8 months ago by chuck.hamilton.

Carlos Benito SSC Enthusiast Points: 159 More actions · Answer 9

Another approach is to back it up to NUL

CHECKPOINT

BACKUP LOG [databasename] TO DISK = 'NUL'

ildjarn.is.dead Ten Centuries Points: 1165 More actions · Answer 10

That doesn't shrink the file.

And the logfile of a simple database can also grow large if you try to do everything in one transaction.

Learn_something_new_everyday Say Hey Kid Points: 670 More actions · Answer 11

This reply was modified 1 year, 8 months ago by Learn_something_new_everyday. Reason: dupe

Learn_something_new_everyday Say Hey Kid Points: 670 More actions · Answer 12

Agree 100%,, I will add that you will need the DBATools cmdlets https://docs.dbatools.io/ . They even have a video on how to install it, you only need to install on your workstation or a dev box that you use to manage your SQL servers.

Learn_something_new_everyday Say Hey Kid Points: 670 More actions · Answer 13

This reply was modified 1 year, 8 months ago by Learn_something_new_everyday. Reason: dupe