Testing linked servers

Its been scripting day, although not too complicated scripting. One of my team mates came up to me asking “Is there any easy way out to test linked servers configured in an instance?”. I said there is a way, but not sure if it would be easy or hard way out. Well, at the end with little tussle around on the script, I realized I should keep it simple and here is the bunch of t-sql statements I have written.

I have built this script from an original script written by someone (thanks to his ideas!).

/*Script to Test Linked Servers connectivity*/

BEGIN
SET NOCOUNT ON
/*Variable Declaration */
DECLARE @ServerName sysname
DECLARE @msg VARCHAR(500)
DECLARE @status INT ;
/*Table variable to capture the linked server status*/
DECLARE
@LinkedSrvrStatus TABLE
(
LineItem INT IDENTITY(1,1)
, LinkedSrvName VARCHAR(200)
, LinkedSrvStatus TINYINT
, TestComments VARCHAR(800)
)

– Get the list of Linked Servers
DECLARE LnkdsrvrCrsr
CURSOR FAST_FORWARD FOR
SELECT name FROM sys.servers
WHERE is_linked=1 AND name NOT IN (”) –Not in Clause can be used to exclude some Test Linked Servers

OPEN LnkdsrvrCrsr
FETCH NEXT FROM LnkdsrvrCrsr INTO @ServerName

WHILE @@FETCH_STATUS = 0
BEGIN
– The Below query will test the connectivity of the linked server
BEGIN TRY
EXEC master..sp_testlinkedserver @ServerName
/*PRINT ‘YIPEE! THERE IS A LINKED SERVER OF SUCH KIND AND CONNECTIVITY IS THROUGH’ */ — Debugging statement, and the content of the statement could be changed appropriately.
INSERT INTO @LinkedSrvrStatus (LinkedSrvName, LinkedSrvStatus, TestComments) VALUES (@ServerName,1,’YIPEE! THERE IS A LINKED SERVER OF SUCH KIND’)
END TRY

– Checking Status and Sending Alert Mail to Team
BEGIN CATCH
PRINT ‘NO SUCH LINKED SERVER OR CREDENTIALS ARE INCORRECT!’
INSERT INTO @LinkedSrvrStatus (LinkedSrvName, LinkedSrvStatus, TestComments) VALUES (@ServerName,0,’NO SUCH LINKED SERVER OR CREDENTIALS ARE INCORRECT!’)
END CATCH

FETCH NEXT FROM LnkdsrvrCrsr INTO @ServerName
END
CLOSE LnkdsrvrCrsr
DEALLOCATE LnkdsrvrCrsr
END

/*Select linked server status from the table variable*/
SELECT * FROM @LinkedSrvrStatus

I hope this script helps someone out there.

Cheers!

Long running sessions

Hi,

One of the DBA’s in my shop wanted to know which query do I use for finding long running sessions in SQL Server 2005. Here is the one I shared, and often use while doing performance tuning (snipped from SSQA.net).

select r.session_id, s.host_name, s.program_name, s.host_process_id,
r.status, r.wait_time,wait_type,r.wait_resource,
substring(qt.text,(r.statement_start_offset/2) +1,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end -r.statement_start_offset)/2)
as stmt_executing,r.blocking_session_id, r.cpu_time,r.total_elapsed_time,r.reads,r.writes,
r.logical_reads, r.plan_handle
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt, sys.dm_exec_sessions s
where r.session_id > 50 and r.session_id=s.session_id
order by r.session_id, s.host_name, s.program_name, r.status

I hope you guys could also make use of this script, and there are lots sitting here.  Thanks to SQL Master of SSQA.net for his efforts in writing these queries and I keep using one of these kinds quite often.

Cheers and enjoy!

Run a SQL Server job in intervals of seconds

One the projects I work required a script to monitor sys.sysprocesses table to capture activity in the database.

At this point my thoughts were running around and I was looking for some scripts on the web (to avoid reinvention of the wheel). At last, I found a script written by Chad Boyd -  check the article here sp_who for Sql 2005 (sp_who2k5).
 
Now what? I had to tweak the above script so that it could be joined up with sys.sysprocesses table instead of sys.dm_exec_sessions. The reason why I did this was to ensure I get information for all the spid’s listed in the sys.sysprocesses table, unlike the sys.dm_exec_sessions which gives only information of running spid. Now I was little perplexed on how to poll the using the above tweak script in seconds.

Well, I was telling myself "Don’t worry, dude!, you will have the breakthrough". And yes, I did have the break when I learnt that we could schedule a SQL Agent Job to run in seconds by setting the value of the parameter @freq_subday_type to 2. If you set this value the Agent job runs every few seconds and by setting the  @freq_subday_interval.

I have the job running for past few weeks and gathering all the information I need from the system table, DMV and DMF’s. I hope this article helps you all!

Happy reading!

Dependent tables

Yesterday, I had a query from one of my fellow workers on how to find the dependent tables of a primary key. I happen to do this script quickly for him, and he was something like "Wow!".

I haven’t yet revealed to him how got the script. Well, it is simple and I am going to share it with everyone out there.

I recollected that I had seen some results like what he wanted by running a system stored procedure. OK, so what next? I did these quickly

1) sp_helptext sp_help
2) Searched for Foreign Key keyword in the stored procedure and I couldn’t find.
    And I realized probably there could a call to a different system sp. So I scanned thro’ and found sp_helpconstraints
3) sp_helptxt sp_helpconstraint
4) Search for Foreign Key keyword, and once I found the query I tweaked around to give him more of what he needs (you see a value add ).

Finally the script is here, it is simple.

select 
    object_name (fk.referenced_object_id)
    , rtrim(schema_name(ObjectProperty(parent_object_id,’schemaid’))) 
     + ‘.’ + object_name(parent_object_id) 
   from sys.foreign_keys fk
    inner join sys.sysobjects so
    on fk.referenced_object_id = so.id
order by object_name(so.id)