[Replication] Running manually / Running by hand

Had to run some replication by hand while the agent was offline.  Here's how to do it.  I'd give thanks but I don't remember where I first read it.  Probably Hilary Cotter.  Thanks, Hilary!

SELECT      subsystem, REPLACE(command, '-Continuous', '')

FROM  msdb.dbo.sysjobsteps

WHERE job_id IN ( SELECT      job_id

                              FROM  msdb.dbo.sysjobs

                              WHERE name LIKE 'yourreplnamehere%' ) --or whatever the job is called

            AND subsystem <> 'TSQL'

            AND subsystem <> 'snapshot'

That gives you the commands needed.  We’ve removed the -Continuous for simplicity. (otherwise it would continue to run, and you’d need 3 windows)

Login to the server as SQLService.  Bring up a command window.

cd "\Program Files\Microsoft SQL Server\90\com\" (or wherever replication is installed; this was an old box)

logread.exe

distrib.exe (repeat for each server as necessary)

[tuning] Statistics - when were they created... OR RECENTLY UPDATED?

(update 2013/08/07 - since it shows recent times...)

Courtesy of Nayan Raval & #SQLHelp, which led me to another article (http://blogs.solidq.com/fabianosqlserver/post.aspx?id=52&title=undocumented+option(querytraceon+%3Ctracenumber%3E)+and+trace+flags+2388%2C+2389%2C+2390) which documents 2 more undocumented trace flags.

How do you find out when statistics were created?  If it's on an indexed field, when the index was created (crdate in sys.indexes).  But statistics on non-indexed fields?  Using Trace Flag 2388 changes the information that SHOW_STATISTICS returns.


DBCC TRACEON (2388)
DBCC SHOW_STATISTICS ('yourtablename','_WA_Sys_x')
DBCC TRACEOFF (2388)

Look for the row with the oldest Updated. The oldest update, depending on how often it's been updated, MAY show you when it was created.

And there's another use for this trace flag... when, aside from the most-recent date, was a statistic updated?   STATS_DATE will show you the LAST time it was updated, but I recently had a problem where I knew the problem existed and quickly updated stats, then realized I wanted to know when BEFORE then it had been updated.  One search to my blog later, code found, and there we go.

[VHD] Mounting a virtual disk easily

I’ve used Mark Russinovich’s “Disk2VHD”  before (http://technet.microsoft.com/en-us/sysinternals/ee656415), and found a clever trick on windows 7 boxes.  (be VERY careful if you have the image file on the same disk that you imaged - don't dare boot off it up.  I'd move it to a new machine)

Once you have the file on your new machine, use Disk Partition to mount the VHD to pull stuff off as needed. That way you don’t need to deal with Virtual PC most of the time.

sel vdisk file="c:\users\yournamehere\desktop\imagename.vhd"

attach vdisk