Sorry to bother you, but could you please send the 2nd attachment? You did not send one in the previous email.
Thanks in advance,
Albert Frazer
Home Decor Products, Inc
www.hdpi.com
732-593-3637 (office)
732-570-4465 (cell)
-----Original Message-----
From: Dwarakanathan Kavitha [mailto:mssqldba-ezmlmshield-x30070805.[Email address protected]
Sent: Wednesday, December 26, 2007 2:52 PM
To: LazyDBA Discussion
Subject: RE: Syntax error
I do not know how big/small your databases are. But according to me, it
is not a good idea to simply do an ONLINE for ALL indexes on ALL tables.
Certain criteria are to be met in order to perform the same. Otherwise,
it is simply going to degrade the performance or increase the
fragmentation. I have attached scripts that we follow here. Based on
certain conditions the script determines whether or not an ONLINE index
need to performed.
1) create the two stored procs on msdb that I have attached here. The
stored procs are self explanatory with adequate comments inside.
2)Execute the following using SQLCMD within a batch file/SQL Agent job
or by any means in accordance with your env. You can adjust the values
according to your needs.=20
use msdb
exec sp_RebuildIndexDb
@maxfrag=3D30,@maxdensity=3D80,@databasename=3DNULL,@dbtype=3D'A',@schem
a=
name=3DNU
LL,@tablename=3DNULL,@online_rebuild_flag=3D'N'=20
I am sending the attachments one after the other.
-Kavitha
-----Original Message-----
From: Mario Martinez
[mailto:mssqldba-ezmlmshield-x34828484.[Email address protected]
Sent: Wednesday, December 26, 2007 2:14 PM
To: LazyDBA Discussion
Subject: RE: Syntax error
I'm using SQL 2005 and I the idea is to run a script for maintenance of
my indices that is going to run at the end of the day Something like
this:
declare @tablename char(255)
DECLARE t_cursor CURSOR for
select 'ALTER INDEX ALL ON ' + name + ' REBUILD WITH (ONLINE =
ON)'
from
sysobjects
where xtype = 'U'
set NOCOUNT on
open t_cursor
FETCH NEXT FROM t_cursor INTO @tablename
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
exec (@tablename)
end
FETCH NEXT FROM t_cursor into @tablename
end
DEALLOCATE t_cursor
Regards
Mario
-----Original Message-----
From: Pete
[mailto:mssqldba-ezmlmshield-x51185639.[Email address protected]
Sent: Wednesday, December 26, 2007 12:30 PM
To: LazyDBA Discussion
Subject: Re: Syntax error
Good catch..
btw, I mis-typed earlier..must be the excess eggnog in my system. The
prefix convention is [Tablename].IndexName, not DatabaseName.TableName
Happy New Year everyone.
Bellizzi Christopher Joseph wrote:
> Yeah good catch guys didn't get the pubs reference until you said
>
> something.
>
> Alter will work on Developer edition as it's essentially enterprise
>
> anyways.
>
>
>
> PAYCHEX
>
> Christopher Bellizzi
>
> Information Technology * Database Administration
>
> 675 Basket Road * Webster, New York 14580
>
> (585) 216-0670 * [Email address protected]
>
>
>
>
>
> -----Original Message-----
>
> From: Dwarakanathan Kavitha
>
> [mailto:mssqldba-ezmlmshield-x34993759.[Email address protected]
>
> Sent: Wednesday, December 26, 2007 12:20 PM
>
> To: LazyDBA Discussion
>
> Subject: RE: Syntax error
>
>
>
> Make sure of few things before you use the following ALTER INDEX
>
> command.
>
>
>
> 1) Primarily, I believe you are using only SQL 2005. Since the example
>
> you have mentioned here is using pubs db (which is not available by
>
> default in SQL 2005), I had this suspicion of whether you are
executing
>
> the cmd in SQL 2005 or SQL 2000.
>
>
>
> 2) If you are using sql 2005, then ONLINE = ON will work only on
>
> Enterprise Edition
>
>
>
> 3)REBUILD with ONLINE = ON will not succeed if the table has
>
> 1) XML index
>
> 2) Large object data type columns: image, text, ntext,
>
> varchar(max), nvarchar(max), varbinary(max), and xml.
>
>
>
>
>
> Kavitha Dwarakanathan
>
> 908-563-2420
>
> Sr.Database Administrator/AVP
>
> 24x7 hotline: 908-563-4364
>
> CMB Equities DBA Group
>
> Citi Markets & Banking (CMB) | Technology
>
>
>
> -----Original Message-----
>
> From: Mario Martinez
>
> [mailto:mssqldba-ezmlmshield-x54947819.[Email address protected]
>
> Sent: Wednesday, December 26, 2007 11:10 AM
>
> To: LazyDBA Discussion
>
> Subject: RE: Syntax error
>
>
>
> I Still stuck with the same error:
>
> Incorrect syntax
>
> near the keyword 'INDEX'.
>
>
>
>
>
> ALTER INDEX ALL ON pubs.dbo.titleauthor REBUILD WITH (FILLFACTOR =
>
> 80,SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = OFF);
>
>
>
> Regards
>
>
>
>
>
>
>
> -----Original Message-----
>
> From: Pete
>
> [mailto:mssqldba-ezmlmshield-x90379321.[Email address protected]
>
> Sent: Wednesday, December 26, 2007 10:57 AM
>
> To: LazyDBA Discussion
>
> Subject: Re: Syntax error
>
>
>
> When changing, rebuilding, or dropping an index the database name has
to
>
>
>
> prefix the index such as
>
> pubs.titleauthor
>
>
>
> Try adding the database as a prefix to the table and it should work
>
> fine.
>
>
>
> Mario Martinez wrote:
>
>
>> Hi people:
>>
>
>
>> Can any body find the syntax error I hace in this sentence:
>>
>
>
>
>
>> ALTER INDEX ALL ON titleauthor REBUILD WITH (ONLINE = ON,FILLFACTOR =
>>
>
>
>> 80,SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);
>>
>
>
>
>
>> Regards
>>
>
>
>> Mark
>>
>
>
>
>
>
>
>
>
>
>
>> ---------------------------------------------------------------------
>>
>
>
>> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
post
>>
>
>
>
>
>> a dba job: http://jobs.lazydba.com To subscribe :
>>
>
>
>> http://www.LazyDBA.com To unsubscribe:
>>
>
>
>> http://www.lazydba.com/unsubscribe.html
>>
>
>
>
>
>
>
>>
>>
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
>
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
a
>
> dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
>
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
>
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
a
>
> dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
>
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
>
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>
> To post a dba job: http://jobs.lazydba.com
>
> To subscribe : http://www.LazyDBA.com
>
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
>
>
>
>
> -----------------------------------------
>
> The information contained in this message may be privileged,
> confidential, and protected from disclosure. If the reader of this
> message is not the intended recipient, or any employee or agent
> responsible for delivering this message to the intended recipient, you
> are hereby notified that any dissemination, distribution, or copying
> of this communication is strictly prohibited. If you have received
> this communication in error, please notify us immediately by replying
> to the message and deleting it from your computer.
>
>
>
> Thank you. Paychex, Inc.
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
> a dba job: http://jobs.lazydba.com To subscribe :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
>
>
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page