Reindexing log shipped database

Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>

Reindexing log shipped database

Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tom
you want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon
|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>
|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ
|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>

Reindexing log shipped database

Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>

Reindexing has different behaviour in 2005

I noticed that when I use Maintenace plan wizard to create maintenance plan
to reindex a database, it runs different than it did on SQL2000.
On SQL2000, the job was running about 15 minutes on my 10GB database. During
that time there was a lot of disk activity (loging) and CPU was around 30%.
Now on SQL2005 if I run reindexing maintenance plan on the same 10 GB
database, it first utilizes CPU to maximum (one thread on hyper-threaded
CPU - 3.4GHz Xeon) and during that time there is no disk activity at all.
Profiler tells me that it selects data about database schema. That runs
about 30 minutes and after that actual reindexing begins that also couses a
lot of disk activity and lasts about 15 minutes (same as on SQL2000).
I'm a bit confused about that first 30 minutes when only CPU is doing all
the work. That part was not there in SQL2000. If it takes 30 minutes on 10GB
database, what will happen on 100GB or larger databases? Has anyone some
info if that behaviour has changed in SQL2005?
TomCan you tell us what command(s) the mp is running? You should be able to
script it out.
"Tom" wrote:

> I noticed that when I use Maintenace plan wizard to create maintenance pla
n
> to reindex a database, it runs different than it did on SQL2000.
> On SQL2000, the job was running about 15 minutes on my 10GB database. Duri
ng
> that time there was a lot of disk activity (loging) and CPU was around 30%
.
> Now on SQL2005 if I run reindexing maintenance plan on the same 10 GB
> database, it first utilizes CPU to maximum (one thread on hyper-threaded
> CPU - 3.4GHz Xeon) and during that time there is no disk activity at all.
> Profiler tells me that it selects data about database schema. That runs
> about 30 minutes and after that actual reindexing begins that also couses
a
> lot of disk activity and lasts about 15 minutes (same as on SQL2000).
> I'm a bit confused about that first 30 minutes when only CPU is doing all
> the work. That part was not there in SQL2000. If it takes 30 minutes on 10
GB
> database, what will happen on 100GB or larger databases? Has anyone some
> info if that behaviour has changed in SQL2005?
> Tom
>
>

reindex versus maintenance plan

Hi,
I would like to reindex all the tables in my database by using a job
instead of a maintenance plan. In a maintenance plan this is possible with
the checkbox: "reorganize data and index pages". How can I do the same thing
in a script without using DDBC REINDEX for every table?Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I would like to reindex all the tables in my database by using a job
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing
> in a script without using DDBC REINDEX for every table?
>
>|||Thanks,
This solved my problem.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> schreef in bericht
news:ePhnW9eIEHA.3376@.TK2MSFTNGP09.phx.gbl...
> Can use sp_msForEachTable system procedure for the same. Like
> EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I would like to reindex all the tables in my database by using a job
> > instead of a maintenance plan. In a maintenance plan this is possible
with
> > the checkbox: "reorganize data and index pages". How can I do the same
> thing
> > in a script without using DDBC REINDEX for every table?
> >
> >
> >
>|||Here is what I use to dbcc indexDefrag all indexes each night:
--***********************************************************************
DECLARE @.Table sysname
DECLARE @.Indid Int
DECLARE cur_tblFetch CURSOR FOR
SELECT Table_Name from information_Schema.tables where table_type = 'base
table'
OPEN cur_tblFetch
FETCH NEXT From cur_tblFetch INTO @.Table
While @.@.FETCH_STATUS = 0
BEGIN
DECLARE cur_indFetch CURSOR FOR
SELECT indid FROM SysIndexes WHERE id = Object_ID(@.Table) AND keycnt > 0
OPEN cur_indFetch
FETCH NEXT FROM cur_indFetch INTO @.Indid
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Indid <> 255
BEGIN
DBCC INDEXDEFRAG(Creditnet,@.Table,@.Indid) WITH NO_INFOMSGS
END
FETCH NEXT FROM cur_indFetch INTO @.Indid
END
CLOSE cur_IndFetch
DEALLOCATE cur_IndFetch
FETCH NEXT FROM cur_tblFetch INTO @.Table
END
CLOSE cur_tblFetch
DEALLOCATE cur_tblFetch
EXEC sp_updatestats
--***********************************************************************
cheers,
Greg Jackson
PDX, Oregon

