Limit Size of Tables
-
I'm looking for a way to put a cap on the size of newly created tables or adding data to existing tables. I do not want the developers to be able to create/grow a table larger than xGB. A trigger at the database level is the first thing that comes to mind but I'm not sure the best way to go about that and if there are any major concerns with doing that. It's also important that any table identified as being able to be larger than the cap is able to be written to. Does anyone have some pointers to get me started?
- RonMexico - Monday, March 25, 2019 6:52 AM
Why?
You can set the maximum size of a data file on your database. This would prevent the database from growing above the size you have entered in database properties\file. -
I want to be able to control it at the table level. They should be able to still create/update multiple tables that add up to xGB but not do so in a fashion that one table grows beyond that point. Controlling with the data files would lead to a lot of maintenance to continually monitor the file has xGB available.
- RonMexico - Monday, March 25, 2019 8:16 AM
I guess the real question is what is the use case behind limiting the size of specific tables.
-
The reason is that some create a table and unintentionally consume a large amount of space. Being able to prevent that from happening would force them to rethink their strategy or come to me for an exception.
-
Stop random users having the ability to create tables in production, developers or otherwise! Put in place change management control, changes should be in Development environment and then approved for production! Unintentional sounds like testing!
...
- RonMexico - Monday, March 25, 2019 8:16 AM
I assume this is a development environment - and if so, then maintenance does not fall to you. Push it back to the developers - when one of them uses up all available space in the database that developer then needs to clean it up. If another developer is trying to work and there is no space available, then you can identify which developer has consumed all of the available space and let them figure it out. Or - at least you can identify the specific table(s)...
Preventing developers from growing a table above a certain size isn't feasible...especially when you consider that a narrow table could consume hundreds of billions of rows before reaching some predefined xGB size whereas a wide table could consume that size with only a couple million rows. I would say the narrow row with 200+ billion rows is much more of a problem than the wide table with 200 million rows - regardless of how much space is used.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thanks for all of the advice. It sounds like I need to change the way our processes are currently handled.
- Jeffrey Williams 3188 - Monday, March 25, 2019 12:42 PM
I have to ask... how will you know who the developer is that populated a table that didn't overrun the disk space but is large enough where other people can't create other tables of any decent size?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I was thinking of some kind of exceptions list that would allow certain tables to grow over the limit. However, my original post looking for a way to limit the tables isn't possible so neither would this.
-
what my friend you need is Policy Based Management - go crazy (test test test and test)
you can create readonly filegroup and move table to RO_filegroup.. just an idea..
- Jeff Moden - Monday, March 25, 2019 7:14 PM
If the process did not consume all available space - but used up enough that other developers cannot complete their tasks, then you can identify any tables recently created and/or large tables (e.g. disk usage by table report). The developers can then follow up with the other team members to identify the developer working on that module so they can correct the problem.
There is the possibility that this new work is required - and the size of the resulting table is correct. The only way to identify that is for the developers to review the process - and determine the requirements for that project. At that point, they can then provide that information to the DBA - and other options can be reviewed. For example, maybe implementing row/page compression will satisfy the requirements - or a different approach can be taken...
With that said - arbitrarily blocking table sizes because they are *too* big isn't going to play well with the developers and will just cause additional strife between the groups.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I agree that even if you could easily limit the size of tables, you shouldn't. And looking for things after the event won't necessarily help a thing and manually monitoring for or looking for the cause post mortem.
What people need is something that will cause an alert and fire up a job that will find out what's going on as it is happening.
The following article gives you more than a hint on how to do such a thing. Disclaimer... I've made such alerts before but I've not tested the methods in the article. A quick read says it follows the same things I did in the past.
https://www.mssqltips.com/sqlservertip/3128/monitor-sql-server-database-file-growth-with-wmi-alerts/">https://www.mssqltips.com/sqlservertip/3128/monitor-sql-server-database-file-growth-with-wmi-alerts/If you do things right, the code the job fires can even catch an execution plan for you but, if nothing else, it can help you identify which table is growing and it certainly provides and email alert to get your attention to a (no pun intended) growing problem.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks, Jeff. That's probably best anyway so that I can draw their attention to it as it happens since a lot of times they don't realize what the size will grow to while they are executing it.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply