526互联

SQL SERVER 数据库的常用DBA命令

发布时间 2023-12-27 17:02:42作者: 西伯利亚寒冬

查看表是否死锁

 select *
  from master..SysProcesses
 where db_Name(dbID) = '数据库名'
   and spId <> @@SpId
   and dbID <> 0
   and blocked >0;

或者

select 
    request_session_id spid,   
    OBJECT_NAME(resource_associated_entity_id) tableName    from 
    sys.dm_tran_locks   where 
    resource_type='OBJECT'

杀死进程

kill 进程号

sql server数据库处理【正在恢复】状态，查看恢复进度

DECLARE @DBName VARCHAR(64) = '数据库名字'

DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT INTO @ErrorLog
EXEC sys.sp_readerrorlog 0, 1, 'Recovery of database', @DBName

SELECT 
[LogDate]
,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
,[TEXT]

查看数据库日志大小

 dbcc sqlperf(logspace)

收缩数据库日志

USE [master]
 GO
 ALTER DATABASE [数据库名字] SET RECOVERY SIMPLE WITH NO_WAIT 
GO
  ALTER DATABASE [数据库名字] SET RECOVERY SIMPLE  --简单模式--
GO
 USE [数据库名字]----blog为需要清除日志的数据库名称
GO
DBCC SHRINKFILE (N'perf_Log' , 11, TRUNCATEONLY) ----perf_Log需要清除日志的数据库名日志名称
 GO
 USE [master]
 GO
 ALTER DATABASE [数据库名字] SET RECOVERY FULL WITH NO_WAIT---
 GO
ALTER DATABASE [数据库名字] SET RECOVERY FULL --还原为完全模式--

查看数据库中表的大小

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY 
    t.NAME, p.Rows
ORDER BY 
    t.Name

查看数据库的表的索引碎片占比

  • 逻辑扫描碎片 小于5% 不做索引的重组和重建
  • 5% - 30% 做索引的重组
  • 大于30% 做索引的重建
DBCC SHOWCONTIG('charge_item_detail') WITH ALL_INDEXES
  • 重新生成索引将删除该索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。
  ALTER INDEX 索引名 on 表名 REBUILD
  • 重新组织索引是通过对叶页进行物理重新排序，使其与叶节点的逻辑顺序（从左到右）相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。使页有序可以提高索引扫描的性能。索引在分配给它的现有页内重新组织，而不会分配新页。如果索引跨多个文件，将一次重新组织一个文件，不会在文件之间迁移页。
  ALTER INDEX 索引名 on 表名 reorganize