C # localdb SHRINKDATABASE command from C # code
I am trying to compress LocalDb using Visual Studio 2017 Community. I have a Win7 Windows client application with a small database (~ 10MB data) which results in a database size of 150MB due to the allocation of free space in LocalDb.
I found this answer ( Performing shrinking a SQL Server database using linq-to-sql command ) which suggests using the following code:
context.Database.ExecuteSqlCommand(
"DBCC SHRINKDATABASE(@file)",
new SqlParameter("@file", DatabaseTools.Instance.DatabasePathName)
);
DatabaseTools.Instance.DatabasePathName
returns the file system location of my database from an instance of the Singleton DatabaseTools class.
The code is executing, but I am getting this exception:
System.Data.SqlClient.SqlException: "Unable to execute shrinkdatabase operation within a user transaction. End the transaction and reissue the statement. '
I have tried COMMIT
before but with no success at all. Any idea on how to compact the database efficiently from C # code? Thank!
source to share
As the docs say for ExecuteSqlCommand
: "If no existing local or external transaction exists, a new transaction will be used to execute the command."
This is what is causing your problem as you cannot call DBCC SHRINKDATABASE
in a transaction. Which is not surprising given what he does.
Use an 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)
);
source to share