C# localdb SHRINKDATABASE command from C# code

Question

I'm trying to shrink a LocalDb with Visual Studio 2017 Community. I have a Win7 client windows form application with a small database (~10MB of data) that results into 150MB database size due to LocalDb free space allocation.

I found this answer (Executing Shrink on SQL Server database using command from linq-to-sql) that suggest to use the following code:

context.Database.ExecuteSqlCommand(
    "DBCC SHRINKDATABASE(@file)",
     new SqlParameter("@file", DatabaseTools.Instance.DatabasePathName)
);

DatabaseTools.Instance.DatabasePathName returns the filesystem location of my database from a singleton DatabaseTools class instance.

The code runs, but I keep getting this exception:

System.Data.SqlClient.SqlException: 'Cannot perform a shrinkdatabase operation inside a user transaction. Terminate the transaction and reissue the statement.'

I tried COMMIT before, but no success at all. Any idea on how to effectively shrink database from C# code? Thanks!

Answer 1

As the docs for ExecuteSqlCommand say, "If there isn't an existing local or ambient transaction a new transaction will be used to execute the command.".

This is what's causing your problem, as you cannot call DBCC SHRINKDATABASE in a transaction. Which isn't really surprising, given what it does.

Use the overload that allows you to pass a TransactionalBehavior and specify TransactionalBehavior.DoNotEnsureTransaction:

context.Database.ExecuteSqlCommand(
    TransactionalBehavior.DoNotEnsureTransaction,
    "DBCC SHRINKDATABASE(@file)",
     new SqlParameter("@file", DatabaseTools.Instance.DatabasePathName)
);