缩小数据库日志的工具源码-阿里云开发者社区

开发者社区> 开发与运维> 正文

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

简介:

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

None.gifusing System;
None.gifusing System.Data;
None.gifusing System.Data.SqlClient;
None.gif
None.gifnamespace WebTruncateLog
ExpandedBlockStart.gifContractedBlock.gifdot.gif{
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// DataAccess 的摘要说明。
ExpandedSubBlockEnd.gif
/// </summary>

InBlock.gifpublic class DataAccess
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif私有变量#region 私有变量
InBlock.gifprivate const string sqlMasterDatabase = "master";
InBlock.gifprivate string sqlServerName = "localhost";
InBlock.gifprivate string sqlUserId = "sa";
InBlock.gifprivate string sqlUserPassword = "";
InBlock.gifprivate string sqlDefaultDatabase = "master";
InBlock.gifprivate bool active = false;
ExpandedSubBlockEnd.gif#endregion

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif组件对象#region 组件对象
InBlock.gifprivate SqlConnection sqlConn ;
ExpandedSubBlockEnd.gif#endregion

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif构造#region 构造
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// 构造函数
ExpandedSubBlockEnd.gif
/// </summary>

InBlock.gifpublic DataAccess()
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gif sqlConn = new SqlConnection();
InBlock.gif active = false;
ExpandedSubBlockEnd.gif }

InBlock.gif
ExpandedSubBlockEnd.gif#endregion

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif方法#region 方法
InBlock.gif
InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// 连接数据库
InBlock.gif
/// </summary>
InBlock.gif
/// <param name="ConnectionString"></param>
ExpandedSubBlockEnd.gif
/// <returns></returns>

InBlock.gifpublic bool Connect(string ConnectionString)
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gif sqlConn.ConnectionString = ConnectionString;
InBlock.giftry
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gif sqlConn.Open();
InBlock.gifthis.active = true;
InBlock.gifreturn true;
ExpandedSubBlockEnd.gif }

InBlock.gifcatch
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifthis.active = false;
InBlock.gifreturn false;
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// 连接数据库
InBlock.gif
/// </summary>
ExpandedSubBlockEnd.gif
/// <returns></returns>

InBlock.gifpublic bool Connect()
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gif sqlConn.ConnectionString = this.SqlConnectionString;
InBlock.giftry
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gif sqlConn.Open();
InBlock.gifthis.active = true;
InBlock.gifreturn true;
ExpandedSubBlockEnd.gif }

InBlock.gifcatch
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifthis.active = false;
InBlock.gifreturn false;
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// 关闭连接
InBlock.gif
/// </summary>
ExpandedSubBlockEnd.gif
/// <returns></returns>

InBlock.gifpublic bool Close()
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifif(sqlConn.State!=System.Data.ConnectionState.Closed)
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.giftry
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gif sqlConn.Close();
InBlock.gifthis.active = !this.active;
InBlock.gifreturn true;
ExpandedSubBlockEnd.gif }

InBlock.gifcatch
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn false;
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

InBlock.gifelse return false;
ExpandedSubBlockEnd.gif }

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// 执行无记录返回sql
InBlock.gif
/// </summary>
InBlock.gif
/// <param name="strSQL"></param>
ExpandedSubBlockEnd.gif
/// <returns></returns>

InBlock.gifpublic bool ExecuteSQL(string strSQL)
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifif(!this.active) return false;
InBlock.gif
InBlock.gif SqlCommand sqlCmd = new SqlCommand(strSQL,sqlConn);
InBlock.giftry
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gif sqlCmd.ExecuteNonQuery();
InBlock.gifreturn true;
ExpandedSubBlockEnd.gif }

InBlock.gifcatch
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn false;
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// 执行有记录返回sql
InBlock.gif
/// </summary>
InBlock.gif
/// <param name="strSQL"></param>
InBlock.gif
/// <param name="TableName"></param>
ExpandedSubBlockEnd.gif
/// <returns></returns>

InBlock.gifpublic DataTable ExecuteSQL(string strSQL,string TableName)
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifif(!this.active) return null;
InBlock.gif
InBlock.gif System.Data.SqlClient.SqlDataAdapter sqlDa = new SqlDataAdapter(strSQL,sqlConn);
InBlock.gif DataSet ds = new DataSet();
InBlock.giftry
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gif sqlDa.Fill( ds,TableName );
InBlock.gifreturn ds.Tables[TableName];
ExpandedSubBlockEnd.gif }

InBlock.gifcatch
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn null;
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// 切换到master库
InBlock.gif
/// </summary>
ExpandedSubBlockEnd.gif
/// <returns></returns>

InBlock.gifpublic bool UseMaster()
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn this.ExecuteSQL("use master");
ExpandedSubBlockEnd.gif }

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// 切换到当前库
InBlock.gif
/// </summary>
ExpandedSubBlockEnd.gif
/// <returns></returns>

