SQL Server 2008 – Script de monitoramento SQLMON

A finalidade do uso desde script é para poder identificar;capturar informações referente ao seu banco de dados, geralmente relacionado a performance.

USE master

GO

IF OBJECT_ID('tempdb..#spBlockerPfe') IS NOT NULL

	DROP PROCEDURE #spBlockerPfe

GO

-----------------------------------------------------------------------------------------------------

CREATE PROCEDURE #spBlockerPFE(@timespan INT = 12)

AS

SET NOCOUNT ON

SET LOCK_TIMEOUT 30000

DECLARE @startDate DATETIME

DECLARE @prevDate DATETIME

DECLARE @endDate DATETIME

DECLARE @step INT = 0

DECLARE @time DATETIME

SELECT @startDate = GETDATE(), @prevDate = '1900-01-01', @step = 0

SET @endDate = DATEADD(HOUR,@timespan,GETDATE())

PRINT 'BLOCKER_PFE_SCRIPT_KATMAI Script v10.0.14 (SQL2008)'

PRINT ''

PRINT '  SQL Instance:       ' + @@SERVERNAME

PRINT '  SQL Version:        ' + CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR) + ' (' + CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR) + ')'

PRINT '  Start time:         ' + CONVERT(VARCHAR(24), GETDATE(), 121)

PRINT '  Scheduled end time: ' + CONVERT(VARCHAR(24), @endDate, 121)

-- SQL_TEXT --------------------------------------

CREATE TABLE #sqlquery_requested

(

	sql_handle			varbinary(64),

	stmt_start			int,

	stmt_end			int,

	query_hash			binary(8),

	query_plan_hash		binary(8)

)

CREATE TABLE #filehandle

(

	file_handle VARBINARY(8) PRIMARY KEY, 

	database_id INT, 

	file_id INT, 

	filename NVARCHAR(260)

)

SET @time = GETDATE()

EXEC #spBlockerPfe_0

WHILE @startDate < @endDate

BEGIN

	PRINT ''

	PRINT 'BLOCKER_PFE_BEGIN SqlMonData ' + CONVERT(VARCHAR(24), GETDATE(), 121)

	SET @startDate = GETDATE()

	EXEC #spBlockerPfe_1 

	EXEC #spBlockerPfe_1_handle

	IF @step % 12 = 0 

	BEGIN

		DECLARE @savePrevDate DATETIME = GETDATE()

		EXEC #spBlockerPfe_2 @prevDate

		SET @prevDate = @savePrevDate

		EXEC #spBlockerPfe_2_handle

	END

	SET @step = @step + 1

	PRINT ''

	PRINT 'BLOCKER_PFE_END SqlMonData ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate())) 

	RAISERROR('',0,1) WITH NOWAIT

	WAITFOR DELAY '0:0:5'

END

GO

-----------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#spBlockerPfe_0') IS NOT NULL

	DROP PROCEDURE #spBlockerPfe_0

IF OBJECT_ID('tempdb..#spBlockerPfe_1') IS NOT NULL

	DROP PROCEDURE #spBlockerPfe_1

IF OBJECT_ID('tempdb..#spBlockerPfe_2') IS NOT NULL

	DROP PROCEDURE #spBlockerPfe_2

IF OBJECT_ID('tempdb..#spBlockerPfe_1_handle') IS NOT NULL

	DROP PROCEDURE #spBlockerPfe_1_handle

IF OBJECT_ID('tempdb..#spBlockerPfe_2_handle') IS NOT NULL

	DROP PROCEDURE #spBlockerPfe_2_handle

-----------------------------------------------------------------------------------------------------

GO

CREATE PROCEDURE #spBlockerPfe_0

AS

SET NOCOUNT ON

DECLARE @time DATETIME

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN MachineInfo'

PRINT ''

PRINT 'GeneralInformation'

PRINT REPLICATE('-',100)

PRINT 'ServerName: ' + @@SERVERNAME

PRINT 'PhysicalName: ' + CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)

PRINT 'ProductVersion: ' + CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)

PRINT 'ProductLevel: ' + CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR)

PRINT 'ResourceVersion: ' + CAST(SERVERPROPERTY('ResourceVersion') AS VARCHAR)

PRINT 'ResourceLastUpdateDateTime: ' + CAST(SERVERPROPERTY('ResourceLastUpdateDateTime') AS VARCHAR)

PRINT 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS VARCHAR)

PRINT 'ProcessId: ' + CAST(SERVERPROPERTY('ProcessId') AS VARCHAR)

PRINT 'SessionId: ' + CAST(@@SPID AS VARCHAR)

PRINT 'Collation: ' + CAST(SERVERPROPERTY('Collation') AS VARCHAR(32))

PRINT ''

PRINT 'BLOCKER_PFE_END MachineInfo ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN @@version'

SELECT @@version AS 'version'

PRINT 'BLOCKER_PFE_END @@version ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN xp_msver'

EXEC xp_msver

PRINT 'BLOCKER_PFE_END xp_msver ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_sys_info'

SELECT 

	sqlserver_start_time, -- 2008

	cpu_count, hyperthread_ratio, 

	physical_memory_in_bytes/1024/1024 AS 'physical_memory(MB)',

	bpool_committed*8/1024 AS 'buffer_pool(MB)', 

	bpool_commit_target*8/1024 AS 'buffer_pool_target(MB)', 

	bpool_visible*8/1024 AS 'buffer_visible(MB)', 

	virtual_memory_in_bytes/1024/1024 AS 'virtual_memory(MB)',

	max_workers_count, scheduler_count

FROM sys.dm_os_sys_info

PRINT 'BLOCKER_PFE_END sys.dm_os_sys_info '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_cluster_nodes'

SELECT NodeName FROM sys.dm_os_cluster_nodes 

PRINT 'BLOCKER_PFE_END sys.dm_os_cluster_nodes '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_io_cluster_shared_drives'

SELECT DriveName FROM sys.dm_io_cluster_shared_drives 

PRINT 'BLOCKER_PFE_END sys.dm_io_cluster_shared_drives '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.configurations'

SELECT name, value=CAST(value AS VARCHAR(16)), value_in_use=CAST(value_in_use AS VARCHAR(16)) 

FROM sys.configurations

