SQL Server数据库日常维护命令与脚本

SQL Server 数据库日常维护命令与脚本

1. 查看数据库信息

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. 查看数据库实例名称

   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. 查看数据库版本号

   SELECT  SERVERPROPERTY('productversion') AS ProductVersion ,
          SERVERPROPERTY('productlevel') AS ProductLevel ,
          SERVERPROPERTY('edition') AS Edition;
    
   SELECT @@VERSION AS PRODUCT_VERSION;

4. 查看数据库实例

   SELECT * FROM sys.databases;

5. 查看排序规则信息

   -- 实例排序规则
   SELECT SERVERPROPERTY(N'Collation');
    
   -- 数据库排序规则
   SELECT name, collation_name FROM sys.databases;

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

   EXEC sp_spaceused;
    
   -- 查看某个数据对象的大小
   EXEC sp_spaceused @objname;

7. 查看数据库启动的相关参数

   EXEC sp_configure;

8. 查看服务器启动时间

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

9. 查看所有数据库名称与大小

   -- 方法 1
   EXEC sp_helpdb;
    
   -- 方法 2
   SELECT database_id AS DataBaseId ,
         DB_NAME(database_id) AS DataBaseName ,
         CAST(SUM(SIZE) * 8.0 / 1024 AS DECIMAL(8, 4)) AS [Size(MB)]
    FROM sys.master_files 
    GROUP BY database_id;

10. 查看数据库的用户和进程信息

    -- 查看所有
    EXEC sp_who;
     
    -- 查看活动用户和进程
    EXEC sp_who 'active';

11. 查看所有数据库用户登录信息

    EXEC sp_helplogins;

12. 查看所有数据库用户所属的角色信息

    EXEC sp_helpsrvrolemember;

13. 更改某个数据对象的用户属主

    注意：更改对象名的任一部分都可能破坏脚本和存储过程。把一台服务器上的数据库用户登录信息备份出来可以用 add_login_to_aserver 脚本。查看某数据库下, 对象级用户权限 sp_helprotect

    EXEC sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'

14. 查看链接服务器

    EXEC sp_helplinkedsrvlogin;

15. 查看远端数据库用户登录信息

    EXEC sp_helpremotelogin;

16. 查看某数据库下某个数据对象的索引信息

    EXEC sp_helpindex @objname;

17. 查看某数据库下某个数据对象的的约束信息

    EXEC sp_helpconstraint @objname;

18. 查看表的相关信息

    EXEC sp_help 'TABLE_NAME';

19. 查看数据库数据文件情况

    -- 查看数据库实例各个数据库的数据文件信息
    SELECT database_id                 AS DataBaseId,
      DB_NAME(database_id)           AS DataBaseName,
      Name                           AS LogicalName,
      type_desc                      AS FileTypeDesc,
      Physical_Name                  AS PhysicalName,
      State_Desc                     AS StateDesc ,
      CASE WHEN max_size = 0  THEN N'不允许增长'
           WHEN max_size = -1 THEN N'自动增长'
           ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G'
      END                            AS MaxSize ,
      CASE WHEN is_percent_growth = 1
           THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'
           ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M'
      END                            AS Growth ,
      Is_Read_Only                   AS IsReadOnly ,
      Is_Percent_Growth              AS IsPercentGrowth ,
      CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) AS [Size(GB)]
    FROM sys.master_files;
     
    -- 查看单个数据库的数据文件信息
    SELECT  Name                    AS DataBaseName,
          Physical_Name           AS PhysicalName,
          type_desc               AS FileTypeDesc,
          State_Desc              AS StateDesc,
          ((size * 8.0) / 1024 / 1024 )   AS [Size(GB)],
          CASE WHEN max_size = 0  THEN N'不允许增长'
               WHEN max_size = -1 THEN N'自动增长'
               ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G'
          END AS MaxSize ,
          CASE WHEN is_percent_growth = 1
               THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'
               ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M'
          END AS Growth ,
          Is_Read_Only                             AS IsReadOnly      ,
          Is_Percent_Growth                        AS IsPercentGrowth ,
          CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) AS [Size(GB)]
    FROM  sys.database_files;
     
    -- 通过数据库名称查看数据文件
    SELECT fileid      AS FileId,
        groupid      AS GroupId,
        size         AS DataBaseSize,
        growth       AS Growth, 
        perf         AS Perf,
        name         AS NAME,
        filename     AS FILENAME
    FROM  <DatabaseName>.dbo.sysfiles ;

20. 查看数据库服务器各数据库日志文件的大小及利用率 / 状态

    DBCC SQLPERF(LOGSPACE);
     
    或
     
    EXEC ('DBCC SQLPERF(LOGSPACE)');

