MSSQL資料庫健康檢查--SQL Server巡檢

lhrbest發表於2020-10-08
SQL資料庫Server

MSSQL資料庫健康檢查--SQL Server巡檢



MSSQL資料庫巡檢報告

Copyright (c) 2015-2100 (http://blog.itpub.net/26736162)  lhrbest. All rights reserved.

巡 檢 人：lhr ([blog：http://blog.itpub.net/26736162] [QQ：646634621] [Nickname：小麥苗] [微信公眾號：DB寶] [提供OCP、OCM、高可用最實用的培訓])
版 本 號：v1.0.0
修改日期：2020-10-07

[ 轉到頁底]

目錄

總體概況 資料庫伺服器基本資訊 資料庫啟動引數 所有資料庫資訊 所有資料庫檔案資訊 所有資料庫備份資訊
臨時資料庫使用情況 使用者和角色 查CPU瓶頸
鎖情況 鎖情況 計算資源等待和訊號量等待時間 使用者和程式資訊 阻塞的會話
SQL部分
索引部分
其它 無主鍵的表 錶行數top 10 檢視最近一週執行較慢的作業



總體概況

★ 資料庫伺服器基本資訊

MSSQL版本 當前時間 主機名 服務名 例項名 是否叢集 產品版本 認證模式 邏輯CPU數量 資料庫伺服器啟動時間 當前資料庫名稱 當前資料庫字符集 當前登入使用者 ServicePack Edition Collation ISFullText
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) 2020-10-07T21:08:11.597 ECS-7D41 ECS-7D41 MSSQLSERVER 0 10.50.1600.1 Mixed Authentication 2 2020-09-18 17:47:18 sany1234 936 sa RTM Enterprise Edition (64-bit) Chinese_PRC_CI_AS 1

★ 其它伺服器資訊

Index Name Internal_Value Character_Value
1 ProductName 0 Microsoft SQL Server
2 ProductVersion 655410 10.50.1600.1
3 Language 2052 中文(簡體，中國)
4 Platform 0 NT x64
5 Comments 0 SQL
6 CompanyName 0 Microsoft Corporation
7 FileDescription 0 SQL Server Windows NT - 64 Bit
8 FileVersion 0 2009.0100.1600.01 ((KJ_RTM).100402-1539 )
9 InternalName 0 SQLSERVR
10 LegalCopyright 0 Microsoft Corp. All rights reserved.
11 LegalTrademarks 0 Microsoft SQL Server is a registered trademark of Microsoft Corporation.
12 OriginalFilename 0 SQLSERVR.EXE
13 PrivateBuild 0
14 SpecialBuild 104857601
15 WindowsVersion 602931718 6.2 (9200)
16 ProcessorCount 2 2
17 ProcessorActiveMask 0 3
18 ProcessorType 8664
19 PhysicalMemory 4095 4095 (4294037504)
20 Product ID 0

★ 資料庫啟動引數（伺服器選項）

configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
1582 access check cache bucket count 0 0 65536 OFF Default hash bucket count for the access check result security cache 1 1
1583 access check cache quota 0 0 2147483647 OFF Default quota for the access check result security cache 1 1
16391 Ad Hoc Distributed Queries 1 0 1 ON Enable or disable Ad Hoc Distributed Queries 1 1
1550 affinity I/O mask 0 -2147483648 2147483647 OFF affinity I/O mask 0 1
1535 affinity mask 0 -2147483648 2147483647 OFF affinity mask 1 1
1551 affinity64 I/O mask 0 -2147483648 2147483647 OFF affinity64 I/O mask 0 1
1549 affinity64 mask 0 -2147483648 2147483647 OFF affinity64 mask 1 1
16384 Agent XPs 1 0 1 ON Enable or disable Agent XPs 1 1
102 allow updates 1 0 1 ON Allow updates to system tables 1 0
1548 awe enabled 0 0 1 OFF AWE enabled in the server 0 1
1579 backup compression default 0 0 1 OFF Enable compression of backups by default 1 0
1569 blocked process threshold (s) 0 0 86400 OFF Blocked process reporting threshold 1 1
544 c2 audit mode 0 0 1 OFF c2 audit mode 0 1
1562 clr enabled 0 0 1 OFF CLR user code execution enabled in the server 1 0
1577 common criteria compliance enabled 0 0 1 OFF Common Criteria compliance mode enabled 0 1
1538 cost threshold for parallelism 5 0 32767 5 cost threshold for parallelism 1 1
400 cross db ownership chaining 0 0 1 OFF Allow cross db ownership chaining 1 0
1531 cursor threshold -1 -1 2147483647 -1 cursor threshold 1 1
16386 Database Mail XPs 0 0 1 OFF Enable or disable Database Mail XPs 1 1
1126 default full-text language 2052 0 2147483647 2052 default full-text language 1 1
124 default language 30 0 9999 30 default language 1 0
1568 default trace enabled 1 0 1 ON Enable or disable the default trace 1 1
114 disallow results from triggers 0 0 1 OFF Disallow returning results from triggers 1 1
1578 EKM provider enabled 0 0 1 OFF Enable or disable EKM provider 0 1
1580 filestream access level 0 0 2 OFF Sets the FILESTREAM access level 1 0
109 fill factor (%) 0 0 100 OFF Default fill factor percentage 0 1
1567 ft crawl bandwidth (max) 100 0 32767 100 Max number of full-text crawl buffers 1 1
1566 ft crawl bandwidth (min) 0 0 32767 OFF Number of reserved full-text crawl buffers 1 1
1565 ft notify bandwidth (max) 100 0 32767 100 Max number of full-text notifications buffers 1 1
1564 ft notify bandwidth (min) 0 0 32767 OFF Number of reserved full-text notifications buffers 1 1
1505 index create memory (KB) 0 704 2147483647 OFF Memory for index create sorts (kBytes) 1 1
1570 in-doubt xact resolution 0 0 2 OFF Recovery policy for DTC transactions with unknown outcome 1 1
1546 lightweight pooling 0 0 1 OFF User mode scheduler uses lightweight pooling 0 1
106 locks 0 5000 2147483647 OFF Number of locks for all users 0 1
1539 max degree of parallelism 0 0 1024 OFF maximum degree of parallelism 1 1
1563 max full-text crawl range 4 0 256 4 Maximum  crawl ranges allowed in full-text indexing 1 1
1544 max server memory (MB) 3072 16 2147483647 3072 Maximum size of server memory (MB) 1 1
1536 max text repl size (B) 65536 -1 2147483647 65536 Maximum size of a text field in replication. 1 0
503 max worker threads 0 128 32767 OFF Maximum worker threads 0 1
1537 media retention 0 0 365 OFF Tape retention period in days 1 1
1540 min memory per query (KB) 1024 512 2147483647 1024 minimum memory per query (kBytes) 1 1
1543 min server memory (MB) 0 0 2147483647 16 Minimum size of server memory (MB) 1 1
115 nested triggers 1 0 1 ON Allow triggers to be invoked within triggers 1 0
505 network packet size (B) 4096 512 32767 4096 Network packet size 1 1
16388 Ole Automation Procedures 0 0 1 OFF Enable or disable Ole Automation Procedures 1 1
107 open objects 0 0 2147483647 OFF Number of open database objects 0 1
1581 optimize for ad hoc workloads 0 0 1 OFF When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload. 1 1
1557 PH timeout (s) 60 1 3600 60 DB connection timeout for full-text protocol handler (s) 1 1
1556 precompute rank 0 0 1 OFF Use precomputed rank for full-text query 1 1
1517 priority boost 0 0 1 OFF Priority boost 0 1
1545 query governor cost limit 0 0 2147483647 OFF Maximum estimated cost allowed by query governor 1 1
1541 query wait (s) -1 -1 2147483647 -1 maximum time to wait for query memory (s) 1 1
101 recovery interval (min) 0 0 32767 OFF Maximum recovery interval in minutes 1 1
117 remote access 1 0 1 ON Allow remote access 0 0
1576 remote admin connections 1 0 1 ON Dedicated Admin Connections are allowed from remote clients 1 0
1519 remote login timeout (s) 20 0 2147483647 20 remote login timeout 1 0
542 remote proc trans 0 0 1 OFF Create DTC transaction for remote procedures 1 0
1520 remote query timeout (s) 600 0 2147483647 600 remote query timeout 1 0
16392 Replication XPs 0 0 1 OFF Enable or disable Replication XPs 1 1
1547 scan for startup procs 0 0 1 OFF scan for startup stored procedures 0 1
116 server trigger recursion 1 0 1 ON Allow recursion for server level triggers 1 0
1532 set working set size 0 0 1 OFF set working set size 0 1
518 show advanced options 1 0 1 ON show advanced options 1 0
16387 SMO and DMO XPs 1 0 1 ON Enable or disable SMO and DMO XPs 1 1
16385 SQL Mail XPs 0 0 1 OFF Enable or disable SQL Mail XPs 1 1
1555 transform noise words 0 0 1 OFF Transform noise words for full-text query 1 1
1127 two digit year cutoff 2049 1753 9999 2049 two digit year cutoff 1 1
103 user connections 0 0 32767 OFF Number of user connections allowed 0 1
1534 user options 0 0 32767 OFF user options 1 0
16390 xp_cmdshell 1 0 1 ON Enable or disable command shell 1 1

★ 所有資料庫

資料庫ID 資料庫 建立時間 恢復模式 排序方式 使用者訪問模式 資料庫狀態 自動建立統計資訊 自動更新統計資訊 自動關閉 自動收縮 自動非同步更新統計資訊 相容性級別 日誌重用等待 頁檢測選項 是否cdc 資料檔案大小(MB) 日誌大小(MB) 資料庫大小(MB)
1 master 2003-04-08T09:13:36.390 SIMPLE Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 4.00 2.00 6.00
2 tempdb 2020-09-18T17:46:54.920 SIMPLE Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 8.00 0.50 8.50
3 model 2003-04-08T09:13:36.390 FULL Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 2.25 0.75 3.00
4 msdb 2010-04-02T17:35:08.970 SIMPLE Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 14.75 4.13 18.88
5 ReportServer 2020-08-28T09:35:42.397 FULL Latin1_General_CI_AS_KS_WS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 4.25 6.25 10.50
6 ReportServerTempDB 2020-08-28T09:35:42.660 SIMPLE Latin1_General_CI_AS_KS_WS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 2.25 0.81 3.06
7 testing 2020-08-29T09:16:05.590 FULL MULTI_USER ONLINE 1 1 1 1 0 80 NOTHING TORN_PAGE_DETECTION 1 530.50 2321.38 2851.88
8 htzhang 2020-08-28T12:37:20.040 SIMPLE MULTI_USER ONLINE 1 1 1 1 0 80 NOTHING TORN_PAGE_DETECTION 0 63.50 1.00 64.50
9 zhanght 2020-08-28T12:41:22.223 SIMPLE MULTI_USER ONLINE 1 1 1 1 0 80 NOTHING TORN_PAGE_DETECTION 0 74.44 1.00 75.44
10 zhanghaitian 2020-08-28T12:45:06.543 SIMPLE MULTI_USER ONLINE 1 1 1 1 0 80 NOTHING TORN_PAGE_DETECTION 0 192.50 99.75 292.25
11 sany1234 2020-08-30T00:20:55.920 FULL Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 1 1 0 80 LOG_BACKUP TORN_PAGE_DETECTION 1 64.63 622.50 687.13
12 LHRDB 2020-08-31T18:35:37.240 FULL Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 3.00 1.00 4.00
13 ggsdb 2020-09-05T21:36:35.690 FULL Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 3.00 1.00 4.00
14 kis 2020-09-07T10:47:59.643 FULL Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 515.19 0.81 516.00
15 lhrtemp 2020-09-28T09:10:48.503 FULL MULTI_USER ONLINE 1 1 1 1 0 80 NOTHING TORN_PAGE_DETECTION 0 1125.19 1.25 1126.44

★ 所有資料庫檔案資訊

資料庫 檔案id 檔名 檔案路徑 檔案型別 檔案狀態 是否百分比增長 增長量 大小(MB) avg_read avg_write io_stall_read_ms num_of_reads io_stall_write_ms num_of_writes
master 1 master D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf ROWS ONLINE 1 10% 4 5.2 0.5 274 52 5873 11009
master 2 mastlog D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf LOG ONLINE 1 10% 2 2.6 0.2 39 14 34699 168167
tempdb 1 tempdev D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ROWS ONLINE 1 10% 8 1.0 2.1 1734 1683 3555 1656
tempdb 2 templog D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf LOG ONLINE 1 10% 0 0.5 1.1 22 47 677 638
model 1 modeldev D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf ROWS ONLINE 0 1MB 2 1.8 0.7 106 59 2 2
model 2 modellog D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf LOG ONLINE 1 10% 0 0.0 2.9 0 5 44 14
msdb 1 MSDBData D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf ROWS ONLINE 1 10% 14 6.1 0.3 1139 186 4602 14614
msdb 2 MSDBLog D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf LOG ONLINE 1 10% 4 5.6 0.3 111 19 53969 206509
ReportServer 1 ReportServer D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf ROWS ONLINE 0 1MB 4 4.0 0.0 200 49 0 1
ReportServer 2 ReportServer_log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServer_log.LDF LOG ONLINE 1 10% 6 0.7 0.2 18 26 2 12
ReportServerTempDB 1 ReportServerTempDB D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf ROWS ONLINE 0 1MB 2 4.3 5.0 103 23 10 1
ReportServerTempDB 2 ReportServerTempDB_log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB_log.LDF LOG ONLINE 1 10% 0 1.4 0.4 11 7 3 7
testing 1 HH91Full_Data D:\MSSQL\data\testing.mdf ROWS ONLINE 1 10% 530 0.0 0.0 0 0 0 0
testing 2 HH91Full_Log D:\MSSQL\data\testing_1.ldf LOG ONLINE 1 10% 2321 0.0 0.0 0 0 0 0
htzhang 1 HH91Full_Data D:\GRASPⅡTOP+\Data\htzhang.mdf ROWS ONLINE 1 10% 63 0.0 0.0 0 0 0 0
htzhang 2 HH91Full_Log D:\GRASPⅡTOP+\Data\htzhang.ldf LOG ONLINE 1 10% 1 0.0 0.0 0 0 0 0
zhanght 1 HH91Full_Data D:\GRASPⅡTOP+\Data\zhanght.mdf ROWS ONLINE 1 10% 74 0.0 0.0 0 0 0 0
zhanght 2 HH91Full_Log D:\GRASPⅡTOP+\Data\zhanght.ldf LOG ONLINE 1 10% 1 0.0 0.0 0 0 0 0
zhanghaitian 1 HH91Full_Data D:\GRASPⅡTOP+\Data\zhanghaitian.mdf ROWS ONLINE 1 10% 192 0.0 0.0 0 0 0 0
zhanghaitian 2 HH91Full_Log D:\GRASPⅡTOP+\Data\zhanghaitian.ldf LOG ONLINE 1 10% 99 0.0 0.0 0 0 0 0
sany1234 1 HH91Full_Data D:\GRASPⅡTOP+\Data\sany1234.mdf ROWS ONLINE 1 10% 64 5.0 0.4 3280 650 230 627
sany1234 2 HH91Full_Log D:\GRASPⅡTOP+\Data\sany1234.ldf LOG ONLINE 1 10% 622 0.3 0.4 155 505 151071 339583
LHRDB 1 LHRDB D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LHRDB.mdf ROWS ONLINE 0 1MB 3 3.1 0.0 83 26 0 1
LHRDB 2 LHRDB_log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LHRDB_log.ldf LOG ONLINE 1 10% 1 1.8 0.6 16 8 5 8
ggsdb 1 ggsdb D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ggsdb.mdf ROWS ONLINE 0 1MB 3 1.5 0.0 37 23 0 1
ggsdb 2 ggsdb_log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ggsdb_log.ldf LOG ONLINE 1 10% 1 0.8 0.3 7 8 7 23
kis 1 KunlunIdentityServer D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\kis.mdf ROWS ONLINE 0 512MB 515 5.4 1.0 193 35 3 2
kis 2 KunlunIdentityServer_log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\kis.ldf LOG ONLINE 0 512MB 0 1.8 0.2 14 7 2 8
lhrtemp 1 HH91Full_Data D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\lhrtemp.mdf ROWS ONLINE 1 10% 1125 0.0 0.0 0 0 0 0
lhrtemp 2 HH91Full_Log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\lhrtemp_1.ldf LOG ONLINE 1 10% 1 0.0 0.0 0 0 0 0

★ 查詢每個資料庫檔案的平均讀寫阻塞時間

資料庫 avg_read_stall_ms avg_write_stall_ms File Size(MB) physical_name type_desc io_stall_read_ms num_of_reads io_stall_write_ms num_of_writes
master 5.2 0.5 4.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf ROWS 274 52 5873 11009
master 2.6 0.2 2.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf LOG 39 14 34699 168167
tempdb 1.0 2.1 8.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ROWS 1734 1683 3555 1656
tempdb 0.5 1.1 0.50 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf LOG 22 47 677 638
model 1.8 0.7 2.25 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf ROWS 106 59 2 2
model 0.0 2.9 0.75 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf LOG 0 5 44 14
msdb 6.1 0.3 14.75 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf ROWS 1139 186 4602 14614
msdb 5.6 0.3 4.13 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf LOG 111 19 53969 206509
ReportServer 4.0 0.0 4.25 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf ROWS 200 49 0 1
ReportServer 0.7 0.2 6.25 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServer_log.LDF LOG 18 26 2 12
ReportServerTempDB 4.3 5.0 2.25 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf ROWS 103 23 10 1
ReportServerTempDB 1.4 0.4 0.81 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB_log.LDF LOG 11 7 3 7
sany1234 5.0 0.4 64.63 D:\GRASPⅡTOP+\Data\sany1234.mdf ROWS 3280 650 230 627
sany1234 0.3 0.4 622.50 D:\GRASPⅡTOP+\Data\sany1234.ldf LOG 155 505 151071 339583
LHRDB 3.1 0.0 3.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LHRDB.mdf ROWS 83 26 0 1
LHRDB 1.8 0.6 1.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LHRDB_log.ldf LOG 16 8 5 8
ggsdb 1.5 0.0 3.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ggsdb.mdf ROWS 37 23 0 1
ggsdb 0.8 0.3 1.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ggsdb_log.ldf LOG 7 8 7 23
kis 5.4 1.0 515.19 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\kis.mdf ROWS 193 35 3 2
kis 1.8 0.2 0.81 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\kis.ldf LOG 14 7 2 8

★ 所有資料庫備份資訊

伺服器名 使用者名稱 資料庫名 備份開始時間 備份結束時間 備份花費時間 備份檔案 備份型別 備份大小（MB） 壓縮大小（MB） first_lsn last_lsn checkpoint_lsn database_backup_lsn software_major_version software_minor_version software_build_version recovery_model collation_name database_version
GENSERVER GENSERVER\Administrator grasp92 2020-04-26T15:21:16 2020-04-26T15:21:20 4 D:\GRASPⅡTOP+\Data\grasp92 Full Backup 72.35 72.35 14362000000026100001 14362000000026400001 14362000000026100003 13767000000012500002 8 0 2039 Chinese_PRC_CI_AS 539
GENSERVER GENSERVER\Administrator Graspdnys 2020-04-26T15:26:44 2020-04-26T15:26:52 8 D:\GRASPⅡTOP+\Data\GraspDNYS Full Backup 147.35 147.35 48766000000008300001 48766000000087100001 48766000000008300003 48104000000057700003 8 0 2039 Chinese_PRC_CI_AS 539
B27B01441\MS3002 NT AUTHORITY\SYSTEM kis 2020-09-07T11:06:13 2020-09-07T11:06:13 0 D:\kis001.bak Differential Database 4.25 0.10 148000000016400037 148000000020600001 148000000019100034 148000000016400037 10 50 6000 FULL Chinese_PRC_CI_AS 661
B27B01441\MS3002 NT AUTHORITY\SYSTEM kis 2020-09-07T10:30:24 2020-09-07T10:30:25 1 D:\kis000.bak Full Backup 4.25 0.41 148000000010800001 148000000018000001 148000000016400037 147000000047000037 10 50 6000 FULL Chinese_PRC_CI_AS 661
B27B01441\MS3002 NT AUTHORITY\SYSTEM kis 2020-09-05T23:54:56 2020-09-05T23:54:56 0 D:\kis1.bak Differential Database 4.25 0.10 148000000001900001 148000000003800001 148000000003600001 147000000047000037 10 50 6000 FULL Chinese_PRC_CI_AS 661
B27B01441\MS3002 NT AUTHORITY\SYSTEM kis 2020-09-05T22:07:01 2020-09-05T22:07:02 1 D:\kis.bak Full Backup 4.25 0.41 147000000044500034 147000000048600001 147000000047000037 147000000034800037 10 50 6000 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa lhrtemp 2020-09-28T09:30:15 2020-09-28T09:30:15 0 D:\SANYCHILE_20200928_OGG_START_LOG.bak Log 0.07 0.07 44405000000006200039 44405000000009100001 44405000000006200039 44405000000006200039 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa lhrtemp 2020-09-28T09:30:04 2020-09-28T09:30:15 11 D:\SANYCHILE_20200928_OGG_START.bak Full Backup 847.08 847.08 44405000000006200039 44405000000008400001 44405000000006200039 44113000000036700076 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa sany1234 2020-09-11T09:33:37 2020-09-11T09:33:38 1 d:\sany1234.bak Full Backup 49.08 49.08 14563000000006300037 14563000000007900001 14563000000006300037 14560000000041900099 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa sany1234 2020-08-30T11:14:09 2020-08-30T11:14:09 0 D:\GRASPⅡTOP+\Backup\sany123420200830111408499 Full Backup 49.08 49.08 14560000000041900099 14560000000045800001 14560000000041900099 14362000000026100003 10 50 1600 SIMPLE Chinese_PRC_CI_AS 661
SANY-HANGKANG sa SANYCHILE 2020-09-28T07:00:00 2020-09-28T07:00:08 8 D:\SANYCHILE_backup_2020_09_28_070000_6886029.bak Full Backup 1064.09 165.07 44113000000036700076 44113000000042500001 44113000000036700076 44108000000001600270 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-10T17:33:45 2020-09-10T17:33:52 7 D:\GRASPⅡTOP+\Backup\testing20200910173346228 Full Backup 487.50 487.50 14881000003068700001 14881000003100800001 14881000003097200092 14881000003077700258 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-10T17:22:02 2020-09-10T17:22:08 6 D:\GRASPⅡTOP+\Backup\HT Full Backup 487.44 487.44 14881000003068700001 14881000003087900001 14881000003077700258 14777000002195700129 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-08T10:32:32 2020-09-08T10:32:36 4 D:\GRASPⅡTOP+\Backup\testing20200908103234862 Full Backup 344.28 344.28 14777000002194500001 14777000002200800001 14777000002195700129 14777000001208800123 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-07T07:37:23 2020-09-07T07:37:27 4 D:\GRASPⅡTOP+\Backup\testing20200907073722518 Full Backup 344.28 344.28 14777000001208500001 14777000001213800001 14777000001208800123 14775000002243200157 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-02T00:56:14 2020-09-02T00:56:19 5 D:\GRASPⅡTOP+\Backup\testing20200902005613393 Full Backup 343.40 343.40 14775000002218800001 14775000002249800001 14775000002243200157 14775000001220400085 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-01T11:52:41 2020-09-01T11:52:45 4 D:\GRASPⅡTOP+\Backup\testing20200901115241205 Full Backup 343.28 343.28 14775000001219800001 14775000001223900001 14775000001220400085 14775000000156200039 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-08-31T11:19:21 2020-08-31T11:19:25 4 D:\GRASPⅡTOP+\Backup\testing20200831111920957 Full Backup 342.40 342.40 14775000000131600001 14775000000158000001 14775000000156200039 14568000000039500037 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-08-29T09:52:39 2020-08-29T09:52:39 0 D:\testing20200825215825849 Full Backup 49.07 49.07 14568000000039500037 14568000000041100001 14568000000039500037 14565000000034500076 10 50 1600 FULL Chinese_PRC_CI_AS 661
SERVER-11A3A1C5 sa testing 2020-08-26T10:57:07 2020-08-26T10:57:07 0 D:\testing20200825215825849 Full Backup 49.08 49.08 14565000000034500076 14565000000037600001 14565000000034500076 14564000000044200076 10 50 1600 SIMPLE Chinese_PRC_CI_AS 661
ECS-7D41 sa zhanghaitian 2020-08-30T00:13:06 2020-08-30T00:13:06 0 D:\GRASPⅡTOP+\Backup\zhanghaitian20200830001305591 Full Backup 58.20 58.20 48883000000309300087 48883000000312600001 48883000000309300087 48883000000298300137 10 50 1600 SIMPLE Chinese_PRC_CI_AS 661
ECS-7D41 sa zhanghaitian 2020-08-30T00:02:12 2020-08-30T00:02:12 0 D:\GRASPⅡTOP+\Backup\zhanghaitian20200830000211372 Full Backup 58.20 58.20 48883000000298300137 48883000000304000001 48883000000298300137 48882000000192800138 10 50 1600 SIMPLE Chinese_PRC_CI_AS 661
ECS-7D41 sa zhanghaitian 2020-08-28T12:50:40 2020-08-28T12:50:40 0 D:\GRASPⅡTOP+\Backup\zhanghaitian20200828125041136 Full Backup 58.20 58.20 48882000000192800138 48882000000198400001 48882000000192800138 48766000000008300003 10 50 1600 SIMPLE Chinese_PRC_CI_AS 661

★ 臨時資料庫使用情況

user_objects_kb internal_objects_kb version_store_kb freespace_kb
1024 512 64 47104

★ 使用者和角色

User_ID User_Status UserName Role_ID Role_Status RoleName
1 0 dbo 16384 0 db_owner
2 0 guest 0 0
3 0 INFORMATION_SCHEMA 0 0
4 0 sys 0 0
5 0 cdc 16384 0 db_owner

★ 查CPU瓶頸

scheduler_id current_tasks_count runnable_tasks_count
0 8 0
1 13 0
[ 回到目錄]

鎖情況

★ 資料庫裡的鎖情況

request_session_id db_name obj_name resource_description request_type request_status request_mode
73 msdb LOCK GRANT IS
73 msdb LOCK GRANT Sch-S
73 msdb LOCK GRANT Sch-S
73 msdb LOCK GRANT IS
73 tempdb LOCK GRANT IS
73 tempdb LOCK GRANT IS
73 msdb LOCK GRANT IS

★ 計算資源等待和訊號量等待時間

% Signal (CPU) Waits % Resource Waits
0.56 99.44

★ 使用者和程式資訊

spid status login hostname blkby dbname command cputime diskio lastbatch programname spid2 requestid
1 BACKGROUND sa . . RESOURCE MONITOR 2296 0 09/18 7:47:18: 1 0
2 BACKGROUND sa . . XE TIMER 484 0 09/18 7:47:18: 2 0
3 BACKGROUND sa . . XE DISPATCHER 93 0 09/18 7:47:18: 3 0
4 BACKGROUND sa . . LAZY WRITER 8046 0 09/18 7:47:18: 4 0
5 BACKGROUND sa . . LOG WRITER 2734 0 09/18 7:47:18: 5 0
6 BACKGROUND sa . . LOCK MONITOR 15 0 09/18 7:47:18: 6 0
7 BACKGROUND sa . . master SIGNAL HANDLER 0 0 09/18 7:47:18: 7 0
8 sleeping sa . . master TASK MANAGER 0 0 09/18 7:47:18: 8 0
9 BACKGROUND sa . . master TRACE QUEUE TASK 125 0 09/18 7:47:18: 9 0
10 BACKGROUND sa . . master BRKR TASK 0 0 09/18 7:47:18: 10 0
11 BACKGROUND sa . . zhanghaitian CHECKPOINT 7281 13223 09/18 7:47:18: 11 0
12 BACKGROUND sa . . master TASK MANAGER 0 0 09/18 7:47:18: 12 0
13 sleeping sa . . master TASK MANAGER 0 103 09/18 7:47:18: 13 0
14 BACKGROUND sa . . master BRKR EVENT HNDLR 15 30 09/18 7:47:18: 14 0
15 BACKGROUND sa . . master BRKR TASK 46 0 09/18 7:47:18: 15 0
16 BACKGROUND sa . . master BRKR TASK 0 0 09/18 7:47:18: 16 0
17 sleeping sa . . master TASK MANAGER 0 195 09/18 7:47:18: 17 0
18 sleeping sa . . master TASK MANAGER 0 165 09/18 7:47:18: 18 0
19 sleeping sa . . master TASK MANAGER 0 199 09/18 7:47:18: 19 0
20 sleeping sa . . master TASK MANAGER 0 195 09/18 7:47:18: 20 0
21 sleeping sa . . master TASK MANAGER 0 205 09/18 7:47:18: 21 0
22 sleeping sa . . master TASK MANAGER 0 195 09/18 7:47:18: 22 0
23 sleeping sa . . master TASK MANAGER 0 168 09/18 7:47:18: 23 0
24 sleeping sa . . master TASK MANAGER 0 162 09/18 7:47:18: 24 0
25 sleeping sa . . master TASK MANAGER 0 176 09/18 7:47:18: 25 0
26 sleeping sa . . master TASK MANAGER 0 223 09/18 7:47:18: 26 0
51 sleeping NT AUTHORITY\SYSTEM ECS-7D41 . msdb AWAITING COMMAND 78 29 09/18 7:47:19: SQLAgent - Generic Refresher 51 0
52 SUSPENDED NT AUTHORITY\SYSTEM ECS-7D41 . sany1234 WAITFOR 1640 717 09/18 7:47:20: SQLAgent - TSQL JobStep (Job 0x3889FE6ECAC14E4FB02D05F87DF2C47D : Step 2) 52 0
53 sleeping sa LHR . sany1234 AWAITING COMMAND 859 945 10/07 8:22:56: Microsoft SQL Server Management Studio - 查詢 53 0
54 sleeping sa ECS-7D41 . master AWAITING COMMAND 31 150 09/30 5:23:14: Microsoft SQL Server Management Studio 54 0
55 sleeping sa LHR . sany1234 AWAITING COMMAND 110 5 10/07 9:56:53: Microsoft SQL Server Management Studio - 查詢 55 0
56 sleeping NT AUTHORITY\SYSTEM ECS-7D41 . msdb AWAITING COMMAND 43477 41182 10/07 1:08:00: SQLAgent - Alert Engine 56 0
57 sleeping NT AUTHORITY\SYSTEM ECS-7D41 . msdb AWAITING COMMAND 93 125353 10/07 1:08:00: SQLAgent - Job invocation engine 57 0
58 sleeping sa LHR . sany1234 AWAITING COMMAND 2360 27 10/07 8:23:02: Microsoft SQL Server Management Studio - 查詢 58 0
59 sleeping sa LHR . master AWAITING COMMAND 16 0 10/07 6:57:13: Microsoft SQL Server Management Studio 59 0
60 sleeping sa LHR . sany1234 AWAITING COMMAND 63 7 10/07 8:23:33: Microsoft SQL Server Management Studio - 查詢 60 0
61 sleeping sa LHR . sany1234 AWAITING COMMAND 172 1 10/07 9:38:12: Microsoft SQL Server Management Studio - 查詢 61 0
62 sleeping sa LHR . sany1234 AWAITING COMMAND 47 6 10/07 9:36:49: Microsoft SQL Server Management Studio - 查詢 62 0
63 sleeping sa LHR . sany1234 AWAITING COMMAND 358 13 10/07 0:09:29: Microsoft SQL Server Management Studio - 查詢 63 0
64 sleeping sa LHR . sany1234 AWAITING COMMAND 187 2 10/07 9:46:10: Microsoft SQL Server Management Studio - 查詢 64 0
65 sleeping sa LHR . sany1234 AWAITING COMMAND 0 0 10/07 0:33:18: Microsoft SQL Server Management Studio - Transact-SQL IntelliSense 65 0
66 sleeping sa LHR . sany1234 AWAITING COMMAND 203 5 10/07 0:23:16: Microsoft SQL Server Management Studio - 查詢 66 0
67 sleeping sa LHR . ggsdb AWAITING COMMAND 329 16 10/07 0:19:02: Navicat 67 0
68 sleeping sa LHR . sany1234 AWAITING COMMAND 375 8 10/07 0:23:07: Microsoft SQL Server Management Studio - 查詢 68 0
69 sleeping sa LHR . master AWAITING COMMAND 889 1967 10/07 0:16:45: Navicat 69 0
70 sleeping sa LHR . sany1234 AWAITING COMMAND 375 11 10/07 1:08:00: Microsoft SQL Server Management Studio - 查詢 70 0
71 sleeping NT AUTHORITY\SYSTEM ECS-7D41 . ReportServer AWAITING COMMAND 0 0 10/07 1:08:01: Report Server 71 0
72 sleeping NT AUTHORITY\SYSTEM ECS-7D41 . ReportServer AWAITING COMMAND 0 0 10/07 1:08:01: Report Server 72 0
73 RUNNABLE sa LHR . sany1234 SELECT INTO 62 6 10/07 1:08:11: Microsoft SQL Server Management Studio - 查詢 73 0

★ 阻塞的會話

blocking_session_id wait_duration_ms session_id
[ 回到目錄]

其他

★ 沒有主鍵的表

資料庫 模式 表名
sany1234 dbo DlyNdxDeposit
sany1234 dbo FVchcodeDlyNdx
sany1234 dbo B_Snbakdly
sany1234 dbo profitRecordbak
sany1234 dbo PY_CheckedCount
sany1234 dbo StockDlyDetail
sany1234 dbo IniDlyDetail
sany1234 dbo Tmp_TranWxPayInfo
sany1234 dbo t_jxc_GroupDetail
sany1234 dbo t_jxc_GroupMain

★ 錶行數TOP 10

資料庫 表名 行數
sany1234 VchColConfig 2172
sany1234 lsn_time_mapping 1079
sany1234 xwc_SysMenu 721
sany1234 xwc_functionlist 573
sany1234 T_ReportTree 547
sany1234 city 368
sany1234 T_ReportInfo 228
sany1234 t_gbl_FieldTypeList 228
sany1234 sysdata 170
sany1234 t_gbl_ActionList 123

★ 檢視最近一週執行較慢的作業

name start_execution_date ExecutedMin AvgRuntimeOnSucceed
syspolicy_purge_history 2020-10-07T02:00:00 68891 1
cdc.testing_cleanup 2020-10-07T02:00:00 68891 5
cdc.sany1234_cleanup 2020-10-07T02:00:00 68891 0

[ 回到目錄]


About Me

........................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在個人微 信公眾號（ DB寶）上有同步更新

● QQ群號： 230161599 、618766405，微信群私聊

● 個人QQ號（646634621），微 訊號（db_bao），註明新增緣由

● 於 2020年10月 在西安完成

● 最新修改時間：2020年10月

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

● 小麥苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/

● 小麥苗OCP、OCM、高可用、DBA學習班： http://blog.itpub.net/26736162/viewspace-2148098/

● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/

........................................................................................................................

請掃描下面的二維碼來關注小麥苗的微 信公眾號（ DB寶）及QQ群（230161599、618766405）、新增小麥苗微 信（db_bao）， 學習最實用的資料庫技術。

........................................................................................................................

 

 



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2725669/，如需轉載，請註明出處，否則將追究法律責任。

相關文章