April 19, 2022 at 8:38 pm
Homebrew01 - I have deleted the last "big" file but can try that if it does it again.
Micheal L John - no normal database activity. Nothing is purged unless its done manually and I am the only one with access to do that. Its been months since I did anything like that.
The database grows daily as do the backups. This reminded me that I do track this database size in a table. I have attached a screen shot of that table. With that said it appears my problem is related to the backup more so than related to the database. But I don't have the experience you guys do so please do hesitate to say I'm wrong.
When I set up the back up I used SSMS, right clicked on database, selected tasks, backup, in the pop up backup type is Full, backup to disk, Its been so long, I cant remember if I checked off append or overwrite on the media option page. I think I would have checked overwrite but I cant be 100% sure. Do you think it would be wise to start the backup job from scratch again? If so should I overwrite all existing backup sets OR backup to a new media set and erase all existing backups?
There is nothing I can see in the backup settings to overwrite at a certain size.
Like I have previously the back up is run nightly by a SQL job. It uses a stored procedure in the master data base and I have included the code.
Last week was the second time I noticed this in a period of 2 weeks. I do look at that folder regularly as I have to delete older files or it fills up the drives. So I would say that it is new.
I took a quick look at that code - and honestly, using the built-in maintenance plans would be much better than using that code. There are additional options for backup that you really need to learn - and implement. For example, the following options are very useful in the backup command: INIT, COMPRESS, MAXTRANSFERSIZE, BUFFERCOUNT.
Ola's utility does all of this for you - but at the expense of really understanding what is being done. This is the same issue with using SSMS maintenance plans and relying on the wizard to create the plan for you. Either way, you can implement something that generally works - but you most likely are going to end up performing more than is needed or necessary.
And - since Ola's utilities relies on the outdated concept of performing index reorgs at lower percentage of fragmentation - and rebuilds at higher, you are stuck with doing that process and possibly (probably) causing issues - or creating your own to rebuild appropriately and disabling that process from Ola's utility.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply