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)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库