數據庫相關檢測修復常用語句

自己的收集的一些　數據庫相關檢測修復常用語句

 

--數據庫相關檢測手段積累
--斷開對應數據庫所有其他用戶連接
--返回当前用户进程的会话 ID、登录名和用户名。
--获取连接SQL服务器的信息(詳細版--邹建)
--查詢死鎖,解決死鎖
--获取连接SQL服务器的信息
--開啓/關閉單用戶模式
--数据库检查/修復
--數據表|索引 檢測/修復
---------------------------------------------------------------------------
--斷開對應數據庫所有其他用戶連接
use master      --当前数据库不能是要断开的数据库，否则无法断开自身连接。
declare @a as varchar(8000)
set @a = ''
select @a=@a+'kill ' + cast(spid as varchar(10)) + ';' from sysprocesses where db_name(dbid)='DGON' and spid<>@@spid
select @a
--exec(@a)
---------------------------------------------------------------------------
--返回当前用户进程的会话 ID、登录名和用户名。
SELECT @@SPID AS 'ID',
       SYSTEM_USER AS 'Login Name',
       USER AS 'User Name'
---------------------------------------------------------------------------
--查詢死鎖,解決死鎖
use master
go
declare @spid int,@bl int
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 N'引起数死鎖的是:'+ltrim(@bl)+N'进程号,其执SQL语法如下'
    else
       select N'进程号：'+ ltrim(@bl)+N'被'+N'进程号：'+ltrim(@bl)+N'阻塞,其当前进 进程執行的SQL语法如下:'
    dbcc inputbuffer (@bl )
    fetch next from s_cur into @spid,@bl
end
---------------------------------------------------------------------------
--获取连接SQL服务器的信息
if object_id('p_getlinkinfo','P')is not null drop proc p_getlinkinfo
go
create proc p_getlinkinfo 
@dbname sysname=null, --要查詢的數據庫名,默認表示所有
@includeip bit=0      --是否顯示IP信息
as 
  begin
    declare @dbid int 
    set @dbid=db_id(@dbname)
    if object_id('tempdb..#tb')is not null drop table #tb
    if object_id('tempdb..#ip')is not null drop table #ip  
    create table #tb
       (id int identity(1,1),
        dbname sysname,
        hostname nchar(128),
        loginname nchar(128),
        net_address nchar(12),
        net_ip nvarchar(15),
        prog_name   nchar(128)) 
    insert into #tb(hostname,dbname,net_address,loginname,prog_name) 
    select distinct hostname,
         db_name(dbid),
         net_address,
         loginame,
         program_name
    from master..sysprocesses 
    where hostname!=''and(@dbid is null or dbid=@dbid) 

    if @includeip=0 goto lb_show --不顯示IP 
    declare @sql varchar(500),@hostname nchar(128),@id int 
    create table #ip(hostname nchar(128),a varchar(200)) 
    declare tb cursor local for select distinct hostname from #tb 
    open tb 
    fetch next from tb into @hostname 
    while @@fetch_status=0 
    begin 
     set @sql='ping   '+@hostname+'   -a   -n   1   -l   1' 
     insert #ip(a) exec master..xp_cmdshell @sql 
     update #ip    set  hostname=@hostname where hostname is null 
     fetch next from tb into @hostname 
    end 
    update #tb set net_ip=left(a,patindex('%:%',a)-1) 
    from #tb a inner join
    (select hostname,a=substring(a,patindex('Ping statistics for %:%',a)+20,20)
    from #ip 
    where a like'Ping statistics for %:%')b
    on a.hostname=b.hostname 
lb_show: 
    select   id,
           dbname,
           hostname,
           loginname,
           net_address,
           net_ip,
           prog_name 
    from #tb 
  end
  go   
exec p_getlinkinfo @dbname='DB_WIP',@includeip=1 
-----------------------------------------------------------------------------------------------------
--获取连接SQL服务器的信息(詳細版--邹建)
if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[p_getlinkinfo]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)  
  drop   procedure   [dbo].[p_getlinkinfo]  
  GO  
  /*--获取连接SQL服务器的信息  

  所有连接本机的:操作的数据库名,计算机名,用户名,网卡物理地址,IP地址,程序名  
  --邹建   2003.11(引用请保留此信息)--*/  
  /*--调用示例  
  --显示所有本机的连接信息  
  exec   p_getlinkinfo  
   
  --显示所有本机的连接信息,包含ip地址  
  exec   p_getlinkinfo   @includeip=1  
   
  --显示连接指定数据库的信息  
  exec   p_getlinkinfo   '客户资料'  
  --*/  
  create   proc   p_getlinkinfo  
  @dbname   sysname=null, --要查询的数据库名,默认查询所有数据库的连接信息  
  @includeip   bit=0 --是否显示IP地址,因为查询IP地址比较费时,所以增加此控制  
  as  
  declare   @dbid   int  
  set   @dbid=db_id(@dbname)  
   
  create   table   #tb(id   int   identity(1,1),dbname   sysname,hostname   nchar(128),loginname   nchar(128),net_address   nchar(12),net_ip   nvarchar(15),prog_name   nchar(128))  
  insert   into   #tb(hostname,dbname,net_address,loginname,prog_name)  
  select   distinct   hostname,db_name(dbid),net_address,loginame,program_name   from   master..sysprocesses  
  where   hostname<>''   and   (@dbid   is   null   or   dbid=@dbid)  
   
  if   @includeip=0   goto   lb_show     --如果不显示IP地址,就直接显示  
   
  declare   @sql   varchar(500),@hostname   nchar(128),@id   int  
  create   table   #ip(hostname   nchar(128),a   varchar(200))  
  declare   tb   cursor   local   for   select   distinct   hostname   from   #tb  
  open   tb  
  fetch   next   from   tb   into   @hostname  
  while   @@fetch_status=0  
  begin  
  set   @sql='ping   '+@hostname+'   -a   -n   1   -l   1'  
  insert   #ip(a)   exec   master..xp_cmdshell   @sql  
  update   #ip   set   hostname=@hostname   where   hostname   is   null  
  fetch   next   from   tb   into   @hostname  
  end  
   
  update   #tb   set   net_ip=left(a,patindex('%:%',a)-1)  
  from   #tb   a   inner   join   (  
  select   hostname,a=substring(a,patindex('Ping   statistics   for   %:%',a)+20,20)   from   #ip  
  where   a   like   'Ping   statistics   for   %:%')   b   on   a.hostname=b.hostname  

  lb_show:  
  select   id,数据库名=dbname,客户机名=hostname,用户名=loginname  
  ,网卡物理地址=net_address,IP地址=net_ip,应用程序名称=prog_name   from   #tb  
  go
-----------------------------------------------------------------------------------------------------
--開啓單用戶模式
EXEC sp_dboption 'userDB', 'single user','ture'
--恢復多用戶模式
EXEC sp_dboption 'userDB', 'single user','false'
-----------------------------------------------------------------------------------------------------
--数据库检查
DBCC CHECKDB ('userDB')
Go
--如果返回结果出现了红色的提示文字，说明数据库中存在错误，需要修复
--数据库修复
--DBCC CHECKDB ('userDB',repair_rebuild)
DBCC CHECKDB ('userDB',repair_allow_data_loss)
Go
--再次数据库检查，如果返回结果中没有了红色的提示文字，说明修复成功；
DBCC CHECKDB ('userDB')
Go
--否则意味着还需要更高级别的修复；尝试将上面修复语句的'repair_rebuild'换为'repair_allow_data_loss'再试，之后再次检查数据库。
--如果还有错误未修复，
---------------------------------------------------------------------------
--數據表檢測
--DBCC CHECKTABLE 对表和索引视图执行物理一致性检查。只用于向后兼容性的 NOINDEX 选项也适用于索引视图。
--对于指定的表，DBCC CHECKTABLE 检查其：
/*索引和数据页是否已正确链接。索引是否按照正确的顺序排列。各指针是否一致。
每页上的数据是否均合理。页面偏移量是否合理。 */
DBCC CHECKTABLE (DGMOCPR)

--數據表修復
exec sp_msforeachtable 'DBCC CHECKTABLE(DGMOCPR,REPAIR_ALLOW_DATA_LOSS)'

--重建對應表所有索引
DBCC DBREINDEX(DGMOCPR)
---------------------------------------------------------------------------