Database compression from .net code

Question

4.00/5 (1 vote)

See more:

Hi,

I would like to Compress the database in SQL Server 2012 using .net code.

First I tried with the following SQL queries:

SQL

DBCC SHRINKDATABASE(DatabaseName);

GO
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (DatabaseName_log, 5)
GO
ALTER DATABASE DatabaseName SET RECOVERY FULL
GO

When I tries to execute the above queries in SQL server directly, it works properly; by executing in Stored procedure as well.

But when I tried to call the stored procedure from .net code, it gives e the exception of {"Cannot perform a shrinkdatabase operation inside a user transaction. Terminate the transaction and reissue the statement."}.

Kindly guide me if there is any problem with the queries, or if there is another solution to Compress the database from .net code.

Thanks.

Posted 28-Sep-14 22:19pm

Mukul Lad

Updated 28-Sep-14 22:21pm

v2

Add a Solution

Comments

Sinisa Hajnal 29-Sep-14 4:49am

Post your code if that is the problem. We cannot solve anything without it. I'd say you have BeginTransaction somewhere in the code :)

Mukul Lad 29-Sep-14 5:54am

I have done it using Entity framework. Here, it is the code.

using (EntityName db = new EntityName())
{
db.spCompressDatabase("DB Name");
}

Sinisa Hajnal 29-Sep-14 6:29am

Well then, you have to delve into EntityName and find out where it opens the transaction :)

Mukul Lad 29-Sep-14 8:15am

Sorry, but I am not getting what you want to inform.

Kornfeld Eliyahu Peter 29-Sep-14 6:37am

Use ExecuteNonQuery - http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

Mukul Lad 29-Sep-14 8:15am

I tried this, it doesn't give error, but it does not compress the database or log file.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mukul Lad · Accepted Answer · 2014-09-29T02:17:00

Solution 1

During the analysis, I found that the database compress feature is only available in Enterprise version of SQL server. Is it so?
Is it not available in express edition.

If anybody know, kindly confirm.

Thanks.

Posted 29-Sep-14 2:17am

Mukul Lad

Updated 29-Sep-14 2:18am

v2

Comments

Kornfeld Eliyahu Peter 29-Sep-14 8:20am

No.
But it need proper admin permissions to run...

Mukul Lad 29-Sep-14 9:21am

Can you please guide how to do so?
I am not able find it.

And, Please check the following URL:
http://msdn.microsoft.com/en-IN/library/cc645993.aspx

I found in this URL about express and enterprise edition.