chenxin's blog
[原创]-Sql Server 常用命令整理

整理工作中常用的Sql 语句,方便使用时快速查找。

数据库日志文件压缩

ALTER DATABASE 数据库名
SET RECOVERY SIMPLE --数据库恢复模式设置为简单模式

GO

DBCC SHRINKFILE (数据库名,0) --将日志文件压缩为0M

GO

ALTER DATABASE 数据库名 
SET RECOVERY FULL --还原数据库恢复模式为完整模式。

 

查询各个表占用的磁盘空间大小

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

 

SQLServer 2019 Linux版本 更改监听端口

sudo /opt/mssql/bin/mssql-conf set network.tcpport 1450

 

SQLServer 2019 Linux版本关闭远程连接

EXEC sp_configure 'remote access', 0 ;  
GO  
RECONFIGURE ;  
GO  

 

SQL Server Linux 版本MSSQL-CLI 自定义端口连接

mssql-cli -S 127.0.0.1,端口号

查询高级配置信息(如允许使用最大的内存)

sp_configure 'show advanced options', 1;
reconfigure;
EXEC sp_configure;  

SQL Server Linux 版本配置允许使用的最大内存

EXEC sys.sp_configure 'max server memory (MB)', '512'
reconfigure with override
非特殊说明,本文版权归 陈新 所有,转载请注明出处.
本文标题:Sql Server 常用命令整理
(0)
(0)
微信扫一扫
支付宝扫一扫
写作不易,如果本文对你所有帮助,扫码请我喝杯饮料可以吗?
评论列表(0条)
还没有任何评论,快来发表你的看法吧!
{{item.userInfo.nickName}}{{item.userInfo.isBlogger?"(博主)":""}}
{{getUserType(item.userInfo.userType)}} {{formatCommentTime(item.commentDate)}}
回复
{{replyItem.userInfo.nickName}}{{replyItem.userInfo.isBlogger?"(博主)":""}}
@{{replyItem.reply.userInfo.nickName+":"}}
{{getUserType(replyItem.userInfo.userType)}} {{formatCommentTime(replyItem.commentDate)}}
回复
正在加载评论列表... 已经到底啦~~~
文章归档 网站地图 闽ICP备2020021271号-1 百度统计