ORDER BY name

PRINT 'BLOCKER_PFE_END sys.configurations ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.databases'

SELECT 

d.database_id, d.name, state_desc=CAST(d.state_desc AS VARCHAR(20)), user_access_desc=CAST(d.user_access_desc AS VARCHAR(16)), d.compatibility_level, 

	d.create_date, collation_name=CAST(d.collation_name AS VARCHAR(64)), d.owner_sid, 

	d.log_reuse_wait_desc, 

	readonly = d.is_read_only, 

	autoclose = d.is_auto_close_on, 

	autoshrink = d.is_auto_shrink_on, 

	standby = d.is_in_standby, 

	cleanshut = d.is_cleanly_shutdown, 

	supplog = d.is_supplemental_logging_enabled, 

	snapshot = d.snapshot_isolation_state, 

	readsnap = d.is_read_committed_snapshot_on, 

	recovery = CAST(d.recovery_model_desc AS VARCHAR(8)), 

	pageverify =  CAST(d.page_verify_option_desc AS VARCHAR(8)), 

	autostat_crt = d.is_auto_create_stats_on, 

	autostat_upd = d.is_auto_update_stats_on, 

	autostat_async = d.is_auto_update_stats_async_on, 

	fulltext = d.is_fulltext_enabled, 

	trustworthy = d.is_trustworthy_on, 

	dbchain = d.is_db_chaining_on, 

	paramforced = d.is_parameterization_forced, 

	masterkey = d.is_master_key_encrypted_by_server, 

	rep_pub = d.is_published, 

	rep_sub = d.is_subscribed, 

	rep_merge = d.is_merge_published, 

	rep_dist = d.is_distributor, 

	sync_bkp = d.is_sync_with_backup, 

	sb_enabled = d.is_broker_enabled, 

	sb_guid = d.service_broker_guid, 

	datacorr = d.is_date_correlation_on

FROM sys.databases d

PRINT 'BLOCKER_PFE_END sys.databases '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.master_files'

-- VIEW ANY DEFINITION

SELECT 

	d.database_id, d.file_id, state_desc = CAST(d.state_desc AS VARCHAR(16)), type_desc=CAST(d.type_desc AS VARCHAR(16)), d.physical_name, d.file_guid, d.data_space_id, d.name, d.size, d.max_size, d.growth, d.is_media_read_only, d.is_read_only, d.is_sparse, d.is_percent_growth 

FROM sys.master_files d

PRINT 'BLOCKER_PFE_END sys.master_files '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.master_files[Size]'

-- VIEW ANY DEFINITION

SELECT 

	d.database_id, type_desc=CAST(d.type_desc AS VARCHAR(16)), 

	CAST(d.size AS BIGINT)*8/1024 AS 'Size(MB)', 

	CASE d.is_percent_growth 

		WHEN 0 THEN CAST(d.growth AS INT)*8/1024

		WHEN 1 THEN CAST(d.growth AS INT)*CAST(d.size AS INT)/100*8/1024

	END AS 'Growth(MB)',

	CASE d.is_percent_growth 

		WHEN 0 THEN CAST( (100*d.growth/d.size) AS SMALLINT )

		WHEN 1 THEN CAST( d.growth AS SMALLINT )

	END AS 'Growth(perc)',	

	d.physical_name

FROM sys.master_files d

ORDER BY d.physical_name

PRINT 'BLOCKER_PFE_END sys.master_files[Size] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.traces'

-- VIEW ANY DEFINITION

SELECT 

	t.id, t.status, t.path, t.max_size, t.stop_time, t.max_files, t.is_rowset, t.is_rollover, t.is_shutdown, t.is_default, t.buffer_count, t.buffer_size, t.file_position, t.reader_spid, t.start_time, t.last_event_time, t.event_count, t.dropped_event_count 

FROM sys.traces t

PRINT 'BLOCKER_PFE_END sys.traces '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

TRUNCATE TABLE #filehandle

INSERT #filehandle(file_handle, database_id, file_id, filename)

SELECT vfs.file_handle, vfs.database_id, vfs.file_id, f.physical_name FROM sys.dm_io_virtual_file_stats(-1,-1) vfs

	LEFT JOIN sys.master_files f ON vfs.database_id = f.database_id AND vfs.file_id = f.file_id

	WHERE file_handle<>0

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_sessions'

SELECT 

	s.session_id, 

	s.login_time,

	s.status, 

	s.cpu_time, s.memory_usage, s.total_scheduled_time, s.total_elapsed_time, 

	s.last_request_start_time, s.last_request_end_time, 

	s.reads, s.writes, s.logical_reads, 

	s.row_count, 

	s.prev_error

FROM sys.dm_exec_sessions s 

PRINT 'BLOCKER_PFE_END sys.dm_exec_sessions '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_connections/sessions'

SELECT 

	s.session_id, 

	s.group_id, 

	CAST(s.status AS VARCHAR(16)) AS 'status',

	CAST(s.host_name AS VARCHAR(20)) AS 'host_name', 

	CAST(s.login_name AS VARCHAR(32)) AS 'login_name', 

	CAST(s.program_name AS VARCHAR(64)) AS 'program_name', 

	s.host_process_id, 

	c.connection_id,

	CAST(s.original_login_name AS VARCHAR(32)) AS 'original_login_name', 

	s.client_interface_name, s.client_version, 

	CAST(c.auth_scheme AS VARCHAR(16)) AS 'auth_scheme', 

	CAST(c.net_transport AS VARCHAR(16)) AS 'net_transport', 

	c.client_net_address, c.client_tcp_port, 

	CAST(c.most_recent_sql_handle AS VARBINARY(26)) AS 'most_recent_sql_handle', 

	c.net_packet_size, c.encrypt_option,

	c.connect_time, s.login_time

FROM sys.dm_exec_connections c left join sys.dm_exec_sessions s on c.session_id = s.session_id

PRINT 'BLOCKER_PFE_END sys.dm_exec_connections/sessions '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

GO

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

CREATE PROCEDURE #spBlockerPfe_1

AS

SET NOCOUNT ON

SET LOCK_TIMEOUT 250

DECLARE @time DATETIME

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_requests'

