[Russian] Olga's Cabbage Rolls (Holubtsi/Helutsi)

(apparently my original posting showed up with the original date I started - reposting so that it's at the top)

Let's see, we've done my Baba's borscht (which, honestly, you should make - calling it "beet soup" does it a disservice, especially when the beet slivers are white when done), but we still have several left to do:

• Piroshki (meat pies, baked)
  
• Piroshki (potato pies, fried)
  
• Vareniki
  
• Haluski (helushki?)
  
• Pork Stew (Goulash)
  

...and Cabbage Rolls (aka "pigs in a blanket", aka "heh-lute-see", which according to Wikipedia is probably spelled Holubtsi but I'm not positive of.)

Basically it's just beef, rice & onions wrapped in a cabbage leaf, fried in a tiny bit of oil, then baked with a sour cream & tomato sauce. So, it does count as baking! *laugh* I spent the better half of a day working with Baba on this, taking her recipe and adding footnotes.

And yes, it tastes much better than that description. Think spiced meat served with a pink sauce, and you're not far off.

Ingredients for the Cabbage Rolls themselves:
3 lb chuck meat (lean), OR 2 lb chuck meat (lean) and 1 pound boneless country ribs
1 1/2 cup rice, cooked as it directs
3 medium onions, diced and chopped
6+ tbl corn oil
5 tbl dried parsley
salt & pepper to taste
2-3 heads cabbage

Ingredients for the sauce:
15 oz can tomato sauce
8 oz can tomato sauce
10.5 oz beef buillion (we use the Campbell's double strength, but you can also use regular beef broth)
8 oz sour cream

Equipment needed:
Grinder (coarse blade)

Prepping the filling:
Take your meat and boil it in water, at least enough to cover. Save 1/2 cup broth. You can do either all beef, or beef and pork - she normally does beef & pork, but not everybody likes pork.
Take the cooked meat and grind on the coarse setting. Yes, you need to cook it then grind it - the texture is totally different if you do it the other way around.
Prepare the 1.5 cups rice as per package directions.
Take the 3 onions, dice and simmer over medium heat with 2-3 tbl oil until clarified.
Now, mix all together - the ground-up & cooked meat, rice, 1/2 cup broth, dried parsley, onions, and salt & pepper to taste. You definitely want this to taste good on its own, so don't be stingy with the pepper.

Prepping the rolls:
Take head of cabbage, score the base so that the leaves will be able to come off. Put in a pot bigger than needed to boil the cabbage. Add water, then heat to boiling. Turn off heat after it boils 30 seconds - we are trying to wilt the leaves and make them pliable. Don't use the whole cabbage - the inner leaves are just too small to hold enough meat.

Making the rolls:
Pull off the outer-most leaves and throw away.
Remove one leaf at a time. Cut away as much of the "vein" as possible on the leaves, so that they can be easily folded. Put 1-2 heaping serving spoon (1.25-2 oz total) of meat in the middle, then fold the sides in, overlapping, then fold over the "top" of the leaf, then fold over the base of the leaf. You now should have a small package. Cook this in 2-3 tbl oil on both sides until GBD (golden-brown and delicious), then set aside. At this point you can freeze them.

Making the sauce:
In a pot, add the tomato sauce, sour cream, beef bullion, 1-2 tsp black pepper and a SMALL pinch of salt (the beef bullion is a salt lick; if you go the low sodium route, you'll wind up having to add salt at the end. Sorry, this isn't healthy.) Heat on medium high heat, stirring frequently, until it comes to a boil. Boil for 30 seconds then take off heat.

Putting it all together:
Take a casserole dish (the deeper the better - we use an old corningware dish about 8 inches deep), and layer the rolls inside, open-side up. You want room between them for the sauce - don't just cram them together. I say open-side up because it's more forgiving when you're pulling them out, but be careful when pouring the sauce lest they open. Pour the sauce over until they're almost completely covered. Bake in a 350-degree oven for 30 minutes - you want it to be bubbly. Use a cooking spoon to pull out 2-3 rolls per person, pouring over sauce as desired. You can either cut it up and eat it, or (for those that don't like cabbage) remove the leaves and pour the sauce over the meat. Enjoy!

[Indexes] Size of unused indexes

Deprecated: use http://thebakingdba.blogspot.com/2011/09/index-size-usage-and-location-of-your.html
which includes the filegroup and location.


Was reading an old post of mine, and wanted to revisit it and add the ability to check the sizes of unused indexes. Pretty easy.

Updated this from a subquery to an inner join, which gives you the usage stats as well.

select

-- i.[object_id],

-- i.index_id,

 o.name as Table_Name,

 i.NAME AS Index_Name,

 i.type_desc,

-- p.partition_number,

 p.rows as [#Records],

 a.total_pages * 8 as [Reserved(kb)],

 a.used_pages * 8 as [Used(kb)],

 s.user_seeks,

 s.user_scans,

 s.user_lookups

from 

 sys.indexes as i

inner join

 sys.partitions as p

on i.object_id = p.object_id

 and i.index_id = p.index_id

inner join SYS.OBJECTS O

 ON I.OBJECT_ID = O.OBJECT_ID

INNER JOIN sys.allocation_units AS a

on (a.type = 2 AND p.partition_id = a.container_id)

OR ((a.type = 1 OR a.type = 3) AND p.hobt_id = a.container_id)

INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S

  ON S.OBJECT_ID = I.OBJECT_ID

  AND I.INDEX_ID = S.INDEX_ID

  AND DATABASE_ID = DB_ID(DB_NAME())

and o.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE')           -- No system tables!

AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100

WHERE    

 OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

 AND i.TYPE_DESC <> 'HEAP'

 and i.type <> 1 -- clustered index

order by

o.NAME, i.name

GO

[Unix] File management

Yes, I know - it's neither Baking nor DBA. But still relevant.

I had a set of files named .Z.aa that I needed to decompress. GUNZIP doesn't like those extensions, so it ignores it. Here's a basic hack using Cygwin. (You are using Cygwin, right?)

for i in *.Z.aa;
do
    j=`basename $i .aa`;
   echo mv $i $j;
    mv $i $j;
   echo gunzip $j;
    gunzip $j;
done

[Sweets] Raspberry Horseshoes

Made these this weekend, trying to emulate something at the Swiss Pastry Shop that I can't get elsewhere. (and when I do make it over there, they're usually sold out). Inspiration struck when I noticed they were listed on the menu as "puff pastry", and I realized I had a package of Puff Pastry (found in your grocer's freezer) in my freezer.

Consider this my knowledge gleaned from a first attempt.
Ingredients
1 Egg
1 Sheet of Puff Pastry
6 tbl Raspberry Preserves
Sugar as needed

Let Puff Pastry thaw, roughly 30-40 minutes.

Puff Pastry comes in a tri-fold sheet. For our purpose, cut each of these 1/3rds in half, lengthwise; you should now have 6 long strips of pastry.

Sprinkle approx 1tbl of sugar on each, evenly distributed. You may need to brush with a little water first, to get the sugar to stick.

Run a thin line of Raspberry preserves lengthwise down the middle. The "1 tbl" is a suggestion, as I'm still trying to get it right. You need to be able to seal them.

Fold in half, lengthwise, then crimp.

Apply egg wash (1 egg mixed with 1 tbl water), and sprinkle on sugar (this is the outside, which hasn't been covered yet) onto both sides.

Place on a pan. I put it onto aluminum, which should've been sprayed with non-stick beforehand.

Bake at 400 for 15 minutes.

[DBA] What's killing my server _right now_?

I'm trying to find/create some code that will tell me at a glance what's pummelling my servers. You know, the ones where you get phone calls saying "what the F is going on?!". This will tell you what current connections are using the most resources. The obvious improvement would be to do a WAITFOR to wait 5 seconds, then compare before/after.

Here's my first stab at it.

(remember to select the beginning to end - there's code hidden off to the sides of the blog columns - grrr.

SELECT
 s.login_name, 
    [spid] = r.session_id,
    [database] = DB_NAME(r.database_id),
    r.start_time,
    r.[status],
    r.command,
 r.wait_type, r.wait_time, r.wait_resource, r.cpu_time, r.reads, 
 r.writes, r.logical_reads, s.HOST_NAME, s.program_name, 
 s.client_interface_name, s.nt_user_name, s.original_login_name,
 r.USER_ID, c.client_net_address, c.client_tcp_port,
    /* add other interesting columns here */
    [obj] = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid]))
    + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])),
    t.[text]
FROM
    sys.dm_exec_requests AS r
LEFT OUTER JOIN sys.dm_exec_connections c 
  ON c.session_id = r.session_id
INNER JOIN sys.dm_exec_sessions s 
  ON r.session_id = s.session_id 
  AND r.connection_id = c.connection_id
CROSS APPLY
    sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE
    r.session_id <> @@SPID
    AND r.session_id > 50 --system SPIDs are below 50
--    AND s.[host_name] NOT IN ('baking')
--    AND login_name not in ('the_baking_dba')
--    AND DB_NAME(r.database_id) <> 'pies'
ORDER BY logical_reads DESC, reads DESC --or you could use cpu_time

[Maintenance] Using Tokens for descriptive job failures

This was the labor of love of one of my former coworkers. I've meant to send this into SQL Server Mag for a while, but never got around to a full writeup. Hope you can use this.

What it does: in jobs, there are what are called Tokens. If you have Token Substitution turned on in the Agent settings, then this SP can be called as a job step, and it'll email the full results of the job failure step. In your job you'd create a new step (set to return failure on success/failure, since it's just reporting details of the failure), that would only be called on failure, with the "sample syntax" enclosed below. If all is set up correctly (it requires, among other things, xp_smtp_mail - but who _doesn't_ have that in a 32-bit environment?) you'll get a large email with the results of the failure.

That beats the heck out of having to dig through a job failure message that usually reads "....the step failed"

/******************************************************************
Author: Mark A. Hill
Object: usp_SQLAgentTokenMail
Description: This stored procedure uses SQL Agent tokens which can
    only be used inside a scheduled job, it will not work in
    Query Analyzer. An operator must be set up to send emails,
    pages, net sends. Use the Notifications tab of the job to
    enable alerts to be written to the Windows event log.

    When setting up the job use the "Sample Syntax" below as
    the last step of the job, then set all of the other steps
    of the job to use "GO TO [step name]" (which contains the
    the usp_SQLAgentTokenMail), the stored procedure will
    determine if the job failed or succeded and if an email
    should be sent or not. Note: The job log information may
    appear different then what you are used to when viewing
    job history, the stored procedure is doing the logging
    instead of the SQL Agent (But it is probably more info
    then SQL Agent normally provides!).

    xp_smtp_sendmail also needs to be installed, it can be found
    at http://SQLDev.Net You may also want to change the [from],
    [from_name] and [server] of the xp_smtp_sendmail code below
    to match your companies specific needs.

Sample Syntax:
exec msdb.dbo.usp_SQLAgentTokenMail
    @job_id = [JOBID]
    , @Date = [STRTDT]
    , @Time = [STRTTM]
    , @Instance = [INST]
    , @stepct = [STEPCT]
    , @stepid = [STEPID]

Legal Stuff:
    You May use this code in whole or in part as public domain
    as long as you don't remove these comments.

System Requirements: This has been tested on SQL Server 2000 SP3a
    I would imagine that it would work on SQL 7.0 as well but
    I have no reason to test it on there. Oh and you will need
    a mail server running SMTP, and don't forget to install
    xp_smtp_sendmail and verify that it is working properly.

*******************************************************************/

alter proc [dbo].[usp_SQLAgentTokenMail]
@job_id uniqueidentifier
, @Date int = 0
, @Time varchar(8) = 0
, @Instance varchar(128) = ''
, @stepct varchar(10) = ''
, @stepid varchar(10) = ''
as
set nocount on
Declare @varmessage nvarchar(4000)
    , @job_name nvarchar(255)
    , @varsubject nvarchar(255)
    , @datetime varchar(20)
    , @vardate varchar(10)
    , @vardatetime varchar(20)
    , @runduration varchar(10)
    , @command varchar(1000)
    , @message varchar(1000)
    , @step_name varchar(128)
    , @subsystem varchar(128)
    , @run_status int
    , @database_name varchar(128)
    , @new_stepid varchar(10)
    , @status varchar(30)
    , @send_email int
    , @send_page int
    , @send_netsend int
    , @send_eventlog int

    , @notify_netsend_operator_id int
    , @notify_page_operator_id int
    , @notify_email_operator_id int
    , @notify_email_operator_name varchar(128)
    , @notify_netsend_operator_name varchar(128)
    , @notify_page_operator_name varchar(128)

    , @notify_level_eventlog int
    , @notify_level_email int
    , @notify_level_netsend int
    , @notify_level_page int

    , @weekday_pager_start_time  int
    , @weekday_pager_end_time  int
    , @saturday_pager_start_time  int
    , @saturday_pager_end_time  int
    , @sunday_pager_start_time  int
    , @sunday_pager_end_time  int

    , @dw int
    , @operator_time int
    , @page_time_valid int
    , @email_address_string varchar(255)
    , @netsend_string nvarchar(4000)
    , @pager_days int
    , @page_day_valid int
    , @page_dw INT
   
    , @servername sysname
   
SET @send_email = 0
SET @send_page = 0
SET @send_netsend = 0
SET @send_eventlog = 0
SET @pager_days = 0
set @page_day_valid = 0

select @job_name = name from msdb.dbo.sysjobs with (nolock) where job_id = @job_id
SELECT @servername = @@SERVERNAME
/******************************************************
    Selecting the Job History
******************************************************/
select top 1
@new_stepid = sjh.step_id
, @step_name = isnull(sjs.step_name,'')
, @subsystem = isnull(sjs.subsystem,'')
, @database_name = isnull(sjs.database_name,'')
, @command = isnull(sjs.command,'')
, @message = isnull(sjh.message,'')
, @run_status = isnull(sjh.run_status,'')
from msdb.dbo.sysjobs sj  with (nolock)
    inner join msdb.dbo.sysjobsteps sjs  with (nolock) on sj.job_id = sjs.job_id
    inner join msdb.dbo.sysjobhistory sjh with (nolock) on sjs.job_id = sjh.job_id
    where sj.job_id = @job_id
order by sjh.instance_id desc


/******************************************************
    Checking if date passed is valid
******************************************************/
IF isdate(@date) = 1
    BEGIN
    set @vardate = convert(varchar(10),convert(datetime,convert(varchar(8),@date)),101)
    END

IF len(convert(varchar(10),@time)) = 6
    BEGIN
    SET @time = substring(@time,1,2) + ':' + substring(@time,3,2) + ':' + substring(@time,5,2)
    SET @datetime = (@vardate + ' ' + @time)
    END

IF isdate(@datetime) = 1
    BEGIN
    SET @vardatetime = @datetime
    END
ELSE
    BEGIN
    SET @vardatetime = @vardate
    END
select @runduration = convert(varchar(10),datediff(ms,@datetime,getdate()))


/******************************************************
    Building the subject and message
******************************************************/

if @run_status = 0
    BEGIN
    SET @varsubject = ('SQL Server Job System: ' + @job_name + ' Failed.')
    SET @status = 'Failed'
    END
ELSE
    BEGIN
    SET @varsubject = ('SQL Server Job System: ' + @job_name + ' Completed.')
    SET @status = 'Completed'
    END

SET @varmessage = ('
Job Name: ' + @job_name + '
Status: ' + @status + '
Server: ' + @@servername + '
Database: ' + @database_name + '
Instance: ' + @instance + '
Date Time: ' + @vardatetime + '
Run Duration: ' + @runduration + ' (Milliseconds)
Step: ' + @step_name + '
Command Type: ' + @subsystem + '
Command Executed: ' + @command + '
Error Message: ' + @message
)


/******************************************************
    Selecting the operator to notify
******************************************************/

select @notify_email_operator_id = notify_email_operator_id
    , @notify_netsend_operator_id = notify_netsend_operator_id
    , @notify_page_operator_id = notify_page_operator_id
    , @notify_level_eventlog = notify_level_eventlog
    , @notify_level_email = notify_level_email
    , @notify_level_netsend = notify_level_netsend
    , @notify_level_page = notify_level_page
    from msdb.dbo.sysjobs with (nolock)
    where job_id = @job_id

IF @notify_email_operator_id = null PRINT 'No Operator exists, create an operator using Enterprise Manager!'   

select @notify_page_operator_name = pager_address
    , @weekday_pager_start_time = weekday_pager_start_time
    , @weekday_pager_end_time = weekday_pager_end_time
    , @saturday_pager_start_time = saturday_pager_start_time
    , @saturday_pager_end_time = saturday_pager_end_time
    , @sunday_pager_start_time = sunday_pager_start_time
    , @sunday_pager_end_time = sunday_pager_end_time
    , @pager_days = pager_days
    from msdb.dbo.sysoperators with (nolock)
    where id = @notify_page_operator_id
    and enabled = 1

select @notify_email_operator_name = email_address
    from msdb.dbo.sysoperators with (nolock)
    where id = @notify_email_operator_id
    and enabled = 1

select @notify_netsend_operator_name = netsend_address
    from msdb.dbo.sysoperators with (nolock)
    where id = @notify_netsend_operator_id
    and enabled = 1

/******************************************************
    Checking which alerts actions to perform.
******************************************************/

/*
notify_level_email
0 = Never
1 = When the job succeeds
2 = When the job fails
3 = Whenever the job completes (regardless of the job outcome)

Run_status
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
*/

IF @notify_level_email = 0
    BEGIN
    SET @send_email = 0
    END
ELSE
    BEGIN
    IF @notify_level_email = 3
        BEGIN
        SET @send_email = 1
        END
    ELSE
        BEGIN
        IF ((@run_status = 0) and (@notify_level_email = 2))
            BEGIN
            SET @send_email = 1
            END
        ELSE
            BEGIN
            IF ((@run_status = 1) and (@notify_level_email = 1))   
                BEGIN
                SET @send_email = 1
                END
            ELSE
                BEGIN
                SET @send_email = 0
                END
            END
        END
    END

IF @notify_level_page = 0
    BEGIN
    SET @send_page = 0
    END
ELSE
    BEGIN
    IF @notify_level_page = 3
        BEGIN
        SET @send_page = 1
        END
    ELSE
        BEGIN
        IF ((@run_status = 0) and (@notify_level_page = 2))
            BEGIN
            SET @send_page = 1
            END
        ELSE
            BEGIN
            IF ((@run_status = 1) and (@notify_level_page = 1))   
                BEGIN
                SET @send_page = 1
                END
            ELSE
                BEGIN
                SET @send_page = 0
                END
            END
        END
    END

IF @notify_level_netsend = 0
    BEGIN
    SET @send_netsend = 0
    END
ELSE
    BEGIN
    IF @notify_level_netsend = 3
        BEGIN
        SET @send_netsend = 1
        END
    ELSE
        BEGIN
        IF ((@run_status = 0) and (@notify_level_netsend = 2))
            BEGIN
            SET @send_netsend = 1
            END
        ELSE
            BEGIN
            IF ((@run_status = 1) and (@notify_level_netsend = 1))   
                BEGIN
                SET @send_netsend = 1
                END
            ELSE
                BEGIN
                SET @send_netsend = 0
                END
            END
        END
    END
       
IF @notify_level_eventlog = 0
    BEGIN
    SET @send_eventlog = 0
    END
ELSE
    BEGIN
    IF @notify_level_eventlog = 3
        BEGIN
        SET @send_eventlog = 1
        END
    ELSE
        BEGIN
        IF ((@run_status = 0) and (@notify_level_eventlog = 2))
            BEGIN
            SET @send_eventlog = 1
            END
        ELSE
            BEGIN
            IF ((@run_status = 1) and (@notify_level_eventlog = 1))   
                BEGIN
                SET @send_eventlog = 1
                END
            ELSE
                BEGIN
                SET @send_eventlog = 0
                END
            END
        END
    END


/******************************************************
    Checking which Days pages should be sent
******************************************************/

set @dw = datepart(dw,getdate())

if @pager_days & 1  = 1 set @page_dw = 1
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 2  = 2 set @page_dw = 2
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 4  = 4 set @page_dw = 3
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 8  = 8 set @page_dw = 4
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 16 = 16 set @page_dw = 5
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 32 = 32 set @page_dw = 6
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 64 = 64 set @page_dw = 7
IF @page_dw = @dw set @page_day_valid = 1


/******************************************************
      Checking if operator is on pager duty.
******************************************************/

set @operator_time = (select convert(int,replace(convert(varchar(10),getdate(),108),':','')))

set @page_time_valid = 0

IF @dw between 2 and 6 -- Weekdays
    BEGIN
    IF @operator_time BETWEEN @weekday_pager_start_time and @weekday_pager_end_time
        BEGIN
        set @page_time_valid = 1
        END
    end

IF @dw = 1 -- Sunday
    BEGIN
    IF @operator_time between @sunday_pager_start_time and @sunday_pager_end_time
        BEGIN
        set @page_time_valid = 1
        END
    end

IF @dw = 7 -- Saturday
    BEGIN
    IF @operator_time between @saturday_pager_start_time and @saturday_pager_end_time
        BEGIN
        set @page_time_valid = 1
        END
    end

/******************************************************
    Checking which email addresses to use.
******************************************************/
   
IF ((@page_time_valid = 1) and (@page_day_valid = 1) and (@send_page = 1))
    BEGIN
    IF @send_email = 1
        BEGIN
        SET @email_address_string = (@notify_page_operator_name + ', ' + @notify_email_operator_name)
        END
    ELSE
        BEGIN
        SET @email_address_string = @notify_page_operator_name
        END
    END
ELSE
    BEGIN
    IF @send_email = 1
        BEGIN
        SET @email_address_string = @notify_email_operator_name
        END
    END


/******************************************************
    Sending the email to the operator.
******************************************************/

IF ((@send_email = 1) or (@send_page = 1))
    BEGIN
    exec master.dbo.xp_smtp_sendmail                    
    @from =     N'sqlservice@yourcompanyname.com'
    , @FROM_NAME  = @servername
    , @to =     @email_address_string
    , @subject =     @varsubject
    , @message =     @varmessage
    , @server =    N'mail'
    END

/******************************************************
    Sending the netsend to the operator
******************************************************/

IF @send_netsend = 1
    BEGIN
    SET @netsend_string = ('net send ' + char(39) + @notify_netsend_operator_name + '
    ' + replace(@varsubject,char(39),'') + '
    ' + replace(@varmessage,char(39),'') + char(39))

    exec master.dbo.xp_cmdshell @netsend_string
    END

/******************************************************
    Passing error messages to Windows eventlog
******************************************************/
IF @send_eventlog = 1
    BEGIN
    RAISERROR (@varmessage, 16, 1) with log
    END

/******************************************************
    Passing error messages to SQL Agent
    So the job will fail and show an red "x"
******************************************************/
IF @run_status != 0
    BEGIN
    RAISERROR (@varmessage, 16, 1)
    END

/******************************************************
    Passing error messages to job output log
******************************************************/
select @varsubject
select @varmessage
Select ('email sent to: ' + @email_address_string)

/*
SELECT @send_email as 'Send Email'
SELECT @send_page as 'Send Page'
SELECT @send_netsend as 'Send Netsend'
SELECT @send_eventlog as 'Write to Event Log'
SELECT @pager_days as 'Pager Days'
SELECT @dw as 'Day of Week'
SELECT @page_dw as 'Day of Week to Page'
SELECT @page_day_valid as 'Page Day Valid'
*/

[Replication] Replication status

Rough first attempt. Doesn't include everything you might need, but gives you a quick view as to how replication is doing.

The Replication Monitor has a nasty habit of not showing you broken replication - it's usually trying to reapply the command rather than actually being in a failed status. I've watched the replication monitor show everything as fine, but when you go look at a subscription you see that it's between failed retry attempts to reapply a row. If your network is slow you can see the subscriptions flash the error icon then go back to showing everything as being okay.

I'll come back to this and see about adding more information to it. Thanks to Hilary Cotter for pointing out the source table (MSDistribution_Status) in an article - but any mistakes in the code are mine.

use distribution
go
SELECT SUM(UndelivCmdsInDistDB),
  MSdistribution_agents.NAME,
  MSdistribution_agents.publication,
  subscriber_id,
  subscriber_db 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
GROUP BY MSdistribution_agents.NAME, MSdistribution_agents.publication,
 subscriber_id, subscriber_db
ORDER BY MSdistribution_agents.NAME, MSdistribution_agents.publication,
 subscriber_id, subscriber_db

[Index] Statistics age

This is pretty handy. Lets you know if your stats are out of date.

Found at http://furrukhbaig.wordpress.com/2007/08/17/index-statistics-age/

SELECT
           'Index Name' = ind.name,
           'Statistics Date' = STATS_DATE(ind.object_id, ind.index_id)
FROM
           SYS.INDEXES ind
WHERE
           OBJECT_NAME(ind.object_id) = ''

[Tools] Some handy commands for Litespeed

Some notes of mine on using Quest Software's Litespeed Backup. Really handy software, but with some quirks.

To backup without having it compressed, when using NCS (Native Command Substitution)

/* ads_translator_deactivate */
BACKUP DATABASE dynamics TO DISK ='e:\dynamics_native.bak'

(the code in the comments is actually read by the parser and turns off the NCS)

To see if Litespeed is installed on a machine

use master
go
exec xp_sqllitespeed_version
go

Basic Restore(needs to be on one line - split here for readability)

exec master.dbo.xp_restore_database
@database = 'datbase',
@filename = '\\path\datbase.bak',
@filenumber = 1,
@with = 'RECOVERY',
@with = 'NOUNLOAD',
@with = 'STATS = 10'

To restore a database with a new name

--First, get a list of all logical files within the backup--
xp_restore_filelistonly @filename= '\\path\datbase.BAK'

--Now do a restore with MOVE.  @database is the new db name
exec master..xp_restore_database @database='datbase2'
  , @filename= '\\path\datbase.BAK'
  , @with = 'MOVE "datbase_Data" TO "e:\SQL\datbase2.MDF"'
  , @with = 'MOVE "datbase_Log" TO "e:\SQL\datbase2.LDF"' 

Extractor
The extractor.exe application will take a compressed backup file and decompress a standard MS SQL backup file. Useful if you don't have Litespeed on your other server, or if you need to send a file to someone who doesn't have it.

To call it:

extractor.exe -Fc:\temp\Northwind.bak -Ec:\temp\NorthwindNative.bak

where -F is the original compressed file and -E is the name for the tape files that will be generated (typically 7 files per backup). These can be restored via SSMS by adding each of the .bak[0-7] files to a restore.

Object-level Restores
OR.exe is the application used to do object-level restores from Litespeed. It uses BCP to pull the data out of one table and to place it in the next. It can only do it on a FULL backup - not a Filegroup, Diff, or TLOG. Irritating, that - maybe they've fixed that in Version 5.

Sample command to restore the "route" table from a backup on ServerA to ServerB. This needs to be on one line, I've split it up to illustrate the parameters.

"C:\Program Files (x86)\Imceda\LiteSpeed\SQL Server\Engine\or.exe"
 -F\\path\datbase.BAK
 -Odbo.route
-R1
-EServerB
-Sexisting_database
-Tdbo.TBD_test_restore

• -F: database backup filename
  
• -O: table name - must include schema
  
• -R: connection type - 1 is trusted
  
• -E: target server
  
• -S: target database
  
• -T: target table name - must include schema.

It can be done within SQL Server as well.

exec master..xp_objectrecovery
    @status_filename='{178FA185-ABC7-4183-910A-4DDE775BB614}',
    @FileName='E:\datbase.BAK',
    @ObjectName='dbo.tbd_test_restore',
    @DestinationServer='qa_database',
    @DestinationDatabase='new_copy_of_datbase',
    @DestinationTable='TBD_newtable_temp',
    @TempDirectory='E:\temp\'

[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

[Indexes] And my unused index query.

Actually, not mine, but it's the one I use. All sorts of variations you can run

SELECT  @@SERVERNAME AS server_name,
            DB_NAME() AS database_name,
            o.name AS object_name,
        i.name AS index_name,
        i.type_desc,
        ISNULL(u.user_seeks, 0) user_seeks,
        ISNULL(u.user_lookups, 0) user_lookups,
        ISNULL(u.user_scans, 0) user_scans,
        ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0) user_total,
        ISNULL(u.user_updates, 0) user_updates,
        last_user_seek,
        GETDATE() AS date_inserted       
--        fill_factor
--    ,'DROP INDEX ' + i.name + ' ON dbo.' + o.name
FROM    sys.indexes i
JOIN    sys.objects o
        ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats u
        ON i.object_id = u.object_id
           AND i.index_id = u.index_id
           AND u.database_id = DB_ID()
WHERE   o.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE')           -- No system tables!
AND (ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0)) < 100
AND i.type_desc NOT IN ('HEAP','CLUSTERED')
AND i.is_primary_key = 0
--AND i.is_unique_constraint = 0
ORDER BY (ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0)),ISNULL(u.user_updates, 0) DESC, o.name, i.name
--ORDER BY ISNULL(u.user_updates, 0) desc, o.name, i.name
--ORDER BY o.name,
--        i.name

[Index] Find the size of your indexes

Something found online (originally by Alejandro Mesa). This is doubly handy now that 2005 can tell us which indexes aren't frequently used. Thanks to N8WEI for the correction on sys.allocation_units.

select
i.[object_id],
i.index_id,
i.NAME AS Index_Name,
i.type_desc,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a
on (a.type = 2 AND p.partition_id = a.container_id)
OR ((a.type = 1 OR a.type = 3) AND p.hobt_id = a.container_id)
--where
-- i.[object_id] = object_id('dbo.thebakingdba_fanmail') --wishful thinking
-- and i.type = 1 -- clustered index
order by
i.name --p.partition_number
go

[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

[Backups] Alter jobs to change your backup server

Something I had to whip up on short notice, so you can see what code I cribbed from SSMS.

Here's what we do:

1. Find any jobs with "Backup" in the name
   
2. Get the job details via sp_help_jobstep
   
3. Step through each job step
   
   
   
   1. Create a different statement, with the new server's name
      
   2. If the step has BACKUP or SQLMAINT, and the old server's name, execute SP_UPDATE_JOBSTEP with the new code.
      
   
   

create table #tmp_sp_help_jobstep
(
step_id int null,
step_name nvarchar(128) null,
subsystem nvarchar(128) collate Latin1_General_CI_AS null,
command nvarchar(max) null,
flags int null,
cmdexec_success_code int null,
on_success_action tinyint null,
on_success_step_id int null,
on_fail_action tinyint null,
on_fail_step_id int null,
server nvarchar(128) null,
database_name sysname null,
database_user_name sysname null,
retry_attempts int null,
retry_interval int null,
os_run_priority int null,
output_file_name nvarchar(300) null,
last_run_outcome int null,
last_run_duration int null,
last_run_retries int null,
last_run_date int null,
last_run_time int null,
proxy_id int null,
job_id uniqueidentifier null)

declare @job_id UNIQUEIDENTIFIER
DECLARE @minid SMALLINT, @maxid SMALLINT
DECLARE @oldservername sysname, @newservername sysname
DECLARE @jobcode NVARCHAR(MAX)
declare crs cursor local fast_forward
for ( SELECT sv.job_id AS [JobID]
 FROM msdb.dbo.sysjobs_view AS sv
 WHERE NAME LIKE '%backup%' )

SELECT @oldservername = 'ServerA' 
SELECT @newservername = 'ServerB'
open crs
fetch crs into @job_id
while @@fetch_status >= 0
begin
TRUNCATE TABLE #tmp_sp_help_jobstep
insert into #tmp_sp_help_jobstep(step_id, step_name, subsystem, command, flags, cmdexec_success_code, on_success_action, on_success_step_id, on_fail_action, on_fail_step_id, server, database_name, database_user_name, retry_attempts, retry_interval, os_run_priority, output_file_name, last_run_outcome, last_run_duration, last_run_retries, last_run_date, last_run_time, proxy_id)
 exec msdb.dbo.sp_help_jobstep @job_id = @job_id
update #tmp_sp_help_jobstep set job_id = @job_id where job_id is NULL
--change to job step occurs here.
SELECT @minid = NULL, @maxid = NULL
SELECT @minid = MIN(step_id), @maxid = MAX(step_id) FROM #tmp_sp_help_jobstep
WHILE @minid <= @maxid   BEGIN    SELECT @jobcode = REPLACE(command, @oldservername, @newservername)      FROM #tmp_sp_help_jobstep      WHERE step_id = @minid    IF EXISTS (SELECT * FROM #tmp_sp_help_jobstep        WHERE step_id = @minid        AND (command LIKE '%backup%' OR command LIKE '%sqlmaint%')       AND command LIKE '%'+@oldservername+'%') --   EXEC msdb.dbo.sp_update_jobstep @job_id=@job_id, @step_id=@minid,  --    @command= @jobcode     SELECT @jobcode    SET @minid = @minid + 1    END  --end change   fetch crs into @job_id end  close crs deallocate crs  DROP table #tmp_sp_help_jobstep 

[Tip] SELECT INTO with an IDENTITY column

Really basic, but I always forget the syntax.

select identity(int, 1,1) as ID [...] into [nonexistent table] from [other tables]

[Indexes] Making sure you CAN reorg/rebuild/defrag indexes

In SSMS, prior to SP2, the default when creating indexes was to create them with page locking disabled. Which means you can't defragment or rebuild them.

Here's a script, courtesy of "TheSQLGuru", which will script out the corrections.
http://www.eggheadcafe.com/software/aspnet/29953067/finding-all-the-indexes-o.aspx

set quoted_identifier off
go
SELECT "alter index [" + i.name + "] ON [" + s.name + "].[" + t.name + "]
SET (ALLOW_PAGE_LOCKS = ON)
go"
-- s.name, t.name, i.name
FROM sys.schemas s
JOIN sys.tables t ON
t.schema_id = s.schema_id
JOIN sys.indexes i ON
i.object_id = t.object_id
WHERE
i.index_id > 0
AND INDEXPROPERTY(i.object_id, i.name, 'IsPageLockDisallowed') = 1
AND INDEXPROPERTY(t.object_id, i.name, 'IsStatistics') = 0
AND NOT EXISTS
(
SELECT *
FROM sys.objects keys
WHERE
keys.parent_object_id = i.object_id AND
keys.name = i.name AND
keys.type IN('PK', 'UQ')
)

[Index] DMV to create your missing indexes

This is pure genius.

http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

Uses the DMV to create missing indexes.

(update 2013/05/10 and something, in retrospect, you need to be careful about - don't just add everything it shows.  I was reading that MS actually had this in a CTP, where it would tell you the indexes to add, like Bart's script below.  And people wrote code that would automatically add them, without thinking about what other similar indexes were there, what the impact would be on inserts and updates, etc.  So they caused massive performance problems.  Practical upshot: be careful)

SELECT
mig.index_group_handle, mid.index_handle,
CONVERT (decimal (28,1),
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
) AS improvement_measure,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

[Documentation] Now where are my databases again?

Everyone probably already has a version of this, but I saw some horrid code for it the other day. The below is probably 2005 specific (obviously, because of sys.master_files). Master.sys.master_files is very nice - keeps track of all the databases in one spot, rather than needing to query each databases' table.

USE master
go
SELECT sysdatabases.NAME, mf.* FROM sys.master_files mf
INNER JOIN sysdatabases ON mf.database_id = sysdatabases.dbid
ORDER BY physical_name

[Shopping] Results of my pressure cooker expedition

So, I got one. Fagor Duo. In retrospect, the Splendid would work fine, since very few recipes call for 7psi - everyone uses 15. And while some things work wonderfully well, it's not a panacea.

For one, most of the recipes are things you could've done in a crockpot. (Although maybe that's a plus...)

Secondly, while some things work wonderfully, others don't. I have a beef stew (I'll put the recipe up, even though it's not baking) that normally needs to cook for 40 minutes. With the pressure cooker, it cooks for 20... but requires 5 minutes to get up to temp, and 15 to cool down (better results). So the same time, and I have to clean the pressure cooker. Some things work well... but is cooking something in 10 minutes that much better than something in 20?

However, the beef stew _does_ gain the following:
1) Even more tender
2) Flavor better
3) The potatoes get cooked with the meat, so I don't have to deal with boiling potatoes or scrubbing that pot to remove the starch.

[Documentation] List your backups in Wiki format

Something simple I cooked up for documentation. Basically, it looks at the backups made in the past 2 weeks and create a table (in Wiki format). Cut and paste into your wiki page, and done.

BTW - if anyone has code to easily POST this to a web site, I'd be grateful. Ideally I'd set this up to run weekly and show changes in the environment.

Cut and paste the code - Wiki tables require that spacing. Make sure to run in TEXT mode

And yes, I know. The code is crude.

CREATE TABLE #wikilist (id INT IDENTITY, formatting VARCHAR(300))

INSERT INTO #wikilist (formatting)
SELECT distinct 'Server: ' + server_name + '{BR}{| border="1"
! Database !! Type !! Location'
FROM msdb.dbo.backupmediafamily backupmediafamily
inner join msdb.dbo.backupset backupset
on backupset.media_set_id = backupmediafamily.media_set_id
where backup_start_date > getdate()-14

