Truncate SQL Server Transaction Log

• Download demo project - 4.31 Kb
• Download source - 6.29 Kb

Introduction

This article explains how you can easily truncate a transaction log file without knowing the logical log file name. The code explains a very easy way to truncate the transaction log file by looking into the sysfiles table.

Using the code

Clearing the transaction log involves two steps. First we need to backup the log with the TRUNCATE_ONLY option and on the next step use DBCC SHRINKFILE function to shrink to the desired size.

The below code snippet shows the easy way to establish a connection and backup the log file first and on the next step execute the DBCC SHRINKFILE command to get the targeted size.

Code portion is shown below:

try
{
    SqlConnection conn = new SqlConnection(GetConnectionString());
    conn.Open();
    DataSet ds = new DataSet();
    SqlCommand cmd = new SqlCommand("select name," + 
         "filename from sysfiles WHERE FILEID=2",conn);
    SqlDataAdapter Adaptor = new SqlDataAdapter(cmd);
    Adaptor.Fill(ds);

    string strLogName = ds.Tables[0].Rows[0][0].ToString().Trim();

    cmd.CommandText = GetBKUPSQL();
    cmd.ExecuteNonQuery();

    cmd.CommandText = GetTruncateSQL(strLogName);
    cmd.ExecuteNonQuery();

    MessageBox.Show("success");
}
catch(Exception ex)
{
    string strMessage = ex.Message;
    MessageBox.Show("Error : \n"+strMessage);
}

You can configure the application so that it will store the frequently used database server name, target size and user ID and password in the config file. Config file will look as shown below:

<?xml version="1.0" encoding="Windows-1252"?>
<configuration>
  <appSettings>
    <add key="PWD" value="mypwd1!" />
    <add key="Size" value="2" />
    <add key="SQLServer" value="SQLSRV1" />
    <add key="UserID" value="sa" />
  </appSettings>
</configuration>

As you can see from the first portion of the code snippet, we are using three private functions to get the general stuff.

This includes:

• GetConnectionString()

  This function will return the connection string to connect to the database.

  C#

  private string GetConnectionString()
  {
      StringBuilder strConn = new StringBuilder();
      strConn.Append("data source=").Append(Server.Text.Trim()).Append(";");
      strConn.Append("initial catalog=").Append(Database.Text.Trim()).Append(";");
      strConn.Append("user id=").Append(UserID.Text.Trim()).Append(";");
      strConn.Append("password=").Append(PWD.Text.Trim());
      return strConn.ToString();
  }

• GetBKUPSQL()

  This will return the backup log statement which needs to execute before shrinking the database file.

  C#

  private string GetBKUPSQL()
  {
      StringBuilder strSQL = new StringBuilder();
      strSQL.Append("backup log ");
      strSQL.Append(Database.Text.Trim());
      strSQL.Append(" with truncate_only");
      return strSQL.ToString();
  }

• GetTruncateSQL()

  This will return the DBCC SHRINKFILE command with the required values for the database selected.

  C#

  private string GetTruncateSQL(string strLogName)
  {
      StringBuilder strSQL = new StringBuilder(); 
      strSQL.Append(" dbcc shrinkfile(");
      strSQL.Append(strLogName);
      strSQL.Append(",");
      strSQL.Append(txtSize.Text.Trim());
      strSQL.Append(")");
      return strSQL.ToString();
  }

Additional Note

This code doesn't contain any input validation as this is supposed to be used by persons with some knowledge of how each type works.

History

• August.15.2005 - Version 0.9.