August 25, 2011 at 12:05 am
HI all,
One of Db server is working fine. Today morning i saw the avialable Mbytes is near 8 GB in perfomon.
After that i ran a one dbcc checkdb('databasename') for 25 GB database.
Now i Available Mbytes is 200 Mb .The sql server is not releasing the memory .
Out 16 GB RAM i allocated 14 Gb ram for SQL server .
With out restarting the server ,how can i release the server memory .
Regards,
Lavanay Sri
August 25, 2011 at 2:39 am
Would you please be a little more specific?
Does SQL Server take more memory than the 14GB you allocated?
dbcc checkdb has little to do with the memory allocation.
But it requires some space in tempdb. You could run dbcc checkdb('databasename') WITH ESTIMATEONLY to see how much space would be needed to actually run the command.
Based on my interpretation you're talking about the available space on the file system, not in memory.
To check the file size of both, data file and log file, run
USE 'databasename'
GO
SELECT name, physical_name,type_desc, size*8.0/1024 AS MB FROM sys.database_files
Or you could use SSMS and right click the db in question ->Reports->Standard Reports->Disk Usage
The Report will also tell you how much space is allocated, but unused.
A few more question (apply to each db on the instance):
What recovery model do you use (Simple/Full/Bulk-logged)?
Do you perform regular data and log backups? What frequency?
Do you know the "normal size" of the log file(s)?
Are data files and log files on the same drive?
This would give us a start point to help further (if it's a good advice to reduce the size of the log file or if the file system is simply not large enough).
August 25, 2011 at 6:21 am
Does SQL Server take more memory than the 14GB you allocated?
No , SQL server taking 14 GB ,I am running total server memory and target serevr memory counters in the perfmon .
Based on my interpretation you're talking about the available space on the file system, not in memory.
i have enough space on my filesystem . Its taking more physical memory.
A few more question (apply to each db on the instance):
What recovery model do you use (Simple/Full/Bulk-logged)? SImple
Do you perform regular data and log backups? What frequency? every day full backups
Do you know the "normal size" of the log file(s)? Intial file size is 12454
Are data files and log files on the same drive? yes both are in the same drive same location
Regards,
Lavanya sri
August 25, 2011 at 2:06 pm
If my memory serves me right DBCC CheckDb uses memory. Quite a bit of it. For all data pages that need to checked, it has to move it memory. TempDB is also used heavily for sorting while DBCC checkDB operation is done.
Therefore there is a chance that the available memory will reduce drastically if you do this. I do not think it is possible to release the memory without restart. Another thing to keep in mind is that even if the physical memory available is only 300 MB, it is OK.
Just my 2 cents
-Roy
November 22, 2011 at 3:28 am
HI Team ,
I added some perfmon counters on my server are total server memory and target server memory of SQl server:Memory manager .
Both counters showing the same memory .
Last weekend i ran rebuild index and database integrity plan After that the available Mbytes counter showing very low bytes .
Now my question is how to release Sql server memory with out restarting the sql server .
If the SQl server is not releasing the memory ... it will effect on server performance... ??????????
Any one share your thoughts on this ??
November 22, 2011 at 3:38 am
Did you set the upper limit for SQLServer am to be used ?
sp_configure 'max server memory' --, 13500 show the current value
sp_configure 'max server memory', 13500
With a 16GB server I would set it to max 13500MB. (because there are also other resources needing ram (os, sqlagent, ..)
(especially with x64 that is a commonly forgotten setting)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 22, 2011 at 4:10 am
HI ,
Recently my server RAm is updated to 20 GB . I configured max server memory to 17.5 GB and unchecked the AWE option .
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply