I noticed that some TSQL operations that I perform regularly have a virtual memory cost.
For instance, if I create a new non-indexed column B, copy data from non-indexed column A to column B, then drop column A, then the database .mdf size goes up even though there is no new data in it.
Another is if I run ALTER INDEX ALL ON Table1 REBUILD for all tables in a database, again I see the overall size of the .mdf go up. I just ran such an operation on one database and its size went up from 193 MB to 258 MB, which is about 33%!!!
There may be very valid reasons why this happens but is there a way to recapture the memory?
I think you are referring to disk space. In either operation - SQL Server needs available space in the file to perform the operation. The first operation needs that additional space while the data is in both tables - and the second operation needs the additional space as the index is being created.
The problem you are running into is that you then subsequently shrink the file/database. You should always plan to have additional space available in the files to allow for growth and these types of maintenance operations.
The problem you are running into is that you then subsequently shrink the file/database.
It's the opposite. My problem is that the file size has increased in order to perform its operations but that it did not subsequently shrink back after the operations. Reading the database properties, it appears that Auto Shrink = TRUE and there is currently 112 MB of available space in the 258 MB envelope. So my question would be what will trigger a "database shrink"? I tried a DBCC SHRINKDATABASE('MyDB', 10) with no effect.
Strongly advise you DO NOT SHRINK your data files. There are numerous side effects, all of them bad. For reference:
In case you find Brent more compellingly, he has a few more links included:
As Jeff pointed out, the file growth you saw is to be expected, if your data files do not have enough free space to perform the operations you're running (adding column, adding data to column, adding new index, or rebuilding indexes). The best approach is to not shrink your data files after these operations, and NEVER turn on Auto-Shrink.
Your current database size is tiny, and if your overall free disk space is so small that you're shrinking the DB, you need to move the database to a larger disk. Shrinking regularly is only going to cause you more problems, and not solve anything.
Thanks for the added precision. I actually never do DBCC SHRINKDATABASE but I thought I'd try it to see if it had any effect on my observations. But now, in retrospect, I understand what Jeff meant. And I will definitely set Auto Shrink = False. Thanks to both of you !
You should also review the individual file sizes. I am guessing - but you are probably seeing the amount of free space as a combination of the data and log file.
And just to be clear - 256MB is tiny. Unless you are deploying to a very small form factor it is insignificant and not something you need to worry about in regards to recovering space.