Click here to Skip to main content
Click here to Skip to main content

Truncate SQL Server Transaction Log

, 19 Aug 2005
Rate this:
Please Sign up or sign in to vote.
An easy way to truncate Transaction Log files to your desired size.

Clearing Transaction Log

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.

    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.

    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.

    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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

lintom
Software Developer
United States United States
No Biography provided

Comments and Discussions

 
GeneralSimple, Precise and Very Useful Article Pinmemberirshadmohideen16-Aug-08 4:02 
Rantrealy useful PinmemberSyntaxCheck31-Jul-08 5:24 
GeneralYou made life easier, thanks! PinmemberAbishek Bellamkonda17-Oct-07 15:12 
GeneralWorked Right Out of the Box Pinmemberjimkomara14-Jun-07 4:07 
GeneralNice work PinmemberGirish Chandra29-May-07 1:11 
A nice an concise piece of code. Just what I needed for a long time.
 
Thanks buddy.
GeneralNeed Help PinmemberSuresh Kumar.A30-Oct-06 0:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140821.2 | Last Updated 19 Aug 2005
Article Copyright 2005 by lintom
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid