How can we help you today? How can we help you today?

Maximum Uncommitted Changes?

I have a scenario where I need to change (increase) the column size on two columns (ColA and ColB for discussion) on every table in a database. I have several databases to perform this task on. I wrote a script to perform this task dynamically based upon Information_Schema. I have clones of the target databases on my desktop workstation. I have one database that is not connected to SQL Source Control (DB_A, 294 tables), and another that is linked to SQL Source Control (DB_B, 403 tables). When I ran my script on DB_A, it ran perfectly, modifying the target fields in all 294 tables (588 changes). When I ran this same script on DB_B, it got 60 changes in and hung. After 30 minutes, I terminated the script. I checked the database and can see 30 tables marked as having pending changes. If I try to manually run the "alter table - alter column" statement on the next table in the sequence, the query hangs and the table is not modified.

I am wondering if SQL Source Control has a limit for Maximum Uncommitted Changes? Why would this work perfectly on a database that is not linked to SQL Source Control, and hang on an otherwise similar database that is linked to SQL Source Control?

My script is as follows:
set nocount on
declare @index int = 0
declare @tabCols table
insert into @tabCols
select max([ID]) as [Change Count]
while ((select min([ID])
  select @query = 'alter table [' + [TABLE_CATALOG] + '].[' + [TABLE_SCHEMA] + '].[' + [TABLE_NAME] + '] alter column [' + [COLUMN_NAME] + '] varchar(128)'
  print @query
set nocount off

Skip Munk
0

Add comment

Please sign in to leave a comment.