SELECT 

	req.session_id, req.blocking_session_id AS 'blocked', 

	req.database_id AS db_id, req.command, 

	req.total_elapsed_time AS 'elapsed_time', req.cpu_time, req.granted_query_memory AS 'granted_memory', req.logical_reads, 

	req.wait_time, CAST(req.wait_type AS VARCHAR(16)) AS 'wait_type', 

	req.open_transaction_count AS 'tran_count', 

	req.reads, req.writes,  

	req.start_time, req.status, req.connection_id, req.user_id, 

	req.group_id, -- KATMAI (SQL2008)

	req.transaction_id, req.request_id, 

	CAST(req.plan_handle AS VARBINARY(26)) AS 'plan_handle', 

	CAST(req.sql_handle AS VARBINARY(26)) AS 'sql_handle', 

	req.nest_level,

	req.statement_start_offset AS 'stmt_start', req.statement_end_offset AS 'stmt_end', 

	req.query_hash, req.query_plan_hash

FROM sys.dm_exec_requests req

WHERE group_id > 1 AND session_id<>@@SPID -- KATMAI (SQL2008)

PRINT 'BLOCKER_PFE_END sys.dm_exec_requests '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_cursors'

SELECT

	c.session_id, c.cursor_id, 

	DATEDIFF(ms, c.creation_time, GETDATE()) AS 'elapsed_time', 

	c.worker_time, c.reads, c.writes, c.dormant_duration, c.fetch_buffer_start, 

	c.ansi_position, c.is_open, c.fetch_status, c.creation_time, 

	CAST(c.sql_handle AS VARBINARY(26)) AS 'sql_handle', 

	c.statement_start_offset AS 'stmt_start', c.statement_end_offset AS 'stmt_end', 

	c.plan_generation_num, c.is_async_population, c.is_close_on_commit, c.fetch_buffer_size

FROM sys.dm_exec_cursors(0) c

PRINT 'BLOCKER_PFE_END sys.dm_exec_cursors '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_query_memory_grants'

SELECT

	mg.session_id, 

	mg.query_cost, mg.dop, 

	convert(VARCHAR(12), datediff(ms,request_time,getdate())) AS 'elapsed_time',

	mg.wait_time_ms, 

	mg.resource_semaphore_id AS 'sem_id', mg.requested_memory_kb, mg.granted_memory_kb, 

	mg.used_memory_kb, mg.required_memory_kb, mg.max_used_memory_kb, mg.ideal_memory_kb, 

	mg.request_time, mg.grant_time, mg.timeout_sec, 

	mg.queue_id, mg.wait_order, 

	mg.request_id,

	CAST(mg.plan_handle AS VARBINARY(26)) AS 'plan_handle', 

	CAST(mg.sql_handle AS VARBINARY(26)) AS 'sql_handle', 

	mg.group_id, mg.pool_id

FROM sys.dm_exec_query_memory_grants mg

PRINT 'BLOCKER_PFE_END sys.dm_exec_query_memory_grants '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_tasks'

SELECT

t.session_id, t.request_id, t.exec_context_id AS 'ecid',

task_state = CAST(t.task_state AS NVARCHAR(10)), 

t.context_switches_count AS 'context_switches', t.pending_io_count AS 'pending_io',

t.scheduler_id

FROM sys.dm_os_tasks t

WHERE session_id IN (SELECT session_id FROM sys.dm_exec_sessions WHERE is_user_process=1) AND session_id<>@@SPID 

PRINT 'BLOCKER_PFE_END sys.dm_os_tasks '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_waiting_tasks'

SELECT

wt.session_id as 'spid', wt.blocking_session_id as 'blocked_spid', 

wt.wait_duration_ms, 

CAST(wt.wait_type AS NVARCHAR(32)) AS 'wait_type', 

CAST(wt.resource_description AS NVARCHAR(128)) AS 'resource_description',

wt.exec_context_id AS 'ecid', wt.blocking_exec_context_id AS 'block_ecid'

FROM sys.dm_os_waiting_tasks wt

WHERE session_id IN (SELECT session_id FROM sys.dm_exec_sessions WHERE is_user_process=1)

PRINT 'BLOCKER_PFE_END sys.dm_os_waiting_tasks  '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_tran_database_transactions'

SELECT 

dt.transaction_id, dt.database_id AS 'db_id', 

DATEDIFF(ms, dt.database_transaction_begin_time, GETDATE()) AS 'time_ms', 

dt.database_transaction_type AS 'type', 

dt.database_transaction_state AS 'state', 

dt.database_transaction_log_record_count AS 'log_record_count', 

dt.database_transaction_log_bytes_used AS 'log_bytes_used', 

dt.database_transaction_log_bytes_reserved AS 'log_bytes_reserved', 

dt.database_transaction_begin_time AS 'begin_time', 

dt.database_transaction_begin_lsn AS 'begin_lsn', 

dt.database_transaction_last_lsn AS 'last_lsn',

dt.database_transaction_last_rollback_lsn AS 'rollback_lsn'

FROM sys.dm_tran_database_transactions dt

WHERE database_transaction_begin_time is NOT NULL

PRINT 'BLOCKER_PFE_END sys.dm_tran_database_transactions '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

GO

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

CREATE PROCEDURE #spBlockerPfe_1_handle

AS

SET NOCOUNT ON

SET LOCK_TIMEOUT 250

DECLARE @time DATETIME

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN CollectSqlHandle'

-- COLLECT ADHOC REQUEST

INSERT #sqlquery_requested

SELECT

	sql_handle,

	statement_start_offset,

	statement_end_offset,

	query_hash,

	query_plan_hash

FROM sys.dm_exec_requests

WHERE sql_handle is not null AND session_id <> @@spid

-- COLLECT CURSOR

INSERT #sqlquery_requested

SELECT

	sql_handle,

	statement_start_offset,

	statement_end_offset,

	NULL,

	NULL

FROM sys.dm_exec_cursors(0)

-- OPENTRAN

INSERT #sqlquery_requested

SELECT 

	c.most_recent_sql_handle,

	0,

	0,

	NULL,

	NULL

FROM sys.dm_exec_connections c

WHERE session_id IN (SELECT session_id FROM sys.dm_tran_session_transactions)  AND session_id <> @@spid

PRINT 'BLOCKER_PFE_END CollectSqlHandle ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

