Invalid object name ‘msdb.dbo.cdc_jobs’

This past weekend, I had to migrate a sql instance from an old UCS blade to a new one.  The new blade would boot from the ZFS san instead of the retarded netapp san.  It might be retarded because of all the hands that has been in it but suffice to say, it suck.

I ran into two unknowns during this migration

Continue reading

DNS alias to SQL named instance

I have a server here at cbtr.net that has multiple sql named instances and only 1 IP address.  All sql instances are listening on that IP address and they are all currently on dynamic ports.  I have created a static DNS entry that looks like this:

CDev     A     10.1.1.222

10.1.1.222 is assigned to a server name HorseShoe.  The idea is to have the devs connect to CDev instead of connecting to HorseShoe\Instance1.

Continue reading

SSIS – rouge running packages

This week has been full of excitement here at cbtr.net.  We had ssis packages that were being executed via a sql jobs.  These jobs were taking forever to finish.  For all our other servers, there’s a Long Running Job setup via DPA to monitor but since many developer uses SSIS and DPA can’t send alerts to specific group of people, the alert was disabled.

Today, I got a message from a dev telling me that some of his packages were running for a long time, 18 hours long (that’s what she said), and it should of taken < 30 seconds (that’s not what she said).

Continue reading

Finding Denied permission

Last week, apparently I messed up revoking some permission and ended up denying it instead so this week, I’m having to go find which user has denied permission when it should of been revoke.  The following query got me thru it:

SELECT  USER_NAME(grantee_principal_id) AS ‘User’
, state_desc AS ‘Permission’
, permission_name AS ‘Action’
, CASE class
WHEN 0 THEN ‘Database::’ + DB_NAME()
WHEN 1 THEN OBJECT_NAME(major_id)
WHEN 3 THEN ‘Schema::’ + SCHEMA_NAME(major_id)
END AS ‘Securable’
FROM    sys.database_permissions dp
WHERE   class IN ( 0, 1, 3 )
AND minor_id = 0
AND state_desc = ‘deny’