SELECT
	user_object_perc = CONVERT(DECIMAL(6,3), u*100.0/(u+i+v+f)),
	internal_object_perc = CONVERT(DECIMAL(6,3), i*100.0/(u+i+v+f)),
	version_store_perc = CONVERT(DECIMAL(6,3), v*100.0/(u+i+v+f)),
	free_space_perc = CONVERT(DECIMAL(6,3), f*100.0/(u+i+v+f)),
	[total] = (u+i+v+f)
FROM (
	SELECT
		u = SUM(user_object_reserved_page_count)*8,
		i = SUM(internal_object_reserved_page_count)*8,
		v = SUM(version_store_reserved_page_count)*8,
		f = SUM(unallocated_extent_page_count)*8
	FROM sys.dm_db_file_space_usage
) x;

--Open transactions, last batch an hour or more ago
select 
	convert(varchar(20), db_name(dbid)) as DatabaseName, 
	datediff(mi, last_batch, getdate()) as TransactionDuration, 
	convert(varchar(30), hostname) as HostName, 
	spid
from master..sysprocesses
where open_tran <> 0 and last_batch < dateadd(hh, -1, getdate())
order by 1 asc, 2 desc

-- Check size of version store
select
	[Version Store Pages Used] =
	sum(version_store_reserved_page_count),
	[Version Store Space MB] = 
	convert(numeric(10,2),round(((sum(version_store_reserved_page_count)*1.0)/128.00),2))
from
	sys.dm_db_file_space_usage;


-- Get info on open snapshot transactions
select
	a.transaction_id,
	a.session_id,
	a.is_snapshot,
	d.login_time,
	b.transaction_begin_time,
	last_batch_time = d.last_batch,
	Login_Hours =
		convert(numeric(8,2),round(datediff(ss,d.login_time,getdate())/3600.0000,2)),
	Transaction_Hours =
		convert(numeric(8,2),round(datediff(ss,b.transaction_begin_time,getdate())/3600.0000,2)),
	Last_Batch_Hours =
		convert(numeric(8,2),round(datediff(ss,d.last_batch,getdate())/3600.0000,2)),
	b.name,
	b.transaction_type,
	transaction_type_desc =
	case b.transaction_type
	when 1 then 'Read/write transaction' 
	when 2 then 'Read-only transaction' 
	when 3 then 'System transaction' 
	when 4 then 'Distributed transaction'
	else ''end,
	b.transaction_state,
	transaction_state_desc =
	case b.transaction_state
	when 0 then 'Transaction has not been completely initialized yet.'
	when 1 then 'Transaction has been initialized but has not started.'
	when 2 then 'Transaction is active. '
	when 3 then 'Transaction has ended. Used for read-only transactions.'
	when 4 then 'Commit process has been initiated on the distributed transaction.'
	when 5 then 'Transaction is in a prepared state and waiting resolution.'
	when 6 then 'Transaction has been committed.'
	when 7 then 'Transaction is being rolled back. '
	when 8 then 'transaction has been rolled back.'
	else '' end,
	hostname = rtrim(d.hostname),
	Program_name = rtrim(d.Program_name),
	loginame= rtrim(d.loginame),
	SQL_text = e.text
from
	sys.dm_tran_active_snapshot_database_transactions a
	join
	sys.dm_tran_active_transactions b
	on a.transaction_id = b.transaction_id 
	join
	master.dbo.sysprocesses d
	on a.session_id = d.spid
	cross apply
	sys.dm_exec_sql_text( d.sql_handle ) e
order by
	a.transaction_id

-- Make sure transaction is completely committed
while @@trancount > 0 begin commit end

while @@trancount > @trancountAtStartOfProc begin commit end[/i]"

quote:

---

Originally posted by Kristen

"-- Make sure transaction is completely committed
while @@trancount > 0 begin commit end"

I think this should be

while @@trancount > @trancountAtStartOfProc begin commit end[/i]"

otherwise you risk committing the "callers" level(s) too, and any ROLLBACK the caller tries to do will then fail (and the data will be "gold" already )

---

quote:

---

Originally posted by tkizer
...Problem is that some of these apps are in sustaining mode and no longer have developers for them....

---