July 7, 2014 at 4:16 am
I recently deployed a standard in-house monitoring proc to a number of newly built SQL 2008 R2 SP2 instances, some of which had been patched to CU12 and some of which were just SP2 (10.50.4305 vs 10.50.4000).
I discovered the following - fairly standard pattern - code created problems of hanging transactions.
First of all it created an uncommitted transaction at the time of creating the stored proc (my first issue was due to missing proc after deployment). But even when the proc is created, it results in hanging trans without the "sweeper" extra commit I added (after the "--Why this is necesary..." comment).
Again, the original code, minus the extra commit, works just fine on SP2 and all previous 2008 R2, 2008 & 2005 instances.
Any ideas? I can post the whole proc if people want to test.
if (@debug = 1)
print @cmd;
else
begin try
begin tran;
exec sp_executesql @cmd;
commit tran;
end try
begin catch
if (xact_state() <> 0) rollback tran; -- whether commitable or not (shouldn't be)
if (error_number() = 1205) -- handle deadlock victim
begin
set @deadlocked = 1;
raiserror('deadlock caught!', 10, 1) with nowait;
end
else -- puke
begin
select @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
goto Quit;
end
end catch
-- Why this is necessary is a complete mystery. Somehow the above is leaving a hanging uncommitted transaction?
if (xact_state() <> 0) commit tran;
July 7, 2014 at 4:30 am
Full proc - there is a dependency on a monitoring table, but just running this proc defn will create a hanging tran in build 4305 that doesn't happen in 4000 (SP2)
if object_id('Database_Table_Size_Update') is not null
drop proc Database_Table_Size_Update
GO
SET ANSI_DEFAULTS ON
GO
/*
=========================================================================================
Database Team STORED PROCEDURE HEADER
File: dbo.Database_Table_Size_Update.Proc
Name: Database_Table_Size_Update
Description:
For each user database in instance, gets rowcounts and reserved, data, index and unused space in Mb for each table
Depends On:
GBEMonitor.dbo.Database_Table_Size
Notes:
Installation:
GBEMonitor
Processing Steps: create insert query with dynamic sql
Parameters:
@debug bit = 0 : 0 run normally; 1 output commands to be run and debug info
Change History:
Date: Author Description
----- ------- -------------------------------------------------
??/??/?? PB Creation
...
15/10/10 PB Fixed 2005 bug for tables with multiple allocation unit types (e.g. with blob fiels)
03/04/14PBEliminated db cursor
07/07/14PBIssue with transaction not committing sice elimination of cursor.
=========================================================================================
*/
CREATE PROCEDURE [dbo].[Database_Table_Size_Update] (
@debug bit = 0
)
as
begin
set nocount on;
set xact_abort on;
set deadlock_priority normal;
set lock_timeout 10000;
-- error handling vars
declare @deadlocked bit, @ErrorMessage nvarchar(4000), @ErrorSeverity int, @ErrorState int;
declare @dbname sysname, @cmd nvarchar(4000);
declare @userdbs table (dbname sysname);
insert into @userdbs
select name
from master.sys.databases
where database_id > 4
and databasepropertyex(name ,'Status') = 'ONLINE'
order by name;
select top 1 @dbname = dbname from @userdbs order by dbname;
--while @@fetch_status >= 0
while @@rowcount > 0
begin
if (@debug = 1) raiserror ('Database: %s', 10, 1, @dbname) with nowait;
if (@@microsoftversion < 0x09000000) -- SQL 2000
set @cmd = 'use [' + @dbname + ']; insert into GBEMonitor.dbo.Database_Table_Size (StatDateTime, DatabaseId, ObjectName, TableRowCount, MbReserved, MbData, MbIndex, MbUnused)
select
-- so.id as [OBJECT_ID],
getdate(), db_id(),
so.name as [OBJECT_NAME],
coalesce(j_rows.rows,0) as [ROWCOUNT],
coalesce(j_ru.sum_reserved,0) * cast(m.low as dec) / 1024 / 1024 as [RESERVED (MB)],
d.data * cast(m.low as dec) / 1024 / 1024 as [DATA (MB)],
(coalesce(j_ru.sum_used,0) - d.data) * cast(m.low as dec) / 1024 / 1024 as [INDEX (MB)],
(coalesce(j_ru.sum_reserved,0) - coalesce(j_ru.sum_used,0)) * cast(m.low as dec) / 1024 / 1024 as [UNUSED (MB)]
from
sysobjects so (nolock)
-- rows
left join sysindexes j_rows (nolock)
on j_rows.indid < 2 and j_rows.id = so.id
/* reserved: sum(reserved) where indid in (0, 1, 255) */
/* index: sum(used) where indid in (0, 1, 255) - data */
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
left join
(
select
id, sum(reserved) as sum_reserved, sum(used) as sum_used
from
sysindexes (nolock)
where
indid in (0, 1, 255)
group by
id
) j_ru on j_ru.id = so.id
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
left join
(
select
j_dpages.id, coalesce(j_dpages._sum,0) + coalesce(j_used._sum,0) as data
from
(
select
id, sum(dpages) as _sum
from
sysindexes (nolock)
where
indid < 2
group by
id
) j_dpages left join
(
select
id, sum(used) as _sum
from
sysindexes (nolock)
where
indid = 255
group by
id
) j_used on j_used.id = j_dpages.id
) d on d.id = so.id
inner join master.dbo.spt_values m (nolock)
on m.number = 1 and m.type = ''E''
where
OBJECTPROPERTY(so.id, N''IsUserTable'') = 1
order by
[RESERVED (MB)] DESC'
else -- SQL 2005 +
set @cmd = 'use [' + @dbname + ']; insert into GBEMonitor.dbo.Database_Table_Size (StatDateTime, DatabaseId, ObjectName, TableRowCount, MbReserved, MbData, MbIndex, MbUnused)
select
getdate()
, db_id()
, so.name as [OBJECT_NAME]
, sum(case when (i.index_id < 2 and a.type = 1) then p.rows else 0 end) as [ROWCOUNT]
, sum(a.total_pages) / 128.0 as [RESERVED (MB)]
, sum(case when i.index_id < 2 then
case
when a.type = 2 then a.used_pages
else a.data_pages
end
else 0
end) / 128.0 as [DATA (MB)]
, sum(case when i.index_id < 2 then (a.used_pages - a.data_pages) else a.used_pages end) / 128.0 as [INDEX (MB)]
, sum(a.total_pages - a.used_pages) / 128.0 as [UNUSED (MB)]
from sys.objects so
join sys.indexes i
on (so.object_id = i.object_id)
join sys.partitions p
on (i.object_id = p.object_id and i.index_id = p.index_id)
join sys.allocation_units a
on (p.hobt_id = a.container_id)
where OBJECTPROPERTY(so.object_id, N''IsUserTable'') = 1
group by so.name
order by [RESERVED (MB)] DESC'
if (@debug = 1)
print @cmd;
else
begin try
begin tran;
exec sp_executesql @cmd;
commit tran;
end try
begin catch
if (xact_state() <> 0) rollback tran; -- whether commitable or not (shouldn't be)
if (error_number() = 1205) -- handle deadlock victim
begin
set @deadlocked = 1;
raiserror('deadlock caught!', 10, 1) with nowait;
end
else -- puke
begin
select @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
goto Quit;
end
end catch
-- Why this is necessary is a complete mystery. Somehow the above is leaving a hanging uncommitted transaction?
--if (xact_state() <> 0) commit tran;
delete from @userdbs where dbname = @dbname;
select top 1 @dbname = dbname from @userdbs order by dbname;
end
Quit:
end
go
-- Why this is necessary at define time is also a complete mystery. Somehow the above proc definition is also leaving a hanging uncommitted transaction?
--if (xact_state() <> 0) commit tran;
--exec [dbo].[Database_Table_Size_Update] @debug = 1
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply