请教专家：附加一个有2GB大的数据库文件后，整个数据库占用CPU资源要50%左右

Question

一个网站的后台SQL2005的数据库系统，有50W多的数据（主数据库文件为2.3GB，日志文件为13GB），整个数据库系统以前运行一直正常，最近发现数据库的SQLSERVER.EXE所占CPU一直居高不下，要到50%左右，把网站关闭并把操作系统重新启动后，CPU资源占用率还是这样。开始以为是日志文件过大引起的，后来对日志文件进行压缩
用一下方法：
1.清空日志   
  DUMP     TRANSACTION     库名     WITH     NO_LOG           
    
  2.截断事务日志：   
  BACKUP   LOG   库名   WITH   NO_LOG   
最后日志文件限定为2GB，但CPU资源占用率还是50%。
后来再把该数据库文件备份并恢复到SQL2008系统中，发现CPU资源占用率还是50%。
但若把该数据库从数据库服务器上删除后，则CPU资源占用率马上降为０％
请教高手是什么问题？是否有可能是数据库中很久没有做索引重建的问题？
机器配置:双核E5300 2.6GHz,1G内存。

Answer 1

先通过 taskmgr 观察一下，是否是 sql server 进程占用了大多数 cpu 时间。
再检查一下是 sql server 中哪一个进程在占用 cpu 时间。
select * from sys.dm_exec_sessions order by cpu_time desc
然后，再做进一步分析。

Answer 2

执行一下代码
select session_id,program_name,host_name,host_process_id, cpu_time,total_elapsed_time,total_elapsed_time from sys.dm_exec_sessions order by cpu_time desc
结果如下：
53 BOBZHU-PC 3252 188 2798 2798
51 BOBZHU-PC 3252 16 142 142
52 BOBZHU-PC 352 0 0 0
54 BOBZHU-PC 3252 0 0 0
但不知道下面如何处理了？呵呵

Answer 3

从上述信息看 53 占用的 cpu 时间不是很多。可以确定是 sql server 进程占用了大量 cpu 时间吗？如果可以 kill 53，再看看 cpu 的占用情况。如果这样做没有作用，可以使用“性能监视器”跟踪一下（性能对象 Memory:page/sec、Processor: %Processor Time、SQL Server:Databases）。-- 检查 SQL Server 系统会话所占用的 cpu 时间
select session_id,command,cpu_time
from sys.dm_exec_requests where session_id<51 order by cpu_time desc-- 检查占用 cpu 最高的用户会话所执行的最后一条成功执行的语句
select session_id,command,cpu_time,[text]
from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle)
order by cpu_time desc

Answer 4

另外，忘了问一下。是不是在服务器没有其他任务和访问的情况下，一附加该数据库，cpu 占用就大？还是在使用过程中，发生这种情况。
如果是前者，看起来很诡异。如果是后者，可以使用“性能监视器”跟踪一下系统性能，找出性能瓶颈，加以解决。如，添加内存，整理索引等。

Answer 5

是在服务器没有其它任务的情况下，刚才把该数据库放到一个性能更好的服务器上了，4核CPU,４Ｇ内存，SQLSERVER.EXE的资源占用保持在20%左右，但是这个数据是在几乎没有访问量的情况下的占用情况。

Answer 6

我运行您的两个TSQL语句分别得到如下信息：
21 GHOST CLEANUP 13034593
1 RESOURCE MONITOR 32812
2 LAZY WRITER 1546
4 LOCK MONITOR 93
7 TRACE QUEUE TASK 31
12 BRKR EVENT HNDLR 31和以下信息
153 SELECT 0 select session_id,command,cpu_time,[text]  from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle)  order by cpu_time desc
感觉问题是在GHOST CLEANUP上，但这个ghost cleanup是个什么东西啊？还得请教

Answer 7

SQL Server 的LOG中有什么信息？

Answer 8

日志中有不少的错误信息：
The current event was not reported to the Windows Events log. Operating system error = 31(error not found). You may need to clear the Windows Events log if it is full.Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
Login failed for user 'sa'. [客户端: <local machine>]

Answer 9

把错误信息贴多一点。WINDOWS系统日志也贴。

Answer 10

出现问题了，索引尽然不能删除了！我用DROP INDEX tb_product.companyid　去删除，已经过了10多分钟了，好像一点反应都没有啊。

Answer 11

现在发现是在tb_product表上的索引坏了，但奇怪的是我想删除索引也不成功，执行dbcc checktable也失败提示如下错误：而且无论对哪个表进行checetable操作，都会失败！
消息 1823，级别 16，状态 2，第 3 行
无法创建数据库快照，因为它未能启动。
消息 7928，级别 16，状态 1，第 3 行
无法创建数据库快照以进行在线检查。可能前一个错误消息已给出原因，或者某个基础卷不支持稀疏文件或备用流。请尝试使用独占访问来运行离线检查。
tb_Admin的 DBCC 结果。
对象 'tb_Admin' 的 1 页中有 1 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
消息 845，级别 17，状态 1，第 3 行
等待用于页 (1:194507)，数据库 ID 7 的缓冲区闩锁类型 3 时发生超时。

Answer 12

1. 将DB置于SINGLE USER模式。2 执行DBCC　CHECKDB　看看报什么信息。

Answer 13

