这几天做测试工作,事务很多,日志很大硬盘很快没空间了,把缩小数据库日志的存储过程封装成一个小工具;这个是压缩日志的代码;觉得手工处理日志麻烦的可以考虑用这个工具试试.
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 版本(源码)
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 版本(源码)
本文转自suifei博客园博客，原文链接：http://www.cnblogs.com/Chinasf/archive/2005/05/20/159553.html，如需转载请自行联系原作者
