I'm pretty new to DB. Running 2008 MS SQL on 2008 R2 64 bit.
I'm running a security application that generates a lot of events. I have gone in over the past couple days and deleted large numbers of old events in an effort to reclaim space on the D: drive where the SQL DB for this app resides. I did this throught the app itself... through its GUI front end.
I'm not having any impact. There is not a file on the same drive where the deleted events are being directed to. If all these events are going away, why does my used space on the drive not shrink?
I came at it from another tack, creating sql queries to delete old events from the same app. I did this through queries in the SQL mgmt studio. The tasks (multiple) completed successfully, and the report on the # of rows 'affected' showed that it was a substantial number.
But, same result. No decrease in the windows drive space being used. Any tips?
SQL Server is not Access. When you delete rows in a SQL Server database, the datafiles do not shrink down. If the file(s) are overallocated, you can shrink them, but it is a manual process, and it can take a lot of time, depending on various factors. To see if the files are overallocated, run this:
select file_id, fileproperty(name, 'SpaceUsed')/128, size/128
This will show you the amount of space used in each file, and the size of that file in MB. If one of them is substantially empty, you can run
dbcc shrinkfile (file_id from above, target size in MB)
You may not want to do this to your log file (usually file_id 2), as that space is very likely to get reallocated the next time there is a large update, import, or deletion of rows.