缩小数据库日志的工具源码

这几天做测试工作,事务很多,日志很大硬盘很快没空间了,把缩小数据库日志的存储过程封装成一个小工具;这个是压缩日志的代码;觉得手工处理日志麻烦的可以考虑用这个工具试试.

 

using  System;
using  System.Data;
using  System.Data.SqlClient;

namespace  WebTruncateLog
{
    /**//// <summary>
    /// DataAccess 的摘要说明。
    /// </summary>
    public class DataAccess
    {

        私有变量#region 私有变量
        private const string sqlMasterDatabase = "master";
        private string sqlServerName = "localhost";
        private string sqlUserId = "sa";
        private string sqlUserPassword = "";
        private string sqlDefaultDatabase = "master";
        private bool active = false;
        #endregion

        组件对象#region 组件对象
        private SqlConnection sqlConn ;
        #endregion
        
        构造#region 构造
        /**//// <summary>
        /// 构造函数
        /// </summary>
        public DataAccess()
        {
            sqlConn = new SqlConnection();
            active = false;
        }

        #endregion

        方法#region 方法


        /**//// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="ConnectionString"></param>
        /// <returns></returns>
        public bool Connect(string ConnectionString)
        {
            sqlConn.ConnectionString = ConnectionString;
            try
            {
                sqlConn.Open();
                this.active = true;
                return true;
            }
            catch
            {
                this.active = false;
                return false;
            }
        }

        /**//// <summary>
        /// 连接数据库
        /// </summary>
        /// <returns></returns>
        public bool Connect()
        {
            sqlConn.ConnectionString = this.SqlConnectionString;
            try
            {
                sqlConn.Open();
                this.active = true;
                return true;
            }
            catch
            {
                this.active = false;
                return false;
            }
        }

        /**//// <summary>
        /// 关闭连接
        /// </summary>
        /// <returns></returns>
        public bool Close()
        {
            if(sqlConn.State!=System.Data.ConnectionState.Closed)
            {
                try
                {
                    sqlConn.Close();
                    this.active = !this.active;
                    return true;
                }
                catch
                {
                    return false;
                }
            }
            else return false;
        }

        /**//// <summary>
        /// 执行无记录返回sql
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public bool ExecuteSQL(string strSQL)
        {
            if(!this.active) return false;

            SqlCommand sqlCmd = new SqlCommand(strSQL,sqlConn);
            try
            {
                sqlCmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /**//// <summary>
        /// 执行有记录返回sql
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public DataTable ExecuteSQL(string strSQL,string TableName)
        {
            if(!this.active) return null;

            System.Data.SqlClient.SqlDataAdapter sqlDa = new SqlDataAdapter(strSQL,sqlConn);
            DataSet ds = new DataSet();
            try 
            {
                sqlDa.Fill( ds,TableName );
                return ds.Tables[TableName];
            }
            catch
            {
                return null;
            }
        }

        /**//// <summary>
        /// 切换到master库
        /// </summary>
        /// <returns></returns>
        public bool UseMaster()
        {
            return this.ExecuteSQL("use master");
        }

        /**//// <summary>
        /// 切换到当前库
        /// </summary>
        /// <returns></returns>
        public bool UseDefaultDatabase()
        {
            return this.ExecuteSQL("use " + this.sqlDefaultDatabase);
        }

        /**//// <summary>
        /// 获取当前所有的数据库
        /// </summary>
        /// <returns></returns>
        public DataTable GetDatabase()
        {
            this.UseMaster(); //切换到master库 
            string sql = "";
            sql += "select\n";
            sql += "[name] as 数据库名称, \n";
            sql += "[dbid] as 数据库ID, \n";
            sql += "[crdate] as 创建日期,\n";
            sql += "[cmptlevel] as 兼容级别,\n ";
            sql += "[filename] as 主文件路径,\n";
            sql += "[version] as 内部版本号\n";
            sql += "from sysdatabases\n";
            sql += "order by dbId asc\n";

            return ExecuteSQL(sql,"sysdatabases");
        }

        /**//// <summary>
        /// 获取当前库的物理文件
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysFile()
        {
            this.UseDefaultDatabase(); //切换到当前库库 
            string sql = "";
            sql += "select\n";
            sql += "[fileid] as 文件标识号,\n";
            sql += "Cast (([size]*8/1024) as Varchar) + ' 兆' as 文件大小,\n";
            sql += "[name] as 逻辑名, \n";
            sql += "[filename] as 物理名\n";
            sql += "from sysfiles\n";
            return ExecuteSQL(sql,"sysfiles");
        }

        /**//// <summary>
        /// 执行压缩日志功能
        /// </summary>
        /// <returns></returns>
        public bool ExecuteTruncateLog(int NewLogFileSize)
        {
            string LogFile = "";
            string strDelProc =
                "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Truncate_Log_File]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Truncate_Log_File]";

            DataTable dt = GetSysFile();
            foreach(DataRow dr in dt.Rows)
            {
                if(dr["物理名"].ToString().ToLower().LastIndexOf(".ldf")!=-1)
                {
                    LogFile = dr["逻辑名"].ToString().Trim();
                    break;
                }
            }

            string strCrtProc = String.Format(
                "CREATE PROCEDURE [Truncate_Log_File] AS\n"
                + "SET NOCOUNT ON\n"
                + "DECLARE @LogicalFileName sysname,@MaxMinutes INT,@NewSize INT\n"
                + "SELECT @LogicalFileName ='{0}',@MaxMinutes = 10,@NewSize = {1}\n"
                + "DECLARE @OriginalSize int\n"
                + "SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName\n"
                + "DECLARE @Counter INT,@StartTime DATETIME,@TruncLog VARCHAR(255)\n"
                + "WHILE @OriginalSize*8/1024>@Newsize\n"
                + "BEGIN\n"
                + "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DummyTrans]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)\n"
                + "drop table [dbo].[DummyTrans]\n"
                + "CREATE TABLE DummyTrans\n"
                + "(DummyColumn char (8000) not null)\n"
                + "SELECT @StartTime = GETDATE(),@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'\n"
                + "DBCC SHRINKFILE (@LogicalFileName, @NewSize)\n"
                + "EXEC (@TruncLog)\n"
                + "WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE())\n"
                + "AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)\n"
                + "AND (@OriginalSize * 8 /1024) > @NewSize\n"
                + "BEGIN\n"
                + "SELECT @Counter = 0\n"
                + "WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 5000))\n"
                + "BEGIN\n"
                + "INSERT DummyTrans valueS ('Fill Log')\n"
                + "DELETE DummyTrans\n"
                + "SELECT @Counter = @Counter + 1\n"
                + "END\n"
                + "EXEC (@TruncLog)\n"
                + "END\n"
                + "SELECT @OriginalSize=size FROM sysfiles WHERE name = @LogicalFileName\n"
                + "DROP TABLE DummyTrans\n"
                + "END\n",LogFile,NewLogFileSize);

            string strExecProc = "exec Truncate_Log_File";

            if(LogFile.Length>0&&this.ExecuteSQL(strDelProc))
            {
                if(this.ExecuteSQL(strCrtProc))
                {
                    return this.ExecuteSQL(strExecProc);
                }
                else
                {
                    return false;
                }
            }
            else
                return false;
        }

        #endregion

        属性#region 属性

        public bool Active
        {
            get
            {
                return this.active;
            }
            set
            {
                if(value)
                {
                    this.Connect();
                }
                else
                {
                    this.Close();
                }
            }
        }

        public string SqlConnectionString 
        {
            get
            {
                return
                    "Database=" + this.sqlDefaultDatabase +
                    ";Server=" + this.sqlServerName +
                    ";User ID="  + this.sqlUserId +
                    ";Password=" + this.sqlUserPassword + ";";
            }
        }

        public string SqlServerName
        {
            get
            {
                return this.sqlServerName;
            }
            set
            {
                this.sqlServerName = value;
            }
        }

        public string SqlUserId
        {
            get
            {
                return this.sqlUserId;
            }
            set
            {
                this.sqlUserId = value;
            }
        }

        public string SqlUserPassword
        {
            get
            {
                return this.sqlUserPassword;
            }
            set
            {
                this.sqlUserPassword = value;
            }
        }

        public string SqlDefaultDatabase
        {
            get
            {
                return this.sqlDefaultDatabase;
            }
            set
            {
                this.sqlDefaultDatabase = value.Replace("'","''");
            }
        }
        #endregion
    }
}

windows Forms 版本(源码)
asp.net 版本(源码)

转载于:https://www.cnblogs.com/Chinasf/archive/2005/05/20/159553.html