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,
LTThis is a multi-part message in MIME format.
--=_NextPart_000_0102_01C3D8F6.AE701000
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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
--=_NextPart_000_0102_01C3D8F6.AE701000
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_0102_01C3D8F6.AE701000--|||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...
> 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|||This is a multi-part message in MIME format.
--=_NextPart_000_025A_01C3D8F3.77BC2F60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
... 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 --=_NextPart_000_025A_01C3D8F3.77BC2F60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
... 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"
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
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_025A_01C3D8F3.77BC2F60--|||This is a multi-part message in MIME format.
--=_NextPart_000_0185_01C3D8FD.7D0E23B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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
--=_NextPart_000_0185_01C3D8FD.7D0E23B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Quentin Ran" =wrote in message news:ul0s1VS2DHA.556@.T=K2MSFTNGP11.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"
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
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_0185_01C3D8FD.7D0E23B0--|||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
>--Original Message--
>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
>|||This is a multi-part message in MIME format.
--=_NextPart_000_02A9_01C3D909.7F209280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Killing it is probably "safe". However, all work will be rolled back.
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
>--Original Message--
>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
>
--=_NextPart_000_02A9_01C3D909.7F209280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Killing it is probably "safe". =However, all work will be rolled back. Potentially, this can take just as =long as it has already been running.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_02A9_01C3D909.7F209280--|||This is a multi-part message in MIME format.
--=_NextPart_000_0035_01C3D9A7.BF40D9D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 days...
"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
--=_NextPart_000_0035_01C3D9A7.BF40D9D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 days...
"Quentin Ran" wrote in message news:ul0s1VS2DHA.556@.T=K2MSFTNGP11.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"
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
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_0035_01C3D9A7.BF40D9D0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0079_01C3D9A6.9C013830
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Right, but that's the tradeoff you make for running the defrag online =instead of rebuilding the index offline.
See the whitepaper at http://www.microsoft.com/technet/treeview/default.asp?url=3D/technet/prod=
technol/sql/maintain/optimize/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 days...
"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
--=_NextPart_000_0079_01C3D9A6.9C013830
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Right, but that's the tradeoff you make =for running the defrag online instead of rebuilding the index offline.
See the whitepaper at http://www.microsoft.=com/technet/treeview/default.asp?url=3D/technet/prodtechnol/sql/maintain/=optimize/ss2kidbp.asp
for more details.
Regards.
-- Paul RandalDev =Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no =rights.
"frankm"
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 days...
"Quentin Ran" wrote in message news:ul0s1VS2DHA.556@.T=K2MSFTNGP11.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"
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
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_0079_01C3D9A6.9C013830--|||FYI- I ended up canceling the job, it did a rollback for
about 10 minutes and that's it! Thanks for your help
again.
>--Original Message--
>Killing it is probably "safe". However, all work will be
rolled back.
>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
>>--Original Message--
>>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
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01D0_01C3DB5E.2C804B40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Makes you wonder what the problem was. Consider DBCC INDEXDEFRAG in the
future.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <anonymous@.discussions.microsoft.com> wrote in message
news:007f01c3db87$88c38a10$a301280a@.phx.gbl...
FYI- I ended up canceling the job, it did a rollback for
about 10 minutes and that's it! Thanks for your help
again.
>--Original Message--
>Killing it is probably "safe". However, all work will be
rolled back.
>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
>>--Original Message--
>>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
>
--=_NextPart_000_01D0_01C3DB5E.2C804B40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Makes you wonder what the problem =was. Consider DBCC INDEXDEFRAG in the future.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura" wrote in message news:007f01c3db87$88=c38a10$a301280a@.phx.gbl...FYI- I ended up canceling the job, it did a rollback for about 10 minutes =and that's it! Thanks for your help =again.>--Original Message-->Killing it is probably "safe". However, all =work will be rolled back.>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>>>"Laur=a"
--=_NextPart_000_01D0_01C3DB5E.2C804B40--
No comments:
Post a Comment