Solved

Unused (KB) in Disk Usage By Table report

Posted on 2008-10-09
18
1,798 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
  • 7
  • 5
  • 3
  • +1
18 Comments
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

What are you shrinking?

The space you see may be in the log file.

HTH
  David
0
 
LVL 1

Author Comment

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

Expert Comment

by:David Todd
Comment Utility
Hi,

From your database, try

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

HTH
  David
0
 
LVL 1

Author Comment

by:fischermx
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Author Comment

by:fischermx
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
.... 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need help with a query 4 43
SQL Date from a string 4 50
SQL Express connecting form remote error 26 7 33
SQL Server 2012 express 24 30
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
How to increase the row limit in Jasper Server.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now