这几天做测试工作,事务很多,日志很大硬盘很快没空间了,把缩小数据库日志的存储过程封装成一个小工具;这个是压缩日志的代码;觉得手工处理日志麻烦的可以考虑用这个工具试试.
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
}
}
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,如需转载请自行联系原作者