January 12, 2024 at 12:57 pm
Hi everyone,
I have inherited a 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
January 12, 2024 at 1:23 pm
2. You need to keep the log file size appropriate for your data system.
( i.e. not needing a auto-grow to accomodate space needs between 2 log backup events )
3. Only shrink if it is actually needed ! ( i.e. if the growth has occurred due to exceptional maintenance etc.)
4. Keep in mind, a transaction log file works different than regular database files
5. HowTo ? "Manage the size of the transaction 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
January 12, 2024 at 1:23 pm
What is the recovery model of the DB?
What is the log wait reuse description for the DB from sys.databases?
If recovery model is full, are their log backups happening?
January 12, 2024 at 2:59 pm
Hi everyone, thanks for the speedy response. Take it easy oneme though, I'm just a developer not a db admin! I will try and answer your queries:
1. Recovery model is: full
2. Transactional log backups are occurring from Mon-Fri incl.(during working hrs)
3. I had a look at the "log_reuse_wait_desc" field in sys.databases for the DB in question and unfortunately it says "Nothing"
I hope this helps. J
January 12, 2024 at 3:17 pm
OK, well the first thing which is concerning is T-Log backups Mon-Fri in working hours.
What is the RTO / RPO here?
What happens to the DB outside of working hours?
You may want to ask the business what is the impact here is say something from 20:00 is lost as you had to rollback the database to 16:00 due to that being the only recovery time you have.
Would recommend reading the links Johan has provided along with reading the following e-book on how to best manage your T-Logs
Secondly as the reuse desc is nothing, that is a relatively good sign that will allow you to shrink the log fairly easily.
Now I would only advocate that the log is shrunk once and you then monitor the log size periodically to check for growth.
Growth is natural obviously, be excessive growth isn't.
If the log is growing to the sizes again then you have a run away process which you need to figure out what it is, or it could be a lot of activity between 17:00 and 09:00 that is causing the growth and as you have no log backups during the off hours that is what is causing the issue.
January 12, 2024 at 4:02 pm
Hi, thanks for the advice. The database in question is interesting in the sense that its a financial number storing database whose values are rebuilt (ie lots of calculations & inserts/updates) 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 thats 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. I do this already with the backup files so I can add the log file to the monitoring process....if I find that its growing excessively fast I will have to dive into it...
Unless of course you have any other suggestions for investigation...
J.
January 12, 2024 at 4:49 pm
Personally I'd just force-shrink the log, even if it "breaks the log chain". You would never want to try to apply a log that large any way.
(1) Take a full backup. (A differential backup would also work, but you probably don't want to mess with that.)
(2) Put the db into SIMPLE model:
ALTER DATABASE [DbFoo] SET RECOVERY SIMPLE;
(3) Truncate the log file:
USE [DbF00];
CHECKPOINT;
DBCC SHRINKFILE(2, 16384);
(4) Put the db back into FULL:
ALTER DATABASE [DbFoo] SET RECOVERY FULL;
(5) Take a full backup -- you must do this, otherwise your log backups will fail.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 12, 2024 at 4:51 pm
I guess we should verify first that nothing else is preventing the log from being cleared. Run this statement and check the result: the best result to see is "Nothing":
SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DbFoo'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 12, 2024 at 4:58 pm
Hi, thanks for the advice. The database in question is interesting in the sense that its a financial number storing database whose values are rebuilt (ie lots of calculations & inserts/updates) every month.
Is the entire database rebuilt every month? and barring corrective updates, does the data remain the same until the next rebuild?
If so, does it need to be in full recovery mode?
If it was in simple recovery mode and someone truncated an important table, what would you lose if you restored a full backup from the previous night? or even the previous weekend?
January 12, 2024 at 5:05 pm
Thank you most kindly Scott for the approach. Let me investigate this (to get my head around it) and I will post if I get stuck. Many, many thanks to all, J.
January 12, 2024 at 6:47 pm
Hope it helps. Btw, the first full backup (step (1)) isn't technically required, so you can skip it. The last full backup (step (5)), as noted, is absolutely required.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 16, 2024 at 4:59 pm
Sorry folks, I made a slight error in communication I think. The database in question gets a full backup every week not every month as incorrectly implied. When I say rebuilt every month, the values in the database tables are re-calculated every month (table structure remains the same, field names/types etc...). Process takes a few hours.
If we lost data (like accidental truncation of a table) then I would run a restore from the nearest backup.
I hope I have made that clear.
J.
January 19, 2024 at 9:38 am
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply