[Jobs] Quick & Dirty - is the job running?

Needed a simple piece of code to tell me if a job was running or not. Several ways to do it, but this is the simplest and probably dirtiest.

EXECUTE sp_get_composite_job_info @job_id = 'B74856BF-3326-4B9F-B3A6-B1D182E1F300', @execution_status = 1

IF @@rowcount = 1
PRINT 'job running'
else print 'nope, not'

sp_MSforeachDB - skipping databases

SP_MSforeachDB is awesome - an easy way to walk through every database and run some code.

But what about databases you don't _want_ to touch?

sp_MSforeachdb 'if ''?'' NOT IN (''tempdb'',''model'',''a'',''b'',''c'') 
--sp_updatestats required after reorg, but not after rebuild
begin 
use ?
exec sp_updatestats 
end' 

Kulich - Russian Easter Bread

FINALLY, something baking related! It's been too long, I've been resting on my laurels.

I'll update this once I've made it myself. One other way to make it is inside a coffee can - I need to get details on that, since that's how I always had it - bullet-shaped, funnily enough, with a sprinkle-laden glaze on top.

6 cups sifted flour
3 egg yolks
1 whole egg
3/4 cups sugar
1 teaspoon salt
1 and a 1/2 packages yeast (come in a pack of 3)
1 and 1/4 cups WARM (not hot) milk
1 stick (1/4 pound) melted butter. 
 (Let cool off so it's not hot.)


Put yeast and milk in bowl and mix.
put eggs, sugar and salt in another bowl and mix on low speed.
Put milk and yeast together with the flour.
Add the eggs,sugar, salt mixture in.
Add melted butter.
Mix well with a wooden spoon.
Knead the dough mixture about 10 minutes (maybe more).
Dough needs to be soft.
Put a cover over the bowl so it can rise.
Dough needs to rise for an hour or more.
Needs to become twice its size.
Separate into 2 loaves.
Put 1 cup raisens for each loaf
Preheat oven to 350.
Use loaf pan sprayed with PAM or greased with butter.
Let dough rise again for 40-60 minutes.
If you want a shiny crust, use the whites from the 3 eggs.
Beat the whites for just a minute with a fork and brush the mixture
over the crust before you put in oven.
Bake 45-60 minutes.
Check dough with toothpick at 45 minutes to see if ready.
Pull the bread out and put on a towel.

[Code] IF EXISTS table check

Because I keep coming over here to find the snippet...

if object_id('tempdb..##temptablename') is not null
    drop table ##temptablename
CREATE TABLE ##temptablename (id INT IDENTITY, filelist VARCHAR(255))

or

 SELECT * FROM tablename.dbo.sysobjects 
 WHERE id = OBJECT_ID(N'tablename_goes_here') 
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1

[Replication] Alternative to SQLMonitor (Replication Monitor)

SQL Server's Replication Monitor is great except for a few major issues
1) It has to be running all the time, and it consumes a large number of window resources.
2) Due to the way SQL reports issues in replication, it can show a publication as good, even if it's been erroring for 2 days.
3) It's active, not passive - I have to go look at it. It can't warn me via email or whatnot.

So, half a day digging through code and error messages, and I've come up with this. This is not fullproof by any stretch - I don't deal with Merge Replication, for instance. But in MY environment, it seems to work pretty well.

