Solved

Unused (KB) in Disk Usage By Table report

Posted on 2008-10-09
18
1,869 Views
Last Modified: 2012-05-05
What is the "Unused(KB)" column in the Disk Usage By Table report?
I already shrinked the database several times, and it still shows a lot of space as "unused"?
Shouldn't be all gone after shrinkage?
0
Comment
Question by:fischermx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +1
18 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 22683750
Hi,

What are you shrinking?

The space you see may be in the log file.

HTH
  David
0
 
LVL 1

Author Comment

by:fischermx
ID: 22683785
I'm shrinking the whole database. The transaction log is 1mb only, I already truncated that.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 22683877
Hi,

From your database, try

execute sp_spaceused @objname = null, @updateusage = 'true'

HTH
  David
0
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
LVL 1

Author Comment

by:fischermx
ID: 22683973
It shows:
 
database_name database_size      unallocated space
------------------  ------------------       ------------------
mydatabase        3653.38 MB         1.06 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
3739136 KB         2079592 KB         1075368 KB         584176 KB

Now, how do I "free" that 584176kb to the OS ?
I already ran DBCC SHRINKDATABASE..... maybe I'm not using correctly.

(BTW, the log file is just 1mb)
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22684582

if u deleted alot of records in your database reindex all tables and then use shrink database.
I think your index fill ratio keep those unused space.

use dbcc reindex for that but keep in mind while reindexinig query's against your data wont use those indexes that are reindex in that moment.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 22689496
Hi,

For a production application wiht a database this size, 1MB is not sufficient for the log file. 1 - 10% of the database size would be better. That is 30 - 300MB minimum.

That unused space is allocated, so its not really empty.

Iif you run dbcc dbreindex as sugested above, what will happen is that space is needed to operate, and if memory is correct, it leaves 1.4 * the finished size of the largest table behind as free space.

That is, there is the very strong possibility that your database will grow by at least the half gig you are concerned about, and that will be left as unallocated space, not unused space.

In my opinion, I wouldn't worry about the unused space. As your database gets more data, that will get used up. The unused space is in indexes and spare space on pages and so on.

Run this command in your database for more detail on the above
execute sp_MSForEachTable 'sp_spaceused @objname = ''?'''
go

I've posted my results from pubs in the code snippet.

There are a couple of things to note: unused space is listed at 152kb (verses the .13MB unallocated), but pub_info is the only table to list unused space at 24kb. Note that all the figures are in 8k chunks. The minimum is 4k pages, while the SQL reads and writes 64k extents.

If you want to know more about these ins and outs I suggest getting Kalen Delany's book on Inside SQL - the storage engine.

If this is a production database, its going to grow again when more data is entered.

If you have the optimise entry selected on this database in a maintenance plan, then it will grow again. Growth during a busy data-entry period is slow and requires computer resources, so ideally you don't want a database to grow then. Repeated shrinks and growth fragment the data files at the os level, and this isn't good for performance.

Unlike almost anything else on your server, SQL is very much disk based, and its response is time critical.

Sure serving files is almost always disk based. But the amount of disk vs the time to respond is very low compared to some sql queries.

Summary
I wouldn't be concerned with the unused disk in your report at this stage.

I'd need to know a lot more about your database and application and how it all is being used before coming up with anything further.

I'd be concerned with a production data-entry application that had only 1MB log file.

HTH
  David
database_name                                                                                                                    database_size      unallocated space
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
Pubs                                                                                                                             6.00 MB            0.13 MB
 
reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
2168 KB            1016 KB            1000 KB            152 KB
 
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
titleauthor                                                                                                                      25          56 KB              8 KB               48 KB              0 KB
 
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
stores                                                                                                                           6           24 KB              8 KB               16 KB              0 KB
 
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
sales                                                                                                                            21          40 KB              8 KB               32 KB              0 KB
 
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
roysched                                                                                                                         86          32 KB              8 KB               24 KB              0 KB
 
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
discounts                                                                                                                        3           16 KB              8 KB               8 KB               0 KB
 
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
jobs                                                                                                                             14          24 KB              8 KB               16 KB              0 KB
 
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
pub_info                                                                                                                         8           160 KB             120 KB             16 KB              24 KB
 
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
employee                                                                                                                         43          40 KB              8 KB               32 KB              0 KB
 
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
authors                                                                                                                          23          40 KB              8 KB               32 KB              0 KB
 
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
publishers                                                                                                                       8           24 KB              8 KB               16 KB              0 KB
 
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
titles                                                                                                                           18          40 KB              8 KB               32 KB              0 KB

