April 29, 2015 at 12:21 pm
Our company was just acquired by another. They DBA team there added some jobs to our servers and one was a DBCC Shrinkdatabase command followed by a DBCC Reindex command. These are SQL Server VM's. I know in the past that running of the DBCC ShrinkDatabase constantly was a bad thing. Does this still apply to VM's? Should this command be run on a scheduled job once a week? This is coming from the Database Manager at our new company.
Thanks
Steve
April 29, 2015 at 12:41 pm
At least they rebuild indexes after completing the shrink database. Actually, I don't agree. As DBAs we should be monitoring our databases and ensuring that they have adequate space for data growth over a 3 to 6 month period. During this time we can add additional space if it is determined more is needed to cover unexpected growth. We should not be dependent on the auto growth feature of SQL Server. That should be for extenuating circumstances where more data was inserted than anticipated over the 3 to 6 month period.
April 29, 2015 at 12:42 pm
Well, I guess it makes sense to rebuild all the indexes. After all, the shrink fragments everything pretty well.
I don't work with VMs, but I don't see why they would want this in place. Do they have any rationale behind it?
April 29, 2015 at 12:56 pm
Are they actually doing anything that causes the databases size to fluctuate enough on a regular basis that this is even worth doing?
April 29, 2015 at 1:09 pm
No rational I can see. Its actually part of a maintenance plan they push out to all the servers. I am trying to get more information on why they do it.
April 29, 2015 at 1:20 pm
Steve Vassallo (4/29/2015)
No rational I can see. Its actually part of a maintenance plan they push out to all the servers. I am trying to get more information on why they do it.
As in an actual SQL Server maintenance plan? The one that uses SSIS to do brute-force attacks maintenance of everything?
April 29, 2015 at 1:40 pm
Yes. Its the Shrink Database task under the Maintenance plan.
April 29, 2015 at 1:49 pm
Two problems.
Shrink Database should not be scheduled. Full stop.
The index rebuild in the maint plan is a brute force rebuild everything that often isn't required.
Recommend:
Remove the Shrink DB. Replace the index maintenance with Ola's tool. https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2015 at 1:52 pm
Gila I agree. Unfortunately at this point I am just a cog. Where I was orginally we had many hats and one of mine was SQL DBA. With the new company, they are moving me to another silo and all I can do is watch and cringe. Once I get any more information on why they run the ShrinkDB weekly I will post here.. Should prove enlightening.
April 29, 2015 at 1:55 pm
ZZartin, no they are not doing anything for the size to fluctuate or have they even asked or monitored the current machines they are placing these maintenance plans on.
April 29, 2015 at 2:02 pm
Personally, I don't like Maintenance Plans. The brute-force approach to everything just isn't necessary. Also, there's no trapping of errors at all. If, for example, you have a log backup and it encounters a new database that hasn't had a full backup run, the job fails. So what about the log backups that come after the one that failed? They don't get run. For me, that's a problem.
When I first became a DBA, one of my priorities was to get rid of the maintenance plans. I'm doing everything in T-SQL now. For the backups, errors are trapped, things run properly and the job emails me whenever problems arise, but the backups are all taken.
April 29, 2015 at 2:15 pm
Another case of the ignorance of Central Planning. Details are important and they always get lost by the time they make it to a central head quarters. Authority and responsibility should be distributed to each level of a system so decisions can be made where they most make sense.
I had a case of a customer with central planning do full backups of my large read only simple mode database four times per day.
April 29, 2015 at 3:06 pm
Steve Vassallo (4/29/2015)
Our company was just acquired by another. They DBA team there added some jobs to our servers and one was a DBCC Shrinkdatabase command followed by a DBCC Reindex command. These are SQL Server VM's. I know in the past that running of the DBCC ShrinkDatabase constantly was a bad thing. Does this still apply to VM's? Should this command be run on a scheduled job once a week? This is coming from the Database Manager at our new company.Thanks
Steve
Totally silly. Nothing redeeming.
I would be happy to come in and give this new company a few slaps around the head and shoulders. I would even consider doing it for free just because they are so egregious!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 29, 2015 at 3:43 pm
Steve Vassallo (4/29/2015)
Our company was just acquired by another. They DBA team there added some jobs to our servers and one was a DBCC Shrinkdatabase command followed by a DBCC Reindex command. These are SQL Server VM's. I know in the past that running of the DBCC ShrinkDatabase constantly was a bad thing. Does this still apply to VM's? Should this command be run on a scheduled job once a week? This is coming from the Database Manager at our new company.Thanks
Steve
Granted there's probably not a thing you can do about it, but I'd still suggest making the attempt. They're harming their servers and someone ought to at least go on record as pointing that out. You find out plenty about why this is a poor practice through a single Boogle search. Just point it out. You'll at least feel better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2015 at 3:49 pm
Steve Vassallo (4/29/2015)
Our company was just acquired by another. They DBA team there added some jobs to our servers and one was a DBCC Shrinkdatabase command followed by a DBCC Reindex command. These are SQL Server VM's. I know in the past that running of the DBCC ShrinkDatabase constantly was a bad thing. Does this still apply to VM's? Should this command be run on a scheduled job once a week? This is coming from the Database Manager at our new company.Thanks
Steve
It's been said nicely.
I'll be blunt that these maintenance practices are piss poor. Whether physical or virtual, you should not be performing these tasks. I would have this crap ripped off the server and the DBA that put it there put under probation. They need to be taught and they need to have their eyes opened wide.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply