简介数据库日志文件的增长

我们经常遇到客户问和数据库日志相关的问题。比如: 1.       我的数据库只有10GB,为什么我的日志文件有40GB,而且还在增长? 2.       我无法对数据库进行更改操作了。报错说我的日志空间已满。 3.       我数据库的恢复模式明明是Simple,为什么还有日志,还那么大? 4.       我的数据库一直处于恢复状态,其他应用程序都报连接出错。 归根结底一句话,我数据库的日志的增长远远超过我的预期,而导致我的业务受到影响。数据库事务日志(Transaction Log),或简称日志文件(Log)文件的异常增长有时候对数据库的应用的影响是致命的。 想象一下,在你的业务最繁忙的时候,你的数据库由于日志文件占满整个磁盘空间,而无法使用。有人说,很简单啊!我把日志文件删掉。如果你真的这样做,你会发现数据库日志文件正被SQL Server进程锁定,而无法删除。于是,你停止SQL Server服务,然后很开心的将Transaction Log文件删除了,节省了大概200GB的空间。再于是,你很“伤心”的发现你的数据库无法启动,因为缺少日志文件。如果你在日志文件原有的路径创建一个同名的日志文件,好吧,既然你那么坚决,SQL Server会告诉你日志文件格式已损坏。突然,你发现自己有个很好的习惯,当你删除那个200GB的日志文件的时候,你只是把它扔到了垃圾箱,于是你把它找了回来,重新启动你的SQL Server,很开心的发现,它成功的启动了。于是你松了一口气,泡上一杯咖啡,考虑是不是找你的Disk Vendor申请更多的磁盘空间。突然你的电话铃声大作,好多连接到你的数据库的应用程序的管理人员都向你抱怨他们无法连接到数据库。你打开SSMS,发觉那个数据库一直处于“恢复中”(In Recovery)状态,好像它永远不会终止。好吧,你是不是有想砸机器的冲动了,并且想气势汹汹的告诉全世界,这是微软SQL Server的另一个Bug? 如果,你认真的读完这篇文章,你会发现这只不过是SQL Server的另一个不恰当应用的一个案例。   什么是日志文件(Transaction Log) 简而言之,日志文件通过一个简化的格式记录了所有对数据库的修改操作,包括Insert, Update和Delete等能够帮助你重现对数据库内容修改的操作。日志文件的后缀名为*.LDF。 数据文件 (*.mdf)Vs. 日志文件 (*.ldf) 有人本以为数据文件是存储数据的“核心”文件,但是很诧异的发现,原来日志文件存储了所有的数据修改。有些人会想到,通过日志文件,其实我就可以把我的数据库恢复到某个特定时间点的状态。这句话,在某些限制条件下是对的,将在下文进行讨论。 那么,数据文件和日志文件的关系是什么呢?他们如何工作? SQL Server是一个很依赖于内存(Memory)使用的系统。任何一个对于数据的读入/修改都是和内存进行交互。当一个修改操作发生时,修改的将是内存中所对应的在内存中的数据页。这个操作将会实时地被写入日志文件。但是,该修改被写入数据文件(*.mdf)的时间,只有在以下三种情况下发生:(1)做Checkpoint 时(2)Lazy write运行时(3)Eager write运行时。Lazy write发生在有内存压力时,而Eager write通常发生在bulk insert和select into操作时的。 这里只谈比较普遍的checkpoint情况。 Checkpoint是SQL Server的一个自动的行为。http://msdn.microsoft.com/en-us/library/ms188748.aspx 所以,你的数据文件和日志文件的数据差异间隔会是两个Checkpoint之间的时间差。Checkpoint本身也会被写入日志文件。如果在某些情况下,Checkpoint并没有如预期那样短时间中发生,数据差异间隔会更长。 日志文件如何影响我数据库的启动? 无论你的SQL Server启动,或者你将某数据库重备份中恢复,或者其他的一些情况,总之在你的数据库能够被正常使用之前,你的数据库都会进入 Recovery的状态。http://msdn.microsoft.com/en-us/library/ms190442.aspx 如果这一个步失败,那么你的数据库就会进入Suspect状态而无法正常使用。在一些特殊情况下,这个Recovery所花费的时间会很长。在数据库进入Online状态之前,我们都不能认为SQL Server可以被正常使用了。 那么在Recovery中做了什么呢? 我们可以在你的SSMS中运行如下语句。在运行之前,你可以先按下Ctrl+T来把结果转化成Plain Text格式。…

4

Database using Full Text Catalog 备份失败

我最近在做一个关于备份失败的Case。这个备份和Full Text Catalog一起使用。这种状况在SharePoint数据库中很常见。它使用了SQL Server中的全文索引功能(Full Text Searching)。如果你遇到了如下的错误: Msg 3007, Level 16, State 1, Line 1 The backup of the file or filegroup “<full_text_catalog>” is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. Msg 3013, Level 16, State…

0

Kerberos 验证

我将在这里阐述关于Kerberos验证的过程。在此之前,我们先记住如下两个规则。我将在下文进行解释。 ·         如果A与B两者拥有只有他们之间想要分享的信息,他们就会对该通信通过Session Key的方式进行加密。通常写为A / B Session Key。 ·         如果A的某些信息需要交到B手中,又不愿意B了解信息的内容,A会使用只有自己知道的Secret Key进行加密   在本文中,将使用到的术语如下: ·         AS – Authentication Server ·         TGS – Ticket Granting Server ·         TGT – Ticket Granting Ticket 在本文中AS和TGS扮演同样的角色。   Kerberos验证的过程可以分为4步:客户端登陆;客户端验证;客户端–服务端验证;客户端–服务端请求;   客户端登陆 1. 客户端接收用户名与密码的输入。 2. 客户端计算密码的Hash值,并保存为客户端的Secret Key     客户端验证 1. 客户端以明文的方式,向验证服务器(Authentication Server / Ticket Granting Server)发送用户名。在微软的Kerberos实现中,验证服务器由KDC来实现。 2. 验证服务器在接收到客户端明文发过来的用户名后,从AD服务器中查询该用户名所对应的用户信息,并取得密码。注意,客户端并没有把密码也发给验证服务器。 3. 验证服务器通过同样的Hash算法,计算上步得到用户在AD服务器中的密码所对应的Hash值,作为Secret Key。我们可以看到,客户端和验证服务器分别通过相同的方法生成Secret…

0

DENALI ALWAYSON 可用性组介绍

【介绍】 SQL Server针对高可用性,有好几种解决方案,包括: 日志传输,复制,镜像,群集。在下一个版本的SQL Server (代码名为DENALI) 引入了新的解决方案叫ALWAYSON。 ALWAYSON兼容了镜像和群集的优点。例如,数据库镜像只支持一个数据库。如果某个应用需要同时引用两个数据库,那么当其中一个数据库在主服务器,而另外一个数据库在镜像服务器上,应用就不能正常工作。ALWAYSON可以把多个数据库绑定为一个可用性组。以确保一个可用性组内的所有数据库,都在同一台服务器上。 另外,对镜像来说,我们只能有一个镜像。如果主服务器和镜像服务器都不能正常工作,那么整个应用就无法工作。而ALWAYSON可以配置超过两个的副本。以提高稳定性。镜像没有虚拟服务器名的概念,我们需要在客户端的连接字符串里,对镜像做特殊的配置。而ALWAYSON则提供了虚拟服务器名,对连接设定更加方便。 对于群集,我们第二个结点passive node一直处于等待状态。这可能对资源来讲,是一种浪费。ALWAYSON充分利用第二个结点,可以做只读操作,如报表浏览。数据备份等等。另外群集需要共享存储。ALWAYSON则不用。 【ALWAYSON场景】 如下图所示,我们的可用性组有两个数据库,当DB1出现问题后,系统会自动把另外一台服务器当做PRIMARY,同时,另外一台SECONDARY数据库依旧保持可读功能。   【ALWAYSON安装】 1.       我们用HyperV搭建了一个虚拟环境。以演示ALWAYSON功能。总共有三台虚拟机: 机器名 作用 网络 ClusterDC 域控制器 公共网络地址:10.10.10.1 Denali1 结点1 心跳网络地址: 169.254.1.38  公共网络地址:10.10.10.38 Denali2 结点2 心跳网络地址: 169.254.1.35  公共网络地址:10.10.10.35   2.       在Denali1和Denali2这两台机器上,分别安装Denali CTP3。我们在安装的时候,要注意,选择stand-alone安装。实例使用缺省实例。 3.       安装完成后,我们到DENALI1和DENALI这两台服务器上,开启TCP/IP和Named Pipe侦听。(需要重启) 4.       然后到DENALI1和DENALI2这两台机器上的SQL Server Configuration Manager上,选择我们的Instance, 右键点击,选择属性。我们就会看到ALWAYSON设置如下图。我们选择 “Enable AlwaysOn Availability Groups”,然后选择Apply。再次重启服务器。   5.       我们用Management Studio 连接到Denali1这台服务器上,创建两个数据库DB1和DB2。并对它们做一次全备份。…

2

SQL Server 2008中的Service SID 介绍

【介绍】 我们打开SQL Server 2008 Management Studio, 会发现有如下几个登录: NT SERVICE\ClusSvc, NT SERVICE\MSSQL$KATMAI和 NT SERVICE\SQLAgent$KATMAI,这几个登录在安装SQL Server的时候,就会被创建。(其中NT SERVICE\ClusSvc则只有在群集环境下被创建)。我们这里介绍一下这些登录,有什么用途,对我们系统的安全性有什么影响,我们能否把他们删除,以及删除掉的后果是什么。 【SERVICE SID的引入】 NT SERVICE\MSSQL$KATMAI, NT SERVICE\SQLAgent$KATMAI和NT SERVICE\ClusSvc 其实都是Service SID所对应的名字。Service SID的引入,是为了解决多个Service可能同用一个service帐号所带来的安全隐患。如IIS 使用Network Service帐号,可能其他服务也使用Network Service帐号。为了使得IIS能够连接到SQL Server, 我们可能会把Network Service作为SQL Server的login, 但是这是不安全的。因为其他服务如果以Network Service做为启动帐号的话,也能访问SQL Server。为了解决这个问题,在SQL Server 2008/Windows Server 2008及以后,我们有了SID这个概念,这样,不同的服务,即使服务启动帐号是相同的,它们的SID也是不同的。 所以,我们这里的一些logins,并不是Windows Users或Group的名字, 而是SID的名字。其相对应的SID值,我们可以用命令 sc  showsid serviceName所得到。如下:   【SQL Server Agent的启动帐号是否需要sysadmin的权限】 如果操作系统是Windows 2003或者SQL Server 版本低于SQL Server 2008,那么SQL…

0

如何有效抓取SQL Server的BLOCKING信息

SQL Server允许并发操作,BLOCKING是指在某一操作没有完成之前,其他操作必须等待,以便于保证数据的完整性。BLOCKING的解决方法要查看BLOCKING的头是什么,为什么BLOCKING头上的语句执行的很慢。通常来讲只要我们能找到BLOCKING头上的语句,我们总能够想出各种各种的办法,来提升性能,缓解或解决BLOCKING的问题。   但是问题的关键是,我们不知道BLOCKING什么时候会发生。用户跟我们抱怨数据库性能很差,等我们连上数据库去查看的时候,那时候有可能BLOCKING可能就已经过去了。性能又变好了。或者由于问题的紧急性,我们直接重新启动服务器以恢复运营。但是问题并没有最终解决,我们不知道下次问题会在什么时候发生。   BLOCKING问题的后果比较严重。因为终端用户能直接体验到。他们提交一个订单的时候,无论如何提交不上去,通常几秒之内能完成的一个订单提交,甚至要等待十几分钟,才能提交完成。更有甚者,极严重的BLOCKING能导致SQL Server停止工作。如下面的SQL ERRORLOG所表示, 在短短的几分钟之内,SPID数据从158增长到694, 并马上导致SQL Server打了一个dump, 停止工作。我们很容易推断出问题的原因是由于BLOCKING导致的,但是我们无法得知BLOCKING HEADER是什么,我们必须要等下次问题重现时,辅之以工具,才能得知BLOCKING HEADER在做什么事情。如果信息抓取时机不对,我们可能要等问题发生好几次,才能抓到。这时候,客户和经理就会有抱怨了。因为我们的系统是生产系统,问题每发生一次,都会对客户带来损失。   2011-06-01 16:22:30.98 spid1931    Alert There are 158 Active database sessions which is too high. 2011-06-01 16:23:31.16 spid3248    Alert There are 342 Active database sessions which is too high. 2011-06-01 16:24:31.06 spid3884    Alert There are 517 Active database sessions which is…

0