GO

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

CREATE PROCEDURE #spBlockerPfe_2(@prevDate DATETIME)

AS

SET NOCOUNT ON

SET LOCK_TIMEOUT 3000

DECLARE @time DATETIME

SELECT @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_resource_governor_resource_pools'

select 

wp.pool_id, CAST(wp.name AS VARCHAR(16)) AS 'name', wp.statistics_start_time, 

wp.total_cpu_usage_ms, wp.cache_memory_kb, wp.compile_memory_kb, wp.used_memgrant_kb, wp.total_memgrant_count, 

wp.total_memgrant_timeout_count, wp.active_memgrant_count, wp.active_memgrant_kb, wp.memgrant_waiter_count, 

wp.max_memory_kb, wp.used_memory_kb, wp.target_memory_kb, 

wp.out_of_memory_count, wp.min_cpu_percent, wp.max_cpu_percent, wp.min_memory_percent, wp.max_memory_percent 

from sys.dm_resource_governor_resource_pools wp

PRINT 'BLOCKER_PFE_END sys.dm_resource_governor_resource_pools '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SELECT @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_resource_governor_workload_groups'

SELECT 

wg.group_id, wg.pool_id, CAST(wg.name AS VARCHAR(16)) AS 'name', 

wg.active_request_count, wg.queued_request_count, wg.blocked_task_count, wg.active_parallel_thread_count, 

wg.statistics_start_time, wg.total_request_count, wg.total_queued_request_count, wg.total_cpu_limit_violation_count, 

wg.total_cpu_usage_ms, 

wg.total_lock_wait_count, 

wg.total_lock_wait_time_ms, 

wg.total_query_optimization_count, 

wg.total_suboptimal_plan_generation_count, 

wg.total_reduced_memgrant_count, 

wg.max_request_cpu_time_ms, 

wg.max_request_grant_memory_kb, 

wg.request_max_memory_grant_percent,

CAST(wg.importance AS VARCHAR(16)) AS 'importance', 

wg.request_max_cpu_time_sec, wg.group_max_requests, 

wg.request_memory_grant_timeout_sec, wg.max_dop

from sys.dm_resource_governor_workload_groups wg

PRINT 'BLOCKER_PFE_END sys.dm_resource_governor_workload_groups '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_schedulers'

SELECT

	sos.scheduler_id, sos.is_online, sos.is_idle, 

	sos.current_tasks_count, sos.runnable_tasks_count, sos.active_workers_count, sos.current_workers_count, sos.work_queue_count, 

	sos.pending_disk_io_count, sos.scheduler_address

FROM sys.dm_os_schedulers sos

PRINT 'BLOCKER_PFE_END sys.dm_os_schedulers '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_waiting_tasks[BlockedSessions]';

;WITH cteBlockedSessions

AS

	(

	SELECT session_id, blocking_session_id FROM sys.dm_os_waiting_tasks 

	WHERE blocking_session_id <> session_id 

	)

SELECT 

	blk.blocking_session_id AS 'session_id', 

	CAST(c.most_recent_sql_handle AS VARBINARY(26)) as 'most_recent_sql_handle',

	at.transaction_id,

	DATEDIFF(ms, at.transaction_begin_time, GETDATE()) AS 'elapsed_time',

	CAST(r.sql_handle AS VARBINARY(26)) as 'sql_handle',

	r.statement_start_offset, r.statement_end_offset,

	at.transaction_begin_time, at.transaction_state 

FROM cteBlockedSessions blk 

	INNER JOIN sys.dm_exec_connections c ON blk.blocking_session_id = c.session_id

	INNER JOIN sys.dm_tran_session_transactions st ON st.session_id = c.session_id

	INNER JOIN sys.dm_tran_active_transactions at ON st.transaction_id = at.transaction_id

	LEFT JOIN sys.dm_exec_requests r ON c.session_id = r.session_id

WHERE blk.blocking_session_id not in (SELECT session_id FROM cteBlockedSessions)

PRINT 'BLOCKER_PFE_END sys.dm_os_waiting_tasks[BlockedSessions] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_sys_memory'

-- SQL 2008

SELECT 

s.system_high_memory_signal_state, s.system_low_memory_signal_state, s.available_physical_memory_kb, 

s.available_page_file_kb, 

s.system_cache_kb, s.kernel_paged_pool_kb, s.kernel_nonpaged_pool_kb, 

s.total_physical_memory_kb, 

s.total_page_file_kb

FROM sys.dm_os_sys_memory s

PRINT 'BLOCKER_PFE_END sys.dm_os_sys_memory '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_process_memory'

-- SQL 2008

SELECT

p.process_physical_memory_low, p.process_virtual_memory_low,

p.memory_utilization_percentage AS 'memory_utilization', p.available_commit_limit_kb, p.virtual_address_space_available_kb,

p.physical_memory_in_use_kb, p.large_page_allocations_kb, p.locked_page_allocations_kb, 

p.virtual_address_space_reserved_kb, p.virtual_address_space_committed_kb, p.total_virtual_address_space_kb

FROM sys.dm_os_process_memory p

PRINT 'BLOCKER_PFE_END dm_os_process_memory '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_memory_clerks'

SELECT

mc.type, total_kb = mc.single_pages_kb+mc.multi_pages_kb+mc.virtual_memory_committed_kb,

mc.memory_node_id, mc.single_pages_kb, mc.multi_pages_kb, mc.virtual_memory_reserved_kb, mc.virtual_memory_committed_kb, mc.awe_allocated_kb, mc.shared_memory_reserved_kb, mc.shared_memory_committed_kb, mc.name

FROM sys.dm_os_memory_clerks mc

WHERE single_pages_kb+multi_pages_kb+virtual_memory_committed_kb > 102400

PRINT 'BLOCKER_PFE_END sys.dm_os_memory_clerks '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_memory_brokers'

SELECT

mb.pool_id, mb.memory_broker_type, 

mb.allocations_kb, mb.allocations_kb_per_sec, 

mb.predicted_allocations_kb, mb.target_allocations_kb, mb.future_allocations_kb, mb.overall_limit_kb, 

last_notification = CAST(mb.last_notification AS VARCHAR(8))

FROM sys.dm_os_memory_brokers mb

