Texas Hill Country BBQ Brisket (yes, that's redundant)

Bare bones, basic Texas BBQ. Have done it twice so far with surprisingly good results.

• One whole brisket, in cryo-vac packaging (aka a "packer-cut brisket"). Weighs about 10 pounts.
  
• One smoker. I cheat and use an electic smoker (Brinkman, $70 at Home Depot).
  
• Wired-probe thermometer. You'll put the probe in the brisket, and the actual temperature will be shown on the other part which will be NEAR the smoker (not on).
  
• One cup yellow mustard
  
• Half-cup kosher salt
  
• Half-cup black pepper
  
• 1-8 pounds Oak chunks (not chips). Can use Hickory, Mesquite, Apple, Cherry.
  

Steps:

1. Soak 1 pound wood in water (I put it in a ziplock-style bag). You want it to soak between 15 minutes and one hour.
   
2. Pull brisket out of bag, drain off juices, slice the "fat cap" (one side will have a substantial amount of fat) with a knife, but not all the way to the actual meat.
   
3. Cut brisket in half - a whole one won't fit on my smoker. Make sure the two pieces are roughly of equal weight, since they're both going to cook the same amount of time. As you cut it in half you'll see that there are two muscles. And, just to make it fun, the grain on one is perpendicular to the other.
   
4. Coat each piece in mustard. A quarter-cup is probably plenty for each, but you want everything coated.
   
5. Mix salt+pepper together. "Dalmatian Dust" or "Dalmatian Rub". Apply liberally to both pieces of brisket.
   
6. Drain wood chunks, put in smoker. Fill water bowl with water. Put in smoker. Put brisket on both top & bottom grates. Put thermometer in bottom piece. Put lid on. Plug in. Apply roughly one pound of moist wood per hour until the meat is very dark in color. Remove once temperature hits 185. Wait 20 minutes. Separate the two cuts of meat. Determine where the grain is (the way the long muscle strands run), then slice perpendicular to grain. Eat.
   

[WAT] Why I hate ISNUMERIC

So, ISNUMERIC is simple, right? Put in numbers, and it tells you whether it is.
Except it has very specific exceptions you may not know about.

Any of these will come back with ISNUMERIC = 1:

• 0D123
• 123D50
• 123E50
• $,,1,,.1

Currency doesn't count (and that's ALL currency symbols), D and E don't count in certain circumstances, commas and periods don't count.



Instead, use something like this:

if (select PATINDEX('%[^0-9.]%','$00.01')) = 0  print 'numeric' 

(AND DON'T  FORGET THAT IT WILL EXCLUDE NULLS) 

[Replication] more replication trouble tracking

--get list of the possible databases by querying the publisher

SELECT * FROM distribution.dbo.MSpublisher_databases

--Now figure out what the article number is
--the database name there is the database that the publication is in.
Replication_Master..sp_helparticle @publication = 'User_Profiles'


--Now that you have the publisher database ID (step 1) and the article id (step 2)
--get the list of commands.
EXEC distribution..sp_browsereplcmds @publisher_database_id = 2, @article_id = 369


--And if you're really lucky, in sqlmonitor you'll get the following:
--(Transaction sequence number: 0x00018A0500009072002A00000000, Command ID: 1)
--in which case...

EXEC distribution..sp_browsereplcmds @publisher_database_id = 2, @article_id = 369, 
@xact_seqno_start = '0x00018A0500009072002A00000000', @xact_seqno_end = '0x00018A0500009072002A00000000'
--(set both start and end to the same value, the one in the error message)

-----------

simplified version 2013/07/12

was having more problems. 

first, look at commands:

use distribution 

 select top 1000 * from dbo.MSrepl_commands ORDER BY command_id  DESC

Now, from there we have two options.  The first gives you the name of the problem child.

 SELECT * FROM distribution.dbo.MSpublisher_databases
 SELECT * FROM dbo.MSarticles ORDER BY article_id

The second gives you the exact commands run

EXEC distribution..sp_browsereplcmds @publisher_database_id = 6, @article_id = 338, 
@xact_seqno_start = '0x0005334A0000049B0001', @xact_seqno_end = '0x0005334A0000049B0001'
--begin and end seqno will be the same.

and if you need to remove old records because you're running out of space and just did a bunch of changes....

(remembering that it saves all the commands for 72 hours by default)

   EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 50

[Code] IF EXISTS for procedures using INFORMATION_SCHEMA

Adam Machanic's code does two things that I love, but don't think to do.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'yourprocname')

      EXEC ('CREATE PROC dbo.yourprocname AS SELECT ''stub version, to be replaced''')

