Shrink a SQL Server database using the linq-to-sql command
I am looking for a way to clear transaction logs; in particular, I want to compress the logs. I know there are bad reasons for this, but in this case it is for a good reason.
I ran this in SQL Server Management:
DBCC SHRINKFILE(DBName_log)
DBCC SHRINKFILE(DBName)
This does what I need. Now I want to execute this command from code using Linq-To-SQL, something like this:
using (MyDC TheDC = new MyDC())
{
TheDC.ExecuteCommand(....);
}
What command do I need to send to perform both of these actions on the database?
Thank.
source to share
Yours DbContext
provides a System.Data.Entity.Database
suggesting method ExecuteSqlCommand()
that has a couple of overloads.
Here's the documentation from the MSDN article.
Runs the specified DDL / DML command against the database. As with any API that accepts SQL, it is important to parameterize any user input to protect against an SQL injection attack. You can include parameter holders in the SQL query string and then supply the parameter values as additional arguments. Any parameter values you supply will be automatically converted to a DbParameter.
According to your needs, I would use the following:
context.Database.ExecuteSqlCommand("DBCC SHRINKFILE(DBName_log)" ... );
The document also explains how to bind a parameter, which for performance I highly recommend you do whenever you run anonymous queries against SQL Server or Oracle.
Alternatively, you can also create a DbParameter and pass it to SqlQuery. This allows named parameters to be used in the SQL query string.
Again, as per your requirement:
context.Database.ExecuteSqlCommand(
"DBCC SHRINKFILE(@file)",
new SqlParameter("@file", DBName_log)
);
source to share