The Rules: 0-4

Here are my current DBA rules. Yours will differ, but these hold me in good stead.

Rule 0: Verify your backups. Obviously I assume you are making backups - you _are_, right?
Rule 1: UPDATE STATISTICS. That will frequently save your hide.
Rule 2: Data/Log always on separate drives. Know what happens if your data and log are on the same drive, and it fills up? No? You don't want to.
Rule 3: Complexity is the enemy. Not quite KISS, but definitely related.
Rule 4: SQL Sentry's "Disable Until" is a godsend. Make sure to use that instead of simply disabling the job. And double-check the time it restarts, since SQL Sentry adds 1 day by default - not useful if you're trying to disable something for 2-3 hours.

Varchar(n) vs Varchar(Max)

Two questions:
1) You have a 1tb table with 1m rows, and need to change a field from varchar(5) to varchar(10). How long does it take, and how much log space is used?
2) You have a 1tb table with 1m rows, and need to change a field from varchar(5) to varchar(MAX). How long does it take, and how much log space is used?


The answer for (1) is simple: instantly, and none. Fortunately, it's a metadata operation, and since it knows everything already in the database must be the right size, no work needs be done.
For (2), I can't tell you - we rolled back after 90 minutes (it then took another 3+ hours to roll back), and it had consumed 200+gb of log space. On the plus side, it's table-partitioned, so we'll upgrade it that way.

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)