21. 查看当前数据库的文件状态

    EXEC ('DBCC showfilestats');

22. 查看数据库存储过程

    -- 方法 1
    EXEC sp_stored_procedures;
     
    -- 方法 2
    SELECT * FROM sys.procedures;
     
    -- 方法 3
    SELECT * FROM sys.sysobjects WHERE xtype='P';

23. 查看存储过程基本信息

    EXEC sp_help 'sp_who';

24. 查看存储过程源代码：

    -- 方法 1
    EXEC sp_helptext '<procedureName>'
     
    -- 方法 2
    SELECT  * FROM SYS.SQL_MODULES
    WHERE object_id = OBJECT_ID(N'<procedureName>');
     
    -- 方法 3
    SELECT s.text                     AS ProcedureText,
         s.encrypted                AS Encrypted,
         s.number                   AS number,
         CONVERT(NCHAR(2), o.xtype) AS xtype,
         DATALENGTH(s.text)         AS ProcedureLen
    FROM dbo.syscomments s, dbo.sysobjects o
    WHERE o.id = s.id AND s.id = OBJECT_ID(N'procedureName')
    ORDER BY s.number, s.colid
    OPTION  (ROBUST PLAN);

25. 检查数据库完整性

    DBCC checkdb(<DBName>)
     
    -- Tablock 选项提高速度
    DBCC checkdb(<DBName>) with tablock

2. 查看服务器环境信息

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_kb / 1024       AS [Physical Memory (MB)] ,
        sqlserver_start_time
   FROM sys.dm_os_sys_info
   OPTION  (RECOMPILE) ;

3. 数据库备份

1. 备份数据库

   declare @sql varchar(255)
   set @sql = 'backup database <DBName> to disk =''<File Path>'+ rtrim(convert(varchar, getdate(), 112)) +'.bak''';
   exec(@sql);

2. 删除 15 天前备份文件

   declare @sql varchar(255);
   set @sql = 'del <File Path>' + rtrim(convert(varchar, getdate()-15), 112)) + '.bak''';
   exec master..xp_cmdshell @sql;

3. 完全备份

   USE Master
   GO 
    
   declare @str varchar(100) 
   set @str='<FilePath>'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.bak' 
   BACKUP DATABASE <DBName> TO DISK=@str 
   WITH (
      RETAINDAYS=15,
      NOFORMAT,
      NOINIT,
      NAME=N'Demo 完整备份',
      SKIP,
      NOREWIND, 
      NOUNLOAD,
      STATS=10
   )
   GO

4. 差异备份

   USE Master 
   GO  
    
   declare @str varchar(100)  
    
   set @str='<FilePath>'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.diff'
    
   BACKUP DATABASE <DBName> TO DISK=@str 
   WITH (
    DIFFERENTIAL,
    RETAINDAYS=8,
    NOFORMAT,
    NOINIT, 
    NAME=N'Demo 差异备份',
    SKIP,
    NOREWIND, 
    NOUNLOAD,
    STATS=10
   )
   GO

5. 日志备份

   USE Master 
   GO  
    
   declare @str varchar(100)  
    
   set @str='<FilePath>'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.trn'  
    
   BACKUP LOG <DBName> TO DISK=@str  
   WITH (
      RETAINDAYS=3,
      NOFORMAT,
      NOINIT, 
      NAME=N'Demo 日志备份',
      SKIP,
      NOREWIND, 
      NOUNLOAD,
      STATS=10 
   )
   GO

4. 数据库常用操作

1. 数据库重命名、修改恢复模式、修改用户模式

   -- 数据库重命名
   ALTER DATABASE <DBName> MODIFY NAME = <NewDBName>
    
   -- 设置数据库为完整恢复模式
   alter database <DBName> set recovery full
    
   -- 只允许一个用户访问数据库, 并指定 10 秒后回滚事务  
   alter database <DBName> set single_user   
    with rollback after 10 seconds
    
   -- 只有 sysadmin,dbcreator,db_owner 角色的成员可以访问数据库
   -- rollback immediate: 立即回滚事务
   alter database <DBName> set restricted_user   
    with rollback immediate
    
   -- 多用户模式
   -- 不等待立即改变，如不能立即完成，那么会导致执行错误 
   alter database <DBName> set multi_user  
   with no_wait

