I am trying to free up HD space on our server. For many years, we never instituted an archiving strategy and have finally gotten around to do so. I have cut our db down from approximately 72 million rows (42GB) to approximately 27 million rows (31GB). However, I am having trouble re-capturing HD space that I know is not being used in tables. No issues with the transaction log, I'm keeping that in check as I delete records.
Here are my questions:
1) Every time I run DBCC SHRINKFILE (data file only) it tells me "Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed."
2) One table in particular has 177MB worth of data but has an index of 15GB. I assume that I either need to reindex this table (from which I have deleted millions of rows) or I have a poor index design (or both). Even with the optimization jobs, it is not giving me space back.
3) Is there anything else that I should be doing as a normal course of action (both now and on-going)?
Start Free Trial
