SQLServer 常用命令整理

参考地址
https://www.cnblogs.com/OliverQin/p/5032014.html

shell是用户与操作系统对话的一个接口，通过shell告诉操作系统让系统执行我们的指令

xp_cmdshell在sqlserver中默认是关闭的存在安全隐患。

--打开xp_cmdshell
 EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;
--关闭xp_cmdshell
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;

以下是跟xp_cmdshell有关的小例子。

1.显示C盘下的内容，这个比较简单

 exec xp_cmdshell 'dir c:\'

#查看SQLServer版本

SELECT @@VERSION;

更改文件路径

官网地址：https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/move-user-databases?viewFallbackFrom=sql-server-2014&view=sql-server-ver15

--设置文件路径
ALTER DATABASE AdventureWorks2017 MODIFY FILE ( NAME = 'AdventureWorks2017', FILENAME = 'D:\DATA\AdventureWorks2017.mdf' );  
ALTER DATABASE AdventureWorks2017 MODIFY FILE ( NAME = 'AdventureWorks2017_log', FILENAME = 'D:\DATA\AdventureWorks2017_log.ldf' );  
--设置离线
ALTER DATABASE AdventureWorks2017 SET OFFLINE;  
--设置在线
ALTER DATABASE AdventureWorks2017 SET ONLINE;

#查看日志空间
dbcc sqlperf(logspace)

#查看日志信息
dbcc loginfo('WSS_Content_443_006')   --Status 为2 表示还不可以释放
#查看日志无法收缩的原因
SELECT name,log_reuse_wait_desc FROM sys.databases where name='DBname'

#查看正在使用的事务
DBCC OPENTRAN(WSS_Content_443_006)
CHECKPOIN  --采用将正在使用的VLF通过发生check point写入到日志文件

AlwaysOn 可用性组

副本之间的数据移动可以是同步的或异步的，Enterprise 版本允许同步多达三个副本（包括主要副本）

Standard 版本中的可用性组（称为 Basic 可用性组）支持两个副本（一个主要副本和一个次要副本），且可用性组中只有一个数据库。 Enterprise 版本不仅允许在一个可用性组中配置多个数据库，而

SQLServer的日期起始

SET DATEFIRST 1 --以星期一作为每周的起点
SELECT @@DATEFIRST

declare @da datetime='2022-07-31' select DATEPART(week,@da) 返回31 如果周日是起点则返回32 或者直接select DATEPART(isoweek,@da) 返回31

备份情况查询在msdb数据库中

SELECT b.backup_finish_date,a.physical_device_name,b.[database_name]
FROM dbo.backupmediafamily a INNER JOIN dbo.backupset b
ON a.media_set_id=b.media_set_id
ORDER BY 1 desc;

删除指定数据库的备份记录

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SQLTestDB'

数据库正在还原或单个用户锁定

RESTORE database DBName with recovery 
单个用户切换： ALTER DATABASE [IMXERP_TEST]SET MULTI_USER

#查看数据库中 表Table 的使用情况

SELECT
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND((SUM(a.total_pages) * 8.00 / 1024), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    CAST(ROUND((SUM(a.total_pages) * 8.00 / 1024 / p.rows), 2) AS NUMERIC(36, 2)) AS AverageRowSpaceKB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY t.NAME, p.Rows
order by t.name

#查看每个库Database 的信息

SELECT 
DB_NAME(database_id) AS [Database Name],[type_desc],
[Name] AS [Logical Name],
[Physical_Name] AS [Physical Name],
((size * 8) / 1024) AS [Size(MB)],
[differential_base_time] AS [Differential Base Time]
FROM sys.master_files