SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DAC - What to Execute when Connected?


DAC - What to Execute when Connected?

Author
Message
Rudy Panigas
Rudy Panigas
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 1311
Comments posted to this topic are about the item DAC - What to Execute when Connected?



Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29225 Visits: 9671
Nice article. Anyone care to spend 20 minutes doing copy/paste work to put all that code in a single file and test it so that this article can actually usable?
nmoore
nmoore
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 173
Interesting article.

Just a note - xp_fixeddrives doesn't return any space info on mount points in the filesystem below the drive letters. If you have database file in such locations thats an important point.

Nigel Moore
======================
D.Oc
D.Oc
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1077 Visits: 6480
Ninja's_RGR'us (12/19/2008)
Nice article. Anyone care to spend 20 minutes doing copy/paste work to put all that code in a single file and test it so that this article can actually usable?


you mean something like this

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



-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."

Rudy Panigas
Rudy Panigas
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 1311
Thanks you for providing better code. I'm hoping that eventually we'll have a a nice stored procedure that you can execute when/if the SQL server gets into trouble.

Is there any other information you think should be collected?

Rudy



nmoore
nmoore
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 173
you can call

wmic volume get caption,capacity,freespace



to get the freespace on all drives including mount points.

Make this call from xp_cmdshell and place the results into a table if you want to processs them further.

Nigel Moore
======================
Rudy Panigas
Rudy Panigas
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 1311
Thanks to Ninja for providing the code in a copy/paste format. Not sure why the article's code is showing up that way.

By the way, if you copy the complete article and paste it into MS Word, the code is now converted to a usable format.

Rudy



Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29225 Visits: 9671
NP, it's easy to ask for something and wait for someone to have it done Wink.
Patrick Draper
Patrick Draper
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 40
Does this stored procedure SQL 2K or is it for 2005/08 only. I have had a look but get a number of errors.
Rudy Panigas
Rudy Panigas
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 1311
This works on SQL 2005/2008, x32bit and x64bit, stand alone or on a multi instance installation.
DAC is not available in SQL 2000 or earlier, but I wish it did.

Rudy



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search