Click here to Skip to main content
15,868,292 members
Articles / Database Development / SQL Server
Article

Truncate SQL Server Transaction Log

Rate me:
Please Sign up or sign in to vote.
4.21/5 (19 votes)
19 Aug 20051 min read 125.8K   1.5K   47   6
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:

C#
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
<?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.

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


Written By
Software Developer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralSimple, Precise and Very Useful Article Pin
irshadmohideen16-Aug-08 4:02
irshadmohideen16-Aug-08 4:02 
Rantrealy useful Pin
SyntaxCheck31-Jul-08 5:24
SyntaxCheck31-Jul-08 5:24 
GeneralYou made life easier, thanks! Pin
Abi Bellamkonda17-Oct-07 15:12
Abi Bellamkonda17-Oct-07 15:12 
GeneralWorked Right Out of the Box Pin
jimkomara14-Jun-07 4:07
jimkomara14-Jun-07 4:07 
This program really helped me get out of trouble with my ballooning log file. Thank you very much! now I will read through the code and improve my C# skills.

Jim Komara

GeneralNice work Pin
Girish Chandra29-May-07 1:11
Girish Chandra29-May-07 1:11 
GeneralNeed Help Pin
Suresh Kumar.A30-Oct-06 0:20
Suresh Kumar.A30-Oct-06 0:20 

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

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