My database is getting kind of big, so I have run sp_spaceused in my database to check the space allocated…
The results are as follows:
Database name database size Unallocated space
inquierosb 467.44 MB -136.90 MB
reserved data index size unused
617816 KB 326520 KB 49616 KB 241680 KB
How can i free up the 241680kb unused space?
What is the meaning of the unallocated space?
Is there a way to view which table are allocating more space in my database ?
Thanks for your replies.
Should i use DBCC SHRINKFILE with the TRUNCATEONLY argument to release the unused space ?
About that interesting command (sp_MSforeachtable), it's not working, am i missing something ? :
Server: Msg 201, Level 16, State 4, Procedure sp_MSforeachtable, Line 0
Procedure 'sp_MSforeachtable' expects parameter '@command1', which was not supplied.
For the sp_MSforeachtable suggestion, use this:
exec sp_MSforeachtable 'exec sp_spaceused ''?'''
For the DBCC SHRINKFILE suggestion, it would be a good idea to do a ReIndex or IndexDefrag of your tables after your done. Some of your tables will get fragmented after running the SHRINKFILE.
Thanks for you replies.
I have ran the update usage command and now the values are as folows:
Databasename database size unallocated space
InquieroSB 467.44 MB 43.65 MB
Reserved data index_size unused
432936 KB 365776 KB 53896 KB 13264 KB
I have some questions, can you can help me understand this:
- What is the diference between unallocated space and unused space
- In the event of running DBCC SHRINKFILE TRUNCATEONLY commend what is the space recovered (unallocated space or unused space ?)
- can I ran DBCC SHRINKFILE TRUNCATEONLY in multi-user mode , I mean can I ran it if there is someone using the database ?
- Can i recover some space by simple doing a reindex? What is the command that I can use - DBCC DBREINDEX or DBCC INDEXDEFRAG ?