我执行以下代码：
Exec sp_dboption 'Cnprice','single user','true'
产生错误：
Msg 5070, Level 16, State 2, Line 1
在其他用户正使用数据库 'CNPrice' 时，无法更改数据库状态
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE 语句失败。
sp_dboption 命令失败。
即使是我把数据库服务停止，然后重新启动也是这样。

Answer 14

SP_WHO2 可查到是什么连接到CNPRCE，可能是JOB。

Answer 15

24    BACKGROUND                     sa   .   . CNPrice GHOST CLEANUP    1262968 9 12/13 0:22:55:                                               24    0    
51    RUNNABLE                       ND10877-LKSSLA7\Administrator ND10877-LKSSLA7   . CNPrice SELECT INTO      93 62 12/13 0:44:07: Microsoft SQL Server Management S[code=SQL]tudio - 查询   51    0    [/code]从上面看好像是ghost cleanup在运行

Answer 16

问题解决了吗？
如果没有解决，我来发表一下看法，
在 sql server 中 GHOST CLEANUP 进程负责删除“幽灵记录”（不知道在其他书上中文如何翻译，姑且如此）。那么，何谓“幽灵记录”？简单的说，就是在表上索引中被删除的记录，这样的记录实际上在被删除时并没有真正被物理删除，只是被标记为“被删除的”（这是性能上的考虑，与活动目录中的 tombstone 类似）。之后由 GHOST CLEANUP 进程（每 5 秒运行一次）负责将其物理删除。
这样，对于楼主的问题，就有两种可能。正常的 GHOST CLEANUP 删除操作和非正常的（废话）。而无论是前者还是后者，GHOST CLEANUP 进程都会阻塞其他任务对索引的修改操作，这也是楼主无法执行索引删除的表检查的原因吧。再说一说解决方法，如果是正常的删除操作，不妨等一等，再看一下 GHOST CLEANUP 进程是否还在运行；那么如果是不正常的，那就要想办法终止GHOST CLEANUP 进程，再对数据库的完整性进行检查和修复。
具体如何终止 GHOST CLEANUP 进程？这个是我搜索到的，看看希望有帮助
http://www.sqlservercentral.com/Forums/Topic496244-149-1.aspx
其实就是，lz 可能启动了数据库的自动收缩功能。不知道对否？如果不对，再议。

Answer 17

原来是表坏了......dbcc checktable修复吧

Answer 18

谢谢大家的帮助，现在问题已经解决：问题主要在索引上，是索引坏了，呵呵。后来解决方案如下：
一：
估计是SQL Server 2005数据库日志文件可能会发生损坏，例如硬件故障、计算机非正常重启或关机等等。 
先后出现的症状如下： 
1、在SQL Server Management Studio中显示数据库处于置疑(suspect)状态，无法展开数据库的树形结构（我在今天恢复的时候连这个状态都没有，只有个图标，跟上周在addison座位上看到的症状一样）。 
2、事件日志可能会出现如下错误信息： 
Could not redo log record (21737:686:9), for transaction ID (0:2334886), on page (1:37527), database ”Test” (database ID 15). Page: LSN = (21735:299:5), type = 2. Log: OpCode = 3, context 19, PrevPageLSN: (21737:615:1). Restore from a backup of the database, or repair the database. 
During redoing of a logged operation in database ”Test”, an error occurred at log record ID (76116:286:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database. 
3、无法分离数据库 
4、用Create DATABASE DBName ON ( FILENAME = N”DBFile” ) FOR ATTACH_REBUILD_LOG附加数据库时出现提示：The log cannot be rebuilt because the database was not cleanly shut down. 
恢复步骤： 
1、停止数据库服务。 
2、将需要恢复的数据库文件复制到另外的位置。 
3、启动数据库服务。 
4、确认要恢复的数据库文件已经成功复制到另外的位置，然后在SQL Server Management Studio中删除要恢复的数据库。 
5、新建同名的数据库(数据库文件名也要相同)。 
6、停止数据库服务。 
7、用第2步中备份的.mdf文件覆盖新数据库的同名文件。 
8、启动数据库服务。 
9、运行alter database dbname set emergency，将数据库设置为emergency mode 
10、运行下面的命令就可以恢复数据库(名称是WSS_Content)（我是依次执行的）：
 代码 
 1--置数据库与紧急状态
 2ALTER DATABASE WSS_Content SET EMERGENCY 
 3--允许对系统目录进行即席更新
 4sp_configure 'allow updates', 1 
 5go 
 6reconfigure with override 
 7go 
 8--单一用户模式
 9sp_dboption 'WSS_Content', 'single user', 'true'
10--创建此数据库对应的日志文件
11dbcc checkdb(WSS_Content,REPAIR_ALLOW_DATA_LOSS)
12--之后可以执行多用于模式的命令
13dbcc checkdb(WSS_Content,REPAIR_REBUILD)
14--多用户模式
15exec sp_dboption 'WSS_Content', 'single user', 'false'
16--禁止对系统目录进行即席更新
17sp_configure 'allow updates', 0 
18go 
19reconfigure with override 
20
注意网上很多关于SQL2000的做法，其中创建日志的命令dbcc rebuild_log在SQL2005中无效。二。并删除该表的所有索引，运行 dbcc checktable对tb_product表进行修复。
最后终于成功了！