Source control transactions

Nicon

Hi.

Let me start by saying that i am not 100% sure this problem is to do with SQL Source Control, but it is definitely one of the Red Gate tools.

I have notice, while running profiler against a database in Source Control, that "Red Gate Software - SQL Tools" is executing a process every couple of seconds against tempDB. This is what is being executed:

-- Copyright © 2009 Red Gate Software Ltd.
SET XACT_ABORT ON ;
SET LOCK_TIMEOUT 100 ;
BEGIN TRAN ;



IF OBJECT_ID(N'tempdb..#RG_NewSysTypes', N'U') IS NOT NULL
    DROP TABLE #RG_NewSysTypes ;


SELECT  sysTypes.user_type_id AS ObjectID ,
        sysSchemas.name AS SchemaName ,
        sysTypes.name AS Name ,
        sysTypes.is_assembly_type AS ClrType ,
        convert(bit, case when sysTypes.system_type_id = 243 then 1 else 0 end) AS TableType
INTO    #RG_NewSysTypes
FROM    [GSABS_Staging].sys.types AS sysTypes
        LEFT JOIN [GSABS_Staging].sys.schemas AS sysSchemas ON sysSchemas.schema_id = sysTypes.schema_id
WHERE   sysTypes.is_user_defined = 1 ;



SELECT  ls.ObjectID AS prevObjectID ,
        curr.ObjectID AS ObjectID ,
        ls.Name AS prevName ,
        curr.Name AS Name ,
        ls.SchemaName AS prevSchemaName ,
        curr.SchemaName AS SchemaName ,
        ls.ClrType AS prevClrType ,
        curr.ClrType AS ClrType ,
        ls.TableType AS prevTableType ,
        curr.TableType AS TableType
FROM    #RG_LastSysTypes AS ls
        FULL OUTER JOIN #RG_NewSysTypes AS curr ON ls.ObjectID = curr.ObjectID
WHERE   ls.ObjectID IS NULL
        OR curr.ObjectID IS NULL
        OR ls.Name <> curr.Name ;


DROP TABLE #RG_LastSysTypes ;

SELECT  *
INTO    #RG_LastSysTypes
FROM    #RG_NewSysTypes ;


COMMIT ;

Is this normal behaviour?

Thanks

James B

This may well be SQL Source Control, yes. It polls the database to check for changed objects so you get the little blue blobs.

If you're finding this is causing a performance hit, then it's possible to alter (or disable) this behaviour. Please refer to this post for details: http://www.red-gate.com/MessageBoard/viewtopic.php?t=12837