reindex versus maintenance plan

Hi,
I would like to reindex all the tables in my database by using a job
instead of a maintenance plan. In a maintenance plan this is possible with
the checkbox: "reorganize data and index pages". How can I do the same thing
in a script without using DDBC REINDEX for every table?
Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I would like to reindex all the tables in my database by using a job
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing
> in a script without using DDBC REINDEX for every table?
>
>
|||Thanks,
This solved my problem.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> schreef in bericht
news:ePhnW9eIEHA.3376@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Can use sp_msForEachTable system procedure for the same. Like
> EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
with
> thing
>
|||Here is what I use to dbcc indexDefrag all indexes each night:
--************************************************** *********************
DECLARE @.Table sysname
DECLARE @.Indid Int
DECLARE cur_tblFetch CURSOR FOR
SELECT Table_Name from information_Schema.tables where table_type = 'base
table'
OPEN cur_tblFetch
FETCH NEXT From cur_tblFetch INTO @.Table
While @.@.FETCH_STATUS = 0
BEGIN
DECLARE cur_indFetch CURSOR FOR
SELECT indid FROM SysIndexes WHERE id = Object_ID(@.Table) AND keycnt > 0
OPEN cur_indFetch
FETCH NEXT FROM cur_indFetch INTO @.Indid
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Indid <> 255
BEGIN
DBCC INDEXDEFRAG(Creditnet,@.Table,@.Indid) WITH NO_INFOMSGS
END
FETCH NEXT FROM cur_indFetch INTO @.Indid
END
CLOSE cur_IndFetch
DEALLOCATE cur_IndFetch
FETCH NEXT FROM cur_tblFetch INTO @.Table
END
CLOSE cur_tblFetch
DEALLOCATE cur_tblFetch
EXEC sp_updatestats
--************************************************** *********************
cheers,
Greg Jackson
PDX, Oregon
|||I have been looking for a resolution to this as well. Thank you for this information! I have another question - how do I write this if I need to do a fill factor of 90?
-- Vinodk wrote: --
Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing[vbcol=seagreen]
> in a script without using DDBC REINDEX for every table?

reindex versus maintenance plan

Hi,
I would like to reindex all the tables in my database by using a job
instead of a maintenance plan. In a maintenance plan this is possible with
the checkbox: "reorganize data and index pages". How can I do the same thing
in a script without using DDBC REINDEX for every table?Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I would like to reindex all the tables in my database by using a job
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing
> in a script without using DDBC REINDEX for every table?
>
>|||Thanks,
This solved my problem.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> schreef in bericht
news:ePhnW9eIEHA.3376@.TK2MSFTNGP09.phx.gbl...
> Can use sp_msForEachTable system procedure for the same. Like
> EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
with
> thing
>|||Here is what I use to dbcc indexDefrag all indexes each night:
-- ****************************************
*******************************
DECLARE @.Table sysname
DECLARE @.Indid Int
DECLARE cur_tblFetch CURSOR FOR
SELECT Table_Name from information_Schema.tables where table_type = 'base
table'
OPEN cur_tblFetch
FETCH NEXT From cur_tblFetch INTO @.Table
While @.@.FETCH_STATUS = 0
BEGIN
DECLARE cur_indFetch CURSOR FOR
SELECT indid FROM SysIndexes WHERE id = Object_ID(@.Table) AND keycnt > 0
OPEN cur_indFetch
FETCH NEXT FROM cur_indFetch INTO @.Indid
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Indid <> 255
BEGIN
DBCC INDEXDEFRAG(Creditnet,@.Table,@.Indid) WITH NO_INFOMSGS
END
FETCH NEXT FROM cur_indFetch INTO @.Indid
END
CLOSE cur_IndFetch
DEALLOCATE cur_IndFetch
FETCH NEXT FROM cur_tblFetch INTO @.Table
END
CLOSE cur_tblFetch
DEALLOCATE cur_tblFetch
EXEC sp_updatestats
-- ****************************************
*******************************
cheers,
Greg Jackson
PDX, Oregon|||I have been looking for a resolution to this as well. Thank you for this in
formation! I have another question - how do I write this if I need to do a
fill factor of 90?
-- Vinodk wrote: --
Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing[vbcol=seagreen]
> in a script without using DDBC REINDEX for every table?

Reindex Maintenance Plan starts with 15hours delay

Hey there,

on a SS2005 Build 3152 i've scheduled a mp with reindexing a 120 GB db. The mp is scheduled to start every sunday at 08.00am.

In the agents history log i can see that it start its job at 8.00am, but the first reindex starts at 11:xxpm. In the mp history log i can see that it starts at 11:xx pm.

Why do I have such a big delay? Is the job waiting because of some locks, or does the job have to check some things before reindexing? But 15 hours? Is it a bug in mp?

Any ideas?

Thanks in advance
tobiasHi,

does nobody have an idea?

regards
tobias
|||

I would like to know what options you have selected on the MP when you have scheduled this job. Also I would ask whether you have applied SP2 for SQL.

120GB database I would expect it will take some time to finish the maintenance tasks and also check what other processes are running.

|||SP2 and http://support.microsoft.com/kb/933097/en-us are applied.

Other jobs are regular transaction-protocol-backups. The workload on sundays is very low, just a view automatic tasks write to the db.

The MP has to tasks.

1. Reindex
o Connection: Local Server
o Databasese: The ERP Database
o Objects: Table & Views
o Selection:
o % Freespace per Page: 10%
o Sort in TempDB: No
o Index Online: No

2. History Cleanup
o cleans everything older than 4 Month

reindex maintenance plan

We have created a maitenance plan that reindex all our tables. Usually it
works, ocassionaly it halts the entire system moments after starting. I
assume this is a reindex issue and not a maintenance plan issue. Any
suggestions on where I can being my search for a fix?
TIA
Paul
not exactly sure what you mean by "Halts Entire System". However, the
maintenance plan wizard uses DBCC DBReindex for index maintenance. DBReindex
places exclusive table locks on tables being defragged.
You may want to consider using your own custom index maintenance routines
and implementing index maintenance via "DBCC IndexDefrag" instead.
cheers
Greg Jackson
Portland, OR
|||The MP uses DBCC DBREINDEX which will attempt to use all available
processors to do the work in as short a time as possible. It will use 100%
or close to that amount of all the processors for some period of time
throughout the process. While it is reindexing a table that particular table
is off line for the duration of the reindex process on that table. If the
use of all the processors is too much of a load you can set thee MAXDOP at
the server level to limit how many are used by any one source. Of coarse
this type of activity should be done when there is little load on the
server.
Andrew J. Kelly SQL MVP
"itchicago" <itchicago@.discussions.microsoft.com> wrote in message
news:05750327-FB10-4E25-82E6-650381C0BCDC@.microsoft.com...
> We have created a maitenance plan that reindex all our tables. Usually it
> works, ocassionaly it halts the entire system moments after starting. I
> assume this is a reindex issue and not a maintenance plan issue. Any
> suggestions on where I can being my search for a fix?
> TIA
> Paul
|||To add to Greg's reply, you should read the whitepaper below which explains
when and how to get rid of index fragmentation. Usually, rebuilding all
indexes in a database is a wasted operation and you can be much more
selective. DBREINDEX will take a X lock (i.e. unavailable for read/write) on
a table if the clustered index is being rebuilt, but only an S lock
(unavailable for write) on the table if a non-clustered index is being
rebuilt. You can use Example E that I wrote for BOL for DBCC SHOWCONTIG as a
good starting point for a custom defrag job.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#qDYI9SDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> not exactly sure what you mean by "Halts Entire System". However, the
> maintenance plan wizard uses DBCC DBReindex for index maintenance.
DBReindex
> places exclusive table locks on tables being defragged.
> You may want to consider using your own custom index maintenance routines
> and implementing index maintenance via "DBCC IndexDefrag" instead.
>
> cheers
> Greg Jackson
> Portland, OR
>

reindex maintenance plan

We have created a maitenance plan that reindex all our tables. Usually it
works, ocassionaly it halts the entire system moments after starting. I
assume this is a reindex issue and not a maintenance plan issue. Any
suggestions on where I can being my search for a fix?
TIA
Paulnot exactly sure what you mean by "Halts Entire System". However, the
maintenance plan wizard uses DBCC DBReindex for index maintenance. DBReindex
places exclusive table locks on tables being defragged.
You may want to consider using your own custom index maintenance routines
and implementing index maintenance via "DBCC IndexDefrag" instead.
cheers
Greg Jackson
Portland, OR|||The MP uses DBCC DBREINDEX which will attempt to use all available
processors to do the work in as short a time as possible. It will use 100%
or close to that amount of all the processors for some period of time
throughout the process. While it is reindexing a table that particular table
is off line for the duration of the reindex process on that table. If the
use of all the processors is too much of a load you can set thee MAXDOP at
the server level to limit how many are used by any one source. Of coarse
this type of activity should be done when there is little load on the
server.
Andrew J. Kelly SQL MVP
"itchicago" <itchicago@.discussions.microsoft.com> wrote in message
news:05750327-FB10-4E25-82E6-650381C0BCDC@.microsoft.com...
> We have created a maitenance plan that reindex all our tables. Usually it
> works, ocassionaly it halts the entire system moments after starting. I
> assume this is a reindex issue and not a maintenance plan issue. Any
> suggestions on where I can being my search for a fix?
> TIA
> Paul|||To add to Greg's reply, you should read the whitepaper below which explains
when and how to get rid of index fragmentation. Usually, rebuilding all
indexes in a database is a wasted operation and you can be much more
selective. DBREINDEX will take a X lock (i.e. unavailable for read/write) on
a table if the clustered index is being rebuilt, but only an S lock
(unavailable for write) on the table if a non-clustered index is being
rebuilt. You can use Example E that I wrote for BOL for DBCC SHOWCONTIG as a
good starting point for a custom defrag job.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#qDYI9SDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> not exactly sure what you mean by "Halts Entire System". However, the
> maintenance plan wizard uses DBCC DBReindex for index maintenance.
DBReindex
> places exclusive table locks on tables being defragged.
> You may want to consider using your own custom index maintenance routines
> and implementing index maintenance via "DBCC IndexDefrag" instead.
>
> cheers
> Greg Jackson
> Portland, OR
>

reindex maintenance plan

We have created a maitenance plan that reindex all our tables. Usually it
works, ocassionaly it halts the entire system moments after starting. I
assume this is a reindex issue and not a maintenance plan issue. Any
suggestions on where I can being my search for a fix?
TIA
Paulnot exactly sure what you mean by "Halts Entire System". However, the
maintenance plan wizard uses DBCC DBReindex for index maintenance. DBReindex
places exclusive table locks on tables being defragged.
You may want to consider using your own custom index maintenance routines
and implementing index maintenance via "DBCC IndexDefrag" instead.
cheers
Greg Jackson
Portland, OR|||The MP uses DBCC DBREINDEX which will attempt to use all available
processors to do the work in as short a time as possible. It will use 100%
or close to that amount of all the processors for some period of time
throughout the process. While it is reindexing a table that particular table
is off line for the duration of the reindex process on that table. If the
use of all the processors is too much of a load you can set thee MAXDOP at
the server level to limit how many are used by any one source. Of coarse
this type of activity should be done when there is little load on the
server.
--
Andrew J. Kelly SQL MVP
"itchicago" <itchicago@.discussions.microsoft.com> wrote in message
news:05750327-FB10-4E25-82E6-650381C0BCDC@.microsoft.com...
> We have created a maitenance plan that reindex all our tables. Usually it
> works, ocassionaly it halts the entire system moments after starting. I
> assume this is a reindex issue and not a maintenance plan issue. Any
> suggestions on where I can being my search for a fix?
> TIA
> Paul|||To add to Greg's reply, you should read the whitepaper below which explains
when and how to get rid of index fragmentation. Usually, rebuilding all
indexes in a database is a wasted operation and you can be much more
selective. DBREINDEX will take a X lock (i.e. unavailable for read/write) on
a table if the clustered index is being rebuilt, but only an S lock
(unavailable for write) on the table if a non-clustered index is being
rebuilt. You can use Example E that I wrote for BOL for DBCC SHOWCONTIG as a
good starting point for a custom defrag job.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#qDYI9SDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> not exactly sure what you mean by "Halts Entire System". However, the
> maintenance plan wizard uses DBCC DBReindex for index maintenance.
DBReindex
> places exclusive table locks on tables being defragged.
> You may want to consider using your own custom index maintenance routines
> and implementing index maintenance via "DBCC IndexDefrag" instead.
>
> cheers
> Greg Jackson
> Portland, OR
>

Reindex and Error 1105

I recently created a new Maintenance Plan to reindex my database. Under the
Optimizations tab I only have Reorganize pages witht the original amount of
free space checked but I still received the 1105 error(I previously had the
Remove unused space from database files checked and received the 1105 error).
I noticed the data file grew from 160GB to 320GB(with ~160GB marked as free
space) after the failed reindex attempt. Please advise how can I fix this
problem. Thanks.
Sometimes autogrow isn't fast enough. In that cases, you need to pre-allocate storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:24455A9F-A5CD-45E5-9B89-A3D75FB1BD2D@.microsoft.com...
>I recently created a new Maintenance Plan to reindex my database. Under the
> Optimizations tab I only have Reorganize pages witht the original amount of
> free space checked but I still received the 1105 error(I previously had the
> Remove unused space from database files checked and received the 1105 error).
> I noticed the data file grew from 160GB to 320GB(with ~160GB marked as free
> space) after the failed reindex attempt. Please advise how can I fix this
> problem. Thanks.
|||When you saw 1105 error, did you check if your disk is full or the data file
reaches to its maximum specified size?
The data file growth is expected, when a index is rebuilding, SQL Server has
to keep the old index around. Hence, the space requirement is doubled (plus
additional space for sorting and logging). SQL Server won't truncate the
file automatically, therefore you have a bigger data file after reindex (it
does not matter if the reindex succeeds or fails).
There is no need to worry about the too much free space problem. Since you
have "Remove unused space from database files" checked, DBCC SHRINKDATABASE
will start and truncate the data file sometime later.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:24455A9F-A5CD-45E5-9B89-A3D75FB1BD2D@.microsoft.com...
> I recently created a new Maintenance Plan to reindex my database. Under
the
> Optimizations tab I only have Reorganize pages witht the original amount
of
> free space checked but I still received the 1105 error(I previously had
the
> Remove unused space from database files checked and received the 1105
error).
> I noticed the data file grew from 160GB to 320GB(with ~160GB marked as
free
> space) after the failed reindex attempt. Please advise how can I fix this
> problem. Thanks.
|||I advise you to read the whitepaper below that will explain a bunch about
fragmentation, what to do about it, and when you really need to do anything
about it. Also, be aware that rebuilding an index requires double the index
size (as a new index has to be built before the old one can be dropped).
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eJGECQ0nEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Sometimes autogrow isn't fast enough. In that cases, you need to
pre-allocate storage.[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ling" <Ling@.discussions.microsoft.com> wrote in message
> news:24455A9F-A5CD-45E5-9B89-A3D75FB1BD2D@.microsoft.com...
the[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
error).[vbcol=seagreen]
free[vbcol=seagreen]
this
>