July 17, 2025 at 10:03 am
On one of my database servers, I have a maintenance plan that includes the following tasks:
1. Check Database Integrity
2. Back Up Database Task
3. Recognize Index (includes Fragmentation > 15% & Page Count > 1000)
4. Update Statistics
5. Clean Up History (older than 4 weeks)
6. Maintenance Cleanup Task (1 week for differential & 2 weeks for full)
I ran out of disk space and couldn't extend the drive and extended the drive.
I was investigating and found out that whatever I need to do to keep the databases as nice and small as possible, I am already doing, and that I should not try to shrink the databases or anything like that.
1. Do you guys agree?
2. When I extended the drive, the databases took an addition of 11 GB right away, before anyone could use the application and add more date to them. What do you think of this phenomenon?
July 17, 2025 at 10:11 am
1.
You can probably disable step 3 "Reorganize index" in this modern world, saving a lot of io/time for minimal gains
Step 4 "Update statistics" is usually sufficient to get the best query plans.
2.
Perhaps some rollback writing to the transactionlog
July 22, 2025 at 12:00 pm
On one of my database servers, I have a maintenance plan that includes the following tasks:
1. Check Database Integrity 2. Back Up Database Task 3. Recognize Index (includes Fragmentation > 15% & Page Count > 1000) 4. Update Statistics 5. Clean Up History (older than 4 weeks) 6. Maintenance Cleanup Task (1 week for differential & 2 weeks for full)
I ran out of disk space and couldn't extend the drive and extended the drive.
I was investigating and found out that whatever I need to do to keep the databases as nice and stall as possible, I am already doing, and that I should not try to shrink the databases or anything like that.
1. Do you guys agree? 2. When I extended the drive, the databases took an addition of 11 GB right away, before anyone could use the application and add more date to them. What do you think of this phenomenon?
remove all of this and download Ola Hallengren's maintenance script\util
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2025 at 2:28 pm
I half-second Perry Whittle's suggestion. I personally don't like maintenance plans as they don't scale well. You spin up a new instance, you have to re-deploy the maintenance plan. Change in the maintenance plan (new backup tool provider for example) means you need to manually update each and every maintenance plan.
Scripts are the way to go. Now, nothing against Ola Hallengren's script, but I prefer something I can support and that is more targeted to my environment. I also prefer small, specific scripts over a single monolith. I like my stored procedures to handle a small, specific task as it makes debugging them a LOT easier. I have inherited a lot of scripts where that isn't the case, but for the most part, anything I write I try to make short and to the point. For example, I have a stored procedure for full backups, differential backup, log backup, index rebuild, index reorg, statistics update, checkdb, and a master script that calls all of those based on parameters. Then I put it into SQL jobs. So the backup job calls the master script and based on the specific job parameters, it'll do a full backup only, a differential backup only, a log backup only, or any of those with extra steps mixed in (index maintenance, statistics updates, checkdb, etc.). IF any of the scripts has a bug, it is easy to find and easy to fix. For example, if someone decided to create an index with page and row locking disabled, you can't reorganize it - it must be rebuilt. If I have one monster script to try to debug, I may need to jump to line 1867 to fix that bug. With my approach, it's line 15 on the index reorg script that handles it. Offhand, I don't know if Ola's scripts handle that (I suspect they do), but I do know they handle a bunch of different backup solutions (RedGate, Idera, native, and more) and I don't run all of those - I just use 1. So for me, I don't need the extra "fluff" in the stored procedure. Instead, I would prefer my backup scripts are targeted to my backup solution. If I upgrade my backup tool, I can test it against one of my systems, fix any bugs or add any additional options introduced in the new version, and then quickly and easily push it out across all systems. If I change my backup tool, I update my 3 backup stored procedures and I'm good to go.
Now, do I need to do the index maintenance, probably not, but I have downtime windows where nobody uses the system, so might as well take advantage of that.
Also, might not hurt to get a database monitoring tool so you can watch for low disk and low database free space warnings and take action before it gets full.
As for database shrinking, it is frowned upon, but there are use cases where it makes sense. Have you recently archived/offloaded a bunch of the data from the database? If so, shrink the database file (mdf). Did you or someone run a query that changed a lot of data that was a one-off query that you run very very rarely? If so, shrinking the log file may not be a bad idea. BUT if both grew from normal use of the system, shrinking either is a very short-term solution and the problem will creep up again. Plus database grow operations are blocking and slow. So if you have a lot of transactions hitting the system, users may notice when the grow occurs as their system may pause. ALSO, is your auto-grow set to a percentage or a MB value? If it is a percentage, change that.
The other thing you can look at is if the recovery model makes sense for the system use. I suspect it is in FULL recovery, but does it need to be? Can your company live with SIMPLE recovery (full backup restores only, no point in time restores, just whenever the last full was completed)?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 22, 2025 at 3:25 pm
only time you'll want to shrink a database is refreshing into a lower level env where space is limited but even that's frowned upon.
Prod data shouldn't be pushed to lower level envs.
If you delete data from your prod db it still makes no sense to shrink it, it will be required again at some point likely!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2025 at 3:30 pm
Now, nothing against Ola Hallengren's script, but I prefer something I can support and that is more targeted to my environment. I also prefer small, specific scripts over a single monolith. I like my stored procedures to handle a small, specific task as it makes debugging them a LOT easier.
Ola's scripts are extremely popular and easily supported, i've never had any issue even when customising them for particular envs
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 12, 2025 at 3:01 pm
@jo-2 Pattyn, I think Recognize Index has nothing to do with Update Statistics. The two operations serve different purposes and are pretty much independent of each other.
@perry Whittle and @Mr. Brian Gale, I have a few servers, each has one instance. The databases are not that huge and not that many. So, maintenance plan are easier to me.
Regarding the server of this tread, I eventually manged to make the consultant of the application fix the issue. They deleted a great deal of transaction logs and limited the logs autogrowth to 20 GB. As a result, around 200 GB has been reclaimed on the disk.
But in Reports > Disk Usage, I see a great of deal of unallocated and unused disk spaces in more than one data files and transaction log. So, I am thinking of removing Recognize Index from the Mon-Sat subplan and adding Rebuild Index to the Sunday subplan. Currently, the Sunday only does full backup at 22:00. This way I will get rid of that fragmentation and keep the databases accessible except for Sunday late evening in my SQL standard edition.
What do you guys think?
August 13, 2025 at 10:35 pm
Mr. Brian Gale wrote:Now, nothing against Ola Hallengren's script, but I prefer something I can support and that is more targeted to my environment. I also prefer small, specific scripts over a single monolith. I like my stored procedures to handle a small, specific task as it makes debugging them a LOT easier.
Ola's scripts are extremely popular and easily supported, i've never had any issue even when customising them for particular envs
As I had said, it was nothing against those scripts - I just don't think I can support those. The maintenance installation script that creates all of the stored procedures is over 9000 lines. I am not going to spend the hours reviewing that and customizing it for my environment as I am likely to introduce error. But that's just me. You may prefer using them, and I know a lot of people do, it's just not for me.
@nonsequitur - if the index rebuilds/reorgs are helping performance or disk/database usage, I say keep doing them. If it isn't helping and you are finding a lot of page splits, you may want to revisit your index setup (fill factor being a likely culprit) or cut back on the maintenance. If you notice no difference, then no harm no foul, eh? Just have to watch with your reorg of indexes that you don't try to reorganize an index that can't be reorganized due to some setting on the index. Also, if you do ETL on a table that results in a truncate/reload, then reorg/rebuild of that index may not be needed or may be more beneficial as part of your ETL.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 14, 2025 at 5:18 am
"The maintenance installation script that creates all of the stored procedures is over 9000 lines. I am not going to spend the hours reviewing that "
I dont review the 9000 lines either aside from the first time I came across it, and just at a high level. I simply refer to the documentation. New abilities to the procedures are added over time which is a nice plus.
----------------------------------------------------
August 14, 2025 at 5:21 pm
"The maintenance installation script that creates all of the stored procedures is over 9000 lines. I am not going to spend the hours reviewing that "
I dont review the 9000 lines either aside from the first time I came across it, and just at a high level. I simply refer to the documentation. New abilities to the procedures are added over time which is a nice plus.
Just to add to this - there are almost 250 open issues with Ola's scripts on github... now I know my scripts are not in github, but mine work in my specific environment. Won't work for everyone but I don't work for everyone - I am a DBA at my company (well, technically not a DBA anymore, but still have some DBA duties like managing backups). Some of the issues are feature requests, but some are bugs.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 16, 2025 at 3:59 am
Although Ola's code has been branded as the "Gold Standard" by good people like Paul Randal, I won't use his code for index maintenance because it measures the wrong things to determine if index maintenance should be done at all. I also would NOT "Generally"" use REORGANIZE except to rebuild LOBs because it is NOT the quiet little kitty that people make it out to be. It can and will cause "log file explosions" depending on the nature of a given index and it's fragmentation pattern. For example, you should NOT run REORGANIZE on Random GUID or other evenly distributed keys because it remove free space when the index needs it the most and that cause page splits to be worse and perpetuates fragmentation instead of preventing it.
And, yeah... I'm also the kind of person that reviews code before using it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply