Post reply

Prevent shrink for specific data files

  • February 11, 2020 at 9:47 am

    #3724633

    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.

  • February 11, 2020 at 10:40 am

    #3724646

    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

  • February 11, 2020 at 10:44 am

    #3724647

    have you considered just reducing permissions?

    MVDBA

  • February 11, 2020 at 11:02 am

    #3724652

    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.

  • February 11, 2020 at 11:12 am

    #3724655

    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

  • February 11, 2020 at 11:18 am

    #3724656

    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.

  • February 11, 2020 at 11:26 am

    #3724657

    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

  • February 11, 2020 at 11:29 am

    #3724658

    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.

  • February 11, 2020 at 11:40 am

    #3724659

    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

  • February 11, 2020 at 11:48 am

    #3724661

    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.

  • February 11, 2020 at 11:51 am

    #3724662

    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

  • February 11, 2020 at 12:14 pm

    #3724672

    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

  • February 14, 2020 at 6:57 pm

    This was removed by the editor as SPAM

  • February 17, 2020 at 11:56 pm

    #3726615

    Currently, I am working on a server on a client's network. Client has the policy of nightly dbcc checkdb and log back up, and weekly a database shrink on all databases in their network. Whatever you might think of those policies, those are the circumstances of my job. The time of day(night) the client runs those commands is exactly the time when we want to run our software. Our software run consists of emptying out the entire database, and reloading data, and lots and lots of selecting and inserting from/into tables. This concerns many millions of rows, and a couple of 100's gb. Naturally, our job and clients policies clash, and we get regular blocks. When I inspect the run and find a block, I kill the shrink or dbcc command to let our run finish. So far client has not objected. So, I very much see the merit in asking such a question. I too, wanted to block interfering commands. I wrote a little c# program to do the job (currently just a poc). I'll try to post some code next week (on mobile now), but the gist is simple: set up an xEvent on dbcc commands (I forgot which was the one, but if you search on dbcc there are only 3 options, all for dbcc check*, for a shrink you may need another xEvent). Then you can write a c# application that captures the event and its session_id and Kill it.

  • February 18, 2020 at 8:16 am

    #3726660

    if a database belongs to your software you can put as a support condition that your databases are not shrink - it is up to the client to add an exclusion to their processes so that your database is excluded from such BAD policy.

     

Viewing 15 posts - 1 through 15 (of 16 total)

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