Cannot shrink virutal log file

lculley

I have a virtual db restore that I am testing with updates and inserts of a million records. Each time I run a transaction, the virtual log grows 1 GB. I have tried backing up the virtual log (successful) but when I try to shrink the file using DBCC shrinkfile (db_log,0), I am unable to shrink the red gate virtual log (the log size stays the same). How can I shrink the virtual log file?

javen

Hi,

If the database you will be using is write intensive (as your test case suggests), you should look at SQL Storage Compress instead. This is designed for write intensive applications. SQL Virtual Restore is better suited to object level recovery, database validation (DBCC CHECKDB) and read intensive applications. I would look at SQL Storage Compress if your test is representative of what you would use the product for.

lculley

The issue that I am trying to solve is the time it takes to restore a red gate backup to development and qa environments and the resulting footprint of that operation. The intention is to allow qa and dev to be able to test applications with the understanding that this product is not for a performance environment.

SQL Storage Compress will only shrink existing production databases and will not help with the restore time of dev/qa environments. I would think that the virutal restore product would allow us to manage the virtual db files in some way and if not, I would hope this would be a future feature request. Also, it would be nice if the tool had some native copying ability of compressed files like the sql backup software has. I had to roll out my own copy code to implement this solution.

Still looks like a very promising product thus far though.

Andrew Hodge

I found you can shrink a hyperbac log file using the hyperutil.exe command line tool after you have done the shrink db option. (it works with storage compress so think it should work with virtual restore).

C:\Program Files (x86)\Red Gate\HyperBac\bin\HyperUtil.exe
with params
/s "<pathtolog>\.ldfx"
/d

/d is defrag but requires write access to the file.

The downside is that it hyperutil needs write access to the file to do this so you have to take the database offline when performing the action.