Truncate Log File in SQL Server 2005

• Download Truncate Log File in SQL Server 2005 - 4.9 KB

Introduction

People who started using SQL 2005 and later versions may sometimes encounter the problem of increasing the size of the Log file.

The log file is the storage of all the transactions done in the Database, and hence it contains details about each and every action done.

Many developers who are not so familiar with database administration may get confused how to manage this huge size and may wonder what the impact can be on their production environment.

Here I have written a small tool that can be used to trim (shrink the log file to the minimum level), I hope it helps.

The Purpose of the Log File

When SQL Server is functioning and operating, the database engine keeps track of almost every change that takes place within the database by making entries into the transaction log so that it can be used later if needed.

The location of the SQL Server transaction log is configured at the same time the database is created. When creating a database, the location of the SQL Server transaction log is specified as well as other options associated with the transaction log.

What is Recovery Model

Recovery model is the way you want the transactions to be registered into the log file. Since log files include all the transactions in the database, they can be used for recovery. There are 3 types of recovery, here they are, and what they do.

Simple Recovery Model

The simple recovery model is just that: simple. In this approach, SQL Server maintains only a minimal amount of information in the transaction log. SQL Server truncates the transaction log each time the database reaches a transaction checkpoint, leaving no log entries for disaster recovery purposes.

In databases using the simple recovery model, you may restore full or differential backups only.

Full Recovery Model

In the event of a database failure, you have the most flexibility restoring databases using the full recovery model. In addition to preserving data modifications stored in the transaction log, the full recovery model allows you to restore a database to a specific point in time.

The bulk-logged recovery model is a special-purpose model that works in a similar manner to the full recovery model. The only difference is in the way it handles bulk data modification operations. The bulk-logged model records these operations in the transaction log using a technical known as minimal logging. This saves significantly on processing time, but prevents you from using the point-in-time restore option.

The Code

The core of the tool lays below in this function.

This function as you can see, takes 3 parameters, the name of the database in concern, the server name, and the new desirable size of the database, which I am assuming to be 0 (it would be 128KB) as minimum.

• Here it connects to the database
• Gets the size of the log file
• Changes the recovery model to Simple
• Issues a checkpoint against the database to write the records from the transaction log to the database
• Empties the Log file, but not the size of the file (it means it deletes the data from the file increasing the blank empty space in the file) because as you may know the size of the log files and the data files do not express exactly the size of the data within, usually they have an empty space with increment policy.
• Reduces the size of the file.
• Gets the new size and displays a small report about the results.

