超详细的sqlserver日常维护管理常用脚本总结

2019-6-4 09:39 |来自: 互联网 1875 0

摘要: 概述关于SQL SERVER 数据库日常维护，管理，巡检过程中可能经常需要用到一些SQL语句（亦或方法）来查看数据库服务器环境（操作系统版本, 磁盘空间，CPU，RAM信息），数据库信息（数据库版本，实例名称...）,数据库对象等。内容比较多，所以分两次做介绍。01查看数据库信息1、查看数据库服务器名称默认 ...

关键词： 数据库 SERVERNAME 实例 SELECT 方法信息 SERVERPROPERTY 27 名称服务器

概述

关于SQL SERVER 数据库日常维护，管理，巡检过程中可能经常需要用到一些SQL语句（亦或方法）来查看数据库服务器环境（操作系统版本, 磁盘空间，CPU，RAM信息），数据库信息（数据库版本，实例名称...）,数据库对象等。内容比较多，所以分两次做介绍。

01 查看数据库信息

1、查看数据库服务器名称

默认实例

SELECT @@SERVERNAME AS SERVERNAME; 
SELECT SERVERPROPERTY('servername') AS ServerName; 
SELECT srvname AS ServerName FROM sys.sysservers; 
SELECT SERVERPROPERTY('MachineName') AS ServerName

命名实例

SELECT SUBSTRING(@@SERVERNAME, 0, CHARINDEX('\', @@SERVERNAME))AS SERVERNAME;
 
SELECT SUBSTRING(CONVERT(VARCHAR(100),SERVERPROPERTY('servername')), 0,CHARINDEX('\',CONVERT(VARCHAR(100),SERVERPROPERTY('servername')))) AS ServerName;
 
SELECT SUBSTRING(srvname, 0, CHARINDEX('\', srvname)) AS ServerName FROM sys.sysservers;
 
SELECT SERVERPROPERTY('MachineName') AS ServerName

2、查看数据库实例名称

方法1：去服务（services.msc）里面查找SQL Server（××××）这样的服务，有多少个就就有多少数据库实例，一般默认实例是SQL Server (MSSQLSERVER)

方法2：去SQL配置管理器的SQL Server服务配置里面找上面描述的服务。

方法3：脚本查询，只是截取数据库服务名称的实例名（其实这个还真没必要，通过上面的脚本就可查看实例，注意默认实例）

SELECT @@SERVICENAME AS InstantName;
 
SELECT ISNULL(SERVERPROPERTY('InstanceName'),'MSSQLSERVER') AS InstanceName;

下面脚本仅对命名实例有效，默认实例查询处理的是计算机名称

SELECT SUBSTRING(@@SERVERNAME,CHARINDEX('\', @@SERVERNAME)+1,100) AS InstantName;
 
SELECT SUBSTRING(srvname, CHARINDEX('\', srvname) +1, 100) AS InstantName FROM sys.sysservers;

3、查看数据库版本号

方法1：相关SQL ：

SELECT SERVERPROPERTY('productversion') AS ProductVersion ,
 SERVERPROPERTY('productlevel') AS ProductLevel ,
 SERVERPROPERTY('edition') AS Edition

方法2：在数据库实例上单击右键，选择“属性”——》“常规”选项里面，你可以看到服务器名称，平台，操作系统,数据库版本信息。

4、查看数据库已经打的补丁

方法1：如上所示，可以通过9.00.4035.00 (Intel X86) 来确定已经数据库已经打的最新补丁SP3。

方法2：在数据库实例上单击右键，选择“属性”——》“常规”选项里面，可以通过看到的版本信息查看补丁

方法3：在SQL配置管理器,找到相应的实例的SQL Server服务,单击右键属性.

5、查看实例数据库的相关信息

查看实例有哪些数据库，创建时间、排序规则.......

方法1：SQL 查询. 其实在视图sys.databases里面你可以查看很多关于数据库的信息，例如，数据库用户访问设置,数据库的状态......

SELECT * FROM sys.databases

方法2: 在Mircrosoft SQL Server Management Studio管理器里面查看。

6、查询当前数据库的磁盘使用情况

如需要查询其他数据库，则需在前面指定数据库名称

EXEC sp_spaceused;

7、查看数据库启动相关参数

EXEC sp_configure;

8、查看数据库启动时间

方法1：

SELECT CONVERT(VARCHAR(30), LOGIN_TIME,120) AS StartDateTime
FROM master..sysprocesses WHERE spid=1

9、查看所有数据库名称及大小

方法1：

EXEC sp_helpdb;

方法2：

 SELECTdatabase_id AS DataBaseId ,
 DB_NAME(database_id) AS DataBaseName ,
 CAST(SUM(SIZE) * 8.0 / 1024 AS DECIMAL(8, 4)) AS [Size(MB)]
 FROMsys.master_files
GROUP BY database_id

10、查看所有数据库用户登录信息

EXEC sp_helplogins;

11、查看所有数据库用户所属的角色信息

EXEC sp_helpsrvrolemember

12、查看远端数据库用户登录信息

EXEC sp_helpremotelogin

02 服务器环境信息

1、查看数据库所在机器操作系统参数

EXEC master..xp_msver

详解：xp_msver返回有关 Microsoft SQL Server 的版本信息。xp_msver 还返回有关服务器的实际内部版本号的信息以及服务器环境的有关信息，例如处理器类型(不能获取具体型号)， RAM 的容量等等。用脚本基本上很难获取详细的硬件信息。

2、查看数据库服务器磁盘分区剩余空间。

EXEC master.dbo.xp_fixeddrives;

3、查看数据库服务器CPU/内存的大概信息

 SELECT cpu_count AS [Logical CPU Count] ,
 hyperthread_ratio AS [Hyperthread Ratio] ,
 cpu_count / hyperthread_ratio AS [Physical CPU Count],
 physical_memory_in_bytes / 1048576 AS [Physical Memory (MB)] ,
 sqlserver_start_time
 FROMsys.dm_os_sys_info
OPTION ( RECOMPILE ) ;

篇幅有限，这块就介绍到这了，后面再介绍下sqlserver其他部分，感兴趣的朋友可以关注一下~