Capturing SQL Agent Job Information

The following query will pull selected data from the sp_help_job stored procedure and put it into the requested table:

USE msdb

IF OBJECT_ID('tempdb..#TmpJobs') IS NOT NULL
DROP TABLE #TmpJobs

SELECT *
INTO #TmpJobs
FROM OPENROWSET('sqloledb'
, 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job')

SELECT originating_server, name, enabled, owner, last_run_date, last_run_outcome, next_run_date
INTO Database..Table
FROM #TmpJobs

This comes in very handy if you want to use a DTS package to grab this data from multiple servers and populate one table that you can query. I am using this in correlation with a truncate command for the table so that a select * will get me only the current data, since historical information is already kept in each server's individual msdb database.

Note: In SQL2005 you will have to have the sp_configure parameter Ad Hoc Distributed Queries enabled.

Find Failed Jobs in MSDB Job Logs

This will get you a short list of the jobs that have failed and are still in sysjobhistory:

select a.server, b.name, a.message, a.run_date
from sysjobs b JOIN sysjobhistory a
on a.step_id = 0
and a.run_status = 0
order by b.name

You can run this query from a remote server via a DTS package against many servers to save some point-and-click time. Have all the data compiled into one monitoring database and you have a quick list you can scan through rather than touching every server in yoru network.

Insert Data Into One Table If Criteria Matches Data in a Different Table

I know this sounds very simplistic, but you'd be surprised how many people simply have no clue how to do this:

You want to update Column1 in TableA to match Column1 in TableB as long as Column2 in TableA matches Column2 in TableB:

Here's how the query should look:

UPDATE TableA
SET Column1 = B.Column1
FROM TableA A
INNER JOIN TableB B
ON A.Column2 = B.Column2

SQL Error 945 While Creating New Database

I ran across a problem creating a new database on a SQL Server that had recently been through a crash and rebuild. It had been running fine for months, but now whenever you tried to create a new database it would throw a SQL 945 error, referring to space or memory allocation issues and then referencing the data or log file of an unrelated database that had been previously deleted.
After some troubleshooting I came to the conclusion that there must be some orphaned data in the Master database. I first checked sysdatabases, and everything looked fine, 23 databases all in a row. Next I looked at sysaltfiles, and lo and behold there were entries for dbid 24, which did not exist. Since it was the next available dbid in sysdatabases, SQL Server was attempting to create the new database as dbid 24, but the data in sysaltfiles did not jive with it. Long story short, I removed the entries for dbid 24 in sysaltfiles and the problem was fixed.

Adding Leading Zero(s) to Field Entries

Adding Leading Zero(s) to field entries in a SQL table may be something you have to do at some point. Why? Beats me, I just know it has come up more than once for me, and the "customer is always right."

This simple query will update the data in TABLE1, field ORDER to include leading zeros for up to 10 total digits. ORDER is varchar(10)

UPDATE TABLE1
SET ORDER = right('0000000000' + ORDER, 10)

It's almost obscene how simple this is, but you will have to make sure that the queries used to add data to this table are updated to include the leading zeros in the INSERT, otherwise you will have to continuously add leading zeros to this field moving forward.

Rebuilding MSDB on SQL2K

1. In SQL Server Enterprise Manager, right-click the server name andclick Properties.
2. On the General tab, click Startup Parameters.
3. Add a new parameter as "-T3608" (without the quotation marks).
4. Stop, and then restart SQL Server.
5. Make sure that the SQL Server Agent service is not currently running.
6. Detach the msdb database as follows:use master GO sp_detach_db 'msdb' GO
7. Delete or rename the msdb mdf and ldf files.
8. Run the INSTMSDB.SQL script
9. Remove the -T3608 trace flag from the startup parameters box in Enterprise Manager
10. Stop, and then restart SQL Server.

MSDB Database is HUGE... try deleting old backup history

We recently ran into a problem with the MSDB database on one of our servers getting too big.

We have about 30 databases on this particular server, and dumpo transaction logs every 15 minutes on many of them. We have backup history clear back to 1998. Since our backup/restore plan only includes going back as far as 60 days, thevast majority of this backup history is obsolete.

So... Microsoft has supplied a wonderful little stored procedure: sp_delete_backuphistory

The problems are:

1. The code is poorly written
2. Any hits against the backup history tables will be table scans since there are no indexes

So, if you run EXEC sp_delete_backuphistory '07/07/07' it will literally take forever. I clocked mine at about 5 rows per minute.

In order to fix this, an index needs to be created on msdb..backupset. MS does not like it when you modify their system tables, so you can't use Enterprise Manager to do this.

Run the following in Query Analyzer:

USE msdb

CREATE INDEX temp_media_set_id

ON backupset (media_set_id)

GO

This took a while to run, but will save you a TON of time when you finally run the sp again:

EXEC sp_delete_backuphistory '07/07/07'