Shrink msde log file

Should we enable "Auto shrink" or create a job to shrink the log file? Improve this question. Lars Lars 61 1 1 silver badge 2 2 bronze badges. No, since the log file grows every time, what did you gain by shrinking it? Being able to say you had some free space for a while? Rent that space out short term? Just leave the log file as big as it needs to be if you can't improve the things that are causing it to grow in the first place.

Shrinking just to grow again is a waste of everyone's time, and can actually make things worse. Add a comment. Active Oldest Votes. Improve this answer. Community Bot 1. Kin Shah Kin Shah Ahmad Abuhasna Ahmad Abuhasna 2, 3 3 gold badges 19 19 silver badges 36 36 bronze badges.

How is shrinking a log file going to cause fragmentation or harm an index? AaronBertrand "auto shrink" is database parameter. Yes, thank you for that, however the OP is having issues with their MSDB log file specifically, and are looking for a resolution to that problem.

Sign up or log in Sign up using Google. We actually reached the point where the data drive free space was consumed. First, step is to do a full backup of MSDB.

You should always make a backup before you plan on doing any changes with a system database. You should also have a plan to restore the system database just in case you have to implement it.

Next, I needed to know why this database was so huge. I ran the following query to see the file size of all database objects within the MSDB database. We are not allowed to take the SQL Server database engine offline so we go with the next best option truncate the tables in question and shrink the database. Okay now that we are done with the disclaimer I created the following script to truncate the log and data files. Now that you did the nasty and shrinked the database data file you need to rebuild your indexes and update your statistics.

You can find the scripts to do this below. Well actually there are quite a few things that could have and should have been done. I will show you how to do both below. Once the properties window comes up select History on the list on the left. You will then be able to remove data by record size or date. Your email address will not be published.

Each VLF file is the unit that can be marked as available for reuse free or not available for reuse used. Note: It should be kept in mind that Log Truncation is not meant to reduce the size of the physical log file.

Log Shrinking is needed to reduce the physical log file. Clearing SQL Server transaction log involves two steps. Instead of truncating transaction logs, we can use simple recovery mode by which we do not generate logs we would not be using.

Another option to shrink SQL transaction log file is to, backup the database log using the following command:. This is how one can get to know how to clear SQL Server Transaction Log file in order to free up the space in it for further storage of transactions.

Transactions are very important to keep track of the changes taking place in corresponding database as well as to rollback just in case it is required.