Open in new window

0
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 168 total points
ID: 22689571
1 MB log file can be if he put database to simple mode and after shrinking and probably truncate log his log file wont get much bigger in simple mode (i dont think 300 MB will ever be size in single mode).
0
 
LVL 35

Expert Comment

by:David Todd
ID: 22689715
Hi Cvijo123,

Why would you put a production database in simple recovery mode? I'd be reluctant to recommend that to a beginner on SQL.

fischermx: Before we make a dangerous assumption, do you have daily backups of this database, that you KNOW you can recover from?

Cheers
  David
0
 
LVL 1

Author Comment

by:fischermx
ID: 22689778
Yes, I have daily database backups and hourly incremental backups.

And yes, Cvijo123 is correct in his assumption, the log file was that size, because I've just truncated it. I am just trying to figure out how much of the space occupied by the database is real data and how much is empty space, regardless if I can use it or not, I wanted to know what it was for, and that's the reason of my question here.

Btw, if I lost my database, either having the recovery mode simple or full won't make a difference respecting recovering from backups anyway !! :)
 (Specially if a backup doesn't exist)
0
 
LVL 35

Expert Comment

by:David Todd
ID: 22689841
Hi,

If on full recovery model, you will need to make transaction log backups at least as often as your full backups ...

Cheers
  David
0
 
LVL 1

Author Comment

by:fischermx
ID: 22690738
I have full backups daily and hourly incremental backups, because I have the database in simple recovery mode.
It's totally pointless to perform transaction log backups in this scenario.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 22690829
Hi,

Agreed.

It is impossible to take a transaction backup in simple recovery mode.

Good to hear that your database is protected so well. Its scary how many systems aren't!

Cheers
  David
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22691382

dtodd: i didnt recomment to put database in simple mode i said his log is 1 MB mybe becouse he has simple mode !

as i said before in my first post, reindexing your dabase will free some unused space, if u know what u are doing u can put your fillfactor to 100% to remove all empty space on pages. BUT this will make your inserts, updates to work alot with page splits and additional I/O work.

So if u know what you are doing and how your database worklow is (density)

To determine how full your pages are u can use DBCC SHOWCONTIG and check fragmentation and density part in results, you can easy google to understand what result is showing.
You can also see DBCC UPDATEUSAGE in books online .. that can correct some space usage
0
 
LVL 1

Author Comment

by:fischermx
ID: 22692155
I couldn't reindex because the database is near the 4Gb cap of SQL Server 2005 Express Edition.
So, I tried by using DBCC INDEXDEFRAG and that got ride of the wasted unused space.
The database growth a lot, near 5.5GB for which some more than 1.8 was marked "space available". I got ride of that after a simple DBCC SHRINKDATABASE.

I understand how the indexes work, and how fill factor affects. My only question was where was that space being used, and now I know, it was caused by index fragmentation.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 166 total points
ID: 22692442
Hi Cvijo123

Thanks for the clarification.

My concern with a dbcc dbreindex was the extra space that the database would grow to.

fischermx
At this sort of size it appears time to think about upgrading from SQL Express, or archiving the data to remain comfortably under the size limit, which from memory was 2GB on MSDE. Not sure about the other versions.

Cheers
  David

0
 
LVL 41

Assisted Solution

by:graye
graye earned 166 total points
ID: 22713928
.... one more thing.   If you've copied these databases from an older SQL 2000 database (via a detach/attach procedure), then the row statistics will be incorrect.
If so, then the sp_spaceused stored procedure will fix the problem.
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

695 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