GO

1. It uses the INFORMATION_SCHEMA tables. These are like the system tables, but are more portable (every database vendor has them and they all look the same), and are meant to be human-readable. The downside is that they don't necessarily have all the details you need. I use INFORMATION_SCHEMA.TABLES AND INFORMATION_SCHEMA.COLUMNS all the time, but forget about ROUTINES, which includes functions and stored procedures.
   
2. By creating a stub entry, it ensures permissions remain, as well as other things like the original creation date. If you have an automated environment, you might do a DROP/CREATE, which would break all your explicit permissions (and reset the create_date). By doing a stub entry then an ALTER, you ensure those remain.
   
   Highly recommended code.

[Replication] Better alternative to Replication Monitor

(changed on 10/12 - whoever woulda thunk a UNION wouldn't work right?)
(changed 2013/04/08 - realized the IF cluase at the end can be confusion if you're not thinking about it)

I hate Replication Monitor. Here are a couple of scripts that, embedded in a job, will better help you. I need to look into adding special alerts for replication. The main change is the addition of snapshot detection - we have missed issues because it somehow falls offline and doesn't notify that the replication needs to be snapshotted.

IF OBJECT_ID('tempdb.dbo.##replication_command_count') IS NOT NULL

DROP TABLE ##replication_command_count

SELECT SUM(UndelivCmdsInDistDB) AS UndelivCmdsInDistDB,

MSdistribution_agents.NAME,

MSdistribution_agents.publication,

subscriber_id,

subscriber_db

INTO ##replication_command_count

FROM MSDistribution_Status

INNER JOIN MSdistribution_agents

ON MSDistribution_Status.agent_id = MSdistribution_agents.id

WHERE UndelivCmdsInDistDB > 0 --show only those that are backed up

AND subscriber_id > 0 --negative subscriber IDs are for those that

--always have a snapshot ready

AND MSdistribution_agents.NAME NOT LIKE '%someserverthatthrowserrors%'

GROUP BY MSdistribution_agents.NAME, MSdistribution_agents.publication,

subscriber_id, subscriber_db

ORDER BY MSdistribution_agents.NAME, MSdistribution_agents.publication,

subscriber_id, subscriber_db

--delete from stuff we don't care about; refinement of the IF below.

delete from ##replication_command_count

where publication = 'a_busy_publication'

and UndelivCmdsInDistDB <>

delete from ##replication_command_count
where (publication = 'abusypublication' and UndelivCmdsInDistDB < 100)
--add whatever OR clauses are needed to remove your busy publications

IF (SELECT MAX(UndelivCmdsInDistDB) FROM ##replication_command_count) > 20
--20 is our threshold to send
BEGIN 

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBA',

@recipients = 'dev@null.com',

@subject = 'Replication is backed up on REPL server'

,@query = 'select left(publication,20), convert(varchar(9),UndelivCmdsInDistDB), left(name,90) from ##replication_command_count where UndelivCmdsInDistDB >20'

,@query_result_header = 0

END 

----------------------------

Script 2 -- looking for errors.

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 '

or (runstatus = 4 and comments like 'The initial snapshot%is not yet available.') GROUP BY agent_id) errors

FULL outer JOIN

(SELECT agent_id, MAX(TIME) AS last_time FROM distribution.dbo.MSdistribution_history with (nolock)

WHERE (runstatus IN (1,2,4) and comments not like 'The initial snapshot %is not yet available.')

OR comments LIKE '%were delivered.' GROUP BY agent_id

) clean

ON errors.agent_id = clean.agent_id

--grab the agent information

LEFT OUTER JOIN distribution.dbo.MSdistribution_agents agentinfo

ON agentinfo.id = errors.agent_id

--and the actual message we'd see in the monitor

LEFT OUTER JOIN distribution.dbo.MSdistribution_history error_messages

ON error_messages.agent_id = errors.agent_id AND error_messages.time = errors.last_time

where errors.last_TIME > ISNULL(clean.last_time,'20100101')

AND comments NOT LIKE '%TCP Provider%'

AND comments NOT LIKE '%Delivering replicated transactions%'

AND name NOT LIKE '%suckyservername%'

IF (SELECT COUNT(*) FROM ##replication_errors) > 0

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'dba',

@recipients = 'dev@null.com',

@subject = 'Replication errors on REPL server'

,@query = 'select * from ##replication_errors'

,@query_result_header = 0

DROP TABLE ##replication_errors

[Replication] Simple replication to find an issue

Say you get a message like this:

Cannot insert duplicate key row in object 'dbo.yourtable' with unique index 'yourtable_Index'

Run this:

--run from wherever the table exists

DECLARE @publisher_database_id INT, @article_id int

SELECT @publisher_database_id = id FROM distribution.dbo.MSpublisher_databases where publisher_db = DB_NAME()

SELECT @article_id = artid FROM dbo.sysarticles WHERE dest_table = 'yourtable'

EXEC distribution..sp_browsereplcmds @publisher_database_id = @publisher_database_id, @article_id = @article_id

[QA] Linked server for QA - faking a prod server

In case you use linked server a lot. This creates a linked server named ServerB that actually connects to the local server. I needed it for testing, as a lot of code references linked servers (no comments on _that_, please)

EXEC sp_addlinkedserver

@server = 'ServerB',

@srvproduct = '',

@provider = 'MSDASQL',

@provstr = 'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=True;'

or
(allows you to query [prodbox01].yourbigdb.dbo.prodtable, but actually get data from [qabox01].yourbigdb.dbo.prodtable)

EXEC master.dbo.sp_addlinkedserver @server = N'PRODBOX01', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'QAbox01'

 

[Tricks] Single-user-mode with a twist

Had to restart a SQL Server in single-user mode. Two problems. One, the configuration was preventing it from starting properly, and there were automatic processes connecting to that single user.

The normal way to do it is:
sqlservr -m
(that's single user mode)
but for this we needed the configurations to not screw us up.
sqlservr -f
and we needed to make sure that it was only us connecting.
sqlservr -f"sqlcmd"
(Yes, you could also do sql server management studio)

[Code] Xtype definitions

'C' =  CHECK constraint 
'D' =  Default or DEFAULT constraint  
'F' =  FOREIGN KEY constraint  
'L' =  Log  
'FN' =  Scalar function  
'IF' =  In-lined table-function  
'P' =  Stored procedure  
'PK' =  PRIMARY KEY constraint (type is K)  
'RF' =  Replication filter stored procedure 
'S' =  System table  
'TF' =  Table function  
'TR' =  Trigger  
'U' =  User table  
'UQ' =  UNIQUE constraint (type is K)  
'V' =  View  
'X' =  Extended stored procedure 

[Ugly] Drop a view using Dynamic SQL

There's a time and a place for Dynamic SQL. This is, as far as I know, one of them.

I need to ensure a view (standard_view) is dropped in a particular database (My2008DB). That database is named for the year, so it's different each year. I can't use sp_msforeachdb for a particular (REDACTED) reason. You probably don't have that problem, I do.

So, best I can tell, the way to do it is to call sp_executesql from within the context of that other database. And since I have to execute the code to verify the drop before doing it, we wind up with this particularly ugly piece of code. Yes, we're 3 layers deep. sp_executesql calls My2008DB.dbo.sp_executesql, which calls the actual drop code. And yes, it works, at least on SQL Server 2005.

DECLARE @date SMALLDATETIME, @drop_view VARCHAR(8000) ,@SQLString nvarchar(500), @ParmDefinition nvarchar(500)
select @date = '5/15/2008'

--DROP VIEW.  Need to look in the DB, determined dynamically via the @date
SELECT @drop_view = 'if object_id(''My' + CONVERT(CHAR(4),@date, 112) + 'DB..standard_view'') is not null
    drop view standard_view'

--now we create a "wrapper" to run in the correct database context.
SELECT @sqlstring = N'execute My' + CONVERT(CHAR(4),@date,112) + 'DB.dbo.sp_executesql @mysql', @ParmDefinition = N'@mysql nvarchar(max)'

--execute in our dynamic db
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@mysql = @drop_view

[Tips] HOW TO find page splits by reading the transaction log

http://strictlysql.blogspot.com/2009/10/identifying-page-splits.html

There are two tricks in here. The first is using ::fn_dblog (undocumented function) to read the transaction log for a database.
The second is pulling out the delete messages involved in a page split and using that to determine where the splits are occurring.

select AllocUnitName, count([AllocUnitName]) [Splits]
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
    and parsename(AllocUnitName,3) <> 'sys'
group by AllocUnitName

[Tuning] Getting only the DATE from a datetime

Many ways to handle dates, and most time I don't think about it - then came across a gigantic thread over two years that discussed it.


Comparing dates. Traditionally, for simplicity, to mark particular dates, I've used convert(char(8),a_date_field,12), but it’s non-SARGable, and not terribly efficient.


Looking online I found a couple alternatives, though they’re mostly useful when having to deal with large tables.

http://blog.sqlauthority.com/2008/10/18/sql-server-retrieve-select-only-date-part-from-datetime-best-practice-part-2/

For SQL Server 2008 it's pretty simple:

SELECT  cast(GETDATE() as date)
or
SELECT CONVERT(date,GETDATE())

(date is a new datatype that only stores the date, and is 3 bytes in size)

For SQL Server 2005, there seem to be two options. Naturally, neither is SARGable from what I’ve seen.

SELECT CONVERT (datetime, FLOOR(CONVERT (float, a_field_name)))
and
SELECT DATEADD ( DAY, DATEDIFF(DAY, 0, a_field_name), 0)

The first one performs math on the internally-stored FLOAT value of the datetime - right now, for instance, is 40315.37003125, where the whole number is the day and the fraction is the time. (Note that you can’t cheat and use INT; it can round up during the conversion).

The second one does date manipulation - there's a whole article online about using datediff and dateadd and the like to get the first day of the month, the first day of last month, etc, etc.

From what I’ve seen online, the FLOOR option can be slightly faster than the DATEADD, but either is considerably faster than CONVERT(char(8)).

[Tuning] Disk usage, redux

Here's a slightly different version of an earlier query, that tells you how much IO each database is getting.

This one is by database, not by file - did this since it makes dealing with TEMPDB easier. So you can't use it for physical file IO - that's the other version. And it's in GB, since that's our issue.

SELECT

sysdb.name AS name,

master_files.type_desc,

vfs.sample_ms,

SUM(vfs.num_of_bytes_written)/1024/1024/1024 AS GB_written,

SUM(num_of_bytes_read)/1024/1024/1024 AS GB_read,

SUM((num_of_bytes_read + num_of_bytes_written))/1024/1024/1024 AS GB_total_IO,

SUM(size_on_disk_bytes)/1024/1024/1024 AS Size_in_GB

FROM sys.dm_io_virtual_file_stats(null, null) vfs

INNER JOIN MASTER.sys.master_files

ON vfs.[file_id] = master_files.[file_id]

AND vfs.database_id = master_files.database_id

INNER JOIN master..sysdatabases sysdb ON vfs.database_id = sysdb.dbid

GROUP BY

sysdb.name, master_files.type_desc,

-- master_files.physical_name,

vfs.sample_ms

ORDER BY SUM((num_of_bytes_read + num_of_bytes_written))/1024/1024/1024 desc

[Tuning] Filtered indexes...and filtered statistics.

So, SQL Server (starting with 2008) now has Filtered Indexes. Which are great - since an index is basically just a copy of the data, why copy particular fields for the entire table when you only query particular subsets of data? Add a WHERE clause to your index, and now you're querying subsets of data.


However, while reading up on it I came across this:
http://msdn.microsoft.com/en-us/library/ms190397.aspx
which introduces filtered statistics(!).

Instead of

CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)

which would give you stats for the entire range, do this:

CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3)

which will only compute statistics for that subset of data.

[Files] Tricking Excel with SP_SEND_DBMAIL

My pain = your gain. I needed to automate a process to send a file to an end user. They would just double-click it and have it open in Excel. The problem was due to a field with a leading zero, which Excel will simply lop off. One way around is to create an XML file. I've done that before, but this is simpler and ideal for this situation. The syntax and parameters are important.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'youremailprofile', 
    @recipients = 'dev@null.com', 
    @subject = 'Here is your file pull done',
    @query = 'SELECT a, b, convert(varchar(12),c) as C, char(61) + char(34) + leadingzerossuck + char(34) as leadingzerossuck FROM mytemptable', 
    @attach_query_result_as_file = 1, 
    @query_attachment_filename = 'yourfilename.csv', 
    @query_result_separator = ' ', -- tab
    @exclude_query_output = 1, 
    @append_query_error = 1

Table Partitioning

Working on learning partitioned tables. We've done it, and it works really well for us, but it's time to start getting clever.

Was reading this, which is an excellent primer:
http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/

A few rules I learned the hard way. I deliberately did it from scratch in hopes that I'd remember it better - what to do, what not to do. In particular, working on switching tables into partitions (our current stuff switches out, not in)

* clustered key of the table you're switching in has to be on the same filegroup as the partitioned table.
* indexes have to match. ALL of them.
* indexes are NOT on the partition scheme
* only one constraint (below)
* Add a constraint to make sure the partitioned key is within the partition range (order_date >='20100428' and order_date <'20100429' and order_date is not null)
* Then, SPLIT the range (code below), and SWITCH the table in.

    ALTER PARTITION SCHEME ps_daily NEXT USED your_partitioned_fg
    
    ALTER PARTITION FUNCTION pf_daily() SPLIT RANGE ( @Day) --@day is one day larger than current max date

    ALTER TABLE orders_stg SWITCH TO dbo.orders PARTITION 200

Now, what if you need to add an old day in? You have up to 5/4/2010, but need 5/3?

ALTER PARTITION SCHEME [ps_daily] NEXT USED your_partitioned_fg
ALTER PARTITION FUNCTION [pf_daily] () SPLIT RANGE (N'2010-05-03')

[Offtopic] How to print to a networked Laserwriter (XP)

Needed this desperately, so posting it here. Worked like a charm for me. Thanks to this Anthony Duplessis chap, whomever he is. Thanks!

Note that once you get to the test page print the first time, you're NOT DONE, so uncheck it. Follow the directions, step by step.

(found some screenshots at: http://blog.eppesconsulting.com/2009/05/05/AppleLaserWriter16600PSOnWindowsVista.aspx)

Printing to an Apple Laserwriter 16/600 using IP from a Win 2K and Win XP
client.

The following procedure explains how to configure desktop clients,
running Windows 2000 or Windows XP, without installing the appletalk
protocol.

• Select Start/Settings/Printers.
  
• Double-click 'Add Printer' icon. Click the 'Next' button.
  
• Select 'Local printer' bullet.
  
• Uncheck 'Automatically detect and install my Plug and Play printer' box.
  
• Click the 'Next' button.
  
• Select 'Create a new port' bullet.
  
• Select 'Standard TCP/IP Port' from the drop-down arrow list.
  
• Click the 'Next' button. Click the 'Next' button again.
  
• Enter the appropriate IP address of the printer. Click the 'Next' button.
  
• Select 'Apple Network Printer' from the drop-down arrow list in the 'device
  type' field.
  
• Click the 'Next' button. Click the 'Finish' button.
  
• Select the appropriate printer driver. Click the 'Next' button.
  
• Accept or modifiy the suggested printer name. Click the 'Next' button.
  
• Select the 'Yes' bullet if you want to make this printer the default, or
  
• Select the 'No' bullet if you do not want to make this printer the default.
• Click the 'Next' button.
  
• Select the 'Do not share this printer' bullet. Click the 'Next' button.
  
• Select the 'No' bullet so that a test page is not printed.
  
• Click the 'Finish' button.
  
• Open the Printers folder.
  
• Select and Right-click the apple printer. Select 'properties' from the
  context menu.
  
• Select the ‘Ports’ tab and then the ‘Configure Ports’ button.
  
• Verify that the 'LPR' bullet is selected in the Protocol section.
  
• Enter ‘lp’ in the 'Queue Name' field. [Note: that's LP, lowercase, not IP]
  
• Click to select the 'LPR Byte Count Enabled' box.
  
• Click the 'OK' button.
  
• Select the ‘General’ tab and then Click the 'Print Test Page' button.
  

Anthony Duplessis.
LIFE is the toughest teacher of all.
First, you get the test.
Then, you're taught the lesson.

[Powershell] What a piece of $#!+

Wow. So, Powershell is Microsoft's idea of UNIX: a command-line shell. Unfortunately, more than a bit half-assed, even with version 2.0.

I grabbed a module to allow UI scripting. Here's what I have to do in order to run a simple Hello World with it:

1. download Powershell
   
2. download PowerShellPack
   
3. install both
   
4. start Powershell
   
5. Set-ExecutionPolicy Unrestricted (so I can run scripts)
   
6. Import-Module PowerShellPack (needs to be done each time)
   
7. Hit ctrl+c because it's going to ask me once per script, and there are about 50. And, each time I run powershell.
   
8. Set-ExecutionPolicy Bypass (hello, usefulness, goodbye protection. Grrrr)
   
9. Import-Module PowerShellPack
   
10. Import-Module WPK
    
11. New-Label "Hello, World" -Show
    
12. get odd error: Exception calling ".ctor" with "0" argument(s): "The calling thread must be STA, because many UI components require this."
    
13. read blog
    
14. change shortcut to Powershell to add -STA
    
15. restart Powershell
    
16. Import-Module PowerShellPack
    
17. Import-Module WPK
    
18. New-Label "Hello, World" -Show
    

Success!

WTF. Am I a bad person if I wonder why the hell I have to jump through all these hoops?

[Partitioned Tables] Does the clustered index take up space if referenced?

Setting up a new partitioned table with associated indexes. I was curious whether the adding the partitioning key to any index would cause the index to grow - I expected not, but you never know.

Our clustered index:

create unique clustered index clustind_pk on ourtable (id, partitionedkey)

Our test indexes:

create nonclustered index A on ourtable (partitionedkey, fielda)
on ps_daily (partitionedkey)

create nonclustered index B on ourtable (fielda)
on ps_daily (partitionedkey)

create nonclustered index C on ourtable (fielda) include (partitionedkey)
on ps_daily (partitionedkey)

Then used this query to check a particular partition for all 3 indexes (thanks to Simon Sabin). Each was within 1 page of the others.

select OBJECT_NAME(p.object_id ), i.name,p.*
from sys.dm_db_partition_stats p
join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
WHERE p.object_id = 2071234567
AND partition_number = 28 

[Install] Restarting your SQL service within SQL

DOES NOT WORK. Not deleting the post below in case someone comes across this. We wound up using powershell instead. WMI-process.



We're building a Powershell script to automatically build out our machines. Not just the install, but the full config: backup jobs, maintenance, tempdb files, model, etc, etc.

So, we need to bounce the service so that all the tempdb files get created, the agent knows the database config, etc.

The trick isn't stopping the server: "net stop mssqlserver" will do that. The real trick is starting it back up, once you've shut down the SQL server. Since our solution is all done via SQLCMD we needed a way, within SQL itself, to start back up.

Our secret is the ampersand; when the command line interpreter catches it, it views it as you hitting the "Enter" key. So, even though the SQL service is off, the job continues.

declare @sql varchar(8000)
select @sql = 'net stop SQLSERVERAGENT & ping -n 15 127.0.0.1 & '
+ 'net stop MSSQLSERVER & ping -n 15 127.0.0.1 & '
+ 'net start MSSQLSERVER & ping -n 15 127.0.0.1 & '
+ 'net start SQLSERVERAGENT'
EXEC xp_cmdshell (@sql)

[Replication] Finding the commands that are breaking

Here's a simple one I had to do today. Documenting since I hate having to dig through the help files.

On your distributor:

SELECT * FROM distribution.dbo.MSpublisher_databases

That gives you a list of the source databases from the publishers. You want the ID field.

Now,

use distribution
go
sp_browsereplcmds @publisher_database_id = 13

will list all the waiting commands. The command field is the actual command, while the article_id can be gotten from running the following on the publisher:

sp_helparticle @publication = 'your_publication_name'

[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.

[Tuning] SPARSE varchar calculation

Since it doesn't appear that anybody has done this before, here you go. I've been comparing SPARSE to COMPRESSION, and for my particular tables, I got 25% space savings via parse. However, I got 40% savings from ROW compression, and 50% savings from PAGE.

Standard
=(Number_Of_Rows*(Average_Varchar_Length+2)
*((100-Percent_Null)/100))
+(Number_Of_Rows*(Percent_Null/100*2))

Sparse:
=(Number_Of_Rows*(Average_Varchar_Length+4))*((100-Percent_Null)/100)

Next up is comparing the CPU for each option. Nobody's really talked about whether the compression is symmetric or asymmetric, though I'd hope it's asymmetric (aka easier to decompress than compress, in this case)

[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.