Common trouble handling of MSSQL

Common troubleshooting and Maintenance methods for MSSQL
1. Database File shrinking
1.1 Free unused space
1.2 Reorganize pages before releasing unused space
1.3 Migrating all data in the specified file to other files in the same filegroup
2. log file shrinkage
2.1 Setting Simple Mode direct shrinkage
3.SQL Agent Job Broker
3.1 Error log correlation
3.1.1 Loop Generation error Log
4.AlwaysOn Problem Related
4.1 Backup and log shrinkage issues
4.2 error:35285, Severity:16, state:1
5. Database Logon/Startup issues
5.1 database is in a suspicious state
5.2 Client Login Encryption failed – Event id:17835

Common fault handling and maintenance methods of MSSQL 1. database file contraction 1.1 free unused space

  
 

   
  
 
   
--TRUNCATEONLY 仅仅是释放没使用的区 而不是页
 
   
USE [transfer]
 
   
GO
 
   
DBCC SHRINKFILE (N‘transfer‘ , 0, TRUNCATEONLY)
 
   
GO
 
  
  
 

1.2 Reorganize pages before releasing unused space

  
 

   
  
 
   
--检索每一个页 重新组织 释放未使用的页和区 然后再收缩
 
   
USE [transfer]
 
   
GO
 
   
DBCC SHRINKFILE (N‘FGFile13‘ , 32138)
 
   
GO
 
  
  
 

1.3 Migrating all data in the specified file to other files in the same filegroup

  
 

   
  

  
--查询文件以及文件组的基本信息
   
  

  
SELECT b.name ,
   
  

  
 b.type ,
   
  

  
 b.type_desc ,
   
  

  
 a.*
   
  

  
FROM sys.database_files a
   
  

  
 JOIN sys.filegroups b ON a.data_space_id = b.data_space_id
   
  

  
WHERE a.data_space_id = 2;
   
  

  

   
  

  
--新增文件到文件组
   
  
alter DATABASE  [  Transfer  ]   ADD FILE   (  NAME  =   N   ' File14 '    FILENAME  =   N   ' F:\DATAFILE\FILE14.NDF '      SIZE  =  Span class= "PLN" >  3090640   KB  ,  Span class= "PLN" > filegrowth  =    5  %)   to FILEGROUP  [ fg1  ;  
   
  

  

   
  

  
--将指定文件中的所有数据迁移到同一文件组中的其他文件，选择空闲的文件迁移过去。
   
  

  
DBCC SHRINKFILE(‘FGFile11‘, EMPTYFILE);
  
 

2. log file contraction 2.1 setting simple mode direct contraction

  
 

   
  

  
USE transfer
   
  

  
GO
   
  

  
--设置简单模式
   
  

  
ALTER DATABASE transfer SET RECOVERY SIMPLE;
   
  

  
GO
   
  

  
DBCC SHRINKFILE(N‘transfer_log‘, 10);
   
  

  
GO
   
  

  
--恢复成日志完整模式
   
  

  
ALTER DATABASE transfer SET RECOVERY FULL;
   
  

  
GO
  
 

3.SQL Agent Job Agent 3.1 error log related 3.1.1 Loop generation error Log

 
   
  

  
    
   
--ERRORLOG自动循环：ERRORLOG替换ERRORLOG.1 以此类推，需要先备份ERRORLOG.6，不然会被覆盖
  
    
   
USE master
  
    
   
GO
  
    
   
EXEC sys.sp_cycle_errorlog;
 
   
  

4.AlwaysOn issues related 4.1 Backup and log shrinkage issues

查看AlwaysOn组属性，可以得知哪一台服务器是首选辅助副本。如果设置为备份应在首选辅助副本执行，那么维护计划就应该建立在那台服务器上。

  
 

   
  
 
   
--判断是否是主辅助副本 / 主副本
 
   
SELECT master.sys.fn_hadr_backup_is_preferred_replica(N‘F10‘) AS preferredReplica, master.sys.fn_hadr_is_primary_replica(N‘F10‘) AS primaryReplica;
 
  
  
 

4.2 error:35285, Severity:16, state:1

 
   
  

  
    
   
ALTER DATABASE [DB_NAME] SET HADR RESUME
 
   
  

5. Database Logon/Startup issue 5.1 The database is in a suspicious state

 
   
  

  
    
   
USE master
  
    
   
GO
  
    
   
--查看数据库状态
  
    
   
SELECT state_desc, * 
  
    
   
FROM sys.databases
  
    
   
WHERE name = ‘F10‘;
 
   
  

• AlwaysOn secondary node warning, database in question (three solutions):

1. make the ALTER ONLINEagain, and command SQL Server to do another restore .
2. discards the current database and restores the backup .

3. set the database state to emergency and continue with the attempt to repair the database .

• SQL Server Status Switch graph:
  

  5.2 Client Login Encryption failed – Event id:17835

  
 

   
  

  
--客户端使用jtds等java类库进行登录报如下错误：
   
  

  
Encryption is required to connect to this server but the client library does not support encryption; the connection has been closed. Please upgrade your client library
   
  

  
--调整jdbc url参数可以解决此问题
   
  

  
ssl=require 或者 ssl=authenticate
  
 

Null

List of attachments

Common trouble handling of MSSQL