Prev: Timestamp default value
Next: running total column help
From: Joe on 16 Apr 2010 14:46
Hello all,
SQL server 2005 Std edition X64 SP3 CU6 running on
Server 2003 Ent Edition SP2
32 GB RAM
2 X quad core procs.
AM using a commonly available Stored Procedure to rebuild\reorg indexes
based on criterion. Unsure where the original came from but I got this copy
from the Web. Please consider this credit to the original writer and
modifiers.

I have made some slight changes to include materialized views.
My problem is on a relatively busy server the procedure will exit in the
middle of the execution with sometimes 10 databases done sometimes 40,
sometimes 5, etc.
On 5 other servers no issues.
I have watched the proc execute
THis proc is being called via a maintanence plan using exec SQL task.
Any advice, suggestions, is welcome.
TIA,
SQL execution task
exec master.dbo.usp_ReorgRebuildIndexes2
@databasename ='Client002_Cigna',
@FragCheck = 10.0,
@DensityCheck = 75.0,
@RebuildThreshold = 30.0,
@online = 0,
@runrebuild = 1,
@maxruntime =10800,
@maxdop = 0,
@MaxDaysofLog = 14

From: Joe on 16 Apr 2010 14:56

Not sure how to post the text of the proc as it keeps saying post too long
"Joe" wrote:

> Hello all,
> SQL server 2005 Std edition X64 SP3 CU6 running on
> Server 2003 Ent Edition SP2
> 32 GB RAM
> 2 X quad core procs.
> AM using a commonly available Stored Procedure to rebuild\reorg indexes
> based on criterion. Unsure where the original came from but I got this copy
> from the Web. Please consider this credit to the original writer and
> modifiers.
>
> I have made some slight changes to include materialized views.
> My problem is on a relatively busy server the procedure will exit in the
> middle of the execution with sometimes 10 databases done sometimes 40,
> sometimes 5, etc.
> On 5 other servers no issues.
> I have watched the proc execute
> THis proc is being called via a maintanence plan using exec SQL task.
> Any advice, suggestions, is welcome.
> TIA,
> SQL execution task
> exec master.dbo.usp_ReorgRebuildIndexes2
> @databasename ='Client002_Cigna',
> @FragCheck = 10.0,
> @DensityCheck = 75.0,
> @RebuildThreshold = 30.0,
> @online = 0,
> @runrebuild = 1,
> @maxruntime =10800,
> @maxdop = 0,
> @MaxDaysofLog = 14
>
From: Tibor Karaszi on 19 Apr 2010 04:47
Perhaps use CMDEXEC job step instead and call SQLCMD.EXE to run this?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Joe" <Joe(a)discussions.microsoft.com> wrote in message
news:180965FD-E1AF-4FCE-8529-EAC9F47BF1FB(a)microsoft.com...
> Hello all,
> SQL server 2005 Std edition X64 SP3 CU6 running on
> Server 2003 Ent Edition SP2
> 32 GB RAM
> 2 X quad core procs.
> AM using a commonly available Stored Procedure to rebuild\reorg indexes
> based on criterion. Unsure where the original came from but I got this
> copy
> from the Web. Please consider this credit to the original writer and
> modifiers.
>
> I have made some slight changes to include materialized views.
> My problem is on a relatively busy server the procedure will exit in the
> middle of the execution with sometimes 10 databases done sometimes 40,
> sometimes 5, etc.
> On 5 other servers no issues.
> I have watched the proc execute
> THis proc is being called via a maintanence plan using exec SQL task.
> Any advice, suggestions, is welcome.
> TIA,
> SQL execution task
> exec master.dbo.usp_ReorgRebuildIndexes2
> @databasename ='Client002_Cigna',
> @FragCheck = 10.0,
> @DensityCheck = 75.0,
> @RebuildThreshold = 30.0,
> @online = 0,
> @runrebuild = 1,
> @maxruntime =10800,
> @maxdop = 0,
> @MaxDaysofLog = 14
>
 | 
Pages: 1
Prev: Timestamp default value
Next: running total column help