2. 扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称

   -- 添加文件组
   ALTER DATABASE <DBName> ADD FILEGROUP <FileGroup>
    
   -- 添加数据文件
   ALTER DATABASE <DBName>
   ADD FILE
   (
    NAME = <FileName>,
    FILENAME = '<FilePath>',
    SIZE = 1mb,
    MAXSIZE = 10mb,
    FILEGROWTH = 1mb
   )
   TO FILEGROUP <FileGroup>
    
   -- 添加日志文件
   ALTER DATABASE <DBName>
   ADD LOG FILE
   (
    NAME = <LogName>,
    FILENAME = '<LogFilePath>',
    SIZE = 1MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 100KB
   )
    
   -- 修改数据文件的大小, 增长大小, 最大大小
   ALTER DATABASE <DBName>
   MODIFY FILE
   (
    NAME = <FileName>,
    SIZE = 2MB, -- 必须大于之前的大小, 否则报错
    MAXSIZE= 8MB,
    FILEGROWTH = 10%
   )
    
   -- 修改数据文件或日志文件的逻辑名称
   ALTER DATABASE <DBName>
   MODIFY FILE
   (
    NAME = <FileName>,
    NEWNAME = <NewFileName>
   )

3. 移动文件

   -- 由于在 SQL Server 中文件组、文件不能离线
   -- 所以必须把整个数据库设置为离线
   checkpoint
   go
    
   ALTER DATABASE <DBName>
   SET OFFLINE
   go
    
   -- 修改文件名称
   ALTER DATABASE <DBName>
   MODIFY FILE
   (
    NAME = <FileName>,
    FILENAME = '<FilePath>'
   )
   go
    
   -- 设置数据库在线
   ALTER DATABASE <DBName>
   SET ONLINE

4. 设置默认文件组、只读文件组

   -- 设置默认文件组
   ALTER DATABASE <DBName>
   MODIFY FILEGROUP <FileGroup> DEFAULT
    
   -- 设为只读文件组
   -- 如果文件已经是某个属性，不能再次设置相同属性
   ALTER DATABASE <DBName>
   MODIFY FILEGROUP <FileGroup> READ_WRITE

5. 收缩数据库、收缩文件

   -- 收缩数据库  
   DBCC SHRINKDATABASE(
    'test',    -- 要收缩的数据库名称或数据库 ID  
    10         -- 收缩后，数据库文件中空间空间占用的百分比  
   )  
    
   DBCC SHRINKDATABASE(
    'test',    -- 要收缩的数据库名称或数据库 ID
    10,        -- 收缩后，数据库文件中空闲空间占用的百分比
    NOTRUNCATE -- 在收缩时，通过数据移动来腾出自由空间  
   )  
    
   DBCC SHRINKDATABASE(
    'test',      -- 要收缩的数据库名称或数据库 ID
    10,          -- 收缩后，数据库文件中空间空间占用的百分比
    TRUNCATEONLY -- 在收缩时，只是把文件尾部的空闲空间释放  
   )
    
   -- 收缩文件  
   DBCC SHRINKFILE(
    wc_fg8,   -- 要收缩的数据文件逻辑名称
    7         -- 要收缩的目标大小，以 MB 为单位  
   )  
    
   DBCC SHRINKFILE(
    wc_fg8,   -- 要收缩的数据文件逻辑名称
    EMPTYFILE -- 清空文件，清空文件后，才可以删除文件  
   )

6. 删除文件、删除文件组

   • 要删除文件，必须要先把文件上的数据删除，或者移动到其他文件或文件组上

     -- 删除数据后，必须要清空文件的内容
     DBCC SHRINKFILE(<FileName>,EMPTYFILE)
      
     -- 删除文件，同时也在文件系统底层删除了文件
     ALTER DATABASE <DBName>
     REMOVE FILE <FileName>

   • 要删除文件组，必须先删除所有文件

     -- 最后删除文件组
     ALTER DATABASE <DBName>
     REMOVE FILEGROUP <FileGroupName>

7. 重新组织索引

   -- 重新组织索引
   ALTER INDEX [IndexName] ON [TableName] 
   REORGANIZE 
   WITH (LOB_COMPACTION = ON)
    
   -- 批量生成重组索引
   use test
   go
    
   select 'DBCC INDEXDEFRAG('+db_name()+','+o.name+','+i.name + ');'
        --,db_name(),
        --o.name,
        --i.name,
        --i.*
   from sysindexes i
   inner join sysobjects o on i.id = o.id
   where o.xtype = 'U'
      and i.indid >0
      and charindex('WA_Sys',i.name) = 0

8. 重建索引

   ALTER INDEX [IndexName] ON [TableName] 
   REBUILD PARTITION = ALL
   WITH ( 
       PAD_INDEX  = OFF, 
       STATISTICS_NORECOMPUTE  = OFF, 
       ALLOW_ROW_LOCKS  = ON, 
       ALLOW_PAGE_LOCKS  = ON, 
       ONLINE = OFF, 
       SORT_IN_TEMPDB = OFF 
   )