Sql server DBA tips

【声明】本文为AdamsLee原创,转载请注明出自围炉网并保留本文有效链接:Sql server DBA tips, 转载请保留本声明!

工作中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 >)

此条目发表在未分类分类目录,贴了标签。将固定链接加入收藏夹。