PRINT 'BLOCKER_PFE_END sys.dm_os_memory_brokers '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_tran_active_transactions'

SELECT

t.transaction_id, t.name, t.transaction_begin_time, t.transaction_type, t.transaction_state

FROM sys.dm_tran_active_transactions t

WHERE transaction_type <> 2 -- read-only transaction

PRINT 'BLOCKER_PFE_END sys.dm_tran_active_transactions '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_tran_session_transactions'

SELECT

session_id, transaction_id, is_user_transaction AS 'is_user', is_local

FROM sys.dm_tran_session_transactions

PRINT 'BLOCKER_PFE_END sys.dm_tran_session_transactions '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_tran_active_snapshot_database_transactions'

SELECT 

	a.session_id, a.elapsed_time_seconds, a.is_snapshot, 

	a.transaction_id, a.transaction_sequence_num, a.first_snapshot_sequence_num, a.max_version_chain_traversed, a.average_version_chain_traversed

FROM sys.dm_tran_active_snapshot_database_transactions a

PRINT 'BLOCKER_PFE_END sys.dm_tran_active_snapshot_database_transactions '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_requests[System]'

SELECT

	req.session_id, req.blocking_session_id AS 'blocked', 

	req.database_id AS db_id, req.command, 

	req.total_elapsed_time AS 'elapsed_time', req.cpu_time, req.granted_query_memory AS 'granted_memory', req.logical_reads, 

	req.wait_time, CAST(req.wait_type AS VARCHAR(16)) AS 'wait_type', 

	req.open_transaction_count AS 'tran_count', 

	req.reads, req.writes,  

	req.start_time, req.status, req.connection_id, 

	req.transaction_id, req.task_address, req.request_id

FROM sys.dm_exec_requests req

WHERE group_id = 1

PRINT 'BLOCKER_PFE_END sys.dm_exec_requests[System] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN dm_os_tasks[System]'

SELECT

t.task_address, t.session_id, t.request_id, t.exec_context_id AS 'ecid',

task_state = CAST(t.task_state AS NVARCHAR(10)), 

t.context_switches_count AS 'context_switches', t.pending_io_count AS 'pending_io',

t.scheduler_id, t.worker_address

FROM sys.dm_os_tasks t

WHERE worker_address IS NOT NULL

	AND (session_id IN (SELECT session_id FROM sys.dm_exec_sessions WHERE is_user_process=0) OR session_id IS NULL)

PRINT 'BLOCKER_PFE_END sys.dm_os_tasks[System] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_sessions[last_request_time]'

DECLARE @lasttime DATETIME

SET @lasttime = @prevDate

-- SET @lasttime = DATEADD(s,-60,GETDATE())

SELECT 

	s.session_id, 

	s.login_time,

	s.status, 

	s.cpu_time, s.memory_usage, s.total_scheduled_time, s.total_elapsed_time, 

	s.last_request_start_time, s.last_request_end_time, 

	s.reads, s.writes, s.logical_reads, 

	s.row_count, 

	s.prev_error

FROM sys.dm_exec_sessions s 

WHERE (s.last_request_end_time > @lasttime OR s.last_request_end_time IS NULL) OR s.last_request_start_time > @lasttime 

PRINT 'BLOCKER_PFE_END sys.dm_exec_sessions[last_request_time] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_connections/sessions'

-- DECLARE @lasttime DATETIME

DECLARE @tbSessions TABLE (spid INT PRIMARY KEY)

INSERT @tbSessions(spid)

SELECT DISTINCT session_id FROM

	(

		SELECT session_id FROM sys.dm_exec_requests

	UNION ALL

		SELECT session_id FROM sys.dm_tran_session_transactions

	UNION ALL

		SELECT blocking_session_id FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL

	UNION ALL 

		SELECT session_id FROM sys.dm_exec_connections c

		WHERE c.connect_time > @lasttime

	) t

	WHERE session_id IS NOT NULL

SELECT 

	s.session_id, 

	s.group_id, 

	CAST(s.status AS VARCHAR(16)) AS 'status',

	CAST(s.host_name AS VARCHAR(20)) AS 'host_name', 

	CAST(s.login_name AS VARCHAR(32)) AS 'login_name', 

	CAST(s.program_name AS VARCHAR(64)) AS 'program_name', 

	s.host_process_id, 

	c.connection_id,

	CAST(s.original_login_name AS VARCHAR(32)) AS 'original_login_name', 

	s.client_interface_name, s.client_version, 

	CAST(c.auth_scheme AS VARCHAR(16)) AS 'auth_scheme', 

	CAST(c.net_transport AS VARCHAR(16)) AS 'net_transport', 

	c.client_net_address, c.client_tcp_port, 

	CAST(c.most_recent_sql_handle AS VARBINARY(26)) AS 'most_recent_sql_handle', 

	c.net_packet_size, c.encrypt_option,

	c.connect_time, s.login_time

FROM sys.dm_exec_connections c left join sys.dm_exec_sessions s on c.session_id = s.session_id

WHERE c.session_id IN (SELECT spid FROM @tbSessions)

PRINT 'BLOCKER_PFE_END sys.dm_exec_connections/sessions '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_wait_stats'

select 

	w.wait_type, 

	w.waiting_tasks_count, 

	w.wait_time_ms, w.max_wait_time_ms, w.signal_wait_time_ms

from sys.dm_os_wait_stats w where wait_time_ms > 60000

PRINT 'BLOCKER_PFE_END sys.dm_os_wait_stats '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_latch_stats'

select

	l.latch_class, l.waiting_requests_count, l.wait_time_ms, l.max_wait_time_ms

from sys.dm_os_latch_stats l where wait_time_ms > 60000

PRINT 'BLOCKER_PFE_END sys.dm_os_latch_stats '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_spinlock_stats'

select 

	CAST(s.name AS VARCHAR(64)) AS 'name', 

	s.collisions, s.spins, s.spins_per_collision, s.sleep_time, s.backoffs

from sys.dm_os_spinlock_stats s where sleep_time > 100

PRINT 'BLOCKER_PFE_END sys.dm_os_spinlock_stats '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_query_resource_semaphores'

SELECT 

	qrsem.resource_semaphore_id, qrsem.target_memory_kb, qrsem.max_target_memory_kb, qrsem.total_memory_kb, qrsem.available_memory_kb, qrsem.granted_memory_kb, qrsem.used_memory_kb, qrsem.grantee_count, qrsem.waiter_count, qrsem.timeout_error_count, qrsem.forced_grant_count, qrsem.pool_id

FROM sys.dm_exec_query_resource_semaphores qrsem

PRINT 'BLOCKER_PFE_END sys.dm_exec_query_resource_semaphores '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_io_pending_io_requests'

DECLARE @pending_io_requests_summary INT = 0

SELECT TOP 512

	io.io_pending_ms_ticks, io.io_pending, io.scheduler_address, io_pending_handle=io.io_handle, io.io_offset

FROM sys.dm_io_pending_io_requests io

ORDER BY io_pending_ms_ticks DESC

--IF @@ROWCOUNT > 32

SET @pending_io_requests_summary = 1

PRINT 'BLOCKER_PFE_END sys.dm_io_pending_io_requests '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

IF @pending_io_requests_summary = 1 

BEGIN

	SET @time = GETDATE()

	PRINT ''

	PRINT 'BLOCKER_PFE_BEGIN sys.dm_io_pending_io_requests[summary]'

	SELECT * FROM 

		(SELECT count=COUNT(*), avg_pending_ms_ticks=AVG(io_pending_ms_ticks), io_handle

		FROM sys.dm_io_pending_io_requests WHERE io_pending = 1 GROUP BY io_handle) i

	LEFT JOIN #filehandle f ON i.io_handle = f.file_handle

	ORDER BY avg_pending_ms_ticks DESC

	PRINT 'BLOCKER_PFE_END sys.dm_io_pending_io_requests[summary] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

END

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_io_virtual_file_stats'

SELECT 

	fs.database_id, fs.file_id, 

	fs.num_of_reads, fs.num_of_bytes_read, fs.io_stall_read_ms, fs.num_of_writes, fs.num_of_bytes_written, fs.io_stall_write_ms, fs.io_stall, 

	fs.size_on_disk_bytes, virtual_file_handle = fs.file_handle

FROM sys.dm_io_virtual_file_stats (-1,-1) fs

PRINT 'BLOCKER_PFE_END sys.dm_io_virtual_file_stats '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.databases[log_reuse_wait_desc]'

SELECT d.name, d.log_reuse_wait_desc

FROM sys.databases d 

WHERE d.log_reuse_wait <> 0

PRINT 'BLOCKER_PFE_END sys.databases[log_reuse_wait_desc] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN DBCC_SQLPERF_LOGSPACE'

DBCC SQLPERF(LOGSPACE)

PRINT 'BLOCKER_PFE_END DBCC_SQLPERF_LOGSPACE '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SELECT @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN DBCC_OPENTRAN'

DECLARE @dbid INT

DECLARE curDatabases CURSOR FAST_FORWARD FOR

SELECT database_id FROM sys.databases WHERE state=0

OPEN curDatabases;

FETCH NEXT FROM curDatabases INTO @dbid;

WHILE (@@FETCH_STATUS <> -1)

BEGIN

	PRINT ''

	DBCC OPENTRAN(@dbid)

	FETCH NEXT FROM curDatabases INTO @dbid

END

DEALLOCATE curDatabases

PRINT 'BLOCKER_PFE_END DBCC_OPENTRAN '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_tran_locks[OBJECTS]'

SELECT

l.resource_database_id AS 'db_id', l.resource_associated_entity_id AS 'entity_id', 

CAST(l.request_mode AS VARCHAR(8)) AS req_mode, req_status = CAST(l.request_status AS VARCHAR(8)),

l.request_session_id AS 'session_id', l.request_owner_id, 

resource_subtype = CAST(l.resource_subtype AS VARCHAR(16)),

object_description = CASE WHEN request_mode NOT LIKE 'Sch-%' THEN 

	CAST(

		DB_NAME(resource_database_id) + N'.' +

		OBJECT_SCHEMA_NAME(resource_associated_entity_id, resource_database_id) + N'.' +

		OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS NVARCHAR(64))

	ELSE CAST(resource_description AS NVARCHAR(64)) END 

FROM sys.dm_tran_locks l

WHERE l.resource_type = 'OBJECT'

PRINT 'BLOCKER_PFE_END sys.dm_tran_locks[OBJECTS] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN sys.dm_tran_locks[WAIT]'

SELECT

CAST(l.resource_type AS VARCHAR(12)) AS 'resource_type',

l.resource_database_id AS 'db_id', l.resource_associated_entity_id AS 'entity_id', 

CAST(l.request_mode AS VARCHAR(8)) AS 'req_mode', req_status = CAST(l.request_status AS VARCHAR(8)),

l.request_session_id AS 'session_id', l.request_owner_id, 

resource_subtype = CAST(l.resource_subtype AS VARCHAR(16)),

resource_description =

	CASE WHEN l.resource_type = 'OBJECT' AND request_mode NOT LIKE 'Sch-%' THEN 

		CAST(

		DB_NAME(resource_database_id) + N'.' +

		OBJECT_SCHEMA_NAME(resource_associated_entity_id, resource_database_id) + N'.' +

		OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS NVARCHAR(64))

	ELSE CAST(resource_description AS NVARCHAR(64)) END

FROM sys.dm_tran_locks l

WHERE l.request_status = 'WAIT'

PRINT 'BLOCKER_PFE_END sys.dm_tran_locks[WAIT] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

GO

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

CREATE PROCEDURE #spBlockerPfe_2_handle

AS

SET NOCOUNT ON

SET LOCK_TIMEOUT 3000

DECLARE @time DATETIME

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN CollectSqlHandle2'

-- OPENTRAN

INSERT #sqlquery_requested

SELECT 

	c.most_recent_sql_handle,

	0,

	0,

	NULL,

	NULL

FROM sys.dm_exec_connections c

WHERE session_id IN (SELECT session_id FROM sys.dm_tran_session_transactions) AND session_id <> @@spid

PRINT 'BLOCKER_PFE_END CollectSqlHandle2 ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN FlushSqlHandle[object_name]'

