how to find missing databse size (Urgent plze guys

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

how to find missing databse size (Urgent plze guys

Author

Topic

prasad0482
Starting Member

12 Posts

Posted - 2010-08-17 : 08:36:20

hi Guys'
I have one query,

I have size of database is 8gb, and 3.25 gb data in that database remain 4.35 free space is disappear, how to find missing free size.

need not recover data, only i have to find missing free space,

Thanks

Prasad.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-17 : 09:12:36

If the free space is there, how can it be missing?

N 56°04'39.26"
E 12°55'05.63"

prasad0482
Starting Member

12 Posts

Posted - 2010-08-17 : 09:38:55

Total allocation size is 8GB,
actual data size is 3.35 GB.
unused space is 4.25.

when I executed this query EXEC sp_spaceused
result will be like this

Database size unallocated space
7566.94 MB 139.59MB

Reserved space data Index_size unused

7597224kb 7460288kb 133256kb 3680kb

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-17 : 11:13:28

And what is your definition of "missing free space". I have never heard that term before.
Free space either exist or it doesn't. It can't be missing.

N 56°04'39.26"
E 12°55'05.63"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-17 : 13:08:42

Run DBCC UPDATEUSAGE(0) and run sp_spaceused again. Sounds like your usage numbers are outdated.

prasad0482
Starting Member

12 Posts

Posted - 2010-08-18 : 02:00:08

Hi peso,
Total allocation size is 8GB,
actual data size is 3.35 GB.
unused space is 4.25.

Reserved space data Index_size unused

7597224kb 7460288kb 133256kb 3680kb

here showing Reserved space is 7.GB,data is 7.4 GB actually I dont have 7.4GB data. Exact my used data is 3.35GB.but when i exec sp_spaceused

it is showing used space is 7.4GB.

how can I retrieve complete unused space.

Thanks

Prasad

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-18 : 02:46:38

Where do you get the

quote:
Total allocation size is 8GB,
actual data size is 3.35 GB.
unused space is 4.25.

data from?
And have you considered the log file size?

N 56°04'39.26"
E 12°55'05.63"

prasad0482
Starting Member

12 Posts

Posted - 2010-08-18 : 03:19:21

I consider log file and mdf file size.

how can we know which table is used much space in database.

quote:
Originally posted by Peso

Where do you get the
quote:
Total allocation size is 8GB,
actual data size is 3.35 GB.
unused space is 4.25.
data from?
And have you considered the log file size?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-18 : 03:41:33

Goto SSMS (SQL Server Management Studio)
Click "Tables" node.
Press F7.
Right click header bar to the right side in the status window.
Check all columns you want (including data space used).

N 56°04'39.26"
E 12°55'05.63"

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2010-08-19 : 18:01:59

@Prasad:
Do you have clustered index on all tables, or are there some heaps? If there are heaps, then you *may* have the issue where pages are not be released, even after deletes. If this is the case, the creation of a clustered index (even if it is dropped afterwards) will re-order the pages, and release this "black space" (also heard it referred to a Swiss-cheese syndrome).

sp_msforeachtable 'sp_spaceused ?'

should generate space usage information at a table level (only accurate if updateusage has been done).

select reserved*8/1024,dpages*8/1024,* from sys.sysindexes order by 1 desc

will show reserved and data pages for indexes.

Will need to go and try Peso's suggestion - not seen that one before ... using the GUI ... eeew - it's sticky :)

Also, do you have any BLOBs? We currently have a call open with MS where they've reproduced an issue where, when deleting from a type with blobs is not releasing space - the DB was upgraded from SQL 2005 to SQL 2008. On the SQL 2005 box, delete free's up space, on SQL 2008, it doesn't. Currently, the noises from them seems to indicate that it might be to do with the garbage collector (appropriately :P)

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources