Hey I have some temp tables which hold data and then gets deleted after. I was just checking the size of these tables and eventough they currently have 0 records in them they are very large in size.
SQL 2005 DB
How Can I reduce the size of these tables ?
Since there are zero rows, the easiest way to do this is to drop and recreate the table.
You can use dbcc shrinkdatabase or dbcc shrinkfile, however you can't shrink a single table unless it is stored in its own a file group.
This "reserved space" that you want to remove can be used for the next new data added to that table, without the database needing to reserve any the next time you insert data. If you remove the space and then insert many rows, the need to allocate this back again will add a little more overhead to the insertion of that new data.
I would not do anything with this empty unused reserved space unless you are really short of disk space. If you need more disk space then it might be better to get more than to constantly need to micromanage this table's disk space.
The SQL Server won't actually release the page until later. Do an index rebuild (not a defrag) on the table, and that should release the free space back to the database.