Shrink Database Log Files One by One

Question

Shrink Database Log Files One by One

Viewing 12 posts - 16 through 26 (of 26 total)

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

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

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.

Carlos Benito SSC Enthusiast Points: 142 More actions · Answer 2

I know... but do you want to keep shrinking and expanding the log file constantly?... I do this in production where I have some databases that are replicated, and I do not need point in time recovery. I have set the transaction log with no auto growth. and run a job every hour.

Carlos Benito SSC Enthusiast Points: 142 More actions · Answer 3

I know... but do you want to keep shrinking and expanding the transaction logs constantly? The backup to NUL is done against the databases with full recovery mode; I do this on production to some databases that are replicated and do not need point in time recovery using a job that runs every hour. Those databases have the transaction log with auto growth off.

Manie Verster SSCertifiable Points: 7152 More actions · Answer 4

@jeff Moden, I thank you from my heart for your comment. I greatly respect you and have certainly learned a lot from you. There are no backups because these are test databases for development purposes. The databases were set up on the clients in full recovery mode and every time I restore a backup from the client it sets my database in full recovery mode. I got tired of going back and set it to simple.

:-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)

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

I think you forgot to include an error handling routine. Here is an example.

begin try
select 1/0
end try
begin catch
raiserror ('April fools!',0,1)
end catch

Manie Verster SSCertifiable Points: 7152 More actions · Answer 6

Thank you all for the comments. I know my code still needs some tweaking. This was a learning process for me. I have in the meantime tried to get a better code with the use of ChatGPT and will post an update here when I have it. Remember this was done for a test environment.

:-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)

aalielmi Grasshopper Points: 16 More actions · Answer 7

The article provides a script to shrink SQL Server database log files one by one using a cursor, which may not be favoured by many DBAs due to its potential performance and data integrity issues.

Changing the recovery model of a database from full to simple and then back to full can cause serious issues with point-in-time recovery (PITR), and changing the recovery model in an Availability Group environment can cause issues with replication and data synchronization between replicas. Additionally, using a cursor to loop through all databases and log files is unnecessary in the script. Cursors can be slow, resource-intensive, and vulnerable to security threats, and can cause blocking and deadlocks.

Points to improve the script:

- To exclude system databases, simply use database_id > 4.

- Use the CONCAT() function instead of using the + operator.

- Ensure that the database is in a read/write state and is online.

- To ensure proper formatting of text in Windows, it is recommended to use a combination of carriage return and line feed characters (CHAR(13)+CHAR(10)) instead of just using CHAR(13) (a carriage return) alone.

Finally, note that the size of the log files may not shrink on the first attempt due to active transactions.

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 8

Manie Verster wrote:

@Jeff Moden, I thank you from my heart for your comment. I greatly respect you and have certainly learned a lot from you. There are no backups because these are test databases for development purposes. The databases were set up on the clients in full recovery mode and every time I restore a backup from the client it sets my database in full recovery mode. I got tired of going back and set it to simple.

Thank you for the kind words, Manie.

I had to ask about the backups because we treat our Dev boxes as if they were production boxes. I's much more likely for their to be an "accident" that only a restore could quickly fix.

If you don't do that, then whatever code you have to do the restores to your Dev box could contain a couple of extra lines of code to 1) set the ownership to the correct user (normally, "sa" and the "sa" user is disabled), 2) change the Recovery Model to SIMPLE, 3) do a log file shrink and regrow to a size appropriate for the Dev environment, 4) change the growth.

We also have code that check synonyms and repoints them according to what is in the Dev environment, drops all users and adds new users appropriate to the Dev environment, and much more.

--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)

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 9

Manie Verster wrote:

Thank you all for the comments. I know my code still needs some tweaking. This was a learning process for me. I have in the meantime tried to get a better code with the use of ChatGPT and will post an update here when I have it. Remember this was done for a test environment.

On the subject of ChatGPT... I'd be really careful about using any answers it comes up with for SQL Server. To date, it has returned some wildly incorrect answers in a super confident manner for several relatively easy questions I've asked of it. Remember that the current state of AI is that it looks at statistics to form a consensus with some "machine learning" and uses a remarkable "conversation generation" to turn the consensus into an almost human tone of conversation. The problem is that the statistics don't necessarily point to the best answer. It points to commonly given answers and those have been wildly and frequently incorrect for a very long time. It also gives you absolutely no inclination that it could be wrong except in the "Use Agreement" which, apparently, either no one reads or no one understands the ramifications of.

--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)

Jason A. Long SSC-Insane Points: 23781 More actions · Answer 10

Maybe I'm missing something but it seems that there are two easier options...

1) put everything on your local test machine into SIMPLE recovery.

2) if #1 isn't an option, just do regular backs using the ‘NUL’ disk option.

Leo Peysakhovich Hall of Fame Points: 3973 More actions · Answer 11

There are few flows in this article. Most of them already mentioned. But if you are placing database in simple mode and then in full mode you will be

failing log backups until full backup is done (assuming that log backups are running on the server)
you lost the backup base and create the problem with restores point in time.
If log backup job exist and run on the server then (based on how it is programmed) it may fail during log shrink process.

masri999 91217 Newbie Points: 1 More actions · Answer 12

Not a good idea to change recovery model to simple from full. In most cases, changing recovery model from full recovery not allowed in production . Hope new DBAs don't use this script blindly