SQL Server运维常用语句 - 好文

1、查询数据文件和日志文件路径

select filename from [TestDB1].dbo.sysfiles

2、查询数据基本信息，包括数据库ID、创建日期、恢复模式、是否收缩等

select * from sys.databases where name = 'TestDB1'

3、查询收缩计划

--1).数据库是否开启自动收缩
SELECT is_auto_shrink_on, * FROM sys.databases

--2).维护计划/作业
SELECT job_id,* FROM msdb.dbo.sysjobsteps WHERE command LIKE '%shrink%'

SELECT * FROM msdb.dbo.sysjobs WHERE
job_id='3A257CC0-FA1E-4D67-91DD-FF9F31B061E1'

4、查询实例下的数据库总数量

select COUNT(*) from sys.databases --包括4个系统数据库和2个报表数据库

select * from sys.databases

5、删除实例下的所有数据库

注意：高危操作，注意识别连接实例和做好数据库备份！

之前客户场景，备份端连接的生产端实例，导致在备端SQL Server管理器里面直接误删除了生产端的数据库，所以执行该操作之前一定确认清楚
，最好做一次全备，再执行删除。
declare @sql varchar(8000) declare @dbname varchar(100)
--查询用户数据库（过滤掉master、msdb、ReportServer等系统数据库），创建游标 declare cur_my cursor for
select name from sys.databases where database_id not in (1,2,3,4, 5, 6) --打开游标
open cur_my fetch next from cur_my into @dbname --循环，直到游标为空 while
@@FETCH_STATUS=0 --删除数据库 begin set @sql='drop database '+ @dbname --print
(@sql) exec (@sql) fetch next from cur_my into @dbname end --关闭游标 close cur_my
--释放游标资源 deallocate cur_my

6、收缩数据库
--收缩数据库 declare @DBname nvarchar(255), @logname varchar(255), @shrinksql
varchar(8000) set @DBname = '' set @logname = @DBname + '_log' set @shrinksql =
'USE [master] ALTER DATABASE ' + @DBname + ' SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE ' + @DBname + ' SET TORN_PAGE_DETECTION ON WITH NO_WAIT ALTER
DATABASE ' + @DBname + ' SET RECOVERY SIMPLE ALTER DATABASE ' + @DBname + ' SET
TORN_PAGE_DETECTION ON USE ' + @DBname + --如果引号里面还有引号，里面的引号需要用两个引号进行转义，即表示为'' '
DBCC SHRINKFILE(N''' + @logname + ''', 10) USE [master] ALTER DATABASE ' +
@DBname + ' SET RECOVERY FULL WITH NO_WAIT ALTER DATABASE ' + @DBname + ' SET
TORN_PAGE_DETECTION ON WITH NO_WAIT ALTER DATABASE ' + @DBname + ' SET RECOVERY
FULL ALTER DATABASE ' + @DBname + ' SET TORN_PAGE_DETECTION ON' --print
@shrinksql exec (@shrinksql)
以TestDB1为例，执行语句如下：

7、查询收缩进度
--查询收缩进度 declare @DBname nvarchar(255) set @DBname = '' SELECT T.text,
R.Status, R.Command, DatabaseName = db_name(R.database_id), R.percent_complete
from sys.dm_exec_requests R cross apply sys.dm_exec_sql_text(R.sql_handle) T
WHERE db_name(R.database_id)=@DBname AND T.text LIKE '%DBCC%' GO

8、设置用户模式

（1）设置为当用户模式：

ALTER DATABASE TestDB1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

立即回滚所有未完成事务，并将数据库设置为单用户模式。

有时候数据库在占用时，想做一些操作，无法操作。可以尝试将数据库切换为单用户模式来操作，操作完之后再切换回多用户模式即可。

（2）设置为多用户模式
ALTER DATABASE TestDB1 SET MULTI_USER

9、设置为完整恢复模式

ALTER DATABASE TestDB1 SET RECOVERY FULL WITH NO_WAIT

设置为简单恢复模式

ALTER DATABASE TestDB1 SET RECOVERY SIMPLE WITH NO_WAIT

10、查询页数据
--第一次查询时，需要使用DBCC TRACEON打开数据页跟踪标记 DBCC TRACEON (3604);
--数据库名|数据库ID，1，页ID（十进制），3 dbcc page('testdb1', 1, 51,3)

11、查询活动日志

select * from fn_dblog(NULL, NULL)

热门工具换一换