INSERT INTO #wikilist (formatting)
--SELECT * FROM
select distinct '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name)))))) AS Location
from msdb.dbo.backupmediafamily backupmediafamily
inner join msdb.dbo.backupset backupset
on backupset.media_set_id = backupmediafamily.media_set_id
where backup_start_date > getdate()-14
and left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))) not like '%:'
GROUP BY '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))))
ORDER BY '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))))

INSERT INTO #wikilist (formatting) VALUES ('|}')
SELECT formatting FROM #wikilist ORDER BY id

[Linked Servers] Trick to making cross-server queries faster

Learned this years ago, and it's one of those nice tricks to keep in your cap.

So, what collation are you running? Do you know? Do you always use the default? Do you use linked servers and need a little more performance?

If you do, then you're in luck. If you have a linked server between two servers running the same collation, enable "collation compatible" and your queries will run faster.

Why? As I remember, if you don't have it enabled, then your query is sent across without the WHERE clause. Once it comes back, it's evaluated, ensuring that collation is properly dealt with. If you have collation compatible = true, then it sends over the whole query, including the WHERE clause. So, fewer results returned, lower I/O on the far-side, and no processing required locally.

One thing, though - make sure you're on the same collation. On 2005, the default is still the same (IIRC), but what it's called has changed.

