allocated, unused and free space

Considering the output of sp_spaceused (below) on my SQL Server 2005
database, is it possible to get back (release to the OS) the UNUSED space,
shrinking does not do it.
Basically I need to resize this database, most of its space is not being
used and is filling up my backup storage.
I have plenty of room in my data drive but I keep serveral days worth of
backups and thouse GB start to add up and fill up my backup drive.
Of the 28 GB only about 4 GB are being used, the rest is just RESERVED.
database_name: c8audit
database_size: 26960.63 MB
unallocated space: 2033.20 MB
reserved: 25521840 KB
data: 3561528 KB
index_size: 8440 KB
unused: 21951872 KB
Thanks,
Tim
DBCC SHRINKFILE will return the space to the OS as long as it is able to
shrink it in the first place. Can you try running that command and post the
actual command you used with all the parameters along with the output from
it? The backups do not include "free space" and only backup the actual
data. So if you have 4GB of data your backups should be roughly 4GB
regardless of how large your db is overall.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tim Bales" <timbales@.bellsouth.net> wrote in message
news:OXgzVobbIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Considering the output of sp_spaceused (below) on my SQL Server 2005
> database, is it possible to get back (release to the OS) the UNUSED space,
> shrinking does not do it.
> Basically I need to resize this database, most of its space is not being
> used and is filling up my backup storage.
> I have plenty of room in my data drive but I keep serveral days worth of
> backups and thouse GB start to add up and fill up my backup drive.
> Of the 28 GB only about 4 GB are being used, the rest is just RESERVED.
> database_name: c8audit
> database_size: 26960.63 MB
> unallocated space: 2033.20 MB
> reserved: 25521840 KB
> data: 3561528 KB
> index_size: 8440 KB
> unused: 21951872 KB
> Thanks,
> Tim
>
|||Hi Tim
There is a difference between allocated and unused space. Unallocated space
could be returned to the OS when you shrink a file, but the unused space is
space that has already been allocated to an object, but just doesn't yet
have any data stored in it.
Without seeing you table definitions and knowing the kind of data you're
storing, it's hard to know why you have so much unused space in your tables.
You might try running sp_spaceused on each table, and see which ones have
the most unused.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tim Bales" <timbales@.bellsouth.net> wrote in message
news:OXgzVobbIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Considering the output of sp_spaceused (below) on my SQL Server 2005
> database, is it possible to get back (release to the OS) the UNUSED space,
> shrinking does not do it.
> Basically I need to resize this database, most of its space is not being
> used and is filling up my backup storage.
> I have plenty of room in my data drive but I keep serveral days worth of
> backups and thouse GB start to add up and fill up my backup drive.
> Of the 28 GB only about 4 GB are being used, the rest is just RESERVED.
> database_name: c8audit
> database_size: 26960.63 MB
> unallocated space: 2033.20 MB
> reserved: 25521840 KB
> data: 3561528 KB
> index_size: 8440 KB
> unused: 21951872 KB
> Thanks,
> Tim
>
|||Thanks all for your help.
Using SQL Server 2005's standard report "Disk Usage by Table" I was able to
find out that about 90% of the unused space has been allocated to ONE table.
Among this table's fields there are two VARCHAR that caught my attention
because their lengths are bigger than normal, one is 1024 the other one is
2000.
The table has about 2 million records and both of these fields are only
storing a few characters, even though I have not done any calculations I
believe the free space is in these almost empty fields.
I can't modify the fields' length, but about 90% of the records on this
table can go, so I'm going to delete the records to regain the space.
I did this in TEST after my original post and it worked, the UNUSED space
became AVAILABLE FREE and shrinking released it.
Again THANKS!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eyBNF$ebIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Hi Tim
> There is a difference between allocated and unused space. Unallocated
> space could be returned to the OS when you shrink a file, but the unused
> space is space that has already been allocated to an object, but just
> doesn't yet have any data stored in it.
> Without seeing you table definitions and knowing the kind of data you're
> storing, it's hard to know why you have so much unused space in your
> tables. You might try running sp_spaceused on each table, and see which
> ones have the most unused.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tim Bales" <timbales@.bellsouth.net> wrote in message
> news:OXgzVobbIHA.4880@.TK2MSFTNGP03.phx.gbl...
>
|||Hi Tim
I'm glad you were able to accomplish your goal.
However, it's a mystery what the cause of all the unused space was. A
varchar column should not reserve more space than it actually uses. So if
there were only a few characters in them, that should not have caused excess
space allocation. It might have been that there was padding with spaces, or
it might have been that the columns were longer originally and then updated.
But if you've deleted the rows, we'll probably never know. ;-)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tim Bales" <timbales@.bellsouth.net> wrote in message
news:%23SIRI7kbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Thanks all for your help.
> Using SQL Server 2005's standard report "Disk Usage by Table" I was able
> to find out that about 90% of the unused space has been allocated to ONE
> table.
> Among this table's fields there are two VARCHAR that caught my attention
> because their lengths are bigger than normal, one is 1024 the other one is
> 2000.
> The table has about 2 million records and both of these fields are only
> storing a few characters, even though I have not done any calculations I
> believe the free space is in these almost empty fields.
> I can't modify the fields' length, but about 90% of the records on this
> table can go, so I'm going to delete the records to regain the space.
> I did this in TEST after my original post and it worked, the UNUSED space
> became AVAILABLE FREE and shrinking released it.
> Again THANKS!
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eyBNF$ebIHA.4208@.TK2MSFTNGP04.phx.gbl...
>