DAC 连接数据库需要做些什么

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: Below is what is collected Shows SQL Servers information Shows top 5 high cpu used statemants Shows who so logged in Shows long running cursor...

Below is what is collected

  • Shows SQL Servers information
  • Shows top 5 high cpu used statemants
  • Shows who so logged in
  • Shows long running cursors
  • Shows idle sessions that have open transactions
  • Shows free space in tempdb database
  • Shows total disk allocated to tempdb database
  • Show active jobs
  • Shows clients connected
  • Shows running batch
  • Shows currently blocked requests
  • Shows last backup dates
  • Shows jobs that are still executing
  • Shows failed MS SQL jobs report
  • Shows disabled jobs
  • Shows avail free DB space
  • Shows total DB size (.MDF+.LDF)
  • Show hard drive space available
 
 
USE master
GO

-- This stored procedure will give you infomation on the SQL server in question.-- Connect with DAC and then execute this stored procedure located in the master database

CREATE PROC sp_dba_DAC
AS
SELECT ' *** Start of DAC Report *** '

SELECT ' -- Shows SQL Servers information '
EXEC ( ' USE MASTER ' )
SELECT CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' MachineName ' )) AS ' MACHINE NAME '
,
CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ServerName ' )) AS ' SQL SERVER NAME '
,(
CASE WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' InstanceName ' )) IS NULL
THEN ' Default Instance '
ELSE CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' InstanceName ' ))
END ) AS ' INSTANCE NAME '
,
CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' EDITION ' )) AS EDITION
,
CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ProductVersion ' )) AS ' PRODUCT VERSION '
,
CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ProductLevel ' )) AS ' PRODUCT LEVL '
,(
CASE WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISClustered ' )) = 1
THEN ' Clustered '
WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISClustered ' )) = 0
THEN ' NOT Clustered '
ELSE ' INVALID INPUT/ERROR '
END ) AS ' FAILOVER CLUSTERED '
,(
CASE WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISIntegratedSecurityOnly ' )) = 1
THEN ' Integrated Security '
WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISIntegratedSecurityOnly ' )) = 0
THEN ' SQL Server Security '
ELSE ' INVALID INPUT/ERROR '
END ) AS ' SECURITY '
,(
CASE WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISSingleUser ' )) = 1
THEN ' Single User '
WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISSingleUser ' )) = 0
THEN ' Multi User '
ELSE ' INVALID INPUT/ERROR '
END ) AS ' USER MODE '
,
CONVERT ( CHAR ( 30 ), SERVERPROPERTY( ' COLLATION ' )) AS COLLATION



