用c#读取并分析sql2005日志
来源:互联网 发布:淘宝开店实名认证不了 编辑:程序博客网 时间:2024/06/05 10:08
用过logExplorer的朋友都会被他强悍的功能吸引,我写过一篇详细的操作文档可以参考
http://blog.csdn.net/jinjazz/archive/2008/05/19/2459692.aspx
我们可以自己用开发工具来实现sql日志的读取,这个应用还是很酷的,具体思路
1、首先要了解一个没有公开的系统函数::fn_dblog,他可以读取sql日志,并返回二进制的行数据
2、然后要了解sql的二进制数据是如何存储的,这个可以参考我的blog文章
http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx
3、用自己擅长的开发工具来分析数据,得到我们需要的信息
我用c#写了一个测试样例,分析了int,char,datetime和varchar的日志情况而且没有考虑null和空字符串的保存,希望感兴趣的朋友能和我一起交流打造属于自己的日志分析工具
详细的试验步骤以及代码如下:
1、首先建立sqlserver的测试环境,我用的sql2005,这个过程不能保证在之前的版本中运行
以下sql语句会建立一个dbLogTest数据库,并建立一张log_test表,然后插入3条数据之后把表清空
2、我们最终的目的是要找到被我们删掉的数据
3、分析日志的c#代码:我已经尽量详细的写了注释
4、更改你的sql连接字符串后运行以上代码,会看到如下输出信息:
http://blog.csdn.net/jinjazz/archive/2008/05/19/2459692.aspx
我们可以自己用开发工具来实现sql日志的读取,这个应用还是很酷的,具体思路
1、首先要了解一个没有公开的系统函数::fn_dblog,他可以读取sql日志,并返回二进制的行数据
2、然后要了解sql的二进制数据是如何存储的,这个可以参考我的blog文章
http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx
3、用自己擅长的开发工具来分析数据,得到我们需要的信息
我用c#写了一个测试样例,分析了int,char,datetime和varchar的日志情况而且没有考虑null和空字符串的保存,希望感兴趣的朋友能和我一起交流打造属于自己的日志分析工具
详细的试验步骤以及代码如下:
1、首先建立sqlserver的测试环境,我用的sql2005,这个过程不能保证在之前的版本中运行
以下sql语句会建立一个dbLogTest数据库,并建立一张log_test表,然后插入3条数据之后把表清空
- use master
- go
- create database dbLogTest
- go
- use dbLogTest
- go
- create table log_test(id int ,code char(10),name varchar(20),date datetime,memo varchar(100))
- insert into log_test select 100, 'id001','jinjazz',getdate(),'剪刀'
- insert into log_test select 65549,'id002','游客',getdate()-1,'这家伙很懒,没有设置昵称'
- insert into log_test select -999,'id003','这家伙来自火星',getdate()-1000,'a'
- delete from log_test
- --use master
- --go
- --drop database dbLogTest
2、我们最终的目的是要找到被我们删掉的数据
3、分析日志的c#代码:我已经尽量详细的写了注释
- using System;
- using System.Collections.Generic;
- using System.Text;
- namespace ConsoleApplication21
- {
- class Program
- {
- /// <summary>
- /// 分析sql2005日志,找回被delete的数据,引用请保留以下信息
- /// 作者:jinjazz (csdn的剪刀)
- /// 作者blog:http://blog.csdn.net/jinjazz
- /// </summary>
- /// <param name="args"></param>
- static void Main(string[] args)
- {
- using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
- {
- conn.ConnectionString = "server=localhost;uid=sa;pwd=sqlgis;database=dbLogTest";
- conn.Open();
- using (System.Data.SqlClient.SqlCommand command = conn.CreateCommand())
- {
- //察看dbo.log_test对象的sql日志
- command.CommandText = @"SELECT allocunitname,operation,[RowLog Contents 0] as r0,[RowLog Contents 1]as r1
- from::fn_dblog (null, null)
- where allocunitname like 'dbo.log_test%'and
- operation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS')";
- System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
- //根据表字段的顺序建立字段数组
- Datacolumn[] columns = new Datacolumn[]
- {
- new Datacolumn("id", System.Data.SqlDbType.Int),
- new Datacolumn("code", System.Data.SqlDbType.Char,10),
- new Datacolumn("name", System.Data.SqlDbType.VarChar),
- new Datacolumn("date", System.Data.SqlDbType.DateTime),
- new Datacolumn("memo", System.Data.SqlDbType.VarChar)
- };
- //循环读取日志
- while (reader.Read())
- {
- byte[] data = (byte[])reader["r0"];
- try
- {
- //把二进制数据结构转换为明文
- TranslateData(data, columns);
- Console.WriteLine("数据对象{1}的{0}操作:", reader["operation"], reader["allocunitname"]);
- foreach (Datacolumn c in columns)
- {
- Console.WriteLine("{0} = {1}", c.Name, c.Value);
- }
- Console.WriteLine();
- }
- catch
- {
- //to-do...
- }
- }
- reader.Close();
- }
- conn.Close();
- }
- Console.WriteLine("************************日志分析完成");
- Console.ReadLine();
- }
- //自定义的column结构
- public class Datacolumn
- {
- public string Name;
- public System.Data.SqlDbType DataType;
- public short Length = -1;
- public object Value = null;
- public Datacolumn(string name, System.Data.SqlDbType type)
- {
- Name = name;
- DataType = type;
- }
- public Datacolumn(string name,System.Data.SqlDbType type,short length)
- {
- Name = name;
- DataType = type;
- Length = length;
- }
- }
- /// <summary>
- /// sql二进制结构翻译,这个比较关键,测试环境为sql2005,其他版本没有测过。
- /// </summary>
- /// <param name="data"></param>
- /// <param name="columns"></param>
- static void TranslateData(byte[] data, Datacolumn[] columns)
- {
- //我只根据示例写了Char,DateTime,Int三种定长度字段和varchar一种不定长字段,其余的有兴趣可以自己补充
- //这里没有暂时没有考虑Null和空字符串两种情况,以后会补充。
- //引用请保留以下信息:
- //作者:jinjazz
- //sql的数据行二进制结构参考我的blog
- //http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx
- //行数据从第5个字节开始
- short index = 4;
- //先取定长字段
- foreach (Datacolumn c in columns)
- {
- switch (c.DataType)
- {
- case System.Data.SqlDbType.Char:
- //读取定长字符串,需要根据表结构指定长度
- c.Value = System.Text.Encoding.Default.GetString(data,index,c.Length);
- index += c.Length;
- break;
- case System.Data.SqlDbType.DateTime:
- //读取datetime字段,sql为8字节保存
- System.DateTime date = new DateTime(1900, 1, 1);
- //前四位1/300秒保存
- int second = BitConverter.ToInt32(data, index);
- date = date.AddSeconds(second/300);
- index += 4;
- //后四位1900-1-1的天数
- int days = BitConverter.ToInt32(data, index);
- date=date.AddDays(days);
- index += 4;
- c.Value = date;
- break;
- case System.Data.SqlDbType.Int:
- //读取int字段,为4个字节保存
- c.Value = BitConverter.ToInt32(data, index);
- index += 4;
- break;
- default:
- //忽略不定长字段和其他不支持以及不愿意考虑的字段
- break;
- }
- }
- //跳过三个字节
- index += 3;
- //取变长字段的数量,保存两个字节
- short varColumnCount = BitConverter.ToInt16(data, index);
- index += 2;
- //接下来,每两个字节保存一个变长字段的结束位置,
- //所以第一个变长字段的开始位置可以算出来
- short startIndex =(short)( index + varColumnCount * 2);
- //第一个变长字段的结束位置也可以算出来
- short endIndex = BitConverter.ToInt16(data, index);
- //循环变长字段列表读取数据
- foreach (Datacolumn c in columns)
- {
- switch (c.DataType)
- {
- case System.Data.SqlDbType.VarChar:
- //根据开始和结束位置,可以算出来每个变长字段的值
- c.Value =System.Text.Encoding.Default.GetString(data, startIndex, endIndex - startIndex);
- //下一个变长字段的开始位置
- startIndex = endIndex;
- //获取下一个变长字段的结束位置
- index += 2;
- endIndex = BitConverter.ToInt16(data, index);
- break;
- default:
- //忽略定长字段和其他不支持以及不愿意考虑的字段
- break;
- }
- }
- //获取完毕
- }
- }
- }
- 数据对象dbo.log_test的LOP_INSERT_ROWS操作:
- id = 100
- code = id001
- name = jinjazz
- date = 2008-8-7 18:14:03
- memo = 剪刀
- 数据对象dbo.log_test的LOP_INSERT_ROWS操作:
- id = 65549
- code = id002
- name = 游客
- date = 2008-8-6 18:14:03
- memo = 这家伙很懒,没有设置昵称
- 数据对象dbo.log_test的LOP_INSERT_ROWS操作:
- id = -999
- code = id003
- name = 这家伙来自火星
- date = 2005-11-11 18:14:03
- memo = a
- 数据对象dbo.log_test的LOP_DELETE_ROWS操作:
- id = 100
- code = id001
- name = jinjazz
- date = 2008-8-7 18:14:03
- memo = 剪刀
- 数据对象dbo.log_test的LOP_DELETE_ROWS操作:
- id = 65549
- code = id002
- name = 游客
- date = 2008-8-6 18:14:03
- memo = 这家伙很懒,没有设置昵称
- 数据对象dbo.log_test的LOP_DELETE_ROWS操作:
- id = -999
- code = id003
- name = 这家伙来自火星
- date = 2005-11-11 18:14:03
- memo = a
- ************************日志分析完成
试验成功~~
dbcc log和fn_dblog函数真的是分析日志文件吗?
这个问题需要了解CheckPoint和sql的存储机制,首先参考如下文档
http://msdn.microsoft.com/zh-cn/library/ms188748.aspx
CheckPoint
将当前数据库的全部脏页写入磁盘。“脏页”是已输入缓存区高速缓存且已修改但尚未写入磁盘的数据页。CHECKPOINT 可创建一个检查点,在该点保证全部脏页都已写入磁盘,从而在以后的恢复过程中节省时间。
什么时候执行它?大致有几种情况:
手动调用、sql自动定时执行、数据库脱机之类操作、数据库进程正常终止等情况。
简单日志模型的原理就是每次checkpoint后自动截断日志。
dbcc log到底有什么用?和checkpoint有什么关系
在事物外执行过CheckPoint后,dbcc log语句将无法获取之前的日志。所以我推断此语句是系统为了在非正常关闭数据库的状态下,来保证日志,内存中的脏页和数据文件的一致性的措施,此方法并不是读取日志文件的全部信息。
log explorer的原理
还没有搞清楚,此前的推断是错误的,它不是通过dbcc log 或者fn_dblog方法实现的,不但CheckPoint对它没有影响,而且他会主动调用这个语句。
日志分析工具宣告流产,不过基本上搞清了日志和数据文件的存储结构,希望以后仍然可以用的上这些经验
- 用c#读取并分析sql2005日志
- 用c#读取并分析sql2005日志
- 用c#读取并分析sql2005日志
- 用c#读取并分析sql2005日志
- 用c#读取并分析sql2005日志
- 用c#读取并分析sql2005日志
- 用c#读取并分析sql2005日志
- 用c#读取并分析sql2005日志
- 用c#读取并分析sql2005日志
- C#2005 EXCEL读取到SQL2005中
- sql2005调用c# dll 读取磁盘文件总结
- c# 读取txt并绘图
- 读取并分析wgetrc文件
- 读取并分析wgetrc文件
- 用C#读取excel中的数据,并绘制曲线
- sql2005 无日志附加
- 清理sql2005事务日志
- sql2005 清除日志
- 输入字符串后退出
- 项目阶段占比分析
- CSV格式文件
- 恢复"我的文档"中三个标准文件夹
- ls连接odbc(SQL Or Oracle)
- 用c#读取并分析sql2005日志
- web Service (技术)
- 矩形相交
- LogExplore的一个详细操作手册
- ls使用ODBC连接Oracle注意的事项
- SQL2005数据行的二进制结构
- API
- 5分钟让你明白金融危机爆发原因
- 2008.11.26