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)