I'll see about adding a couple more fields, but I want to get this on paper.
(And yes, this is a request: if there's a better way than this, let me know)

--Courtesy of The Baking DBA
--Replication query that shows what's currently in error.
--1.01 added "delivering" exclusion, email, fixed 
--   line-too-long-breaks-blogspot formatting
IF OBJECT_ID('tempdb.dbo.##replication_errors') IS NOT NULL 
    DROP TABLE ##replication_errors

SELECT 
 errors.agent_id, 
 errors.last_time, 
 agentinfo.name, 
 agentinfo.publication, 
 agentinfo.subscriber_db,
 error_messages.comments AS ERROR
INTO ##replication_errors
FROM 
--find our errors; note that a runstatus 3 
--can be the last message, even if it's actually idle and good
(SELECT agent_id, MAX(TIME) AS last_time 
 FROM distribution.dbo.MSdistribution_history with (nolock)
 WHERE runstatus IN (3,5,6) 
 AND comments NOT LIKE '%were delivered.' 
 AND comments NOT LIKE ' GROUP BY agent_id) errors
INNER JOIN 
(SELECT agent_id, MAX(TIME) AS last_time 
 FROM distribution.dbo.MSdistribution_history with (nolock)
 WHERE runstatus IN (1,2,4) 
 OR comments LIKE '%were delivered.'
 GROUP BY agent_id) clean
 ON errors.agent_id = clean.agent_id 
 AND errors.last_TIME > clean.last_time
--grab the agent information
INNER JOIN distribution.dbo.MSdistribution_agents agentinfo
ON agentinfo.id = errors.agent_id
--and the actual message we'd see in the monitor
inner JOIN distribution.dbo.MSdistribution_history error_messages
ON error_messages.agent_id = errors.agent_id 
AND error_messages.time = errors.last_time
AND comments NOT LIKE '%TCP Provider%'
AND comments NOT LIKE '%Delivering replicated transactions%'

IF (SELECT COUNT(*) FROM ##replication_errors) > 0 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'email_profile',
    @recipients = 'blah@tbd.com',
    @subject = 'Replication errors'
   ,@query = 'select * from ##replication_errors'
   ,@query_result_header = 0

DROP TABLE ##replication_errors

IF EXISTS for tables

I know - I should have this memorized. And it doesn't deal with schemas.

IF EXISTS
(
SELECT * FROM databasename.dbo.sysobjects
WHERE id = OBJECT_ID(N'tablename')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
DROP TABLE databasename.dbo.tablename

[Setup] Making sure your disks are optimized

(update 2014: at this point, the newer OSs take care of this.  And you.... never.... get LUNs mapped over from old servers, right?  *grin*)

At this point, probably everyone knows that you need to make sure to format your drives properly to take full advantage of them. There's two different issues: the cluster size, and the offset.

Practically, you want the offset to be 1024kb (leaves room for SAN "headers"), and the block size to be 64k.

(Link to MS whitepaper, which includes pretty charts showing major improvement: http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx)


Here's how to make sure.

Block size.

c:\users\you> fsutil fsinfo ntfsinfo d:

That will give you a bunch of info. What you care about is the Bytes Per Cluster, which should be 65536 (aka 64k)

Cluster offset:
Using DISKPART:

> diskpart

> list disk

> select disk 1 (or whichever disk you want to look at)

> list partition

Look for the "offset" column

Powershell: See
http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!291.entry
for a powershell script.

To format a drive properly:
diskpart
list disk
select disk 2
create partition primary align=1024
format fs=ntfs unit=64K label="yourdrivenamehere" nowait
exit

[Text] Counts of a word within a file. Not by line, total.

Came across this (courtesy of Franklin52 in the unix.com forums).
http://www.unix.com/shell-programming-scripting/63576-how-find-count-word-within-file.html

Say you need a count of a particular word in a file. For example, in an XML file where the word can repeat within a line. Can't use WC or GREP or FIND, but AWK will do the job. (You do have these tools on your Windows box, right? [if you have a UNIX box, it's assumed you do])

awk 'BEGIN{RS=" "}/WORDTOCOUNT/{h++}END{print h}' blah.txt

One note: this assumes that there will be a space somewhere between the occurrence of the words. TESTTEST and
TEST
TEST
would each only count as 1, since there's no space to "reset" the find. (It's a stream function - search for the word. If you find it, increment by one and skip forward to the next space. When you hit a space, start searching again.) For our XML, there are spaces after the tag we searched for, so the counts work.

Replication and DDL Triggers - DO NOT MIX

So, we had started rolling out DDL Triggers, and then today replication broke.

How? A weird ARITHABORT error trying to add a table via the GUI. Weird. So I disable the trigger and add it - at which point replication itself starts throwing the error :

Target string size is too small to represent the XML instance (Source: MSSQLServer, Error number: 6354)
Get help: http://help/6354


Well, it turns out I have an XML trigger on the TARGET database, and it can't deal with the large commands involved in transactions.

How to diagnose and find the exact commands causing problems?

use [replicated_table]
go
sp_helparticle @publication = N'publication_name', @article = 'article_name'
go
use distribution
go
sp_browsereplcmds @article_id = 77 --where 77 is the article_id from above

DDL Triggers

We're slowly starting to roll this out, based on the below code. A very well written article; our concern is on performance.

http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx


--------------------
UPDATE:
Below is the code we originally rolled out, then rolled back due to XML errors with replication (see other posts with tag DDL Triggers)

With my luck it was something stupid in my code, but I haven't gone back and looked - I'm using Event Notifications now.



--1.1 version MDB 20091119.  Removed the XML field as that's a lot of data being held for no reason.
/*
use dba_repo
If Object_ID('dba_repo.dbo.DDL_Event_Log') IS NOT NULL
DROP TABLE dbo.DDL_Event_Log
CREATE TABLE dbo.DDL_Event_Log(

ID int IDENTITY(1,1) NOT NULL,
EventTime datetime NULL,
EventType varchar(15) NULL,
LoginName VARCHAR(50),
ServerName varchar(25) NULL,
DatabaseName varchar(25) NULL,
ObjectType varchar(25) NULL,
ObjectName varchar(60) NULL,
UserName varchar(15) NULL,
CommandText varchar(max) NULL
--,Entire_Event_Data XML
)
go
*/
CREATE TRIGGER [ddltrg_Audit_Log] ON DATABASE -- Create Database DDL Trigger
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
CREATE_INDEX, DROP_INDEX, ALTER_INDEX,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_USER, ALTER_USER, DROP_USER
/*
CREATE TRIGGER ddltrg_Server_Audit_Log ON ALL SERVER -- Create Database DDL Trigger
FOR
CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
*/
AS
--http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx
--http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1346274,00.html for event types
--See http://msdn.microsoft.com/en-us/library/ms189871%28SQL.90%29.aspx for event types
SET NOCOUNT ON
If Object_ID('dba_repo.dbo.DDL_Event_Log') IS NOT NULL
BEGIN
DECLARE @xmlEventData XML
-- Capture the event data that is created
SET @xmlEventData = eventdata()
-- Insert information to a Event_Log table
INSERT INTO dba_repo.dbo.DDL_Event_Log
(
EventTime,
EventType,
LoginName,
ServerName,
DatabaseName,
ObjectType,
ObjectName,
UserName,
CommandText
-- , Entire_Event_Data
)

SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(60), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
-- , @xmlEventData
END