Prevent shrink for specific data files

  • Atif

    Hall of Fame

    Points: 3578

    How can we prevent shrink operation for specific data files? If any one executes shrink command for that file then it should be denied and error message may be generated.

    Thanks

    DBDigger Microsoft Data Platform Consultancy.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21453

    I'm not sure if this is possible

    you might be able to use a database trigger, but I've not seen it done before

    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-trigger-transact-sql?view=sql-server-ver15

    not even sure that altering a file size is a triggerable event

    MVDBA

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21453

    have you considered just reducing permissions?

    MVDBA

  • Atif

    Hall of Fame

    Points: 3578

    Yes you are right, shrink is not triggerable. Permissions for DBA group are full and cannot be reduced for this purpose only.

    We will be looking to write and use a wrapper SP for file shrink and put the prevention logic in it.

    Thanks for sharing your thoughts on it.

    DBDigger Microsoft Data Platform Consultancy.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21453

    if you use a SP for shrinking ,  but your DBA group have sysadmin then they can still run DBCC shrinkfile.

    maybe education is the way forward

    MVDBA

  • Atif

    Hall of Fame

    Points: 3578

    Purpose of wrapper SP is to first check if given file is restricted for shrink operations. If file is restricted then DBCC command will not be executed but a message will be displayed. We already have list of restricted files in a lookup table.

    DBDigger Microsoft Data Platform Consultancy.

  • anthony.green

    SSC Guru

    Points: 112351

    But if the user is a "SysAdmin" or a "db_owner" they can do what they want, when they want, where they want and you cannot stop them for love nor money with any wrapper code.

    Education and permission restriction is what your going to have to do

  • Atif

    Hall of Fame

    Points: 3578

    DBCC shrink command will be executed as dynamic SQL statement in the wrapper SP. So if given file is restricted then dynamic SQL of shrink will not be executed in the flow. It is not related to permissions but conditions based decision in SP.

    DBDigger Microsoft Data Platform Consultancy.

  • anthony.green

    SSC Guru

    Points: 112351

    But you need SYSADMIN or DB_OWNER to be able to execute the shrink.

    You can't stop someone executing a shrink outside the wrapper code.

    So while you might block them in the code, they will just spawn a new query window and do a DBCC SHRINKFILE anyway outside of your wrapper.

    Again education is the best policy

  • Atif

    Hall of Fame

    Points: 3578

    Yes education in this case is best policy. As a policy we will educate to use wrapper SP for shrink. It will help to avoid worry about looking list of restricted files each time.

    DBDigger Microsoft Data Platform Consultancy.

  • anthony.green

    SSC Guru

    Points: 112351

    OK if that's how you want to play it that's fine, just remember you will get the rouge people who negate your wrapper code, so yeah it will happen, not an IF but a WHEN.

    OK now what you going to do about the fragmentation that shrinking causes?  You going to write a wrapper code to fix that too?

    What are you actually fixing in shrinking?  What you think you will fix 99% of the time the after remediation will just reclaim that space anyway

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21453

    Anthony is 100% correct - if you are sysadmin then you can just ignore the proc and go and do anything you want.

    the proc is a waste of time unless you are giving it to lower permission users (and using an execute as statement, or scheduling it in a queue)

    maybe a new question … who and why ? who is shrinking the files? why are they doing it ? if it's t-log then every index rebuild will put it back up to full size.. if it's tempdb then you need to start looking at your code. if its a data file size then my guess will be bulk data imports and then drop tables

    MVDBA

  • This was removed by the editor as SPAM

Viewing 13 posts - 1 through 13 (of 13 total)

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