Post reply

linked server question

  • June 14, 2006 at 10:06 am

    #169167

    I have exact 2 logging databases that reside on 2 different servers.  I would like to copy the production database over to the support database.  What I would like to do once I have the initial copy done, is run a job say once per month to append to the Support database, then truncate the Production database.  I've setup a linked server from Production to Support SQL Server.  I can query the support database fine.

    This was by sql statement, but seems to run for ever and don't know if this is the most efficient way:

    INSERT INTO PSFTSQLZAP.SQLPERF.dbo.BLOCKED_PROCESSES

    SELECT BLOCKED_DTTM, BLOCKED_SPID, BLOCKED_CONTEXT, BLOCKER_SPID, BLOCKER_CONTEXT, BLOCKER_STATUS, WAIT, DB, TABLE_NAME, INDEX_ID, LOCK_LEVEL, LOCK_REQUESTED, BLOCKER_SQL, BLOCKED_SQL FROM SRVPSS02.SQLPERF.dbo.BLOCKED_PROCESSES

    WHERE BLOCKER_SPID = 110

     

    Appreciate ideas/thoughts

  • June 14, 2006 at 10:16 am

    #643707

    You dont need to use 2 linked servers.  Try running this query on the support server:

    INSERT INTO dbo.BLOCKED_PROCESSES

    SELECT BLOCKED_DTTM, BLOCKED_SPID, BLOCKED_CONTEXT, BLOCKER_SPID, BLOCKER_CONTEXT, BLOCKER_STATUS, WAIT, DB, TABLE_NAME, INDEX_ID, LOCK_LEVEL, LOCK_REQUESTED, BLOCKER_SQL, BLOCKED_SQL FROM SRVPSS02.SQLPERF.dbo.BLOCKED_PROCESSES

    WHERE BLOCKER_SPID = 110

    Also, it is a best practice to always qualify the inserted columns (INSERT Table1(col1, col2) SELECT col1, col2 from Table2).

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply