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
