CODEWITHSUNDEEP
c#sqldatabasevisual-studio
jeffmcnd
jeffmcnd

Reputation: 582

Managing SQL Log File Size

I've got a project in Visual Studio 2012 that uses one database with files that we'll call

db.mdf, db_log.ldf, db_db_0.ldf (Not sure where this one comes from)

What I'm trying to do is make sure the .ldf file size stays small. I've been told to make a backup of the database (I'm assuming the .mdf) and then recreate the .ldf file from that point in time. So, any transactions that came before performing these steps are gone and the .ldf file is smaller and more manageable.

Things I've tried:

string detachQuery = String.Format("sp_detach_db @dbname='{0}'", _connection.Database);

using (var cmd = new SqlCommand(detachQuery, _connection))
{
    try
    {
        cmd.ExecuteNonQuery();
    }
    catch (Exception e) { }
}

The idea here is to detach the database from the .ldf file after having backed up the database, and then reattach it to a new .ldf file, though I'm not sure how to create the new .ldf file. The problem with this is that I get the following exception:

"Cannot detach the database because it is currently in use."

My question is, in C#, how is this done? My secondary question is: Is there a better way to achieve the desired effect of a more manageable log file?

Upvotes: 0

Views: 465

Answers (1)

StingyJack
StingyJack

Reputation: 19459

With the Detach method, you are not taking a correct approach. If you want to keep the log file small, you need to run transaction log backups periodically (every 15 minutes), and sometimes shrink the file.

If you don't care about point in time recovery (I would assume, since you are considering detaching a db file), just turn on Simple Recovery mode in the database properties.

An overview of recovery modes can be found here. Figure out which one is the right one, but c# code is not required to solve this problem at all. http://technet.microsoft.com/en-us/library/ms189275(v=sql.105).aspx

Upvotes: 3

Related Questions