• What is the diference between unallocated space and unused space

    not sure if this is entirely accurate but here goes - unused is allocated space that has not been used by the objects yet - this can't be shrunk using a shrinkfile.

    - In the event of running DBCC SHRINKFILE TRUNCATEONLY commend what is the space recovered (unallocated space or unused space ?)

    unallocated - but not necessarilly all of it (in some cases none) - if you have allocated space (might be unused) at then end of the file then you won't reclaim any space back

    the most you can reclaim from your database is 43Mb - but the next time you rdatabase grows due to autogrow 10% (if thats what you have it at) then it wuill grow by 46Mb - so don't run the shrink!!!

    - can I ran DBCC SHRINKFILE TRUNCATEONLY in multi-user mode , I mean can I ran it if there is someone using the database ?

    you can run it if other users are operating.

    - Can i recover some space by simple doing a reindex? What is the command that I can use - DBCC DBREINDEX or DBCC INDEXDEFRAG ?

    the re-index or defrag operations will consume more space during the excercise and then de-allocate the space (but your file may have grown during the operation)

    INDEXDEFRAG can be used while users are on the system (sort of) and won't really claim you any space back - it'll just make the indexes run better - t-log useage is pretty small as it's many operations not one big drop and rebuild

    REINDEX will create new indexes and drop the old ones - (beware you need space to hold 2 copies of the index + working space + space in your t-log file to do this) but you should reduce the index size (depending on the fill factor you specify)

    MVDBA