解决方案 »
- SQL server 2005 Express 如何支持中文?
- 未能在sysindexs找到数据库Id9中Id对象Id1索引Id1对应的行请对sysindexs执行
- 请叫大虾们一个问题,谢谢
- 求助,大数据量查询造成其他人用不了
- 如何显示某个表中,某个字段有重复的全部记录出来呢 急~~~~谢谢进来,谢谢,谢谢,改天把分送上
- sql server 2000 1067错误。。。
- 有关自定义数据类型
- 救命!我使用update 更新了一个表里的全部内容,如何恢复?在线等
- 用存储过程如何实现模糊查询功能?急!!!!!!!!!!!!!!!!!!!!!!!!111
- 新手求助!!!
- SQL Server 使用了VARCHAR(MAX)会不会降低性能?
- 藏文在sql里如何搜索
select * from sys.sysprocesses where blocked<>0看看是否有阻塞发生
数据库大小 3.42G,日志:565M
SELECT resource_type, request_mode, resource_description,request_session_id, DB_NAME(resource_database_id)as resource_database
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'
查询结果:用kill 命令无法结束进程7,提示非用户进程
FROM [dbo].[zs_OrderDetail] WITH (NOLOCK) 一样显示查询中
PS: 要慎用kill语句,不确定不要用喔,否则后果很严重.
2、如果在恢复,只要不是read-only状态,你还是不能读的
alter database [数据库名] set multi_user
DECLARE s_cur CURSOR FOR select 0 ,blocked from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid) union
select spid,blocked from sysprocesses where blocked>0 OPEN s_cur FETCH NEXT FROM s_cur INTO @spid,@bl WHILE @@FETCH_STATUS = 0
begin if @spid =0
select ' 引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + ' 进程号, 其执行的SQL 语法如下' else
select ' 进程号SPID :'+ CAST(@spid AS VARCHAR(10))+ ' 被' + ' 进程号SPID :'+ CAST(@bl AS VARCHAR(10)) +' 阻塞, 其当前进程执行的SQL 语法如下'
DBCC INPUTBUFFER (@bl ) FETCH NEXT FROM s_cur INTO @spid,@bl end CLOSE s_cur
DEALLOCATE s_cur
执行下这个看看
活动监视器显示的等待状态为:LCK_M_IS
引起数据库死锁的是: 7 进程号, 其执行的SQL 语法如下
No Event 0 NULL
进程号SPID :58 被 进程号SPID :7 阻塞, 其当前进程执行的SQL 语法如下
No Event 0 NULL进程7是要逆天了
引起数据库死锁的是: 7 进程号, 其执行的SQL 语法如下
No Event 0 NULL
进程号SPID :58 被 进程号SPID :7 阻塞, 其当前进程执行的SQL 语法如下
No Event 0 NULL进程7是要逆天了重启数据库
Date,Source,Severity,Message
06/18/2014 11:26:54,spid52,Unknown,Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_enumerrorlogs'. This is an informational message only; no user action is required.
06/18/2014 11:26:54,spid52,Unknown,Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
06/18/2014 11:26:47,spid14s,Unknown,Recovery of database 'eBayHelper' (7) is 10% complete (approximately 78 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
06/18/2014 11:26:47,spid14s,Unknown,718 transactions rolled forward in database 'eBayHelper' (7). This is an informational message only. No user action is required.
06/18/2014 11:26:47,spid14s,Unknown,Recovery of database 'eBayHelper' (7) is 10% complete (approximately 78 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
06/18/2014 11:26:45,spid14s,Unknown,Recovery of database 'eBayHelper' (7) is 6% complete (approximately 79 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
06/18/2014 11:26:43,登录,Unknown,Login failed for user 'NYCVFSDG8789\Administrator'. 原因: 无法打开在登录名属性中指定的数据库。 [客户端: <local machine>]
06/18/2014 11:26:43,登录,Unknown,错误: 18456,严重性: 14,状态: 40。
06/18/2014 11:26:42,spid14s,Unknown,Recovery of database 'eBayHelper' (7) is 4% complete (approximately 81 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
06/18/2014 11:26:39,spid14s,Unknown,Recovery of database 'eBayHelper' (7) is 1% complete (approximately 91 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
06/18/2014 11:26:39,spid14s,Unknown,Recovery of database 'eBayHelper' (7) is 0% complete (approximately 183 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
06/18/2014 11:26:39,spid14s,Unknown,Recovery of database 'eBayHelper' (7) is 0% complete (approximately 171 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
06/18/2014 11:26:39,spid14s,Unknown,Recovery of database 'eBayHelper' (7) is 0% complete (approximately 101 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
06/18/2014 11:26:38,spid7s,Unknown,Recovery is writing a checkpoint in database 'onsale4ever' (8). This is an informational message only. No user action is required.
06/18/2014 11:26:38,spid7s,Unknown,0 transactions rolled back in database 'onsale4ever' (8). This is an informational message only. No user action is required.
06/18/2014 11:26:37,spid15s,Unknown,29 transactions rolled forward in database 'onsale4ever' (8). This is an informational message only. No user action is required.
06/18/2014 11:26:37,spid13s,Unknown,Service Broker manager has started.
06/18/2014 11:26:37,spid13s,Unknown,The Database Mirroring protocol transport is disabled or not configured.
06/18/2014 11:26:37,spid13s,Unknown,The Service Broker protocol transport is disabled or not configured.
06/18/2014 11:26:37,spid10s,Unknown,Starting up database 'tempdb'.
06/18/2014 11:26:37,服务器,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
06/18/2014 11:26:37,服务器,Unknown,The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b<c/> state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
06/18/2014 11:26:37,服务器,Unknown,Dedicated admin connection support was established for listening locally on port 1434.
06/18/2014 11:26:37,服务器,Unknown,Server is listening on [ 127.0.0.1 <ipv4> 1434].
06/18/2014 11:26:37,服务器,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
06/18/2014 11:26:37,服务器,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
06/18/2014 11:26:37,服务器,Unknown,Server is listening on [ 'any' <ipv4> 14339].
06/18/2014 11:26:36,服务器,Unknown,A self-generated certificate was successfully loaded for encryption.
06/18/2014 11:26:36,spid10s,Unknown,Clearing tempdb database.
06/18/2014 11:26:36,spid15s,Unknown,Starting up database 'onsale4ever'.
06/18/2014 11:26:36,spid14s,Unknown,Starting up database 'eBayHelper'.
06/18/2014 11:26:36,spid13s,Unknown,Starting up database 'msdb'.
06/18/2014 11:26:36,spid13s,Unknown,SQL Server failed to communicate with filter daemon launch service (Windows error: 无法启动服务,原因可能是已被禁用或与其相关联的设备没有启动。<nl/>). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.
06/18/2014 11:26:36,spid13s,Unknown,错误: 9954,严重性: 16,状态: 1。
06/18/2014 11:26:36,spid7s,Unknown,Server name is 'NYCVFSDG8789'. This is an informational message only. No user action is required.
06/18/2014 11:26:36,spid10s,Unknown,Starting up database 'model'.
06/18/2014 11:26:36,spid7s,Unknown,The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
06/18/2014 11:26:36,spid7s,Unknown,Starting up database 'mssqlsystemresource'.
06/18/2014 11:26:36,spid7s,Unknown,SQL Trace ID 1 was started by login "sa".
06/18/2014 11:26:36,spid7s,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.
06/18/2014 11:26:36,spid7s,Unknown,SQL Server Audit has started the audits. This is an informational message. No user action is required.
06/18/2014 11:26:36,spid7s,Unknown,SQL Server Audit is starting the audits. This is an informational message. No user action is required.
06/18/2014 11:26:36,spid7s,Unknown,Resource governor reconfiguration succeeded.
06/18/2014 11:26:36,spid7s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
06/18/2014 11:26:36,spid7s,Unknown,0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
06/18/2014 11:26:36,spid7s,Unknown,2 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
06/18/2014 11:26:36,spid7s,Unknown,Starting up database 'master'.
06/18/2014 11:26:36,服务器,Unknown,Node configuration: node 0: CPU mask: 0x00000003:0 Active CPU mask: 0x00000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
06/18/2014 11:26:36,服务器,Unknown,Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
06/18/2014 11:26:36,服务器,Unknown,Detected 2 CPUs. This is an informational message; no user action is required.
06/18/2014 11:26:36,服务器,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
06/18/2014 11:26:36,Server,Unknown,Registry startup parameters: <nl/> -d E:\mssql\mssql2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf<nl/> -e E:\mssql\mssql2008\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG<nl/> -l E:\mssql\mssql2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
06/18/2014 11:26:36,Server,Unknown,This instance of SQL Server last reported using a process ID of 5624 at 2014-6-18 10:35:08 (local) 2014-6-18 2:35:08 (UTC). This is an informational message only; no user action is required.
06/18/2014 11:26:36,Server,Unknown,Logging SQL Server messages in file 'E:\mssql\mssql2008\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
06/18/2014 11:26:36,Server,Unknown,Authentication mode is MIXED.
06/18/2014 11:26:36,Server,Unknown,Server process ID is 4056.
06/18/2014 11:26:36,Server,Unknown,All rights reserved.
06/18/2014 11:26:36,Server,Unknown,(c) Microsoft Corporation.
06/18/2014 11:26:36,Server,Unknown,Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) <nl/> Apr 2 2010 15:53:02 <nl/> Copyright (c) Microsoft Corporation<nl/> Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) (Hypervisor)
eBayHelper ONLINE
数据库应该没问题
DBCC CHECKTABLE('zs_orderdetail') WITH PHYSICAL_ONLY;
DBCC CHECKTABLE('zs_orderdetail') WITH PHYSICAL_ONLY;CHECK 语句已中止。数据库包含延迟的事务。 所有的DBCC都是这样