InBlock.gifpublic bool UseDefaultDatabase()
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn this.ExecuteSQL("use " + this.sqlDefaultDatabase);
ExpandedSubBlockEnd.gif }

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// 获取当前所有的数据库
InBlock.gif
/// </summary>
ExpandedSubBlockEnd.gif
/// <returns></returns>

InBlock.gifpublic DataTable GetDatabase()
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifthis.UseMaster(); //切换到master库
InBlock.gif
string sql = "";
InBlock.gif sql += "select\n";
InBlock.gif sql += "[name] as 数据库名称, \n";
InBlock.gif sql += "[dbid] as 数据库ID, \n";
InBlock.gif sql += "[crdate] as 创建日期,\n";
InBlock.gif sql += "[cmptlevel] as 兼容级别,\n ";
InBlock.gif sql += "[filename] as 主文件路径,\n";
InBlock.gif sql += "[version] as 内部版本号\n";
InBlock.gif sql += "from sysdatabases\n";
InBlock.gif sql += "order by dbId asc\n";
InBlock.gif
InBlock.gifreturn ExecuteSQL(sql,"sysdatabases");
ExpandedSubBlockEnd.gif }

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// 获取当前库的物理文件
InBlock.gif
/// </summary>
ExpandedSubBlockEnd.gif
/// <returns></returns>

InBlock.gifpublic DataTable GetSysFile()
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifthis.UseDefaultDatabase(); //切换到当前库库
InBlock.gif
string sql = "";
InBlock.gif sql += "select\n";
InBlock.gif sql += "[fileid] as 文件标识号,\n";
InBlock.gif sql += "Cast (([size]*8/1024) as Varchar) + ' 兆' as 文件大小,\n";
InBlock.gif sql += "[name] as 逻辑名, \n";
InBlock.gif sql += "[filename] as 物理名\n";
InBlock.gif sql += "from sysfiles\n";
InBlock.gifreturn ExecuteSQL(sql,"sysfiles");
ExpandedSubBlockEnd.gif }

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif/**//// <summary>
InBlock.gif
/// 执行压缩日志功能
InBlock.gif
/// </summary>
ExpandedSubBlockEnd.gif
/// <returns></returns>

InBlock.gifpublic bool ExecuteTruncateLog(int NewLogFileSize)
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifstring LogFile = "";
InBlock.gifstring strDelProc =
InBlock.gif "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]";
InBlock.gif
InBlock.gif DataTable dt = GetSysFile();
InBlock.gifforeach(DataRow dr in dt.Rows)
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifif(dr["物理名"].ToString().ToLower().LastIndexOf(".ldf")!=-1)
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gif LogFile = dr["逻辑名"].ToString().Trim();
InBlock.gifbreak;
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

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

InBlock.gifelse
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn false;
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

InBlock.gifelse
InBlock.gifreturn false;
ExpandedSubBlockEnd.gif }

InBlock.gif
ExpandedSubBlockEnd.gif#endregion

InBlock.gif
ExpandedSubBlockStart.gifContractedSubBlock.gif属性#region 属性
InBlock.gif
InBlock.gifpublic bool Active
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifget
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn this.active;
ExpandedSubBlockEnd.gif }

InBlock.gifset
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifif(value)
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifthis.Connect();
ExpandedSubBlockEnd.gif }

InBlock.gifelse
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifthis.Close();
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

InBlock.gif
InBlock.gifpublic string SqlConnectionString
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifget
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn
InBlock.gif "Database=" + this.sqlDefaultDatabase +
InBlock.gif ";Server=" + this.sqlServerName +
InBlock.gif ";User ID=" + this.sqlUserId +
InBlock.gif ";Password=" + this.sqlUserPassword + ";";
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

InBlock.gif
InBlock.gifpublic string SqlServerName
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifget
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn this.sqlServerName;
ExpandedSubBlockEnd.gif }

InBlock.gifset
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifthis.sqlServerName = value;
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

InBlock.gif
InBlock.gifpublic string SqlUserId
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifget
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn this.sqlUserId;
ExpandedSubBlockEnd.gif }

InBlock.gifset
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifthis.sqlUserId = value;
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

InBlock.gif
InBlock.gifpublic string SqlUserPassword
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifget
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn this.sqlUserPassword;
ExpandedSubBlockEnd.gif }

InBlock.gifset
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifthis.sqlUserPassword = value;
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

InBlock.gif
InBlock.gifpublic string SqlDefaultDatabase
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifget
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifreturn this.sqlDefaultDatabase;
ExpandedSubBlockEnd.gif }

InBlock.gifset
ExpandedSubBlockStart.gifContractedSubBlock.gifdot.gif{
InBlock.gifthis.sqlDefaultDatabase = value.Replace("'","''");
ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif }

ExpandedSubBlockEnd.gif#endregion

ExpandedSubBlockEnd.gif }

ExpandedBlockEnd.gif}

None.gif

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



本文转自suifei博客园博客,原文链接:http://www.cnblogs.com/Chinasf/archive/2005/05/20/159553.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
开发与运维
使用钉钉扫一扫加入圈子
+ 订阅

集结各类场景实战经验,助你开发运维畅行无忧

其他文章