Options
Maximum Uncommitted Changes?
Skip_M
Posts: 1 New member
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:
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<br> ,@query varchar(max)
declare @tabCols table<br>(<br> [ID] int identity(1,1)<br>,[TABLE_CATALOG] varchar(128)<br>,[TABLE_SCHEMA] varchar(128)<br>,[TABLE_NAME] varchar(128)<br>,[COLUMN_NAME] varchar(128)<br>)
insert into @tabCols<br>(<br> [TABLE_CATALOG]<br>, [TABLE_SCHEMA]<br>, [TABLE_NAME]<br>, [COLUMN_NAME]<br>)<br>select [col].[TABLE_CATALOG]<br> , [col].[TABLE_SCHEMA]<br> , [col].[TABLE_NAME]<br> , [col].[COLUMN_NAME]<br> from [INFORMATION_SCHEMA].[COLUMNS] [col]<br> inner join [INFORMATION_SCHEMA].[TABLES] [tab]<br> on (([tab].[TABLE_CATALOG] = [col].[TABLE_CATALOG])<br> and ([tab].[TABLE_SCHEMA] = [col].[TABLE_SCHEMA])<br> and ([tab].[TABLE_NAME] = [col].[TABLE_NAME]))<br> where ([col].[COLUMN_NAME] in ('ColA','ColB'))<br> and ([col].[CHARACTER_MAXIMUM_LENGTH] < 128)<br> and ([tab].[TABLE_TYPE] = 'BASE TABLE')<br> order by [col].[TABLE_CATALOG]<br> , [TABLE_SCHEMA]<br> , [TABLE_NAME]<br> , [COLUMN_NAME]select max([ID]) as [Change Count]<br> from @tabCols
while ((select min([ID])<br> from @tabCols<br> where ([ID] > @index)) is not null)<br>begin<br> select @index = min([ID])<br> from @tabCols<br> where ([ID] > @index)
select @query = 'alter table [' + [TABLE_CATALOG] + '].[' + [TABLE_SCHEMA] + '].[' + [TABLE_NAME] + '] alter column [' + [COLUMN_NAME] + '] varchar(128)'<br> from @tabCols<br> where ([ID] = @index)
print @query<br> exec(@query)<br>end
set nocount off
