[Sp3] Another reason not to use DDL Triggers

So, I've already posted about my issues using DDL triggers with Replication (http://thebakingdba.blogspot.com/2009/12/replication-and-ddl-triggers-do-not-mix.html). Well, it looks like it may have contributed to issues with an active/active cluster upgrade (2005 SP3).

Installing the patch on the first active node, we got a failure on the upgrade. The error message? "Target string size is too small to represent the XML instance". But, oddly enough, the passive (which is upgraded first) worked and was on SP3.

So we deleted the trigger (which, interestingly enough, showed that replication uses DDL triggers - which makes sense but I hadn't thought about), and ran it again.

It failed again, but this time because the passive node wasn't upgraded. Check both - and yes, SP3. Roll back and forth, everything looks good. A sucky upgrade, but we got through it.

[Tip] getting local context from sp_ in master

In SQL 2005, if you create an SP in master that queries from system tables (or the INFORMATION_SCHEMA views), it'll return the details from the Master database - regardless of where you're running it. In SQL 2000, it worked.

So, an unsupported workaround, pointed out to me by Erland Sommarskog, SQL MVP & SQL God:

EXEC sp_MS_marksystemobject [your_sp_name]

[Cruft] Fixing dependencies

EXEC sys.sp_refreshsqlmodule 'dbo.MyProcFnOrView'

That will update your dependencies table, allowing you to use sp_depends even if the objects weren't inserted in order. New to 2005, thankfully someone at MS realized and fixed sp_depends' weakness.

When objects are added, rows are added to the internal dependency table. However, it's very easy to get out of sync - say if you add SP A that calls SP B, but add them in the reverse order. Which _never_ happens. Sure.

So, anyhow. Use that, then use one of the handy pieces of code online that assume that the dependency table always works.


UPDATE 2012:
or, you could always use the versions that 2008+ have (don't bother on 2005; not there):

sys.dm_sql_referenced_entities
sys.dm_sql_referencing_entities


sys.sql_expression_dependencies

[Maint] Quick and Dirty maintenance

Is this suitable most places? No. Is it fast and easy? Yes.

Reindex all tables in a database (2000 & 2005)

exec sp_MSforeachtable "DBCC DBREINDEX ('?')"

or

EXEC sp_MSforeachtable "print '?' DBCC DBREINDEX ('?', ' ', 85)"

Update statistics in a database
For 2000 (since sp_updatestats can break certain things)

EXEC sp_MSforeachtable "update STATISTICS ?"

For 2005:

sp_updatestats

[Objects] dropping objects via code

The following code works in both SQL 2000 and SQL 2005.

Tables

if object_id('tempdb..#database_details') is not null
    DROP TABLE #database_details

Stored Procedures

IF EXISTS 
 (
 SELECT * FROM dbo.sysobjects 
 WHERE id = OBJECT_ID(N'[dbo].[the_procedure_name]') 
 AND OBJECTPROPERTY(id, N'IsProcedure') = 1
 )
DROP PROCEDURE the_procedure_name

Alternatively, the following code will CREATE a dummy SP if it doesn't exist, then ALTER it. This way you will only CREATE, not DROP, which can come in handy in certain circumstances, since it will save permissions. Note that this uses INFORMATION_SCHEMA, which is more portable than sysobjects.

IF NOT EXISTS 
 (
 SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
 WHERE ROUTINE_NAME = 'the_routine_name' 
 and routine_type = 'PROCEDURE' --could also be 'FUNCTION' for a function
 )
        EXEC ('CREATE PROC dbo.the_procedure_name AS SELECT 1')
GO
ALTER PROCEDURE dbo.the_procedure_name
AS
SELECT  *
FROM    myTable

[Index] Find duplicate indexes

I've been working on indexes lately, if it's not been obvious. I went looking for code to detect identical indexes, and this one seems the best.

http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx

Here's the top one, which looks for identical indexes.

-- exact duplicates
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

[Jobs] Code to email when job finishes

If you have to run a job outside its normal time, and didn't remember to change the notification before you ran it, fret not. By hitting the system tables, you can have it notify you when the job finishes.

WHILE (SELECT    COUNT(*) 
FROM     msdb.dbo.sysjobhistory h
         INNER JOIN msdb.dbo.sysjobs j
           ON h.job_id = j.job_id
         INNER JOIN msdb.dbo.sysjobsteps s
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_date >= '20090107' and --today's date
         j.NAME = 'your_job_name' AND 
--max step for the job, so you don't get emailed when step 1 of 5 finishes
         h.step_id = 3 
         )=0
BEGIN 
  PRINT 'waiting'
        WAITFOR DELAY '00:01:00'
END         

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBAs',
    @recipients = 'thebakingdba@yourcompanyname.com',
    @query = 'SELECT    j.[name],
         s.step_name,
         h.step_id,
         h.step_name,
         h.run_date,
         h.run_time,
         h.sql_severity,
         h.SERVER,
         h.run_status
FROM     msdb.dbo.sysjobhistory h
         INNER JOIN msdb.dbo.sysjobs j
           ON h.job_id = j.job_id
         INNER JOIN msdb.dbo.sysjobsteps s
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_date >= ''20090107'' and 
         j.NAME = ''your_job_name'' AND 
         h.step_id = 3
' ,
    @subject = 'Your Job Finished',
    @query_result_separator =  ' ',
    @query_result_width = 750,
    @attach_query_result_as_file = 0 ;

[Jobs] Starting a job on a foreign server

Man, I'm lazy right now, copying useful code from other people.

This comes from "SQLAdmin" on the SQL Server Mag forums. Set this as a job step, and it'll run a job on a different server. Check your perms. Note that all this does is kick off the job and tell you if it kicked off successfully.

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=83712&enterthread=y

declare @retcode int
declare @job_name varchar(300)
declare @server_name varchar(200)
declare @query varchar(8000)
declare @cmd varchar(8000)

set @job_name = 'My Test Job' ------------------Job name goes here.
set @server_name = 'MyRemoteServer' ------------------Server name goes here.

set @query = 'exec msdb.dbo.sp_start_job @job_name = ''' + @job_name + ''''
set @cmd = 'osql -E -S ' + @server_name + ' -Q "' + @query + '"'

print ' @job_name = ' +isnull(@job_name,'NULL @job_name')
print ' @server_name = ' +isnull(@server_name,'NULL @server_name')
print ' @query = ' +isnull(@query,'NULL @query')
print ' @cmd = ' +isnull(@cmd,'NULL @cmd')

exec @retcode = master.dbo.xp_cmdshell @cmd

if @retcode <> 0 or @retcode is null
begin
print 'xp_cmdshell @retcode = '+isnull(convert(varchar(20),@retcode),'NULL @retcode')
end

[Free Space] SIMPLE mode yet TLOG still growing?

Had to track down an issue today - a log file had gone from 37gb to 55gb in about 6 hours. Yup, database was in simple mode.

Make sure the log file is actually growing.
http://thebakingdba.blogspot.com/2008/03/maint-show-free-space-within-database.html

Find out _why_ it's still growing.

SELECT name, log_reuse_wait, log_reuse_wait_desc
FROM sys.databases
ORDER BY name

Our result was ACTIVE TRANSACTION. This could be either a transaction, or replication.


Why does this matter?
If the oldest transaction is still open, everything since then has to go in a new part of the data file - think of it like something blocking the entry to your cube. It doesn't have to be big, there's plenty of room inside the cube, but you need to get rid of the item to get in.


Fortunately, finding the errand SPID is easy.

DBCC OPENTRAN ()

It gives you the SPID of the errant process. In our case, it was a user process people had forgotten about. Kill the spid (or get the person to stop it) and rerun your free-space-within-database again.


There are other ways to find the open transactions.

SELECT * FROM sys.dm_tran_session_transactions

, but that's a bit more vague. It'll give you the SPID (session_id) of all open transactions, but for what I was doing it didn't seem to give me the SPID I needed to kill. You could also select from sys.processes, but honestly OPENTRAN is simpler.

-TBD

[Index] Easily find the missing indexes in a query

(run in Grid Mode in SSMS)

SET STATISTICS XML ON
[put your query here]
SET STATISTICS XML OFF 

Once you run that, in the results pane there's a "Microsoft SQL Server 2005 XML Showplan" with XML. Double-click on it - it'll open a new tab in SSMS with the XML broken out. Search for "Missing" - there will be a block entitled "Missing Indexes". It has the INEQUALITY columns, the EQUALITY columns, and the INCLUDEs that it wants.

It may be old news to some of you, but it's one of those things I hadn't played with until recently, and I'm really impressed with.

[Setup] Setting up Database Mail on 2005 servers

Just a little script to set up Database Mail on 2005 boxes. I used to use XP_SMTP_MAIL, since the IMAP mail in SQL Server 2000 was a POS. This is considerably better.

-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Database_Email',
    @description = 'Mail account for use by all database users.',
    @email_address = 'Database_Email@yourcompanyname.com',
    @replyto_address = 'Database_Email@yourcompanyname.com',
    @display_name = 'Database_Email',
    @mailserver_name = 'mail.yourcompanyname.com' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Database_Email',
    @description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Database_Email',
    @account_name = 'Database_Email',
    @sequence_number =1 ;

-- Grant access to the profile to all users in the msdb database

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Database_Email',
    @principal_name = 'public',
    @is_default = 1 ;

go
--Enable advanced options
sp_configure 'show advanced options',1
go
RECONFIGURE
go
--Now enable the server to send mail
sp_configure 'Database Mail XPs',1
go
reconfigure
go

--test mail
declare @test varchar(50)
select @test = 'Test Email from ' + @@servername
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Database_Email',
    @recipients = 'you@yourcompanyname.com',
    @subject = @test