How to release the memory allocated to SQL Server?

The task manager shows my SQL Server use 1.7G memory. However, it returns
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:

> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:

> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:

How to release the memory allocated to SQL Server?

The task manager shows my SQL Server use 1.7G memory. However, it returns
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:

> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:

> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
>|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
>sql

How to release the memory allocated to SQL Server?

The task manager shows my SQL Server use 1.7G memory. However, it returns
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
--
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?

How to release large unused/allocated space

Hi,

I have 6 filegroups that contain over 30 gigs of data. I manage the transaction log space efficiently by using a script and 'shrink database'. But, the allocated unused space of the data files are very large, over 30 gigs.

The configuration is standard for all files:

- Automatically grow file by percent

- Maximum file size - Unrestricted

How do I release the unused space?

Thanks,

- gshaf

Hi Gshaf,

You can issue the DBCC SHRINKDATABASE statement with TRUNCATEONLY. This will shrink the database the maximum amount possible, and release the space back to windows. If you want to control how much space in the db left for growth, you'll need to issue DBCC SHRINKFILE and specify the amount (MB) of space you want left:

DBCC SHRINKFILE(MyDataFileNameHere, 15000)

The above command will attempt to shrink the datafile "MyDataFileNameHere" to 15GB.

Cheers,

Rob

sql

How to release large unused/allocated space

Hi,

I have 6 filegroups that contain over 30 gigs of data. I manage the transaction log space efficiently by using a script and 'shrink database'. But, the allocated unused space of the data files are very large, over 30 gigs.

The configuration is standard for all files:

- Automatically grow file by percent

- Maximum file size - Unrestricted

How do I release the unused space?

Thanks,

- gshaf

Hi Gshaf,

You can issue the DBCC SHRINKDATABASE statement with TRUNCATEONLY. This will shrink the database the maximum amount possible, and release the space back to windows. If you want to control how much space in the db left for growth, you'll need to issue DBCC SHRINKFILE and specify the amount (MB) of space you want left:

DBCC SHRINKFILE(MyDataFileNameHere, 15000)

The above command will attempt to shrink the datafile "MyDataFileNameHere" to 15GB.

Cheers,

Rob

How to reduce the data space allocated ?

Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?
DBCC SHRINKDATABASE
<http://msdn.microsoft.com/library/en..._dbcc_3pd1.asp>
or
DBCC SHRINKFILE
<http://msdn.microsoft.com/library/en..._dbcc_8b51.asp>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow again.
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:
How do I reclaim space in SQL Server?
<http://www.aspfaq.com/show.asp?id=2471>
Why you want to be restrictive with shrink of data files
<http://www.karaszi.com/SQLServer/info_dont_shrink.asp>
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
kelvinweb@.gmail.com wrote:

>Hi All,
>I don't know how to change data space allocated ?
>My SQL data have 10GB, but the "space allocated" was allocated 50GB.
>The usage of data storage is 79%. I want to restore 20GB space from
>the "space allocated". How to do that ?
>
>
sql

How to reduce the data space allocated ?

Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?DBCC SHRINKDATABASE
<http://msdn.microsoft.com/library/e...s_dbcc_3pd1.asp>
or
DBCC SHRINKFILE
<http://msdn.microsoft.com/library/e...s_dbcc_8b51.asp>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow again.
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:
How do I reclaim space in SQL Server?
<http://www.aspfaq.com/show.asp?id=2471>
Why you want to be restrictive with shrink of data files
<http://www.karaszi.com/SQLServer/info_dont_shrink.asp>
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
kelvinweb@.gmail.com wrote:

>Hi All,
>I don't know how to change data space allocated ?
>My SQL data have 10GB, but the "space allocated" was allocated 50GB.
>The usage of data storage is 79%. I want to restore 20GB space from
>the "space allocated". How to do that ?
>
>

How to reduce the data space allocated ?

Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?This is a multi-part message in MIME format.
--040706030801020607030300
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
DBCC SHRINKDATABASE
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_3pd1.asp>
or
DBCC SHRINKFILE
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_8b51.asp>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow again.
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:
How do I reclaim space in SQL Server?
<http://www.aspfaq.com/show.asp?id=2471>
Why you want to be restrictive with shrink of data files
<http://www.karaszi.com/SQLServer/info_dont_shrink.asp>
Hope this helps.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
kelvinweb@.gmail.com wrote:
>Hi All,
>I don't know how to change data space allocated ?
>My SQL data have 10GB, but the "space allocated" was allocated 50GB.
>The usage of data storage is 79%. I want to restore 20GB space from
>the "space allocated". How to do that ?
>
>
--040706030801020607030300
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt><a
href="http://links.10026.com/?link=DBCC">http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_3pd1.asp">DBCC
SHRINKDATABASE</a><br>
or<br>
<a
href="http://links.10026.com/?link=DBCC">http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_8b51.asp">DBCC
SHRINKFILE</a><br>
<br>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow
again.<br>
<br>
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:<br>
<a href="http://links.10026.com/?link=How">http://www.aspfaq.com/show.asp?id=2471">How do I reclaim space
in SQL Server?</a><br>
<a href="http://links.10026.com/?link=Why">http://www.karaszi.com/SQLServer/info_dont_shrink.asp">Why you
want to be restrictive with shrink of data files</a><br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:kelvinweb@.gmail.com">kelvinweb@.gmail.com</a> wrote:
<blockquote
cite="mid1128560354.037374.148950@.o13g2000cwo.googlegroups.com"
type="cite">
<pre wrap="">Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?
</pre>
</blockquote>
</body>
</html>
--040706030801020607030300--