Why can't you save the results to a table, out of curiosity?

Would saving it to a file be a better option?

Literally your only option here is to run a profiler...

Did you already configure the distributor as a distributor?

I.e. connect to the server, right click Replication, and go to Configure Distribution?

marc 96091 (10/4/2010)

---

Thanks for the replies Gents!

So I'll switch to Full recovery and just do nightly Full backups.

Then I'll do T Log backups every 15 mins....why not do Diff backups...

Ok, did some more research/testing and I see an issue now.

Out of curiosity..you set the phone number as:

SET @Phone = @Phone +...

Seems like a waste in my opinion..that's still 800MB worth of IO every hour. I would bring it to full, do full backups every night, and a trans log backup...

You are using the same exact variable names at the very end of the sp_executesql..Gail's article used a different set (prefixed with _)

Try replacing your executesql statement with this:

...

Out of curiosity (unless I missed it)..what error are you getting?

The true CTE way would be like this:

WITH CTE

AS (SELECT woper

...

The issue is you're updating every row in the table by using a subquery within a subquery..causing a huge number of reads.

You should consider rewriting it to use inner joins...

If you're setting up a dynamic query, you might as well just replace the variables with the static values anyway. I also thought sp_exec or executesql ran in its own...

Converting the date like that is automatically an index/table scan, so I'm sure that piece is hurting performance. Convert your input to match your static data, not the other way...

Yep..there's actually a field for it:

select object_name(object_id) as [Table],Name as...

There's really no reason to set MSDB to full. I'm not even sure if you can, actually. Anyway I was just verifying to make sure it was set to simple.

Run...

Agreed..manually cleaning out all the hypothetical indexes it creates and never cleans up is also a bucket...

Is your MSDB on Full recovery or something? I've never seen an MSDB log that large.

Also run dbcc opentran on it to see if there's an open transaction causing it...