[Replication] Document what tables are replicated

I needed this, and rather than do it the easy/long way once, I wrote a piece of code to do it from now on. Feel free to modify it, and if you can get that inner loop to create the #Articles table dynamically, PLEASE let me know. I also decided to use the SPs to try and keep it somewhat portable, and because I wanted to play with the prior post (saving SPs to tables).

Oh yeah, what this does: finds each replicated database, then walks each publication and get a list of the articles in that publication. Then formats it for a wiki, using bullets
(aka
* item 1
* item 2).

You'll have to enable data access on your local machine:
EXEC sp_serveroption [your_server_name] , 'data access' , 'true'

CREATE TABLE #Articles
(
[article id] INT,
[article name] sysname,
[base object] nvarchar(257),
[destination object] sysname null,
[synchronization object] nvarchar(257),
[type] SMALLINT,
[status] TINYINT,
filter nvarchar(257),
[description] nvarchar(255),
insert_command nvarchar(255),
update_command nvarchar(255),
delete_command nvarchar(255),
[creation script path] nvarchar(255),
[vertical partition] BIT,
pre_creation_cmd TINYINT,
filter_clause NTEXT,
schema_option binary(8),
dest_owner sysname null,
source_owner sysname null,
unqua_source_object sysname null,
sync_object_owner sysname null,
unqualified_sync_object sysname null,
filter_owner sysname null,
unqua_filter sysname null,
auto_identity_range INT,
publisher_identity_range INT,
identity_range BIGINT,
threshold BIGINT,
identityrangemanagementoption INT,
fire_triggers_on_snapshot BIT
)

CREATE TABLE #finished_list (id INT IDENTITY(1,1), names VARCHAR(100))

DECLARE
@minid SMALLINT,
@maxid SMALLINT,
@min_database SMALLINT,
@max_database SMALLINT,
@min_publication SMALLINT,
@max_publication SMALLINT,
@sqlstatement VARCHAR(1000),
@db_name sysname,
@publication_name sysname

SELECT identity(int, 1,1) as ID, name INTO #Databases FROM MASTER.dbo.sysdatabases WHERE category > 0 AND NAME <> 'distribution'
SELECT @min_database = MIN(id), @max_database = MAX(id) FROM #Databases

