Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my SQL-Server instance, there are many databases. I want to restrict the memory size of each database (for example 8 GB). When I tried using Resource Governor, there are options like max_memory_percent and min_memory_percent and it is given in percentage value (values from 0 to 100). But I want to give the database size explicitly in GB and it should not go beyond that maximum limit.

Is there any way to restrict the database size or to get alerts if the database size exceeds the limit?

Can I implement this using Resource Governor?

ALTER RESOURCE POOL

share|improve this question

migrated from stackoverflow.com Mar 24 at 10:47

This question came from our site for professional and enthusiast programmers.

1  
The RG setting that controls memory only applies to query memory, not buffer pool. The documentation is incredibly deceiving in that respect. I don't believe it's currently possible to limit memory by database. You'd have to separate them into their own instances. –  Jon Seigel Mar 24 at 13:33
1  
What goal are you trying to complete? –  mrdenny Mar 25 at 0:00
add comment

2 Answers 2

There is no way to limit the amount of memory a specific database takes. SQL Server uses a least used / first evicted from memory policy (basically).

share|improve this answer
add comment

You can assign minimum and maximum memory in MB for an instance from SQL Server Management Studio using inbuilt functionality. The steps are as follows:

  1. Right click on the instance name and click on Properties.
  2. Select Memory page.
  3. Assign minimum and maximum memory in MB.

Please let me know if this is in line with what you intend to do.

share|improve this answer
    
Thanks for your answer. You have given the method for assigning memory for an instance.IS there any way to set the memory limit for each database. –  user2967729 Mar 24 at 10:12
add comment

Your Answer

 
discard

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