SQL Server运维常用语句

客官°小女子只卖身不卖艺 2023-12-08 06:46 69阅读 0赞

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)  
\---------------------  
作者：rammuschow  
来源：CSDN  
原文：https://blog.csdn.net/rammuschow/article/details/81663525  
版权声明：本文为博主原创文章，转载请附上博文链接！