TDE开启模式下恢复数据库备份

  TDE是“透明数据加密”(Transparent Data Encryption),为SQL server 2008的一个新功能。(更多介绍,请参考http://msdn.microsoft.com/zh-cn/library/bb934049.aspx)   此案例将介绍如何重置TDE(即,移除所有TDE设置,重新打开TDE), 并在TDE开启的模式下恢复数据库备份。   其中我们需要特别注意的是,当移除TDE设置时,如果只移除证书但不移除数据库的加密密钥,数据库则不能启动,即使该数据库已经完全解密。此外,当数据库没有打开TDE但保留了密钥时,数据库不可在另外一个服务器上恢复。必须首先在目标服务器上恢复证书和主密钥(master key),或者在备份数据库前首先移除数据库密钥。   以下是清理TDE设置,重新打开TDE和恢复备份的一些步骤 (红色文本需要根据您的需求进行修改)。            1.  移除所有TDE相关的密钥和证书,以便从头来过:(可选)      —首先关闭 数据库加密。解密操作将启动                                                  ALTER DATABASE TDE2                                                SET ENCRYPTION OFF   /*对于大型数据库,解密操作需要等待一段时间, 可用以下语句查询,encryption_state  为1代表没有加密(即,解密完成)*/                                                 Use TDE2                                               GO                                              select DB_NAME(database_id) , encryption_state from sys.dm_database_encryption_keys                                               GO /*移除数据库加密密钥。注意,本操作只是移除某个特定的数据库的密钥(例如本范例中的TDE2)。如果我们需要清理多个数据库,则需要对这些数据库重复前三个步骤*/                                              Use TDE2                                              GO…

0

Delete大量数据后SQL Server性能下降

今天给大家分享一个”删除大量数据后SQL Server性能下降”的案例。一般而言,数据库数据减少后,应该有助于提高SQL server的整体性能。可是在这个案例中,情况恰恰相反。   症状 ========= –          删除大量数据后SQL Server性能下降 –          一些存储过程之前运行20分钟左右,现在需要运行2-3个小时。   背景信息 ========= –          大量数据通过DELETE语句而删除 –          数据删除后,客户进行了相关的维护工作 : 重建索引和更新统计数据 –          性能变慢的存储过程会对一些表做很多的”DELETE”,”INSERT”和”SELECT”操作。   调查 ========= –          相关的表都是堆( heap table)   –          这些表中并没有大量数据   –          DBCC CHECKCONTIG 结果显示表很大,但其页的密度 (Page Density) 却相当小。   DBCC SHOWCONTIG scanning ‘tblA’ table… Table: ‘tblA’ (322816212); index ID: 0, database ID: 14…

3

分布式死锁的一个例子

有时候你会发现你的程序没有响应了,而此刻你在SQL server里面发现对应的线程在等待网络IO如下所示: select session_id,blocking_session_id,wait_type,wait_time,wait_resource,* from sys.dm_exec_requests where session_id>50     上面图示 spid 57被spid55阻塞了。Spid55等待网络IO (ASYNC_NETWORK_IO). 等待网络IO的意思是等待客户端程序来拿数据, 也就是客户端拿数据不够快。在很多情况下,这个假设是对的。比如客户端的程序每次只fetch一条数据逐条处理,就会导致网络IO等待。   但是如果有别的spid等待这个等待网络IO的spid,如上面的spid57等待spid55,那么要小心你是不是碰到了分布式死锁。 分布式死锁是比较难检测的。 光SQL server 自己不能形成分布式死锁。SQL server 和客户端程序一起才能形成分布式死锁。下面就是分布式死锁的一个例子:     上面的图说明,客户端程序和SQL server的资源等待形成了一个环,这个就叫分布式死锁。 造成上面分布式死锁的代码如下:   SqlDataReader rdr = null;             SqlConnection con = null;             SqlCommand cmd = null;               try             {                 // Open connection to the…

2

什么是PAGELATCH和PAGEIOLATCH

 在分析SQL server 性能的时候你可能经常看到 PAGELATCH和PAGEIOLATCH。比方说 Select * from sys.dm_os_wait_stats 的输出里面就有Latch的有关信息: 那么什么是PAGELATCH和PAGEIOLATCH呢? 先说什么Latch。 Latch是SQL server内部用来同步资源访问的一个数据结构,和操作系统的critical section 或 ReaderWriterLock类似。Latch保护了那些想保护的资源,使得访问同步有序。比方说,当某个线程获得某个资源的latch的独占使用权的时候,别的线程如果也需要访问这个latch则它必须等待。 那么什么是PAGELATCH呢? 很简单,用来同步访问数据库PAGE的latch就是PAGELATCH了。SQL server的Buffpool里每个数据库页(8kb的PAGE)都有一个对应的LATCH。 要访问某个PAGE必须首先获得这个PAGE的LATCH。PAGELATCH有很多种,如共享的PAGELATCH_SH,独占的PAGELATCH_EX等。独占的意思是排他性访问。共享的意思是可以有多个线程同时获得这个latch。 那么什么是PAGEIOLATCH呢? 就是当这个数据库页不在内存里面必须从磁盘读取的时候,那么latch的类型就是PAGEIOLATCH了。其它方面和PAGELATCH一样。 一个有趣的现象是,有时候你发现某个SPID被自己阻塞住了,等待的latch是PAGEIOLATCH_SH. 为什么会自己等待自己呢? 这个得从SQL server读取页的过程说起。SQL server从磁盘读取一个page的过程如下:  Acquires an EX latch on the page that does not exist in the buffer pool (EX mode: blocking others, and no duplicate same IO will be issued) 该页不在内存里面,SQL…

0