SELECT ' -- Shows top 5 high cpu used statemants '
SELECT TOP 5
total_worker_time
/ execution_count AS [ Avg CPU Time ]
,
SUBSTRING (st. text , ( qs.statement_start_offset / 2 ) + 1 ,
( (
CASE qs.statement_end_offset
WHEN - 1 THEN DATALENGTH (st. text )
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2 ) + 1 ) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time / execution_count DESC ;



SELECT ' -- Shows who so logged in '
SELECT login_name
,
COUNT (session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name ;



SELECT ' -- Shows long running cursors '
EXEC ( ' USE master ' )

SELECT creation_time
,cursor_id
,name
,c.session_id
,login_name
FROM sys.dm_exec_cursors ( 0 ) AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE DATEDIFF (mi, c.creation_time, GETDATE ()) > 5 ;

SELECT ' -- Shows idle sessions that have open transactions '
SELECT s. *
FROM sys.dm_exec_sessions AS s
WHERE EXISTS ( SELECT *
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id )
AND NOT EXISTS ( SELECT *
FROM sys.dm_exec_requests AS r
WHERE r.session_id = s.session_id ) ;

SELECT ' -- Shows free space in tempdb database '
SELECT SUM (unallocated_extent_page_count) AS [ free pages ]
,(
SUM (unallocated_extent_page_count) * 1.0 / 128 ) AS [ free space in MB ]
FROM sys.dm_db_file_space_usage ;



SELECT ' -- Shows total disk allocated to tempdb database '
SELECT SUM (size) * 1.0 / 128 AS [ size in MB ]
FROM tempdb.sys.database_files

SELECT ' -- Show active jobs '
SELECT DB_NAME (database_id) AS [ Database ]
,
COUNT ( * ) AS [ Active Async Jobs ]
FROM sys.dm_exec_background_job_queue
WHERE in_progress = 1
GROUP BY database_id ;



SELECT ' --Shows clients connected '
SELECT session_id
,client_net_address
,client_tcp_port
FROM sys.dm_exec_connections ;

SELECT ' --Shows running batch '
SELECT *
FROM sys.dm_exec_requests ;



SELECT ' --Shows currently blocked requests '
SELECT session_id
,status
,blocking_session_id
,wait_type
,wait_time
,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N ' suspended '



SELECT ' --Shows last backup dates ' AS ' '
SELECT B.name AS Database_Name
,
ISNULL ( STR ( ABS ( DATEDIFF ( day , GETDATE (), MAX (Backup_finish_date)))),
' NEVER ' ) AS DaysSinceLastBackup
,
ISNULL ( CONVERT ( CHAR ( 10 ), MAX (backup_finish_date), 101 ), ' NEVER ' ) AS LastBackupDate
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name
AND A.type = ' D '
GROUP BY B.Name
ORDER BY B.name

SELECT ' --Shows jobs that are still executing ' AS ' '
EXEC msdb.dbo.sp_get_composite_job_info NULL , NULL , NULL , NULL , NULL , NULL ,
1 , NULL , NULL

SELECT ' --Shows failed MS SQL jobs report ' AS ' '
SELECT name
FROM msdb.dbo.sysjobs A
,msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id
AND B.last_run_outcome = 0

SELECT ' --Shows disabled jobs ' AS ' '
SELECT name
FROM msdb.dbo.sysjobs
WHERE enabled = 0
ORDER BY name

SELECT ' --Shows avail free DB space ' AS ' '
EXEC sp_MSForEachDB ' Use ? SELECT name AS '' Name of File '' , size/128.0 -CAST(FILEPROPERTY(name, '' SpaceUsed '' ) AS int)/128.0 AS '' Available Space In MB '' FROM .SYSFILES '

SELECT ' --Shows total DB size (.MDF+.LDF) ' AS ' '
SET nocount ON
DECLARE @name SYSNAME
DECLARE @SQL NVARCHAR ( 600 ) -- Use temporary table to sum up database size w/o using group by
CREATE TABLE #databases
(
DATABASE_NAME SYSNAME
NOT NULL
,size
INT NOT NULL
)
DECLARE c1 CURSOR FOR SELECT name FROM master.dbo.sysdatabases
-- where has_dbaccess(name) = 1
-- Only look at databases to which we have access
OPEN c1
FETCH c1 INTO @name

WHILE @@fetch_status >= 0
BEGIN
SELECT @SQL = ' insert into #databases select N ''' + @name
+ ''' , sum(size) from ' + QUOTENAME ( @name )
+ ' .dbo.sysfiles ' -- Insert row for each database
EXECUTE ( @SQL )
FETCH c1 INTO @name
END
DEALLOCATE c1

SELECT DATABASE_NAME
,DATABASE_SIZE_MB
= size * 8 / 1000 -- Convert from 8192 byte pages to K and then convert to MB
FROM #databases
ORDER BY 1

SELECT SUM (size * 8 / 1000 ) AS ' --Shows disk space used - ALL DBs - MB '
FROM #databases ;

DROP TABLE #databases ;

SELECT ' --Show hard drive space available ' AS ' ' ;
EXEC master..xp_fixeddrives ;
SELECT ' *** End of Report **** ' ;

GO
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
2月前
|
关系型数据库 MySQL 数据库
6-2|测试连接数据库的命令
6-2|测试连接数据库的命令
|
3月前
|
测试技术 Windows
win7 x64配置TestLink执行用例提交BUG配置[连接mantisbt]
这篇文章介绍了如何在Windows 7 x64系统上配置TestLink以执行测试用例并提交BUG到MantisBT,包括修改TestLink和Mantis的配置文件以实现两者之间的BUG关联,以及在Mantis中删除BUG时自动删除TestLink中的关联。
33 0
|
SQL 关系型数据库 MySQL
QTP连接MYSQL数据库方法
1.首先安装mysql数据驱动,qtp在windows系统下操作连接mysql,所以下载mysql-connector-odbc-   5.1.8-win32.msi    下载地址:http://mysql.mirrors.pair.com/Downloads/Connector-ODBC/5.1/mysqlconnector-odbc-5.1.8-win32.msi 2.安装mysql驱动 ,默认安装即可。
1027 2
|
关系型数据库 MySQL 数据库连接
loadrunner通过odbc测mysql数据库语句
#include "lrd.h"  Action()  { double trans_time;  //定义一个double型变量用来保存事务执行时间  static LRD_INIT_INFO InitInfo = {LRD_INIT_INFO_EYECAT};  static...
1203 0
|
数据库 Windows
外部数据库驱动程序(1)中的意外错误
原文:用友u8各版本在输出的时候报错提示:外部数据库驱动程序(1)中的意外错误 ,报错内容“外部数据库驱动程序(1)中的意外错误”,经初步分析有以下解决方案:1、卸载微软的补丁:(1)如果是PC操作系统(一般是客户端)请按以下系统对应补丁进行处理:Win7系统卸载微软补丁KB4041678与KB4...
1908 0
下一篇
无影云桌面