I have inherited a SQL Server 2019 database with a very large log file that appears to be increasing. Let me explain.
I have a database called DbFoo (lets say) and it gets a full db backup every week Saturday. The following facts are current:
Actual DbFoo Data Size: 14,310,016 KB
Actual DbFoo Log Size: 107,597,568 KB
Full DbFoo backup file: 11,235,212 KB
As can be seen the log file is clearly hugh. Not sure how it got so big as I have inherited this particualar db and have been asked to reduce the size of the log file...
Do you have any suggestions/advice as to how to safely reduce the log file (given the fact the full backup run tomorrow)
Thanks in advance,
Jelly.
More likely than not, one or more databases have full recovery model. For databases with full or bulk recovery model, you need to schedule and run log backups. If you don't do that the log file will continuously grow. See here.
So do these steps:
Look at the recovery model for each database. You can run SELECT recovery_model,* FROM sys.databases to see this.
For those that are in full or bulk-logged (3,2) check if there is log backup scheduled. If not set those up.
Shrink the log file (not the database file) to some size that you want it to be.
Google for the details and experiment on a development server if you are not familiar with the process.
Hi, thanks for the speedy reponse guys. Peloase note that I'm not a DB sysadmin but just a dev who inherited this issue. Yes, the database in question has a full recovery model. Yes, the full database backup is scheduled for once a week and we also backup the transactional logs Mon-Fri every hour during work hours. Is that what you mean by check to see if log backup scheduled?
I'm not sure how to shrink the the log file? Can you sketch out a method?
Your setup appears to be fine. If that is the case, then the log file is large probably because there are transactions running on the server that require a lot of log space. SQL server adds space to the log file as needed, but does not release them automatically when it is not needed anymore.
Run the following command on your server DBCC LOGINFO('YourDatabaseNameHere')
It will list all the Virtual Log Files (VLFs) and their statuses. Any with status = 0 are free and can be released back to the system as long as there are no VLFs with status = 2 with higher FSeqNo. If you see a lot of free VLFs you can shrink the log file.
But, this process - releasing the VLFs should have automatically happened when you do the log backups. So if that is not happening, it may be your log backup configuration. You can do log backups with copy-only option (if I remember right), which would not release the space nor prevent the monotonic growth.
Read some of the articles that @RogierPronk pointed to and get an understanding of how to manage the log file. I am developer too, and like you, had to take on some of the db admin responsibilities. For a developer, learning those tasks are comparatively easy, in my opinion.
Thanks for the advice. I ran the command you recommended for the DB in question and there were 120 rows with a status of zero.
Perhasp I should explain (in general terms) what this DB is used for as I think it will assist.
The database in question is interesting in the sense that its a financial number storing database whose values are rebuilt (ie lots of calculations) once every month. For the rest of that time, it is queried via numerous sprocs to provide fiscal information for reports ie virtually no other insert or update action occurs (well, very occasionally a little bit of corrective action is done manually but that's rare & very focused).
After rebuilding, yes, I noticed we have a very big transaction log backup (first thing the next morning) but for the rest of the time the trans logs are quite small TBH...
After work hours very little happens to the database TTBOMK. All queries occur during working hours...
So, the RPO would be same day so that business can get its reports. And RPO would be the nearest full backup that is available. We keep 4 weeks backlog before the the least recent backup is removed...
Not sure why the DB log file got so big (as I say I inherited this one), but I'm leaning in the direction of shrinking the log file and then monitoring for growth but let me investigate the pre-existing log backup with copy-only option if I can find where these options are located!
Based on what you have described - i.e., sizer of the transaction log backups correspond to the activity in the database, the transaction log backups are set up correctly. Had that not been the case, and if it was copy-only all log backups would be big.
I think that it is the rebuilding calculations at the end of the month that requires large amount of log space. You can shrink the log file (be sure to shrink only the log file. DO NOT shrink the data file), and if my conjecture is correct, it will grow back again when you do the rebuilding calculations at the end of the month.
To add to what @JamesK has stated - the command to shrink the log file is DBCC SHRINKFILE. The problem you are going to have is that the log file will grow again when you run that monthly process.
Shrinking the file - having it grow again next month - to just shrink it again is a waste of resources. If the log needs to be that large to support the workload - then that is how large it should be set.
A large log file won't cause any issues - but a log file that is too small and needs to grow will cause issues. It takes time to grow the log file and processes will wait for that growth - making those processes take longer to complete.
You should also check the autogrowth setting for the transaction log. If it is not set to a fixed size then you would want to shrink it to as small as you can and manually grow it back out using a fixed size. I don't think that is the case here since you only have 120 VLF's - but it is something to validate.
Just before the monthly updates, I'd switch the database to the SIMPLE Recovery Model occur, take a final FULL backup, and shrink the log file to as close to zero as possible.
After they've had their way with the monthly updates, if the logfile had grown to anything bigger than 50 to 100 MB, I'd shrink it back down to new zero again, put the DB back in FULL recovery, do a FULL backup to restart the log chain, and continue as before.
Jeff, sorry I saw a made a bad typo. This database gets a full backup weekly not monthly as previously indicated. Apologies for the error in communication. However, I don't think that will change your advice...