Iâ've asked several â'expertsâ' and none of them have a way to do this. Do you
know of any way to do this while the users are on line (without using DBCC
Indexdefrag)? As I understand the Indexdefrag, eventually youâ'll have to
rebuild the indexes anyway.
I have 3 databases and each of them has several very large tables (one of
them exceeds 182 million rows). In order to truly be 7x24, I need to find a
way to rebuild the indexes while the users are in the database.
Thanks in advance for any help you can provide,
Regards,
GeorgeThis is a multi-part message in MIME format.
--090901060608010606000008
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
You can't with SQL 2000. The best you can do without disrupting users
is to use DBCC INDEXDEFRAG, which will reorder pages in the index but
not shuffle extents so that they're contiguous. It will improve the
logical fragmentation as reported by DBCC SHOWCONTIG but not the extent
fragmentation.
I guess your options are 1) to upgrade to SQL 2005, which allows online
index rebuild operations (although there are a few caveats around those,
for example they're only available with the Enterprise Edition, can't be
done on clustered indexes when the underlying table contains LOB data,
can't be done on nonclustered indexes that contain LOB columns...), or
2) have an outage while you rebuild your clustered index on your large
tables (nonclustered indexes are normally not as big a problem).
Personally, I'm looking for a good excuse to rebuild one of our big
clustered indexes online in SQL 2005 just for the fun of it...and
because we can. <g>
--
*mike hodgson*
http://sqlnerd.blogspot.com
George wrote:
>Iâ've asked several â'expertsâ' and none of them have a way to do this. Do you
>know of any way to do this while the users are on line (without using DBCC
>Indexdefrag)? As I understand the Indexdefrag, eventually youâ'll have to
>rebuild the indexes anyway.
>I have 3 databases and each of them has several very large tables (one of
>them exceeds 182 million rows). In order to truly be 7x24, I need to find a
>way to rebuild the indexes while the users are in the database.
>Thanks in advance for any help you can provide,
>Regards,
>George
>
--090901060608010606000008
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can't with SQL 2000. The best you can do without disrupting
users is to use DBCC INDEXDEFRAG, which will reorder pages in the index
but not shuffle extents so that they're contiguous. It will improve
the logical fragmentation as reported by DBCC SHOWCONTIG but not the
extent fragmentation.<br>
<br>
I guess your options are 1) to upgrade to SQL 2005, which allows online
index rebuild operations (although there are a few caveats around
those, for example they're only available with the Enterprise Edition,
can't be done on clustered indexes when the underlying table contains
LOB data, can't be done on nonclustered indexes that contain LOB
columns...), or 2) have an outage while you rebuild your clustered
index on your large tables (nonclustered indexes are normally not as
big a problem).<br>
<br>
Personally, I'm looking for a good excuse to rebuild one of our big
clustered indexes online in SQL 2005 just for the fun of it...and
because we can. <g><br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
George wrote:
<blockquote cite="mid7CD1AF2F-2BCD-45A9-BA06-292B8A159885@.microsoft.com"
type="cite">
<pre wrap="">Iâ've asked several â'expertsâ' and none of them have a way to do this. Do you
know of any way to do this while the users are on line (without using DBCC
Indexdefrag)? As I understand the Indexdefrag, eventually youâ'll have to
rebuild the indexes anyway.
I have 3 databases and each of them has several very large tables (one of
them exceeds 182 million rows). In order to truly be 7x24, I need to find a
way to rebuild the indexes while the users are in the database.
Thanks in advance for any help you can provide,
Regards,
George
</pre>
</blockquote>
</body>
</html>
--090901060608010606000008--
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment