On a production database, there is a 2GB database, when I run
sp_spaceused it indicates a very high quanity of unused space. The
database has been shrunk & free space sent to the OS. Why is this
value so high, what can I do to reclaim the space?
database_name database_size unallocated space
------------------------------------------------------------------------------
DB_00001 2004.13 MB 49.64 MB
reserved data index_size unused
------------------ ------------------ ------------------
--------------
1531248 KB 412720 KB 165168 KB 953360 KB 5 28854
[posted and mailed, please reply in news]
Brad Tobin (br*******@yahoo.com) writes: On a production database, there is a 2GB database, when I run sp_spaceused it indicates a very high quanity of unused space. The database has been shrunk & free space sent to the OS. Why is this value so high, what can I do to reclaim the space?
database_name database_size unallocated space -------------------------------------------------------------------------- DB_00001 2004.13 MB 49.64 MB
reserved data index_size unused ------------------ ------------------ ------------------ -------------- 1531248 KB 412720 KB 165168 KB 953360 KB
Unused is not the same as free. Your unallocated space is 50 MB.
The unused space is reserved for existing tables to grow in. SQL Server
reserves space for tables in extents of 8 pages @ 8192 bytes. (The
first page for a table goes into a mixed extent though.) An extent
is not reclaimed, until all pages in the extent are unused.
Your high level of unused, around 2/3 of what is reserved, indicates a
high level of fragmentation. A DBCC DBREINDEX should take care of that.
Not only will you get more disk space you can reclaim, but you can also
win quite some in performance.
Note: if you expect that you will need the space again, shrinking the
data file is not a very good idea, as SQL Server will have to autogrow,
and this is a costly operation.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>... [posted and mailed, please reply in news]
Brad Tobin (br*******@yahoo.com) writes: On a production database, there is a 2GB database, when I run sp_spaceused it indicates a very high quanity of unused space. The database has been shrunk & free space sent to the OS. Why is this value so high, what can I do to reclaim the space?
database_name database_size unallocated space -------------------------------------------------------------------------- DB_00001 2004.13 MB 49.64 MB
reserved data index_size unused ------------------ ------------------ ------------------ -------------- 1531248 KB 412720 KB 165168 KB 953360 KB
Unused is not the same as free. Your unallocated space is 50 MB. The unused space is reserved for existing tables to grow in. SQL Server reserves space for tables in extents of 8 pages @ 8192 bytes. (The first page for a table goes into a mixed extent though.) An extent is not reclaimed, until all pages in the extent are unused.
Your high level of unused, around 2/3 of what is reserved, indicates a high level of fragmentation. A DBCC DBREINDEX should take care of that. Not only will you get more disk space you can reclaim, but you can also win quite some in performance.
Note: if you expect that you will need the space again, shrinking the data file is not a very good idea, as SQL Server will have to autogrow, and this is a costly operation.
Thanks Erland,
I did a DBCC DBREINDEX on every table that has an index, but still no
luck :(
Unused is about the same number and I cannot shrink the database and
further. I appreciate the info and if you have any ideas, please let
me know.
Brad,
DBCC DBREINDEX only has effect on tables with a clustered index.
If your table does not have a clustered index, you can temporarily add
one, just for the purpose of DBCC DBREINDEX, or you can change one of
your existing indexes to be the clustered one.
Gert-Jan
Brad Tobin wrote: Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>... [posted and mailed, please reply in news]
Brad Tobin (br*******@yahoo.com) writes: On a production database, there is a 2GB database, when I run sp_spaceused it indicates a very high quanity of unused space. The database has been shrunk & free space sent to the OS. Why is this value so high, what can I do to reclaim the space?
database_name database_size unallocated space -------------------------------------------------------------------------- DB_00001 2004.13 MB 49.64 MB
reserved data index_size unused ------------------ ------------------ ------------------ -------------- 1531248 KB 412720 KB 165168 KB 953360 KB
Unused is not the same as free. Your unallocated space is 50 MB. The unused space is reserved for existing tables to grow in. SQL Server reserves space for tables in extents of 8 pages @ 8192 bytes. (The first page for a table goes into a mixed extent though.) An extent is not reclaimed, until all pages in the extent are unused.
Your high level of unused, around 2/3 of what is reserved, indicates a high level of fragmentation. A DBCC DBREINDEX should take care of that. Not only will you get more disk space you can reclaim, but you can also win quite some in performance.
Note: if you expect that you will need the space again, shrinking the data file is not a very good idea, as SQL Server will have to autogrow, and this is a costly operation.
Additionally when I do a DBCC DBREINDEX on the table, then use DBCC Showcontig the Scan Density has not changed, which indicates to be that the fragmentation still exists.
Erland Sommarskog wrote: Gert-Jan Strik (so***@toomuchspamalready.nl) writes: If your table does not have a clustered index, you can temporarily add one, just for the purpose of DBCC DBREINDEX, or you can change one of your existing indexes to be the clustered one.
Actually, wouldn't just adding a clustered index and then dropping it have the same effect as a DBCC DBREINDEX? <snip>
Yes it would. Of course, DBCC DBREINDEX on a table is less expensive
than adding a clustered index to a heap and then removing it again. The
DBCC DBREINDEX time is the same as the time it takes to add a clustered
index. However, the removal of the clustered index can take quite some
time, even more time than it takes to add the index.
So I agree with you that Brad should consider adding a permanent
clustered index.
Gert-Jan
You guys are great I'll try that thanks :)
If your ever in the Sonora California area I owe you a dinner and a beer.
Brad This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: spilich |
last post by:
I have a large table that I recently purged a year of data from.
However, the table size in sp_spaceused hasn't decreased as much as I
would expect. (there are no text or large object columns on...
|
by: skullcap |
last post by:
Is there any way using HTML 4.01 of wrapping text and other inline
elements into an 'L' or 'C' shaped space in a table - i.e. telling the
browser that a series of table cells which don't form a...
|
by: Bopolissimus Platypus Jr |
last post by:
hello all,
i've got a database that takes up 4G of space. when i run a script that
deletes all rows and then vacuum, the data directory gets down to
around 3-3.5G. what i'd like is to get a...
|
by: Konstantin Andreev |
last post by:
Recently I became interested, - Are the data, bulk loaded in the table with
LOAD utility, consume the same disk space as loaded with IMPORT utility? The
answer turned out to be NOT !
Here is a...
|
by: Dan |
last post by:
Hi -
I am very new to VB.NET, making the transition from VB6. I converted one of
my apps using the wizard, and notice it's running considerably slower than
the VB6 version. I'm thinking it may...
|
by: rsrinivasan |
last post by:
Hi all,
How to view current java JVM? If anyone know reply me immediately.
Thanks,
|
by: Keith Hughitt |
last post by:
For example,
If you have a list:
<ul>
<li>item 1 is short.</li>
<li>item 2 is a little bit longer</li>
</ul>
regardless of the size of the contents of each list item, the element
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |