Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
I don't quite understand why you would want to do this at the DB level; maybe giving more insight of what you're doing would lead to a better design? Sounds like a design level question to me. So assuming you have a valid reason, I have to ask: what do you expect the app tier to do when the DB runs out of space? – Kilo Aug 23 '10 at 15:36

1 Answer

up vote 0 down vote

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.

  1. 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'
  2. 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

share|improve this answer
Is there a way to do the file size limitation trough management studio? Also how to limit the number of databases per user? (Say user a can't have more than 2)? Thanks – Tom Aug 23 '10 at 15:27
Yes. Right-click the database and select properties. On the 'select a page' frame select 'Files'. In the AutoGrowth column press the ellipses button for the file that you want to set the size limit on. There is an option to set a max size on the window that will launch. As far as databases that a user has access to this is a security concern. I would recommend reading the following before you proceed. An understanding of this might prove crucial to the project maintainability. msdn.microsoft.com/en-US/library/ms181127(v=SQL.105).aspx – doug_w Aug 23 '10 at 18:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.