Click here to Skip to main content
13,457,438 members
Click here to Skip to main content
Add your own
alternative version


47 bookmarked
Posted 19 Aug 2005

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


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:

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

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

    cmd.CommandText = GetBKUPSQL();

    cmd.CommandText = GetTruncateSQL(strLogName);

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"?>
    <add key="PWD" value="mypwd1!" />
    <add key="Size" value="2" />
    <add key="SQLServer" value="SQLSRV1" />
    <add key="UserID" value="sa" />

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(";");
        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(" 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(");
        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.


  • August.15.2005 - Version 0.9.


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


About the Author

Software Developer
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralSimple, Precise and Very Useful Article Pin
irshadmohideen16-Aug-08 4:02
memberirshadmohideen16-Aug-08 4:02 
Rantrealy useful Pin
SyntaxCheck31-Jul-08 5:24
memberSyntaxCheck31-Jul-08 5:24 
GeneralYou made life easier, thanks! Pin
Abishek Bellamkonda17-Oct-07 15:12
memberAbishek Bellamkonda17-Oct-07 15:12 
GeneralWorked Right Out of the Box Pin
jimkomara14-Jun-07 4:07
memberjimkomara14-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
memberGirish Chandra29-May-07 1:11 
GeneralNeed Help Pin
Suresh Kumar.A30-Oct-06 0:20
memberSuresh 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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.180322.1 | Last Updated 19 Aug 2005
Article Copyright 2005 by lintom
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid