?
Solved

Allocate Fixed memory to Database in SQL Server 2005

Posted on 2011-10-24
11
Medium Priority
?
550 Views
Last Modified: 2012-06-21
Hi experts

Please I need help , I need to allcoate fixed memory to database ... suppose i have database called
xyz in SQL server 2005, I need to allocate certain memory for this database to utilize only 500MB from 4 GB available for the SQL server.

so I need to know how can I do that for database level NOT SQL server leverl.
0
Comment
Question by:Haidar1
9 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 2000 total points
ID: 37022551
This cannot be done in SQL Server 2005, Resource governor on SQL Server 2008 is meant for that.
0
 
LVL 15

Expert Comment

by:Anuj
ID: 37022554
Another option is to separate this database to new instance and allot the memory at server level for that instance.
0
 
LVL 9

Expert Comment

by:Ramanhp
ID: 37022572
well the option is to partition a hard disk logically in a size of 500mb and keep your database therein
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 

Author Comment

by:Haidar1
ID: 37022606
Thank you experts,

But I need to use same instance ,,, since I can’t create another instance cause .. I need to use same allocated memory for the current server to utilize only 500 MB for xyz DB out of 4GB
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37022635
Try using this you may ristrict the database file size

CREATE DATABASE [DbName] ON  PRIMARY 
( NAME = N'DbName', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLR2\MSSQL\DATA\DbName.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DbName_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLR2\MSSQL\DATA\DbName_log.ldf' , SIZE = 1024KB , MAXSIZE = 512000KB , FILEGROWTH = 10%)
GO

Open in new window

or

CreateDatabase.png
0
 
LVL 15

Expert Comment

by:Anuj
ID: 37022674
There is no other way to achieve this in SQL SERVER 2005 other than separating the instance. Luckily SQL Server 2008 Resource governor have this feature. Sorry!
0
 

Author Comment

by:Haidar1
ID: 37028673
I've requested that this question be deleted for the following reason:

Question deleted becuase no solution available to solve the problem.
0
 

Author Comment

by:Haidar1
ID: 37023406
no solution available to solve this problem
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37028674
http://www.experts-exchange.com/help.jsp#hs=29&hi=405
The correct answer to some questions is "You can't do that."
Sometimes, you will get an answer that isn't what you want to read, but it still may be the correct answer, and you should award points to the Expert that gave you that answer.


The correct solution was submitted here: http:#a37022551
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question