--outer loop - each database that has publications
WHILE @min_database <= @max_database
BEGIN
SET @db_name = null
SELECT @db_name = NAME FROM #Databases WHERE id = @min_database
IF @min_database = 1
SELECT @sqlstatement = 'SELECT identity(int, 1,1) as ID, * INTO ##Publications FROM OPENQUERY( [your_server],''SET FMTONLY OFF {call ' + @db_name + '..sp_helppublication}'')'
ELSE SELECT @sqlstatement = 'insert INTO ##Publications exec ' + @db_name + '..sp_helppublication'
EXEC(@sqlstatement)
INSERT INTO #finished_list (names) select '* ' + @db_name + ''
SELECT @min_publication = MIN(id), @max_publication = MAX(id) FROM ##Publications
--inner loop - each particular article
WHILE @min_publication <= @max_publication
BEGIN
SET @publication_name = NULL
SELECT @publication_name = NAME FROM ##publications WHERE id = @min_publication
INSERT INTO #finished_list (names) SELECT '** ' + @publication_name + ''
SELECT @sqlstatement = 'INSERT INTO #Articles EXEC ' + @db_name + '..sp_helparticle @publication = ''' + @publication_name + ''''
--I can't get this to work - if you can, please share. Probably something involving sp_executesql
-- IF @min_publication = 1
-- SELECT @sqlstatement = 'SELECT identity(int, 1,1) as ID, * INTO ##Articles FROM OPENQUERY( [your_server],''SET FMTONLY OFF {call ' + @db_name + '..sp_helparticle @publication = N''''' + @publication_name +'''''}'')'
-- ELSE SELECT @sqlstatement = 'insert into ##articles EXEC ' + @db_name + '..sp_helparticle @publication = N'''+ @publication_name +''' '
EXEC(@sqlstatement)
INSERT INTO #finished_list (names) SELECT '*** ' + [article name]+ '' FROM #articles ORDER BY [article name]
truncate table #articles
SET @min_publication = @min_publication + 1
END

TRUNCATE TABLE ##publications
SET @min_database = @min_database+1
END

SELECT * FROM #finished_list ORDER BY id
DROP TABLE #Databases
DROP TABLE ##Publications
DROP TABLE #finished_list
DROP TABLE #articles


SELECT INTO from SP

Found this on usenet, courtesy of BP Margolin (who pointed at a post by Umachandar Jayachandran). I've always just figured out what the columns are and built a table, but this will save me a ton of work.

-- To enable data access locally, do:
EXEC sp_serveroption localsrvr , 'data access' , 'true'
--(where localsrvr is your server's name)

-- To do SELECT...INTO from SPs results do:
SELECT * INTO SomeTbl FROM OPENQUERY(localsrvr , '{call sp_who}')

-- If the SP uses temporary tables,
-- you have to do something like the following b'cos
-- the above call will fail
SELECT * INTO SomeTbl FROM OPENQUERY( localsrvr ,'SET FMTONLY OFF {call sp_who}')

But you need to understand that this will result in the SP being executed twice once when OPENQUERY tries to determine the metadata for the result set & again
when it actually executes the call.


[Shopping] Buying a pressure cooker

Mostly for my benefit, but maybe it'll help someone else.

Why a pressure cooker?

Easy - time. Cooks things much faster. Boiling point of water in a pressure cooker (at 15psi) is a mere 250 degrees. Which means you can boil things in 2/3rds the time. And some other impressive things - a 6-hour stock in an hour.

Rules for pressure cookers

1. Don't low-ball the price. You're talking about something that will hold boiling liquids/food at pressure. As in bike-tire pressures. The last thing you want is for it to give - it can hurt you, maim you, and at the very least make a mess of the kitchen that will be Epic.
   
2. Same goes for used. Be safe on something like this.
   
3. If it feels cheap, it probably is. You want heft.
   
4. You want Stainless Steel, not aluminum. Aluminum will pit and hold gunk within - not a happy thing.
   
5. 3-layer bottoms. Makes it cook more evenly.
   
6. Bigger is better, you need room for the steam to build pressure. Supposed 6 quarts is the magic number.
   
7. Get a modified-first-gen (pressure valve) or second-gen (spring valve), not a "jiggle top".
   
8. Expect to spend between $70 and $250.
   

Brands I've seen recommended: Fagor (budget pick), Kuhn Rikon ("mercedes of pressure cookers"), Magefesa, and WMF (brand used by Alton Brown, but man that's pricey).

references:
http://missvickie.com/workshop/buying.html
http://www.realfoodliving.com/KuhnRikon.htm
http://query.nytimes.com/gst/fullpage.html?res=980DE5D61E3CF93BA15750C0A9679C8B63&sec=&spon=&pagewanted=all

My decision:
Either the Fagor or the Kuhn Rikon. Fagor's about $50 less, but I think either will be a good choice. The way I figure it, I cheaped out once on a different piece of cookware (a cast-iron skillet), and I might as well throw it out... tried seasoning it for years, and it still has yet to taste as good as the Lodge Logic we bought.

[Sysadmin] Kick users after midnight

Not mine, but we use it. Pretty basic - looks for SPIDs that are in a particular database and kills the SPIDs. We use it to ensure that people don't have active connections during maintenance time.

The one downside is that it's very literal - if you aren't explicitly in the system as sysadmin, out you go. Set in a job to run right before your maintenance.


set nocount on
declare @spid nvarchar(10)
declare @killem nvarchar(20)
declare spid_csr insensitive cursor for
select spid from master..sysprocesses
where sid not in
(
select sid from master..syslogins
where sysadmin = 1
)
and dbid in (db_id('Main'),db_id('AnotherOne'))
and loginame like 'MyDomain%'
open spid_csr
fetch next from spid_csr into @spid
while @@fetch_status = 0
begin
select @killem = 'kill ' + convert(varchar(3),@spid)
exec (@killem)
fetch next from spid_csr into @spid
end
close spid_csr
deallocate spid_csr

[Baking] Chicago Deep Dish Pizza

This weekend I expanded my repertoire, and made pizza. While I love Gino's & Due's in Chicago, getting them shipped is _pricey_.
I used the following recipe I found on the net.
http://reviewboard.com/articles_ektid256.aspx
Well, that's nifty - the site has changed owners. Thank goodness for the Internet Wayback Machine, which had a copy.

The Best Deep Dish Pizza

by Philip Ferreira
Best Deep Dish Pizza Background

When I was a kid I used to work at a place in Chicago that made some of the best Italian food that I ever ate. One of their specialties was Chicago Deep Dish Pizza. Now that I live on the East Coast I find myself wishing more and more that I had access to that pizza. Not that I can't make it, but because like anything it is a chore and it is easier to pick up the phone and order a few for the family.

Alas I am the only person in my house that knows how to do it so the chore gets put on me fairly regularly. Normally I don't give up my secrets without a fight, but living on the East Coast has made me sympathetic to the people that are not in Chicago who do not have access to the great pizza. That being said here I go:
Best Deep Dish Pizza Recipe:

You'll be able to make two good sized pizzas with this recipe. It takes about 3 hours so make sure you have everything you need before you start. This recipe is expensive, probably comparable to what it would cost to buy them. The ingrediants need to be fresh, and if you substitute or don't do something the way I tell you to, don't blame me for the results. Making Authentic Deep Dish Pizza is an art, it took me a long time to get it right.
Best Deep Dish Pizza - What you need to start:

You will need an electric mixer with a dough hook. If you don't have one you can try and follow along by kneeding the dough yourself (You will need to add another 45 minutes to this process if you kneed the dough).

Make sure you have the following ready:

2 18" deep dish pizza pans - Don't use a baking dish, go out and splurge on a few pans, this is serious pizza and you shouldn't go screwing it up by trying to make it in a 9x15 baking dish it won't cook right, it won't be the same and you will probably end up thinking this recipe stinks.

2 Tablespoons of Sugar (Needs to be sugar, the yeast feeds on it and won't proof without it).
4 Cups of Warm Water (110 degrees when you pour it in the bowl it will cool by the time you get everything else done)
4 Packages of Yeast or if you have the jar you can do 8 teaspoons of Yeast.
1 cup of First Press REALLY GOOD Extra Virgin olive oil.
1 Cup of Yellow Cornmeal
9 Cups of Flour (Up to 10)
Best Deep Dish Pizza - Technique

Proofing the yeast is an important part of this process. Make sure you do it right, you want your bowl of water to be about 95 - 100 degrees. Mix in the 2 tablespoons of sugar and stir it with a wisk. Once you disolve the sugar in the water, put the yeast in and make sure it all gets wet. (Yeast tends to float on the top and some of it won't proof if you don't wet it). Now walk away from it for about 10 minutes. It should be in a big bowl because this stuff is going to FOAM up and it will spill over if you don't have a big enough bowl - you have been warned ;)

In your mixer mix the olive oil, the cornmeal and 5 cups of flour. Mix it up for about 1 minute and add the yeast slowly while it is mixing up. Slowly add the rest of the flour and let the mixer mix on about 1/3 speed for 5 minutes. The dough should not be sticky or wet, it should feel like really soft smooth elastic. Coat a plastic bowl with a little olive oil and put the dough into the bowl (Big bowl). Cover the top of it with a damp towel and let it rise until it is double the size.

Punch it down and let it rise again.
Best Deep Dish Pizza - Prep Your Pans First!

Prep your pizza pans, spread a little olive oil on the surface and sides and sprinkle yellow cornmeal on the bottom of it. This will prevent the pizza from sticking to the pan. Alternately (and I do it this way quite a bit) you can take REAL butter and really give it a good coating all away around and in the pan. Layer it on very thick. It gives the crust an amazing flavor. Sprinkle with yellow cornmeal the same way you would if you used olive oil.
Best Deep Dish Pizza - Mix Your Cheeses and Make Your Sauce

Make your sauce & cheese mixes while you are waiting around for the dough to rise. Here is what you do:

Cheese Mix:
4 Pounds of Grated Mozzarella
1 Pound of Provolone
1 Pound of Romano, Parmigiano, Asiago mix (You can get them predone at the store in the deli section)

Mix the cheese mix in a big bowl so it is blended well.

Sauce:
4 28 oz Cans of Plum Tomatoes, Drain them and then put them through your blender for about 10 - 15 seconds you want them to be crushed up and chunky, but not liquid.
5 Teaspoons of FRESH Chopped up Basil
5 Teaspoons of FRESH Chopped Oregano
2 Tablespoons of Sugar (Or Splenda, I use Splenda)
10 Cloves of FRESH Garlic Peeled and Crushed with a Garlic Press
Salt and Pepper to taste
1/2 cup of Parmigiano Cheese Grated

Combine the ingrediants together and make sure it has good time to sit and steep in the acids from the tomatoes. This will bring out the flavors of the seasonings.
Best Deep Dish Pizza - Bringing Everything Together.

Once your dough has doubled again take it out and divide it into two sections with a knife. Roll it out onto your deep dish pizza pans (last chance to go out and buy pizza pans if you are using a baking dish you will not get the consistancy you need and you will not be happy). When you are laying the dough onto the pan push the dough to the edge. You can then turn the pan slowly while you pull the dough up the sides. If you have extra dough (and you should) roll it flat put it on a cookie sheet mist it with some olive oil, sprinkle it with garlic powder, Parmigiano, a little salt and bake it with your pizzas. You slice it with your pizza cutter after it's baked and dip it the left over pizza sauce. Presto free pizza bread / bread sticks.

Now that you have your pizza dough ready take a brush and brush olive oil onto the dough. Add your toppings (I use Portabella Mushrooms, Italian Sausage, Pepperoni, Green Pepper, Onion and Black Olives.) on the dough, then put half of your cheese mix on one pizza, half on the other. Use it all!

Pour your sauce on top until it reaches the edge of the dough (which should be all the way up the side of the pan), spread it out evenly and sprinkle with Parmigiano. Note: For all you folks that have never had Chicago Deep Dish Pizza, the sauce is on TOP so it's a red top pizza. This is traditional, and trust me it is good!
Best Deep Dish Pizza - Heat Your Oven and Make Sure You Let It Cool!

Preheat your oven and bake for 25 minutes on 350 degrees. After 25 minutes crank the oven up to 475 and bake for another 10 - 15 minutes. You want to watch the pizza and take it out when the top is light golden brownish and the crust is a light golden brown.

IMPORTANT: Let this pizza cool for 20 minutes, if you do not it will be all over the place. Once it cools for 20 minutes it will be just the right temp and will come out of the pan the right way. Cut and serve. This recipe should feed a family of 7 with maybe a slice left over.

Looking at it, I had that "aha!" moment. I've always wondered how they got the texture of the pizza crust - now I know. 1 cup of cornmeal. So, made it this weekend, with a few substitutions

* I couldn't find 18" deep-dish pizza pans nearby. So I bought 2 3"x13" pizza(?) pans at Ace-mart (local restaurant supply shop). A little too deep, but close enough!
* Halved the recipe. Which means that you get pretty close on the size (18" = 254 square inches, 13"*2 = 264 square inches). Which explains why I was a little short on crust. But also made it cheaper - I think I spent $30 on cheese alone.

Thoughts from making it:
* Garlic - rather than run in through a press, you can huck it in your blender first. Put on chop - when I dropped the garlic in (through the small opening on the top of the blender), it bounced around for a while, which wound up with it getting chopped into bitty bits quite nicely. (And we went with much less - 2 toes of garlic instead of 10). Faster than chop and it just falls straight to the bottom. Chop allowed it to keep getting flung about the blender.
* If you use the pan I did, don't attempt to go all the way up the sides. About half-way will do you. The slices still weigh 10 ounces or so, and definitely are deep dish pizza goodness.
* Maybe get whole tomatoes in a can, or just buy crushed. I bought diced, and about 3 seconds in the blender was all it took - both to mix it all up, and to make a horrid mess as my blender barely held all the tomatoes.

Some changes for next time, I think:
* Try to make a little more crust. Or just make sure I distribute it evenly. I wound up with ultra-thick sides, and not enough on the bottom. Still yummy, though.
* A little less of the parmesan/asiago/romano mix
* A little less cheese, a little more sauce. It seems like a ton of sauce - it's not.

Overall? A triumph. I'm making a note here: huge success.
Awesome Chicago pizza.

-TBD

[WAT] Weird conversion error in SQL Server

Here's a fun conversion that will bite you.

DECLARE @test VARCHAR(10)
SET @test = '50000'
SELECT convert(varchar(5), convert(int, @test))


You get back "50000".
Now do this:

DECLARE @test VARCHAR(10)
SET @test = '500000'
SELECT convert(varchar(5), convert(int, @test))

What do you get back? "*". Nifty.

[Bake] Baking tip - let your oven heat up!

So, for years we used the "preheat" cycle on our oven. 4 minutes and we can start cooking things? Awesome! Or so we thought.

One day we bought an oven thermometer, mostly so I could work on my barbecuing method (smoked-meat barbecue, not charcoal grill barbecue). Lo and behold, the temperature was 220 after the preheat cycle was finished. Gee, I think I know why we felt our oven was temperamental and not all that good.

Ignore the preheat cycle - it just warms the air inside up, and not all that well.
For 350 degrees, let it heat up for 15 minutes.
For 400 degrees, let it heat up for 20 minutes.
(etc, etc).

This will instantly make your oven MUCH better than it ever was before.
-- TBD

[Cake] Olga's Jam Cake

Okay, here's another from my Baba's trove. I was starting to think I didn't have anything useful to add, cooking-wise - then I realized that, at the very least, I have all my Baba's recipes. Which, if nothing else, you should go make the Borscht. Trust me on this.

Anyhow, here's another of her recipes growing up. You might find it a bit dry - I don't. The combination of the crumbliness of the cake and the jam holding it together works really well. I used Raspberry Preserves this last time - go for seedless. Strawberry also works really well with this.

Baba's Jam Cake
Ingredients:
1 stick butter
1 stick margarine
1 cup sugar
3 cups AP flour
1.5 teaspoon baking powder (less if wanting a denser consistency)
4 egg yolks
1/2 cup sour cream
18-24 Ounce jar of Fruit Preserves (your choice of flavor)

* Set butter & margarine out so they soften
* Cream butter/margarine & sugar (aka put them in the mixer, and let them mix for 3-5 minutes on medium. The color will lighten and it'll get a little fluffy)
* Mix everything but the flour together
* Mix in flour with a spoon (practically, I do everything in my kitchenaid, paddle attachment, then add the flower in small installments, on low.)
* Take 3/4 of the dough and put in a 9x12 pan (or cookie sheet, but it needs to be 1-1.5 inches deep). Make it even with a knife/spatula/etc.
* Pour your preserves over this (1.5 pounds, aka 18-24 ounce jar) Using a knife, spread it evenly over the cake
* Take that last 1/4 of dough, add flour, and roll it out into strips. Make a lattice, taking care to include all 4 sides of the pan.

This was the hardest part for me, never having made it before. What I did was to take the last 1/4 of dough, put it on a sheet of wax paper, fold the wax paper over (so it's covered) then roll out to about the right length. Stick in the freezer 20-30 minutes. Bring it out, and quickly cut the strips with a pizza cutter, and lay them out. I didn't do anything fancy - one strip on each edge (yes, some will be too long. Cut them short.), then lengthwise, then widthwise. You're making a crisscross pattern. If you want to get fancy do every other stripe in one direction, then the other, then finish the first direction, then the other.

Bake at 350 degrees for 45-50 minutes. Watch it starting at about 40 minutes. One thing you're also looking for, besides being Golden Brown on the lattice, is that the jam/preserves "crystallize". It winds up being just a little crunchy, probably due to the jam caramellizing (is that how it's spelled?). The first time I did it it came out perfectly. The second time it didn't gel, and so you didn't wind up with that wonderful texture. My only other thought, besides "I pulled it out too early", was that I used Jam instead of preserves. Ironic since it's called "Jam cake", so I bet I just pulled it early.
-- TBD

[Maint] Show free space within a database

I use this all the time. Not sure where it came from, though it's probably from Simon Sabin's Taskpad_view report for SSMS. This will run on 2000 or 2005, and will show you how big your database is, as well as how much of that is used.


create table #data(Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL)


create table #log(dbname sysname NOT NULL,
LogSize numeric(15,7) NOT NULL,
LogUsed numeric(9,5) NOT NULL,
Status int NOT NULL)
insert #data exec('DBCC showfilestats with no_infomsgs')
insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')

select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from
(
select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]
from #data
union all
select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()
--order by [Type],[Name]
)a
order by [Type],[Name]
drop table #data
drop table #log

[ETL] Disabling foreign key constraints

When you're doing bulk loads, the Foreign Keys can bring you to a standstill. I can't load that one without this one going first, etc.

Quick way around it: disable all the constraints, load, then enable. Thanks to "sqladmin" at http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=48410&enterthread=y for this one, as well as Erland for the WITH CHECK.

I love sp_MSforeach[...] - makes it very easy to walk through databases/tables. Indispensable.

One note, though: even if you disable the constraints, on SQL Server 2005 SP2 (and possibly others; haven't thoroughly tested yet) you still cannot TRUNCATE the table. You can DELETE from it, just not TRUNCATE.

To Disable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

To Disable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

To Enable all Constraints (the WITH CHECK forces it to verify all the constraints are now good. Very important, as it helps ensure good execution plans)
exec sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

Enable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

[Maint] Running sql code on multiple servers

This isn't mine. A big "hey!" to Mark Hill, who came up with this many many years ago.

Very simple premise. For each .sql file in the directory of the below file (which you will save as a .bat), it will run it against each server listed, and save the results of each to a separate results file. Practically, it lets you run the same code against a bunch of servers in parallel. Hence the "maint" tag of this.

No, it's not nearly as easy/cool/pretty as SQL Farm or SQL Multi Script or the like, but it works well. And let's be honest - if we wanted our stuff to be shiny and pretty, we wouldn't be SQL DBAs, would we? We'd be building C# apps and Flash sites and the like.

Bonus points for coming up with a way to use a separate file that just has a list of servers.
(if you don't see all the code, just highlight the beginning and end and copy)

-----code begins-----
title %0
for %%a in ( .\*.sql ) do (
Start OSQL -i "%%a" -o "%%a.SERVER1_Output.txt" -E -S"SERVER1" -dMaster -n -w500
Start OSQL -i "%%a" -o "%%a.SERVER2_Output.txt" -E -S"SERVER2" -dMaster -n -w500
)
-----code ends-----

[NoBake] Tiramisu

First: Tiramisu is tasty.
Second: Given the right recipe, it's easy.
Third: ...if you can find ladyfingers.

I made this Monday, had some Tuesday, and I doubt it'll last through tonight.
We made this slightly differently - we used really strong coffee (you want probably closer to regular-strength coffee, if you don't want to buy espresso... but I'll buy the espresso for next time). The marscapone we found in our Albertson's, albeit in the "front cheese" section of store (with the Gruyere, Fontina, Goat cheese, etc), rather than the cheese section in the back of the store that had cheddar, swiss, etc.

The ladyfingers were a bit trickier to get ahold of. This first time I made it, I wound up using "Boudoir biscuits" at the local Central Market (high-end grocery store). Then I got lucky, and discovered (naturally, after I'd made the first batch) that normal grocery stores carry them, but they keep them in the bakery's freezer. That being said, here's where you order "real" ladyfingers from: http://www.sbiladyfingers.com/Ordering.htm . $18 for a case. Seems easier than what I went through.

Addendum: made it again. Using "real" ladyfingers. And... they get waterlogged much more easily. So be careful when you dunk them, not to get them too wet. Quick dunk. Don't wait to see it absorb the coffee/rum.

Fourth: even made poorly, it's still pretty decent.
Tiramisu
Copyright, 2002, Barefoot Contessa Family Style, All rights reserved
6 extra-large egg yolks, at room temperature*
1/4 cup sugar
1/2 cup good dark rum, divided
1 1/2 cups brewed espresso, divided
16 to 17 ounces mascarpone cheese
30 Italian ladyfingers, or savoiardi
Bittersweet chocolate, shaved or grated
Confectioners' sugar (optional)

Whisk the egg yolks and sugar in the bowl of an electric mixer fitted with the whisk attachment on high speed for about 5 minutes, or until very thick and light yellow [note - we had to remove one side of our bowl from it's holder, otherwise it didn't hit the bottom enough]. Lower the speed to medium and add 1/4 cup rum, 1/4 cup espresso, and the mascarpone. Whisk until smooth.
Combine the remaining 1/4 cup rum and 1 1/4 cups espresso in a shallow bowl. Dip 1 side of each ladyfinger in the espresso/rum mixture and line the bottom of a 9 by 12 by 2-inch dish. Pour half the espresso cream mixture evenly on top. Dip 1 side of the remaining ladyfingers in the espresso/rum mixture and place them in a second layer in the dish. Pour the rest of the espresso cream over the top. Smooth the top and cover with plastic wrap. Refrigerate overnight.
Before serving, sprinkle the top with shaved chocolate and dust lightly with confectioners' sugar, if desired.

*RAW EGG WARNING
Food Network Kitchens suggest caution in consuming raw and lightly-cooked eggs due to the slight risk of Salmonella or other food-borne illness. To reduce this risk, we recommend you use only fresh, properly-refrigerated, clean, grade A or AA eggs with intact shells, and avoid contact between the yolks or whites and the shell.

Notes: To make espresso for this recipe in your electric drip coffee maker, use enough water for 4 cups of coffee plus 1/3 cup of ground espresso.
You can find savoiardi and mascarpone in an Italian specialty store.

[Bread] Sourdough recipe, using starter

Simple recipe, assuming you have a starter. If not, it's fairly easy to make your own - but if you ask around, odds are that someone you know is already keeping one alive, and (trust me) is more than happy to share. I'll credit the person who sent me this, if he sees this. ;)

I feed the starter with 3/4 cup sugar, 1 cup flour, 2 tbsp potato flakes, and one cup warm water. Then I leave it out on the counter for 8 hrs. It gets all foamy or frothy. (If it's cold, put it in the oven with the oven light on)

When I'm ready to mix up the bread, I stir the starter with a wisk. And then pour one cup starter into my mixing bowl. Then I add 1/3 cup veg oil, 1 tbsp salt, 3/4 cups sugar, and 1.5 cups of warm water...stir that up with the wisk. Then I add 6 cups flour and mix it all together by hand. May have to add a bit more water or flour to get right consistency...but not too much more.

Once all is mixed into a lump of dough I cover the bowl with saran-wrap and then put it in the oven(with light on) to rise the first time for 8 hrs. Once that has risen I take it out and make 2 or 3 loaves and put those in loaf pans that have been sprayed with crisco for baking...Then I put the panned loaves in the oven(with light on) to rise the second time. The second rise may be 4-6 hrs depending. Lastly, I take the loaves out and set the oven for 350 degrees. Bake the loaves for 20-30 minutes. Use a bamboo screwer to poke in the bread to see if it is cooked through.

The way I do it, we feed it Friday night, make dough Saturday morning, make loaves Saturday evening, then bake Sunday morning. Even if they rise too much, don't worry - when we did it, they didn't rise any further in the oven.

[Cake] Chocolate Pound Cake

My wife has been going gaga over this recipe. Why, I'm not sure - the flavor is fine, but it's not amazingly moist, and not dense enough for me. But she likes loves it, and that's good enough. Bonus for all you aspiring bakers - this is a pretty simple recipe, and comes together quickly.

Not my recipe - I'm not that talented. Shamelessly cribbed from Paula Deen, here:
http://www.foodnetwork.com/food/recipes/recipe/0,,FOOD_9936_36917,00.html

Two notes:
1) This is an _easy_ cake. The only downside is that it cooks for over an hour and a half. But it takes maybe 15 minutes to put together. The secret, for those of you new to baking (Baking DBAs in training, mayhaps?), is to make sure the butter/shortening & sugar are thoroughly creamed. What I normally do is let the butter hit room temp (or about 12 seconds in the microwave), toss in the mixing bowl of our stand mixer (which is getting so much more use these days now that I'm baking) along with sugar, and put on 4 for about 5 minutes. What you're doing is using the sugar to puncture the cell walls in the butter. That's what "creaming" does. The longer it goes, the lighter and fluffier it gets. Five minutes may be too long, and that may be why it's not moist enough. I don't know. But it comes out fine, so I'm doing it that way for now. Make sure that, after creaming, to turn it down to low while you add all the other ingredients - otherwise you wind up with a cloud of flouy/chocolate/etc.

2) A 10" bundt pan can mean many things. Our 10" (across) bundt pan holds 12 cups, which isn't anywhere near big enough. The first time I made it, we wound up having to pour some out into a second loaf pan. The second time, I went looking for this mythical "ten inch bundt". Um, yeah. Or not. Fortunately, it turns out that an angel-food-cake pan, which normally holds 14-16 cups, is big enough. And since it has the "smokestack" in the middle, can sub for a bundt without any problems. (This was told to me by the owner of the local kitchen supply store - nice guy, they're on Little Road, just south of I-20 but north of the Little Road exit, in Arlington, Texas). Practical upshot: make sure your cake pan is big enough. Yes, this will rise.

Here's the recipe.
"The Bag Lady's Favorite Chocolate Pound Cake"
3 cups all-purpose flour
1/2 teaspoon baking soda
1/2 teaspoon baking powder
1/2 teaspoon salt
5 tablespoons cocoa
1 cup (2 sticks) butter, softened
1/2 cup vegetable shortening
3 cups sugar
5 eggs
1 tablespoon pure vanilla extract
1 cup buttermilk [this is probably optional - the taste it imparts is fine but not great. However, since buttermilk is acidic you'll have to change the recipe to deal with the pH change - no easy feat - TBD]

Special equipment: 10-inch bundt pan [again, use an angel-food cake pan - TBD]

Preheat oven to 325 degrees F.

Grease and flour a 10-inch bundt pan. Sift together flour, baking soda, baking powder, salt, and cocoa and set aside. Using an electric mixer, cream together butter, shortening, and sugar until fluffy. Add eggs, 1 at a time, and mix well after each addition.

Add flour and buttermilk alternately to butter mixture, beginning and ending with flour. Add vanilla and mix well. Pour batter into prepared pan. Bake for 1 hour and 45 minutes or until cake is done.

Remove from oven and allow cake to cool in pan for 10 minutes. Invert onto cake plate and serve.

[Logins] Resync logins after moving a database across servers

Not mine. This is courtesy of Sql-server-performance.com, which you should already be reading. Putting it here for my edification.

When you move a database from one server to another, the user IDs will no longer match. So you can't create a user with the same name in that database, and you can't fix the problem easily. This will sync it.


--Script to resync orphan SQL Server login IDs and database user IDs


USE database_name --Change to active database name
GO

DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
ORDER BY name

OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName

WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM orphanuser_cur INTO @UserName
END

CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur

[Maint] List size of each table in a DB

One of the handiest pieces of code I've ever used (aside from sp_who_3, but that's another post).
Put this in Master. Call it from whatever DB you're in. It'll list the table name, how many rows it has, how much space the Data uses, how much space the Indexes use, and the create date.

I don't know where it came from, but I didn't write it.

Enjoy!
TBD


USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_helptb] Script Date: 01/29/2008 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[sp_helptb]

AS

set NOCOUNT ON


IF EXISTS (SELECT * FROM tempdb..sysobjects where name like '#spaceused%' )
DROP TABLE #spaceused

IF EXISTS (SELECT * FROM tempdb..sysobjects where name like '#TableOrder%' )
DROP TABLE #TableOrder

Create Table #spaceused(
id int IDENTITY(1,1),
Row int,
DataSpaceUsed float,
IndexSpaceUsed float
)

Create Table #TableOrder(
id int IDENTITY(1,1),
TableName varchar(100)
)


DECLARE @TableName sysname
DECLARE @Owner sysname
DECLARE @FQTableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
select sysusers.name, sysobjects.name
from sysobjects
inner join sysusers on sysobjects.uid = sysusers.uid
where xtype = 'U'
order by sysobjects.name

OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @Owner, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

SET @FQTableName = @Owner + '.' + @TableName
Insert into #spaceused
exec sp_MStablespace @FQTableName
Insert into #TableOrder
select @TableName


FETCH NEXT FROM cur_tblfetch INTO @Owner, @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch


select TableName, Row, DataSpaceUsed/1024 DataSpaceUsed_MB, IndexSpaceUsed/1024 IndexSpaceUsed_MB, sysobjects.crdate AS CreateDate
from #TableOrder
JOIN
#spaceused
ON #spaceused.id = #TableOrder.id
JOIN
sysobjects ON sysobjects.name = #TableOrder.TableName
ORDER BY TableName

[Maint] Listing jobs that won't run

We all have them. Those jobs that get disabled, or schedules that get mis-set. This aims to fix that. It does two things
* Checks for "enabled = 0" in the job itself
* Checks sysjobschedules to make sure it's going to run

Hope it helps. I've also added code to only look for jobs that have changed in the past two weeks. Also, set up a job (below) that will email you every morning. This isn't really needed if you've got triggers on things, but I haven't implemented that yet.

I believe the SP works on 2000 and 2005, but the job is a 2005 job - I just scripted it out.


--create a database to hold these kinds of objects, and put it on every server you care about
/****** Object: StoredProcedure [dbo].[Monitor_JobChecker] Script Date: 01/29/2008 09:36:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************************

Name: Monitor_JobChecker

Creator: Michael Bourgon

Purpose: Will check and email jobs that have inadvertently been set to not run.
If the enabled is 0, then it's not set as "Runondemand" or "Decommissioned".
If the enabled is 1, then it does not have an active schedule. If it is
part of a SQL Sentry chain, put "event chain" in the description.

Dependencies:

History: 1.00 MDB 20070628 Looks good.
1.1 MDB 20080129 Adding code to only check the past month.


***********************************************************/

CREATE proc [dbo].[Monitor_JobChecker]
as
set nocount on
--Just a cursory check for jobs that aren't enabled
select name,
convert(smalldatetime, date_modified) as date_modified,
enabled,
description
from msdb.dbo.sysjobs
where enabled = 0
and category_id not in
(
select category_id
from msdb.dbo.syscategories
where name in ('Decommissioned', 'RunOnDemand', 'TemporarilyDisabled')
)
--mdb 20080129
and convert(smalldatetime, date_modified) > GETDATE()-14
union all
--A more thorough check that looks at the job schedules
select name,
convert(smalldatetime, date_modified) as date_modified,
enabled,
description
from msdb.dbo.sysjobs
where job_id in
(
select job_id
from msdb.dbo.sysjobschedules sysjobschedules
inner join msdb.dbo.sysschedules sysschedules
on sysjobschedules.schedule_id = sysschedules.schedule_id
where job_id not in
(
select job_id
from msdb.dbo.sysjobschedules
where next_run_date > 0
)
and sysschedules.freq_type <64
)
and enabled > 0
and description not like '%event chain%'
and category_id not in
(
select category_id
from msdb.dbo.syscategories
where name in ('Decommissioned', 'RunOnDemand', 'TemporarilyDisabled')
)
--mdb 20080129
and convert(smalldatetime, date_modified) > GETDATE()-14
order by enabled, name

/* -- to change the job status easily, use this:
USE msdb
EXEC sp_update_job @job_name = 'Re-Run Aggs',
@category_name = 'RunOnDemand'
*/
set nocount off



---------and now the job--------
USE [msdb]
GO
/****** Object: Job [Monitor - Jobs that will not run] Script Date: 01/29/2008 09:49:03 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 01/29/2008 09:49:04 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Monitor - Jobs that will not run',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Check system tables for jobs that will not run, either because they are disabled, or because they have no active schedules. Excluded are RunOnDemand and Decommissioned, or those jobs with "event_chain" (remove underscore) in their description, as those aren''t supposed to have regular schedules.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'support', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Run monitor_jobchecker] Script Date: 01/29/2008 09:49:04 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run monitor_jobchecker',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'create table #temp_monitor (name sysname, date_modified smalldatetime, enabled smallint, description varchar(7500))
insert into #temp_monitor exec msdb.dbo.monitor_jobchecker
if (select count(*) from #temp_monitor) > 0

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''IT_Databases'',
@recipients = ''support@dev.null'',
@query = ''exec dbautils.dbo.monitor_jobchecker'' ,
@subject = ''Monitor (yourservernamehere) - Job Checker'',
@body = ''This is a list of jobs on yourservernamehere that will not run. If enabled = 0, then they are not enabled. If
enabled = 1 then they do not have a schedule that is enabled. To fix please use:
USE msdb
EXEC sp_update_job @job_name = ''''Re-Run Aggs'''',
@category_name = ''''RunOnDemand''''
'',
@query_attachment_filename = ''Jobs That Will Not Run on yourservernamehere.txt'',
@query_result_separator = '' '',
@query_result_width = 750,
@attach_query_result_as_file = 1 ;
',
@database_name=N'msdb', --this should be your DBA database, whatever it's called
@output_file_name=N'C:\Logs\Monitor_-_Jobs_that_will_not_run.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monitor Schedule - daily at 9:30',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20070628,
@active_end_date=99991231,
@active_start_time=93000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: