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--

How to reduce tempdb use?

It would be useful to get a good understanding of how tempdb is used by SQL
Server before considering how to reduce the tempdb usage. Here's a good
Microsoft whitepaper on tempdb usage (primarily on SQL2005).
http://download.microsoft.com/downl...gWithTempDB.doc
Linchi
"paulgyro@.gmail.com" wrote:

> I'm wondering if there are any general guidelines on how one can go
> about reducing SQL Server's use to the tempdb?
> Add RAM to the server? Index columns? I'm not sure how to go about
> reducing the tempdb use on DB as I think it is being used too often.
>I'm wondering if there are any general guidelines on how one can go
about reducing SQL Server's use to the tempdb?
Add RAM to the server? Index columns? I'm not sure how to go about
reducing the tempdb use on DB as I think it is being used too often.|||It would be useful to get a good understanding of how tempdb is used by SQL
Server before considering how to reduce the tempdb usage. Here's a good
Microsoft whitepaper on tempdb usage (primarily on SQL2005).
http://download.microsoft.com/downl...gWithTempDB.doc
Linchi
"paulgyro@.gmail.com" wrote:

> I'm wondering if there are any general guidelines on how one can go
> about reducing SQL Server's use to the tempdb?
> Add RAM to the server? Index columns? I'm not sure how to go about
> reducing the tempdb use on DB as I think it is being used too often.
>|||Thanks for pointing me to this fine document. It provided additional
detail into what I already knew. I am fairly sure that the issue is
with the queries. Seeing I have no way to tune the specific queries
what other steps like I've suggested before could I take to reduce
tempdb use?
Paul
Linchi Shea wrote:[vbcol=seagreen]
> It would be useful to get a good understanding of how tempdb is used by SQ
L
> Server before considering how to reduce the tempdb usage. Here's a good
> Microsoft whitepaper on tempdb usage (primarily on SQL2005).
> http://download.microsoft.com/downl...gWithTempDB.doc
> Linchi
> "paulgyro@.gmail.com" wrote:
>|||If you cannot change the queries then there's not much you can do to
reduce the use of tempdb; it still gets used for the same things
regardless of how much memory is in your server, although the creation
of more useful indexes *may* reduce the optimiser's need to use tempdb
as much during query execution (better sort orders, covering indexes, etc.).
However, there are a few things you can do, if you have the resources,
to make tempdb use more efficient, like moving it onto its own disk,
splitting it into multiple files (1 per CPU or core is a good rule of
thumb, and they should all be equal in size), place tempdb (or its
parts) on more efficient disks or RAID arrays (RAID 1 or RAID 1/0), put
more spindles in the RAID array(s) on which it is located (ie. more
parallel disk operations), etc.
There is a little information in BOL about optimising tempdb performance:
http://msdn2.microsoft.com/en-us/library/ms175527.aspx
but most of that information is already contained in the document Linchi
pointed you to.
After thinking about it a little more, _if you had memory pressure_ then
adding more memory *might* make tempdb a little more efficient as it
wouldn't be quite so reliant on disk I/O. It would still have the same
level of activity but might not need to commit it to disk quite as often
(but that all depends on the frequency of commits and dirty page flushes
and stuff like that). However, I suspect you'd likely just be throwing
money away if tempdb was the only reason you were adding memory to your
SQL box.
*mike hodgson*
http://sqlnerd.blogspot.com
paulgyro@.gmail.com wrote:

