Sql-server – Linked server issues and administration

I believe consolidation like you are planning to do is a good thing. Depending on how the linked servers are queried, you may find a huge jump in performance (cross server joins are usually quite horrible when compared to cross database joins on the same server).

The downside is that your entire system is now sitting on one machine. When this machine is down, all your apps are down.

Here is what I suggest you do to prepare:

Run the SQL Server Upgrade Advisor against each database. It will tell you of any incompatible code that may exist (e.g. "*=" join syntax, no longer supported).

Identify DTS packages running against the old systems. DTS is no longer supported. You will have to rewrite them using SSIS.

Make sure the new system is powerful enough to handle the workoad.

SQL is usually pretty good about closing linked server connections. BUT, I've seen SQL server 'hang' linked server queries of certain types if all of the result set isn't fully fetched and/or closed properly by the client. IOW, SQL doesn't think that the app has finished retrieving the data, so it doesn't close the connection to the other server. For whatever reason, the offending app didn't report any errors or query time outs (from it's perspective), but we did have to recycle the IIS application every night or it would eventually eat up all of the RAM on the IIS server.

I would start by running my usual "find any connection that is waiting" query:

SELECT 
@@servername 'ServerInstance',es.session_id,er.blocking_session_id
,es.status
,es.login_name,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name,er.command 
,es.reads,es.writes
,es.cpu_time
,er.wait_type,er.wait_time,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' 
    WHEN 1 THEN 'ReadUncommitted' 
    WHEN 2 THEN 'ReadCommitted' 
    WHEN 3 THEN 'Repeatable' 
    WHEN 4 THEN 'Serializable' 
    WHEN 5 THEN 'Snapshot' 
    END AS transaction_isolation_level 
,last_request_start_time
,last_request_end_time
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2, 
    (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2 
        ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text   
,ph.query_plan
FROM sys.dm_exec_connections ec 
  LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
  LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
  OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
  OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID 
-- AND es.status = 'running'
AND (
es.status != 'Sleeping'
OR wait_time > 0
OR es.session_id in (select blocking_session_id from sys.dm_exec_requests where  blocking_session_id != 0)
)
ORDER BY es.session_id

I'd look for any sort of wait type like "OLEDB" (which should indicate linked server activity, and possibly other things) and try to tie those back to code that accesses the oracle server. I'd also pay attention to the last request start and end times.