工作中DB维护经常用到的脚本
-
Exclusive access could not be obtained because the database is in use
Alter Database DbName SET SINGLE_USER With ROLLBACK IMMEDIATE
Alter Database DbName SET MULTI_USER With ROLLBACK IMMEDIATE
-
Clear Cache
CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
-
view tsql cached objects
SELECT count (*) AS cached_pages_count, obj .name, index_id, i .name AS IndexName
FROM sys .dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_id , object_name (object_id) AS name
,index_id , allocation_unit_id
FROM sys .allocation_units AS au
INNER JOIN sys. partitions AS p
ON au. container_id = p .hobt_id
AND ( au.type = 1 OR au. type = 3)
UNION ALL
SELECT object_id , object_name (object_id) AS name
,index_id, allocation_unit_id
FROM sys .allocation_units AS au
INNER JOIN sys. partitions AS p
ON au. container_id = p .partition_id
AND au. type = 2
) AS obj
ON bd. allocation_unit_id = obj .allocation_unit_id
LEFT JOIN sysindexes i ON obj. object_id = i.id AND obj. index_id = i.indid
WHERE database_id = db_id()
GROUP BY obj.name , index_id, i.name
ORDER BY cached_pages_count DESC;
-
Clear SQL Log
USE Lba;
ALTER DATABASE Lba
SET RECOVERY SIMPLE; <span style="font-size: 20px; color: rgb(0, 128, 0); font-family: "Courier New";"–<设置简单恢复模式
DBCC SHRINKFILE (Lba_Log, 1);
ALTER DATABASE Lba
SET RECOVERY FULL; <span style="font-size: 20px; color: rgb(0, 128, 0); font-family: "Courier New";"–<恢复为原模式
GO
-
List db connections and Kill
EXEC sys .sp_who2
KILL (<SPID >)