SELECT DISTINCT

	CAST(req.sql_handle AS VARBINARY(26)) AS 'sql_handle',

	CAST(

		DB_NAME(dbid) + N'.' + 

		OBJECT_SCHEMA_NAME(objectid,dbid) + N'.' + 

		OBJECT_NAME(objectid,dbid) AS NVARCHAR(128)) AS 'object_name',

	st.dbid, st.objectid

FROM #sqlquery_requested req

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) st

WHERE objectid IS NOT NULL

ORDER BY dbid, objectid

PRINT 'BLOCKER_PFE_END FlushSqlHandle[object_name] ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN FlushSqlHandle[sqlquery_requested]'

SELECT

'COUNT=',			Count=COUNT(*),

'SQLHANDLE=',		CAST(req.sql_handle AS VARBINARY(26)) AS 'sql_handle',

	req.stmt_start, 

	req.stmt_end,

	'QUERY_HASH=',		req.query_hash,

	'QUERY_PLAN_HASH=',	req.query_plan_hash

FROM #sqlquery_requested req

WHERE req.query_hash IS NOT NULL

GROUP BY sql_handle, query_hash, stmt_start, req.stmt_end, req.query_plan_hash

ORDER BY COUNT(*) DESC

PRINT 'BLOCKER_PFE_END FlushSqlHandle[sqlquery_requested] ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()

PRINT ''

PRINT 'BLOCKER_PFE_BEGIN FlushSqlHandle[dm_exec_sql_text]'

SELECT TOP 1000

'COUNT=',			COUNT(*),

'SQLHANDLE=',		CAST(req.sql_handle AS VARBINARY(26)) AS 'sql_handle',

'SQLHASH=',			req.query_hash,

	req.stmt_start, 

	req.stmt_end,

	CHAR(13) + CHAR(10),

	'SQLTEXT=',			sqltext=(SELECT SUBSTRING(	text, stmt_start/2 + 1, 

												((CASE	WHEN stmt_end = -1 THEN DATALENGTH(text) 

														WHEN stmt_end = 0 THEN 1024

														ELSE stmt_end END) - stmt_start)/2 )

						FROM sys.dm_exec_sql_text(sql_handle))

FROM #sqlquery_requested req 

GROUP BY sql_handle, query_hash, stmt_start, req.stmt_end

ORDER BY COUNT(*) DESC

TRUNCATE TABLE #sqlquery_requested

PRINT 'BLOCKER_PFE_END FlushSqlHandle[dm_exec_sql_text] ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

GO

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

GO

-----------------------------------------------------------------------------------------------------

EXEC #spBlockerPFE-----------------------------------------------------------------------------------------------------

Como usar o script sqlmon?

Siga os passos:

  • Copie o script do SQLMON para a sua máquina
  • Abra o SQL Management Studio e cole o script
  • Configure para gerar os resultados para arquivo
  • Execute o script e deixe rodando durante o problema

image

Criador do script: Fabrício Cantae

Anúncios

SQL Server 2008 – Importando arquivos XML

Me deparei com uma situação hoje, minha noiva me liga, informando que seu servidor SQL Server estava faltando alguns dados em uma determinado tabela e só possui estes arquivos em XML. Isto, realmente é um caso critico ainda mais quando não sabemos por onde começar, pois bem, passei o seguinte exemplo abaixo e tudo foi solucionado.

Aqui está um breve exemplo de importar um arquivo XML em SQL Server tabela. Isto é conseguido usando a opção BULK de OPENROWSET para carregar o arquivo, e então utilizando os recursos XQuery do SQL Server para analisar o XML para o formato tabela normalizada. Este exemplo requer o SQL Server 2005 ou SQL Server 2008.

Primeiro passo: Vamos criar um XML em “C:\produtos.xml”

<Produtos>

<Produtos>

<SKU>1</SKU>

<Desc>Livro</Desc>

</Produtos>

<Produtos>

<SKU>2</SKU>

<Desc>DVD</Desc>

</Produtos>

<Produtos>

<SKU>3</SKU>

<Desc>Video</Desc>

</Produtos>

</Produtos>

Segundo passo: Vamos criar uma tabela para armazenar os dados XML

CREATE TABLE Produtos(

sku INT PRIMARY KEY,

produtos_desc VARCHAR(30));

Terceiro passo: Finalmente, a seguinte declaração abaixo irá carregar o arquivo XMLm analisar os elementos XML, para colunas, e inserir na tabela produtos.

INSERT INTO Produtos (sku, produtos_desc)

SELECT X.produtos.query(‘SKU’).value(‘.’, ‘INT’),

X.produtos.query(‘Desc’).value(‘.’, ‘VARCHAR(30)’)

FROM (

SELECT CAST(x AS XML)

FROM OPENROWSET(

BULK ‘C:\Produtos.xml’,

SINGLE_BLOB) AS T(x)

) AS T(x)

CROSS APPLY x.nodes(‘Produtos/Produtos’) AS X(product);

Resultado:

SELECT sku, produtos_desc

FROM Produtos;

/*

Results:

sku         produtos_desc

———– ————-

1           Livro

2           DVD

3           Video

*/

Existem diversas formas de armazenar um arquivo XML em uma tabela, como o citado abaixo.

INSERT

INTO import.XMLCompanyDetail

(

XMLData

)

SELECT BulkColumn AS XMLData

FROM OPENROWSET (BULK ‘\\local_do_arquivo\arquivos.xml’, SINGLE_BLOB) AS XMLDATA

Importando dados atravês do comando BULK INSERT

Importando dados atravês do comando BULK INSERT
Como tinha comentando em outro post, estaria ensinando a importar um arquivo *.CSV para uma tabela no SQL Server, atravês do comando bulk insert, pois o comando é simples até já foi comentado aqui, segue abaixo.
bulk insert nome_da_tabela from ‘ caminho_do_arquivo.csv ‘
with (fieldterminator =‘;’, FIRSTROW = 2)
Obs.: O arquivo *.CSV precisa estar formatado igual as tabelas criadas, nome por nome de cada coluna.
Caso queira criar um nova tabela á partir do arquivo *.CSV, segue comando abaixo.
create table nome_da_tabela
(
CPF VARCHAR(50)
, DDD VARCHAR(50)
, TELEFONE VARCHAR(50)
, NOME VARCHAR(50)
, STATUS VARCHAR(50)
)
 

