Try the following link...you may have to search for follow-up articles as there were some revisions/bug fixes to this code.

http://www.sqlteam.com/item.asp?ItemID=2955

to LauraS

"I suppose it could take that long depending on your hardware, how busy your db is and if there are any indexes involved"

If you can answer some of the...

"one more question why xp_cmdshell  is recommended to be disabled?"

Because it runs under the security priviliges of the SQL Account....and can open a security hole on your server.  Remember in...

Only thing I can think of would be to write triggers for ALL tables for all UPDATE/INSERT/DELETE statements to disallow same when the user comes from a specific IP/HOSTNAME.

options...

1. re-design the SP to guarantee to run within 1 minute.

2. re-structure your hardware to guarantee to run within 1 minute.

3. re-design your SP/Application to live with this SP not-running every...

Many thanks for the (re)ward. I was an early adopter of PromptSQL (@ 20$ it was worth a try), but my usage had vastly diminished before the Red-Gate...

I think this is related to using NT Authentication instead of SQL Authentication when signing into SQL.  Experimentation on a TEST box would clarify same.

Personally...I'd also keep the PDF's out of SQL, and just store references to their actual LAN location.  Putting them in SQL generates a large image management issue into SQL (backup/restore/read...

The issue of CACHE affecting the runtime's + physical read etc also needs to be considered.  True testing would involve using the DBCC DROPCLEANBUFFERS + DBCC FREEPROCCACHE commands between runs.

I'd...

Don't know.....but if you can locate some links on SQLTeam.com....try asking the people who provided the answers.  They have more direct experience of the problem and the solution.

Also....have you tried...

Have you recompiled the PROC.

Also looking into getting the actual execution plan.

And finally, you may be experiencing "Parameter Sniffing"...if you do a search of this site (and SQLTeam.com) you will...

"...nvarchar so Clustered Index is not recommended"

Whether or not the col is nvarchar or not is irrelevent for the Clustered Index...it's the cardinality of the column that affects is suitability...ie...

you have a lot of clustered-index scans....which I think are worse than tablescans....you want index seeks...for real performance.  only way to do that is to rework the wildcards....

using the likes...

1.  have a look at the execution plan, and try to see why indices aren't being fully used.

2.  your LIKE statements....won't use indices...especially with leading %

3.  qualify your tables with...

Don't know about that....but a workaround would be to have the jobs log their progress (finishing status) outside of SQL...and give your user access to that.