I am new to SQL Server and don't fully understand its architecture. I've looked around, Googled, asked around but found no simple (understandable) answer to the problem. I'm not even sure if it is problem in technical terms or just the default behavior, but in terms of my business operation, it is a big problem.
I have acquired SQL Server 2008 R2 [from the developer who doesn't work here anymore], with a database of physical storage size of 2 TB. I've created a database, say TEST with
TEST.mdfdefault at 10 MB, 10% growth and max size: unlimited. There are 40 files comprising the
TEST_part40with same configuration as
The log file -
TEST_log.ldffile also has same configuration as
The database handles millions of rows in daily basis, with lots and lots of insert, delete, update, drop etc. operations.
I am running out of space. While trying to shrink the database, I saw that the
TEST.mdfcould be shrunk by 41% and
Is 41% and 99% of recoverable space actually taken by garbage, un-used data? If so can I selectively remove/purge (if not all than most of ) the 41% and 99% of data?
Is there any temporary or otherwise structure/objects created by the SQL Server itself, which I can safely delete/purge?