DBCC Shrinkdatabase run every week?

  • 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

  • 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.

  • 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?

  • Are they actually doing anything that causes the databases size to fluctuate enough on a regular basis that this is even worth doing?

  • 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.

  • 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?

  • Yes. Its the Shrink Database task under the Maintenance plan.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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.

  • 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.

  • 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.

  • 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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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 34 total)

You must be logged in to reply to this topic. Login to reply