Reclaim .mdf Space

Last Post 09 Mar 2009 09:43 AM by sqldba347. 9 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
chokachi
New Member
New Member

--
20 Aug 2008 08:52 AM
Hi Guys,

I have a .mdf with 12GB, but only 1.7 GB with data, indexes...

When i run sp_spaceused:

database_name: BD
database_size: 12096.75 MB
unallocated space: 10395.03 MB

reserved: 1715744 KB
data: 1668048 KB
index_size: 22872 KB
unused: 24824 KB

I have run shrink file, UPDATEUSAGE, and nothing results...

how i can delete unallocated space ? (it's not log file...)

Regards
chokachi
New Member
New Member

--
20 Aug 2008 10:24 AM

how can o create a BD with less size ? to use in sql server express with 4gb limitation...

only solutions is create new BD, copy data with Data Export, script constraints, sp, users, etc... and run the script ?

chokachi
New Member
New Member

--
21 Aug 2008 08:20 AM

DBCC SHRINKFILE (file_dat, 2000);

Dbid: 41
Field: 1
CurrentSize: 1545032
MinimumSize: 256
UsedPages: 214544
EstimatedPages: 214544


Yes its because db initial size, how can i reduced ?

or i can create identical BD with new database ?


regards
chokachi
New Member
New Member

--
22 Aug 2008 01:23 AM

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQLUSA
New Member
New Member

--
22 Aug 2008 04:32 AM
Can you try this:

detach/attach db, put db into single user mode, shrink it again

Let us know if works.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
chokachi
New Member
New Member

--
22 Aug 2008 07:18 AM

- detach

- attach

- single user - EXEC sp_dboption 'database_name', 'single user', 'TRUE'

- shrinkfile - DBCC SHRINKFILE (database_data_file, 2000);

- not single user - EXEC sp_dboption 'database_name', 'single user', 'OFF'

no results keep same initial size data file

sqldba347
New Member
New Member

--
06 Mar 2009 10:38 AM
I have a similar issue. my database size is 448GB and 297 GB unallocated.
I tried running shrinkfile with small chunks but it takes forever and never completes. there are no other activity happening on the database or the server. please provide some guidance.

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
149347160 KB 117707448 KB 31382776 KB 256936 KB

Thanks
sqldba347
New Member
New Member

--
09 Mar 2009 09:43 AM
Thanks for the response. I have clustered indexes for all big tables and I rebuild the indexes before running the shrink. The shrink statement ran this weekend but it did shrink only 5GB out of 300GB free space.

Please suggest why its taking so much time for shrinking 1 GB though it has so much free space and if there any faster way of shrinking.

Appreciate your help on this.
sqldba347
New Member
New Member

--
09 Mar 2009 12:35 PM
I just figured out that I have few large tables which do not have clustered indexes..here is their space used details. I do not see much unused space on them.

name rows reserved data index_size unused
T1 47708511 19540880 KB 19490648 KB 144 KB 50088 KB
T2 98194364 12752448 KB 4385728 KB 8364632 KB 2088 KB
T3 57161115 5709608 KB 5695968 KB 112 KB 13528 KB
T4 96512890 4297992 KB 4297192 KB 136 KB 664 KB
T5 26805386 1251088 KB 1250920 KB 16 KB 152 KB

Is this cause any affect for shrinking if they don't have clustered indexes? Please suggest.
sqldba347
New Member
New Member

--
10 Mar 2009 12:44 PM
None of the tables are fragmented, Scan density is 99.xx for all the tables and Avg. Bytes free per page is <2000 bytes.

the Unused column space is not almost same as the total free bytes size for all pages with in the table?

I'm shrinking it chunks and I had it run last night ..it shrinked around 40GB after it ran for 16hrs.

This is the latest sp_spaceused result. it still has 172Gigs of unallocated.

database_name database_size unallocated_space
HandSetOrder 317745.13 MB 172747.76 MB

reserved data index_size unused
145405304 KB 116663832 KB 28523456 KB 218016 KB
You are not authorized to post a reply.

Acceptable Use Policy