I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LTRun sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather
using DBCC INDEXDEFRAG so that you can defrag online and allow your users
access to the table.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||sp_who2 will show the processes that are building the Index. You may see 4
or more processes at work if you have a box with multiple processes.
Every time you run sp_who2, the values for the processes columns "DiskIO"
and "CPUTime" will be seen to increase. This indicates that the processes
are still running and alive.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
quote:|||... and if you do feel needing to reindex, run the indexing script, to rein
> Hi,
> I have a very large table (400 Gig) that I am in the middle of
> reindexing, it has been 60 hours. I have reindexed this table before
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually doing anything or
> if it is stuck?
> Please if anyone has any suggestions, let me know. The users are
> complaining about this table not being availiable during business
> hours!!!
> Thanks,
> LT
dex some of them at one weekend, the next portion the following weekend etc.
Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:eFiDyCS2DHA.34
96@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather u
sing DBCC INDEXDEFRAG so that you can defrag online and allow your users acc
ess to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message news:4fdf6507.0401120718.11fe
ddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||Yep. Taking that a little further, consider horizontal partitioning. Break
up the table into more manageable chunks and use a partitioned view. It
could very well be that only one partition need reindexing.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Quentin Ran" <ab@.who.com> wrote in message
news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
... and if you do feel needing to reindex, run the indexing script, to
reindex some of them at one weekend, the next portion the following weekend
etc. Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with
the index rebuild. Also, in future, consider not doing a rebuild but,
rather using DBCC INDEXDEFRAG so that you can defrag online and allow your
users access to the table.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||Thanks for your help. I did notice the DISK IO moving up,
but not by much. And I haven't seen it move in the past 30
minutes. Do you know what the consequences of killing the
job will be? Will the table get corrupt? How long will it
take to rollback?
Thanks,
LT
quote:
>--Original Message--
>Yep. Taking that a little further, consider horizontal
partitioning. Break
quote:
>up the table into more manageable chunks and use a
partitioned view. It
quote:
>could very well be that only one partition need
reindexing.
quote:
>--
>Tom
>----
--
quote:
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Quentin Ran" <ab@.who.com> wrote in message
>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>... and if you do feel needing to reindex, run the
indexing script, to
quote:
>reindex some of them at one weekend, the next portion the
following weekend
quote:
>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
about index defrag.
quote:
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
> Run sp_who2 and see if DiskIO is increasing for the
SPID associated with
quote:
>the index rebuild. Also, in future, consider not doing a
rebuild but,
quote:
>rather using DBCC INDEXDEFRAG so that you can defrag
online and allow your
quote:
>users access to the table.
> --
> Tom
> ---
--
quote:
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Laura" <lntaggart@.yahoo.com> wrote in message
>news:4fdf6507.0401120718.11feddb@.posting.google.com...
> Hi,
> I have a very large table (400 Gig) that I am in the
middle of
quote:
> reindexing, it has been 60 hours. I have reindexed this
table before
quote:
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually
doing anything or
quote:
> if it is stuck?
> Please if anyone has any suggestions, let me know. The
users are
quote:
> complaining about this table not being availiable
during business
quote:|||Killing it is probably "safe". However, all work will be rolled back.
> hours!!!
> Thanks,
> LT
>
Potentially, this can take just as long as it has already been running.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...
Thanks for your help. I did notice the DISK IO moving up,
but not by much. And I haven't seen it move in the past 30
minutes. Do you know what the consequences of killing the
job will be? Will the table get corrupt? How long will it
take to rollback?
Thanks,
LT
quote:
>--Original Message--
>Yep. Taking that a little further, consider horizontal
partitioning. Break
quote:
>up the table into more manageable chunks and use a
partitioned view. It
quote:
>could very well be that only one partition need
reindexing.
quote:
>--
>Tom
>----
--
quote:
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Quentin Ran" <ab@.who.com> wrote in message
>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>... and if you do feel needing to reindex, run the
indexing script, to
quote:
>reindex some of them at one weekend, the next portion the
following weekend
quote:
>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
about index defrag.
quote:
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
> Run sp_who2 and see if DiskIO is increasing for the
SPID associated with
quote:
>the index rebuild. Also, in future, consider not doing a
rebuild but,
quote:
>rather using DBCC INDEXDEFRAG so that you can defrag
online and allow your
quote:
>users access to the table.
> --
> Tom
> ---
--
quote:
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Laura" <lntaggart@.yahoo.com> wrote in message
>news:4fdf6507.0401120718.11feddb@.posting.google.com...
> Hi,
> I have a very large table (400 Gig) that I am in the
middle of
quote:
> reindexing, it has been 60 hours. I have reindexed this
table before
quote:
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually
doing anything or
quote:
> if it is stuck?
> Please if anyone has any suggestions, let me know. The
users are
quote:
> complaining about this table not being availiable
during business
quote:|||Good advise ... Because IndexDefrag runs in short fully logged transactions
> hours!!!
> Thanks,
> LT
>
, the log file could get really big!
The script is in books online - it does the defrag by percent fragmentation
- so start at something like 70% and work your way down over a period of day
s...
"Quentin Ran" <ab@.who.com> wrote in message news:ul0s1VS2DHA.556@.TK2MSFTNGP1
1.phx.gbl...
.. and if you do feel needing to reindex, run the indexing script, to reind
ex some of them at one weekend, the next portion the following weekend etc.
Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:eFiDyCS2DHA.34
96@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather u
sing DBCC INDEXDEFRAG so that you can defrag online and allow your users acc
ess to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message news:4fdf6507.0401120718.11fe
ddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||Right, but that's the tradeoff you make for running the defrag online instea
d of rebuilding the index offline.
See the whitepaper at
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
for more details.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message news:e4YBLod2DHA
.3216@.TK2MSFTNGP11.phx.gbl...
Good advise ... Because IndexDefrag runs in short fully logged transactions
, the log file could get really big!
The script is in books online - it does the defrag by percent fragmentation
- so start at something like 70% and work your way down over a period of day
s...
"Quentin Ran" <ab@.who.com> wrote in message news:ul0s1VS2DHA.556@.TK2MSFTNGP1
1.phx.gbl...
.. and if you do feel needing to reindex, run the indexing script, to reind
ex some of them at one weekend, the next portion the following weekend etc.
Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:eFiDyCS2DHA.34
96@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather u
sing DBCC INDEXDEFRAG so that you can defrag online and allow your users acc
ess to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message news:4fdf6507.0401120718.11fe
ddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||FYI- I ended up canceling the job, it did a rollback for
about 10 minutes and that's it! Thanks for your help
again.
quote:
>--Original Message--
>Killing it is probably "safe". However, all work will be
rolled back.
quote:
>Potentially, this can take just as long as it has already
been running.
quote:
>--
>Tom
>----
--
quote:
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Laura" <lntaggart@.yahoo.com> wrote in message
>news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...
>Thanks for your help. I did notice the DISK IO moving up,
>but not by much. And I haven't seen it move in the past 30
>minutes. Do you know what the consequences of killing the
>job will be? Will the table get corrupt? How long will it
>take to rollback?
>Thanks,
>LT
>
>partitioning. Break
>partitioned view. It
>reindexing.
-[QUOTE]
>--
>indexing script, to
>following weekend
>about index defrag.
message[QUOTE]
>SPID associated with
>rebuild but,
>online and allow your
-[QUOTE]
>--
>middle of
>table before
>doing anything or
>users are
>during business
>
No comments:
Post a Comment