Best option, there is a script sp_blocker_pss80 from MS.

There there is a neat Tool called sherlock, which can be used to read for blocking scenarios, this too from MS PSS...

to Copy package stored in MSDB.

dtutil /SQL PAckageName /COPY DTS;PackageName

Package stored as file system;

dtutil /FILE c:\myPackages\mypackage.dtsx /COPY FILE;c:\myTestPackages\mynewpackage.dtsx

checkout more here: http://msdn.microsoft.com/en-us/library/ms162820.aspx

Or something like:

SELECT substring(cast(run_date as char),1,4)+'-'+substring(cast(run_date as char),5,2)+'-'+substring(cast(run_date as char),7,2)+ ' '

+ STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')

FROM msdb.dbo.sysjobhistory

Did you apply any patches or is it a base install.?

Check the latest Cumulative Updates, I remember there is somewhere a Patch that addresses this issue, i had the same...

ALTER DATABASE [DBNAME]

set ONLINE

this will get the database off emerency mode.

Well, you are following the method as it should be.

But why does the node hang, did you check the error logs, did you try to DROP and re-add the node....

Create a Database SNAPSHOT.That should let the other users Query the Database for SELECTS only.

SQL 2005:

Use the SQLAgentUserRole and SQLAgentReaderRole to define users who want to view the jobs only.

If they want to execut the jobs, place them under SQLAgentOperatorRole

This SP, builds up the SQL statement in the @sql clause and then EXEC (@sql) executes it in the ending notes.

Use PRINT (@SQL) insted of EXEC (@SQL)

also use PRINT Statements...

I have a 75GB Database and i have space constraints on the Drives and the SYSADMIN wont listen for the past 3 years.

So I only keep 1 days worth of...

sp_helptext sp_spaceused

Then use the underlying Query Structure to Format your TABle Structure and Query

select * from sys.dm_exec_requests

select * from sys.dm_exec_connections

Select * from sys.dm_exec_sessions

These should be your best bet on SQL Server 2005. These DMV's Provide more info than sysprocesses and sp_who2.

Join them to...

Check ErrorLog, if you have the LOGIN Auditing feature TURNED ON in the SQl server Properties and set to atleast Failed Logins.

Create #table and dum xp_readerrorlog into the Table and...

select SPID,

DB_NAME(dbid) as dbname,

dbid,

hostname,

program_name,

loginame from master.dbo.sysprocesses

Order by login_time desc

Or use xp_logininfo 'DOMAIN\USER'

or use xp_logininfo 'DOMAIN\groupname','members' - this will list all the members of the NT group.

Check this Query from BOL.

DECLARE @datevar DATETIME;

SET @datevar = '1998/12/31';

SELECT @datevar AS DateVar;