Sql-server – Limit a database’s max disk size and limit user total database size usage

sql serversql-server-2008

Is there a way to do this in SQL Server 2008 express?

I need to limit the maximum allowed size of a specific database.

I also would like to limit the maximum allowed total size of databases for a specific user.

Is this possible and how, thank you.

Best Answer

The file is relatively simple compared to the user size restriction.

alter database test 
modify file
(
  name = 'test_datafile1'
  ,MaxSize = 5 GB
)

With the user restriction you may try one of the following based upon your database design.

Log the row owner and calculate the size of the row based on the datatype(s) and their corresponding storage requirements. Then multiply by the number of rows that the user 'owns'
query the size of the table that the user 'owns' to determine if they have exceeded the limit.

It is a little difficult to say which way would work best because if all user data is in the same table versus separate tables then the approach will be entirely different.

HTH

Related Solutions

Sql-server – Limit Concurrent Database Connections by SQL Server User Account

You could try Login Triggers: http://technet.microsoft.com/en-us/library/bb326598.aspx

TechNet suggests that:

You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login.

Their first code example limits a user to 3 connections (Quoting TechNet):

USE master;
GO

CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

Large database in SQL Server Express 2008

As an alternative, is it feasible to use a Developer copy for your development work?

Best Answer

Related Solutions

Sql-server – Limit Concurrent Database Connections by SQL Server User Account

Large database in SQL Server Express 2008

Related Topic