SQL Server – Using table variable to persist data within Transactions

\ by

This was a great idea taken from Three Really Handy Tricks With SQL Server Table Variables post.

1.Create a table variable @LOG which stores log text for each step and maybe some other key data about the step.
2.In each step, INSERT a row into @LOG.
3.When rollback occurs, INSERT another row in @LOG giving the reason for the rollback.
4.ROLLBACK
5.SELECT * FROM @LOG

All the code calling this batch has to do is interrogate the returned record set. If its last record is the one recording the ROLLBACK, then it knows something went wrong and can alert the user somehow, giving the full log of what happened. You cannot do this with a temporary table because temporary tables are transactional so the rollback will erase the log.

Posted in: SQL

SQL Server – Check for running SQL Jobs

\ by

I like to run this against a server group in SSMS to check multiple servers at once.

SELECT  job.Name,
        job.job_ID,
        job.Originating_Server,
        activity.run_requested_Date,
        DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) AS ElapsedMinutes,
        activity.last_executed_step_date,
        activity.last_executed_step_id
FROM    msdb.dbo.sysjobs_view job
JOIN    msdb.dbo.sysjobactivity activity
ON      job.job_id = activity.job_id
JOIN    msdb.dbo.syssessions sess
ON      sess.session_id = activity.session_id
JOIN    (
         SELECT MAX(agent_start_date) AS max_agent_start_date
         FROM   msdb.dbo.syssessions
        ) sess_max
ON      sess.agent_start_date = sess_max.max_agent_start_date
WHERE   run_Requested_date IS NOT NULL
        AND stop_execution_date IS NULL
    GO

Here is what I use to prevent one job from trying to restart another job.

EXEC msdb.dbo.sp_help_job @execution_status = 1,
 @job_aspect = 'JOB',
 @job_name = 'DBA - Daily Database Maintenance'

IF (@@ROWCOUNT = 0)
 PRINT 'Start job'
ELSE
 PRINT 'Job already running ... Nothing to do'
Posted in: Uncategorized

SQL Server – Script to Detach Attach Databases

\ by

This uses the old syntax.

SELECT DISTINCT
 DB_NAME(dbid) AS DatabaseName,
 fileid,
 CASE WHEN fileid = 1 THEN 'exec sp_detach_db ''' + DB_NAME(dbid) + ''';'
 ELSE ''
 END DetachScript,
 CASE WHEN fileid = 1
 THEN 'exec sp_attach_db @dbname = N''' + DB_NAME(dbid) + '''' + ',@filename' + CONVERT(VARCHAR(10), fileid) + '=N''' + filename + ''''
 ELSE ',@filename' + CONVERT(VARCHAR(10), fileid) + '=N''' + filename + ''''
 END AS AttachScript
FROM master.dbo.sysaltfiles
WHERE DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE'
 AND DB_NAME(dbid) IN ('Sandbox')
ORDER BY DB_NAME(dbid),
 fileid
GO
Posted in: SQL