>Thanks for pointing me to this fine document. It provided additional
>detail into what I already knew. I am fairly sure that the issue is
>with the queries. Seeing I have no way to tune the specific queries
>what other steps like I've suggested before could I take to reduce
>tempdb use?
>Paul
>
>Linchi Shea wrote:
>
>
>|||Thanks for pointing me to this fine document. It provided additional
detail into what I already knew. I am fairly sure that the issue is
with the queries. Seeing I have no way to tune the specific queries
what other steps like I've suggested before could I take to reduce
tempdb use?
Paul
Linchi Shea wrote:[vbcol=seagreen]
> It would be useful to get a good understanding of how tempdb is used by SQ
L
> Server before considering how to reduce the tempdb usage. Here's a good
> Microsoft whitepaper on tempdb usage (primarily on SQL2005).
> http://download.microsoft.com/downl...gWithTempDB.doc
> Linchi
> "paulgyro@.gmail.com" wrote:
>|||If you cannot change the queries then there's not much you can do to
reduce the use of tempdb; it still gets used for the same things
regardless of how much memory is in your server, although the creation
of more useful indexes *may* reduce the optimiser's need to use tempdb
as much during query execution (better sort orders, covering indexes, etc.).
However, there are a few things you can do, if you have the resources,
to make tempdb use more efficient, like moving it onto its own disk,
splitting it into multiple files (1 per CPU or core is a good rule of
thumb, and they should all be equal in size), place tempdb (or its
parts) on more efficient disks or RAID arrays (RAID 1 or RAID 1/0), put
more spindles in the RAID array(s) on which it is located (ie. more
parallel disk operations), etc.
There is a little information in BOL about optimising tempdb performance:
http://msdn2.microsoft.com/en-us/library/ms175527.aspx
but most of that information is already contained in the document Linchi
pointed you to.
After thinking about it a little more, _if you had memory pressure_ then
adding more memory *might* make tempdb a little more efficient as it
wouldn't be quite so reliant on disk I/O. It would still have the same
level of activity but might not need to commit it to disk quite as often
(but that all depends on the frequency of commits and dirty page flushes
and stuff like that). However, I suspect you'd likely just be throwing
money away if tempdb was the only reason you were adding memory to your
SQL box.
*mike hodgson*
http://sqlnerd.blogspot.com
paulgyro@.gmail.com wrote:

>Thanks for pointing me to this fine document. It provided additional
>detail into what I already knew. I am fairly sure that the issue is
>with the queries. Seeing I have no way to tune the specific queries
>what other steps like I've suggested before could I take to reduce
>tempdb use?
>Paul
>
>Linchi Shea wrote:
>
>
>|||<paulgyro@.gmail.com> wrote in message
news:1151514894.873943.289410@.i40g2000cwc.googlegroups.com...
> I'm wondering if there are any general guidelines on how one can go
> about reducing SQL Server's use to the tempdb?
> Add RAM to the server? Index columns? I'm not sure how to go about
> reducing the tempdb use on DB as I think it is being used too often.
Indexing may help, but not really sure w/o seeing the queries.
Incidentally why do you think the tempdb is being used to often?

>|||<paulgyro@.gmail.com> wrote in message
news:1151514894.873943.289410@.i40g2000cwc.googlegroups.com...
> I'm wondering if there are any general guidelines on how one can go
> about reducing SQL Server's use to the tempdb?
> Add RAM to the server? Index columns? I'm not sure how to go about
> reducing the tempdb use on DB as I think it is being used too often.
Indexing may help, but not really sure w/o seeing the queries.
Incidentally why do you think the tempdb is being used to often?

>|||I understand that you would need to see the queries to know if
additional indexes would help. I am just looking for general info at
this point as I've never had to trouble shoot tempdb usage before.
We see the tempdb on our 40 gig SQL Server 2000 DB grows quickly over
the course of 2 - 3 days to 10+ gig in size. It appears the product's
queries all contain order by's and many of the queries are accessing
millions of rows. Over a course of the 2- 3 days there comes a point
where the system is brought to it's knees and we need to kick all users
off and allow SQL Server at least 20 mins to recover. Recently we
require a reboot to get SQL Server useable again. Even restarting the
SQL Server process does not seen to fix that issue that a reboot does.
We have already moved the tempdb to it's own hard drive volume which
seemed to help just a little to reduce I/O contention but not
eliminating the core problem.
The product vendor doesn't know what to say other then it's a SQL
Server issue.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> <paulgyro@.gmail.com> wrote in message
> news:1151514894.873943.289410@.i40g2000cwc.googlegroups.com...
> Indexing may help, but not really sure w/o seeing the queries.
> Incidentally why do you think the tempdb is being used to often?
>