Build a quota system for SQL Server user defined tables

By: Aaron Bertrand   |   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

• On a test system, play with filegroups, files and DDL triggers that can help guide how to implement this type of quota system in your production environment.
• Review the following tips and other resources:
  • SQL Server DDL Triggers to Track All Database Changes
  • Using the EventData() Function with DDL triggers in SQL Server 2005
  • Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005
  • Designing DDL Triggers
  • Implementing DDL Triggers

About the author

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 also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips