Unused (KB) in Disk Usage By Table report
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?
I already shrinked the database several times, and it still shows a lot of space as "unused"?
Shouldn't be all gone after shrinkage?
ASKER
I'm shrinking the whole database. The transaction log is 1mb only, I already truncated that.
Hi,
From your database, try
execute sp_spaceused @objname = null, @updateusage = 'true'
HTH
David
From your database, try
execute sp_spaceused @objname = null, @updateusage = 'true'
HTH
David
ASKER
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)
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)
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.
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
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 KBASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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)
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)
Hi,
If on full recovery model, you will need to make transaction log backups at least as often as your full backups ...
Cheers
David
If on full recovery model, you will need to make transaction log backups at least as often as your full backups ...
Cheers
David
ASKER
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.
It's totally pointless to perform transaction log backups in this scenario.
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
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
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
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What are you shrinking?
The space you see may be in the log file.
HTH
David