private static void ShrinkDatabase(string Database, string ServerName, int NewSize)
{
    int OldSize;
    SqlConnection Cnn = null;
    try
    {
        Cnn =
        new SqlConnection(string.Format
		("trusted_connection=true; database={0};server={1}", Database,
                  ServerName));
        Cnn.Open();

        SqlCommand Cmm = new SqlCommand("", Cnn);
        Cmm.CommandType = CommandType.Text;
        Cmm.CommandText = string.Format("SELECT [size] 
				FROM sysfiles WHERE name LIKE '%LOG%'");
        OldSize = (int) Cmm.ExecuteScalar();
        Cmm.CommandText = string.Format("ALTER DATABASE {0} 
				SET RECOVERY SIMPLE", Database);
        Cmm.ExecuteNonQuery();
        Cmm.CommandText = string.Format("CHECKPOINT");
        Cmm.ExecuteNonQuery();//issue a checkpoint against the database to 
			//write the records from the transaction log to the database.
        Cmm.CommandText = string.Format("BACKUP LOG [{0}] WITH NO_LOG", Database);
        Cmm.ExecuteNonQuery();	//This empties the log file but does not 
					//reduce its size.
        Cmm.CommandText = string.Format("SELECT Name FROM sysfiles 
			WHERE name LIKE '%LOG%'");
        Database = (string) Cmm.ExecuteScalar();
        Cmm.CommandText = string.Format("DBCC SHRINKFILE ({0}, {1})", 
						Database, NewSize);
        Cmm.ExecuteNonQuery();//reduce the size of the individual LDF file(s).
        Cmm.CommandType = System.Data.CommandType.Text;
        Cmm.CommandText = "SELECT [size] FROM sysfiles WHERE name LIKE '%LOG%'";
        NewSize = (int) Cmm.ExecuteScalar();
        Console.WriteLine(" The Old Size was {0}KB \n 
		The New Size is {1}KB \n The Logfile has shrinked with 
		{2}KB \n and you gained {3}% of the file size",OldSize , 
		NewSize , OldSize - NewSize,
                  (100-(NewSize *100.00/ OldSize)).ToString("0.00")); 
     }
     catch(Exception Ex)
     {
         Console.WriteLine(Ex.ToString () ); 
     }
     finally
     {
         if (Cnn != null)
         {
             Cnn.Close();
             Cnn.Dispose();
         } 
     }
 } 

One helpful function is the one below that helps you to display the database and their sizes, and that can simply be done by calling the system stored procedure “sp_databases”, However, our concern is the log file, not the data file and therefore we need to inquire the size of the log for each database.

private static SortedList<int, string> DisplayDatabases(string Servername)
{
    SqlConnection Cnn = null; SqlCommand Cmm= null;
    SortedList<int, string> Result = new SortedList<int, string>();
    try
    {
        Cnn = new SqlConnection( string.Format
           ( "trusted_connection=true; server={0}", Servername));
        SqlConnection CnnLogSize=new SqlConnection( string.Format
           ( "trusted_connection=true; server={0}", Servername));
        Cmm = new SqlCommand("sp_databases", Cnn);
        Cmm.CommandType = CommandType.StoredProcedure;
        Cnn.Open();
        SqlDataReader sqlDataReader = Cmm.ExecuteReader
               (CommandBehavior.CloseConnection);
        int Index=0;
        Console.Write("#".PadRight(4));
        Console.Write("Name".PadRight(40));
        Console.Write("\t");
        Console.Write("Size".PadRight(20));
        Console.WriteLine("Log Size");
        Console.WriteLine
   ("--------------------------------------------------------------------");
        string sqlQueryLogSize = null;
        SqlCommand cmdLogSize = new SqlCommand();
        if (sqlDataReader != null)
            while (sqlDataReader.Read ())
            {
                sqlQueryLogSize = string.Format("use {0}
           SELECT [size] FROM sysfiles where name like
           '%{0}%' and name like '%log%'", sqlDataReader.GetString(0));
                cmdLogSize.CommandText = sqlQueryLogSize;
                cmdLogSize.Connection = CnnLogSize ;
                CnnLogSize.Open();
                var LogFileSize = cmdLogSize.ExecuteScalar();
                CnnLogSize.Close();
                Console.Write(Index.ToString ().PadRight (4) );
                Console.Write(sqlDataReader.GetString(0).PadRight (40));
                Console.Write("\t");
                Console.Write(sqlDataReader.GetInt32 (1).ToString ().PadRight (20));
                if (LogFileSize != null) Console.Write(LogFileSize.ToString());
                    Console.WriteLine();
                Result.Add(Index++, sqlDataReader.GetString(0));
           }

       if (sqlDataReader != null)
       {
            sqlDataReader.Close();
            sqlDataReader.Dispose();
       }
       cmdLogSize.Dispose();
       CnnLogSize.Dispose();
}
catch (Exception Ex)
{
    Console.WriteLine(Ex.ToString());
}

finally
{
    if (Cnn != null)
    {
        Cnn.Close();
        Cnn.Dispose();
    }
}
Console.WriteLine
   ("--------------------------------------------------------------------");
Console.WriteLine("Please Choose a number from 0 to {0},
   Hit Ctrl+C to exit", Result.Count - 1);
return Result;
}

The main function here will just call the above functions and display some messages for the user to decide which database he/she wants to truncate the log file of.

static void Main(string[] args)
{
    string ServerName;
    if (args.Length == 0 )ServerName = "Localhost";
    else ServerName = args[0] ;
    A:
    SortedList<int, string> L = DisplayDatabases(ServerName);
    string Input = Console.ReadLine();
    int IntInput=0;
    bool isInt= int.TryParse(Input, out IntInput);

    while (!(IntInput > -1 && IntInput < L.Count && isInt))
    {
        L = DisplayDatabases(ServerName );
        Input = Console.ReadLine();
        isInt = int.TryParse(Input, out IntInput);
    }
    ShrinkDatabase(L[IntInput], ServerName, 0);
    Console.WriteLine("------------------------------");
    Console.WriteLine("Do you want to Shrink another? hit Y if Yes, Otherwise N");
    Input = Console.ReadLine();
    if (Input != null) if (Input.ToLower() =="y" ) goto A;
}

This article is mainly based on:

Managing SQL Server 2000 Transaction Log Growth
Written By: Jeremy Kadlec -- 7/10/2006
http://www.mssqltips.com/tip.asp?tip=950 

Useful Links

• truncate_log_SQL_server.aspx
• http://www.databasedesign-resource.com/sql-server-transaction-log.html
• http://stackoverflow.com/questions/480897/how-can-i-manage-sql-server-log-size