bulk insert nome_da_tabela from ‘ caminho_do_arquivo.csv ‘
with (fieldterminator =‘;’, FIRSTROW = 2)
Obs.: O tipo de dado deverá ser criado conforme sua necessidade, estamos aqui somente citando um exemplo.
Maiores informações sobre o comando bulk insert acesse: Fonte bulk insert

Importando dados atravês do comando BULK INSERT

Importando dados atravês do comando BULK INSERT
Me deparei com uma situação, a qual eu tinha que alimentar um arquivo txt com varios telefones, para um determinado banco de dados.
Pensei pootz agora sim me ferrei, o arquivo tinha mais de 5 mil linhas.
Mas graças o T-SQL existe uma forma muito prática de estar solucionando este problema, o SQL Server permite importar dados diretamente para o banco de dados, utilizando o comando BULK INSERT é bem simples, vejo o exemplo.

BULK INSERT ( nome da tebela )

FROM‘unidade_do_arquivo:\nome_do_arquivo.txt’ WITH (FIELDTERMINATOR = ‘”,”‘)

Lembrando que o comando irá variar conforme o formato do arquivo. Estarei em breve publicando um outro artigo informando como alimentar um arquivo com o formato abaixo:

CPF; DDD; TELEFONE; NOME

SQL Server 2008 Active-Passive/Active-Active Cluster Installation

SQL Server 2008 Active-Passive/Active-Active Cluster Installation

Como o SQL Server 2005, SQL Server 2008 suporta clustering usando as edições Standard e Enterprise. A edição Standard suporta um cluster de dois nós, Enterprise Edition suporta o número máximo de nós que o SO suporta.

As maiores mudanças no SQL Server 2008 cluster é que agora você pode usar o DHCP para o agrupamento e utilização de endereços IP de sub-redes diferentes para cada lado do cluster. A habilidade de usar endereços IP de sub-redes diferentes é mais útil para a criação de clusters em vários locais, chamados clusters geograficamente distribuídos.

Instalação do SQL Server Cluster

Passo 1: Para que possamos instalar o cluster do SQL Server 2008, precisamos que todos os pré-requisitos sejam instalados, neste caso estamos utilizando o Windows Server 2003, o qual você irá precisar baixar e instalar o hotfix especificado no artigo da Microsoft em: http://support.microsoft.com/kb/937444 antes de instalar o SQL Server 2008.

Nota: A instalação do hotfix acima requer um reboot, então você tem que cancelar a instalação nesse momento e reinicie o processo depois de instalar esse hotfix em todos os nós do cluster.

Passo 2: Vamos iniciar a instalação do SQL Server 2008. Inicie a instalação conforme demonstrado na imagem abaixo, selecione a opção New SQL Server Failover Cluster como em destaque.

Nota: Para instalação independente, temos que selecionar a opção standalone.

Passo 3: Depois de ter selecionado a opção 2, a instalação irá verificar se há regras de apoio à instalação para identificar o problema. Se for falha por qualquer motivo, você tem que corrigir o problema. Por favor, encontrar a tela abaixo. Se ele passou, clique no botão de comando OK.

Passo 4: Assistente de Instalação irá instalar os arquivos de suporte da instalação, clique no botão Instalar.

Passo 5: Esta etapa irá orientá-lo todas as informações exigidas conforme abaixo. Clique no botão Avançar

1. Product Key

2. Licence Terms

3. Feature Selection

4. Disk Space Reuirements

5. Error and Usage Reporting

6. Cluster instllation Rules

7. Ready to install

8. Installation Progress

6: Enter Product Key ou levará automaticamente. Veja a imagem abaixo para isso.

Passo 7: Selecione os recursos necessários, o que você quer instalar.

Passo 8: Especifique o nome da rede / Nome do virtual para o SQL Server para failover. Como estamos instalando cluster ativo-passivo, temos que especificar um nome de rede virtual /. (Nota: Para Active-Active clustering você especificar nomes de rede diferentes conforme o número de nós)

Passo 9: O SQL Server irá rever a exigência de espaço em disco para os recursos que você selecionou.

Passo 10: Esta etapa irá criar um novo grupo de recursos de cluster para o failover do SQL Server.

Passo 11: Selecionar os recursos compartilhados disco de cluster para o cluster de failover do SQL Server.

Passo 12: Aqui você tem que selecionar os recursos da rede para o cluster de failover do SQL Server. Você tem que adicionar o endereço IP também.

Passo 13: Aqui você tem que especificar a conta de serviço para os serviços do SQL Server. É uma boa prática sempre mantém SQL Services em cluster no modo manual.

Passo 14: Especifique a autenticação Mixed Mode. E os locais aonde serão salvos os arquivos de log.

Passo 15: Selecione as caixas de seleção como por sua exigência, não ter verificado qualquer opção. Continue com o botão ao lado.

Passo 16: Assistente de instalação irá verificar as regras de instalação de cluster, por favor corrijam se você encontrar qualquer falha e proceder com o botão próximo.

Passo 17: Agora o cluster do SQL Server está pronto para instalar. Clique no botão Instalar.

Passo 18: Aplicar o Service Pack exigido / Patch e reiniciar o servidor.

Passo 19: Depois que a instalação é feita em nó, temos que prosseguir com a instalação do SQL Server em nós de cluster. SQL Server 2005 foi a última edição do SQL Server para instalar automaticamente o SQL Server em todos os nós de uma vez. Começando com o SQL Server 2008, devemos instalar manualmente e corrigir cada nó individualmente. Este processo permite menos tempo de inatividade como estamos consertando o cluster ou atualizar seu cluster de SQL Server 2005 para o SQL Server 2008.

Passo 20: Nos outros nós é semelhante a instalar no primeiro nó, mas temos selecione o nó Adicionar a uma opção de cluster failover do SQL Server a partir do menu inicial. Os requisitos de software mesmo se aplica para os outros nós do cluster, para que o instalador irá lidar com a verificação e instalação desses componentes também.

Porque a maioria das configurações são captados a partir de outro nó (s) no cluster que já tem o SQL Server instalado neles, o processo de adicionar um nó ao cluster é um pouco menor do que a instalação no primeiro nó