Thursday, July 8, 2010

Log Shipping error

I had a problem where I noticed on my primary log shipping machine (the one the data originates on) that the LS_Alerts job was failing. It said that a new database we were working on was failing. I also noticed that I had two jobs that didn't belong on that machine..I had a LS_Copy and a LS_Restore...which should have been on the secondary machine. I believe that another admin set it up from the development machine during the initial phase for some reason, but I couldn't find a source machine. I delete the two jobs from the Job manager, but the Alert job still showed failures when run. I decided to figure out what was the problem, and this is what I did to look into it:

I saw that the alert job ran the proc sys.sp_check_log_shipping_monitor_alert, so I opened up the proc to see what it was doing. It calls the following:

(select primary_server
,isnull(threshold_alert, 14420)
,datediff(minute, last_backup_date_utc, @curdate_utc)
,cast(0 as int)
from msdb.dbo.log_shipping_monitor_primary
where threshold_alert_enabled = 1
and datediff(minute, last_backup_date_utc, @curdate_utc) > backup_threshold)
(select secondary_server
,isnull(threshold_alert, 14421)
,datediff(minute, last_restored_date_utc, @curdate_utc)
from msdb.dbo.log_shipping_monitor_secondary
where threshold_alert_enabled = 1
and (datediff(minute, last_restored_date_utc, @curdate_utc) > restore_threshold
or last_restored_latency > restore_threshold))

and raises an error if there are records.

When I ran it on my primary machine the second statement (from log_shipping_monitor_secondary) had a record in it; but the odd thing was that when I just did a select * against the table, I saw that the primary server for that statement was the same server as the secondary it was set to logship to its self. From that point, it was simple, delete the record and the error went away.

Hope that helps someone in the future.

Thursday, July 1, 2010

Scary Script

This has to be one of the scariest scripts I have ever co-worker sent it to me. It is used to delete all data from all tables in a given database.

-- change me!
USE [MyDatabase]

-- don't touch below here

-- disable all constraints

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints

-- reseed identity columns
EXEC sp_MSForEachTable "DBCC CHECKIDENT ( '?', RESEED, 0)"

-- check to see if any tables have rows
-- this could probably be handled better
CREATE TABLE #CheckRows (TableName VARCHAR(60), NumRows INT)
EXEC sp_MSforeachtable "INSERT INTO #CheckRows SELECT '?',COUNT(1) FROM ?"
SELECT * FROM #CheckRows WHERE NumRows > 0