Build a quota system for SQL Server user defined tables


By:   |   Updated: 2012-09-12   |   Comments (2)   |   Related: More > Database Administration

Problem

In some scenarios you have multiple users that can build their own tables within your SQL Server database(s). It can be quite difficult to manage the objects that they build, and keep their object sizes under a certain threshold. There are plenty of facilities for reducing resource usage (mainly CPU and memory), as well as limiting concurrency. But other than overall database size (e.g. the 10GB limit in SQL Server Express Edition) and the bounds of the hard drive itself, there isn't much you can do to prevent a user from exceeding a certain threshold on disk space (or from crowding out other users).   Check out this tip to learn how build a quota for user defined tables in SQL Server.

Solution

One idea I've had to help with this scenario is to implement an actual quota system. This can be done easily if you have the ability to lay out individual filegroups for each user - those data files can be capped at a maximum size, and you can enforce that a certain user can only create tables and other objects on their own filegroup. So let's say we wanted to add a requirement that UserX should be limited to 100 MB of space. We could create the following filegroup, with a single file that is capped at 100 MB:

ALTER DATABASE database_name ADD FILEGROUP UserX;
GO
ALTER DATABASE database_name ADD FILE 
(
  NAME = UserX, 
  FILENAME = 'C:\...wherever...\database_name_UserX.mdf',
  MAXSIZE = 100MB
) TO FILEGROUP UserX;
GO

Now when a table is created, we need to know if the user creating the table should be restricted to this filegroup. We can create a simple table that stores the user to filegroup mapping:

CREATE TABLE dbo.UserFileGroups
(
  Username      SYSNAME PRIMARY KEY,
  FileGroupName SYSNAME
);
INSERT dbo.UserFileGroups(UserName, FileGroupName)
  SELECT N'UserX', N'UserX';

(You could of course map multiple users to a single filegroup, or, if easier, indicate which users do *not* have to be constrained to this filegroup.)

Now, to ensure that tables by UserX are only created on the UserX filegroup, we can solicit the help of a DDL trigger:

CREATE TRIGGER MapFileGroup
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
  DECLARE 
    @username  NVARCHAR(128),
    @filegroup NVARCHAR(128),
    @eventdata XML,
    @schema    NVARCHAR(128),
    @object    NVARCHAR(128);
    
  SELECT
    @username  = USER_NAME(), -- depending on auth mode you may want SUSER_SNAME()
    @eventdata = EVENTDATA();
    
  SELECT
    @schema = @eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)'),
    @object = @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)');
    
  SELECT @filegroup = FileGroupName 
    FROM dbo.UserFileGroups 
    WHERE UserName = @username;
  
  IF @filegroup IS NOT NULL -- only check when it's a user that should be limited
  BEGIN
    -- need to ensure it's on the right filegroup
    
    IF NOT EXISTS
    (
      SELECT 1 
       FROM sys.indexes AS i 
       INNER JOIN sys.data_spaces AS d
         ON i.data_space_id = d.data_space_id
       WHERE d.name = @filegroup
       AND i.index_id IN (0,1)
       AND i.[object_id] = OBJECT_ID(QUOTENAME(@schema) + '.' + QUOTENAME(@object))
    )
    BEGIN
      RAISERROR('Your objects must be on the %s filegroup.', 11, 1, @filegroup);
      ROLLBACK TRANSACTION;
    END
  END
END
GO

This will roll back the entire batch for any table that is created by UserX that is not on the UserX filegroup. So let's say UserX tried to create the following table:

CREATE TABLE dbo.foo(bar INT);
CREATE UNIQUE CLUSTERED INDEX x ON dbo.foo(bar);

The error messages will be:

Msg 50000, Level 11, State 1, Procedure MapFileGroup
Your objects must be on the UserX filegroup.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

The following, however, will work:

CREATE TABLE dbo.foo(bar INT) ON [UserX];
CREATE UNIQUE CLUSTERED INDEX x ON dbo.foo(bar);

You could even add logic to show users how much of their quota they're using, by looking at their filegroup properties and calculating the used space in each filegroup based on sys.dm_db_partition_stats:

;WITH quota(FileGroupName, UsedSize) AS 
(
  SELECT d.name, SUM(p.reserved_page_count)
    FROM sys.dm_db_partition_stats AS p
    INNER JOIN sys.indexes AS i
    ON p.index_id = i.index_id
    AND p.object_id = i.object_id
    INNER JOIN sys.data_spaces AS d
    ON i.data_space_id = d.data_space_id
    GROUP BY d.name
),
fgs(FileGroupName, MaxSize)  AS 
(
  SELECT d.name, SUM(f.max_size)
    FROM sys.data_spaces AS d
    INNER JOIN sys.database_files AS f
    ON d.data_space_id = f.data_space_id
    WHERE f.type_desc = N'ROWS'
    AND f.max_size > 0
 GROUP BY d.name
)
SELECT ufg.UserName, ufg.FileGroupName, 
  UsedSizeKB = q.UsedSize * 8, 
  MaxSizeKB = fgs.MaxSize * 8, 
  Percentage = CONVERT(DECIMAL(5,2), q.UsedSize*100.0/fgs.MaxSize)
FROM quota AS q
INNER JOIN dbo.UserFileGroups AS ufg
ON ufg.FileGroupName = q.FileGroupName
INNER JOIN fgs ON q.FileGroupName = fgs.FileGroupName;

Caveats

  1. There are of course ways that users can bypass the trigger. They can disable or alter it if they have sufficient privileges, or they can log in as someone else, or they can even reverse engineer it if you decide to encrypt it. They could also hack into the UserFileGroups table. Overall security concerns are out of scope for this tip, but you should be aware of them.
     
  2. This solution doesn't currently handle re-creating a clustered index; for example, the following will allow the table to be moved back to the primary filegroup:
    CREATE UNIQUE CLUSTERED INDEX x ON dbo.foo(bar) 
      WITH (DROP_EXISTING = ON) ON [primary];
    I'll leave handling that case as an exercise to the reader.
     
  3. A popular misconception is that a DDL Trigger can actually prevent a table from being created (the documentation suggests this too). It is actually implemented as an *after* trigger, so the DDL is allowed to complete, but you have an opportunity to roll it back. In the case of CREATE TABLE this isn't much of a concern, but keep in mind this trigger will also fire for SELECT INTO. Since SELECT INTO does not provide the opportunity to specify a target filegroup, the population of the table would finish, then get rolled back, leaving the user with no way to create such a table.
Next Steps


Last Updated: 2012-09-12


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, September 12, 2012 - 1:33:59 PM - Aaron Bertrand Back To Top

Ralf, no solution is going to be perfect. The problem with the other end that you propose is that if they're all on the same filegroup, one person can effectively end the ability for all other users to insert data until you correct the situation. Separate filegroups aren't really that painful to manage - for anything you need to automate you can generate scripts for 10 filegroups the same way you can for 500.


Wednesday, September 12, 2012 - 10:17:30 AM - Ralf Dietrich Back To Top

Even this solution is really interesthing It can be a big mess, having a lot of file groups.
I suggest having the information ready about object owners (stored in system tables) and calculate the size of this objects.

to make organsisation easy you can have a copmbination of both solutions. Store ALL user created objects on a specific file group.

Having this information in an admin table ready you can store you can have a data driven subscription (from SSRS) and | or log on trigger ...



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools