By: Aaron Bertrand | Last Updated: 2012-09-12 | Comments (2) | Database Administration
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.
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)', 'NVARCHAR(128)'), @object = @eventdata.value('(/EVENT_INSTANCE/ObjectName)', '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;
- 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.
- 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.
- 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.
- 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:
Last Updated: 2012-09-12
About the author
View all my tips