SQLSERVER小记

最新推荐文章于 2024-07-06 08:00:00 发布

_Zephyrus_

最新推荐文章于 2024-07-06 08:00:00 发布

阅读量150

点赞数

CC 4.0 BY-SA版权

分类专栏： SQLServer

本文链接：https://blog.youkuaiyun.com/wangxw1803/article/details/117663993

SQLServer 专栏收录该内容

15 篇文章

订阅专栏

SQLSERVER 锁表查询及解锁语句

--查看被锁表：
SELECT
request_session_id spid,
OBJECT_NAME(
resource_associated_entity_id
) tableName
FROM
sys.dm_tran_locks
WHERE
resource_type = 'OBJECT'
ORDER BY request_session_id ASC
--spid 锁表进程
--tableName 被锁表名

--根据锁表进程查询相应进程互锁的SQL语句
DBCC INPUTBUFFER (249)


-- 解锁：
DECLARE
@spid INT
SET @spid = 52--锁表进程
DECLARE
@SQL VARCHAR (1000)
SET @SQL = 'kill ' + CAST (@spid AS VARCHAR) EXEC (@SQL)


--生成解锁SQL
SELECT
DISTINCT 'DECLARE @spid INT SET @spid = ',request_session_id,' DECLARE @SQL VARCHAR (1000) SET @SQL = ''kill '' + CAST (@spid AS VARCHAR) EXEC (@SQL);' as s
FROM
sys.dm_tran_locks
WHERE
resource_type = 'OBJECT' --spid 锁表进程
--tableName 被锁表名

SQLSERVER 查看文件空间使用情况

SELECT a.name [文件名称]  
    ,cast(a.[size]*1.0/128 as decimal(12,1)) AS [文件设置大小(MB)]  
    ,CAST( fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [文件所占空间(MB)]  
    ,CAST( (fileproperty(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0  AS DECIMAL(12,1)) AS [所占空间率%]  
    ,CASE WHEN A.growth =0 THEN '文件大小固定，不会增长' ELSE '文件将自动增长' end [增长模式]  
    ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN '增量为固定大小'  
        WHEN A.growth > 0 AND is_percent_growth = 1 THEN '增量将用整数百分比表示'  
        ELSE '文件大小固定，不会增长' END AS [增量模式]  
    ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128as decimal(12,0)) AS VARCHAR)+'MB'  
        WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+'%'  
        ELSE '文件大小固定，不会增长' end AS [增长值(%或MB)]  
    ,a.physical_name AS [文件所在目录]  
    ,a.type_desc AS [文件类型]  
FROM sys.database_files  a  
INNER JOIN sys.sysfiles AS s ON a.[file_id]=s.fileid  
LEFT JOIN sys.dm_db_file_space_usage b ON a.[file_id]=b.[file_id]