Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to have the database release the space. I see no reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with another method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXDEFRAG.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
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:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> > Hi,
> >
> > I have deleted 100K records from a table in a database. I ran the DBCC
shrinkfile & DBCC shrinkdatabase to
> have the database release the space. I see no reduction in the database
size.
> >
> > I have run the following;
> > DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> > GO
> > DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> > GO
> > BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> > GO
> > DBCC SHRICKDATABASE (VIRTEN, 20)
> > GO
> > Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> > GO
> >
> > Please can anyone tell me, where I am making a mistake, or provide me
with another method to release the
> space.
> >
> > Many thanks.
> >
> > Daniel
>
Showing posts with label ran. Show all posts
Showing posts with label ran. Show all posts
Friday, March 30, 2012
release database unused space
Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrin
kfile & DBCC shrinkdatabase to have the database release the space. I see no
reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with a
nother method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXD
EFRAG.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile
& DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.">
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another
method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
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:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
shrinkfile & DBCC shrinkdatabase to[vbcol=seagreen]
> have the database release the space. I see no reduction in the database
size.
with another method to release the[vbcol=seagreen]
> space.
>sql
I have deleted 100K records from a table in a database. I ran the DBCC shrin
kfile & DBCC shrinkdatabase to have the database release the space. I see no
reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with a
nother method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXD
EFRAG.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile
& DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.">
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another
method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
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:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
shrinkfile & DBCC shrinkdatabase to[vbcol=seagreen]
> have the database release the space. I see no reduction in the database
size.
with another method to release the[vbcol=seagreen]
> space.
>sql
Release A Install Problems
We have packaged MSDE 2000 release A with our applicaiton. A user ran the
install, but when MSDE goes to start it says, MSDE is either currport or has
been tampered with, please uninstall and reinstall MSDE, invalid package ID.
We go to unstill MSDE and it says MSDE is not installed... We tried
reinstalling, since it thought it wasn't installed and when the installer
starts, we get the same invaled package id error... Does anyone have any
ideas, or thoughts on what is happening or what I can do to fix this? Thanks,
Brian
Did you define a strong password for SA using the SAPWD parameter? MSDE SP3A
setup.exe will not install a new instance without it.
joe.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C8D287B9-BCD6-4423-AF48-21455901D596@.microsoft.com...
> We have packaged MSDE 2000 release A with our applicaiton. A user ran the
> install, but when MSDE goes to start it says, MSDE is either currport or
> has
> been tampered with, please uninstall and reinstall MSDE, invalid package
> ID.
> We go to unstill MSDE and it says MSDE is not installed... We tried
> reinstalling, since it thought it wasn't installed and when the installer
> starts, we get the same invaled package id error... Does anyone have any
> ideas, or thoughts on what is happening or what I can do to fix this?
> Thanks,
> Brian
>
install, but when MSDE goes to start it says, MSDE is either currport or has
been tampered with, please uninstall and reinstall MSDE, invalid package ID.
We go to unstill MSDE and it says MSDE is not installed... We tried
reinstalling, since it thought it wasn't installed and when the installer
starts, we get the same invaled package id error... Does anyone have any
ideas, or thoughts on what is happening or what I can do to fix this? Thanks,
Brian
Did you define a strong password for SA using the SAPWD parameter? MSDE SP3A
setup.exe will not install a new instance without it.
joe.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C8D287B9-BCD6-4423-AF48-21455901D596@.microsoft.com...
> We have packaged MSDE 2000 release A with our applicaiton. A user ran the
> install, but when MSDE goes to start it says, MSDE is either currport or
> has
> been tampered with, please uninstall and reinstall MSDE, invalid package
> ID.
> We go to unstill MSDE and it says MSDE is not installed... We tried
> reinstalling, since it thought it wasn't installed and when the installer
> starts, we get the same invaled package id error... Does anyone have any
> ideas, or thoughts on what is happening or what I can do to fix this?
> Thanks,
> Brian
>
Wednesday, March 28, 2012
Relationship between inserted and deleted tables?
Hi all,
I just ran across an issue on a SQL 2000 sp4 db where RI was being maintained solely with triggers. I am attempting to change the primary key of a parent table and cascade the results to all its children without using the vendor-supplied trigger code (long story...) using an INSTEAD OF trigger.
My question is: does SQL Server create any kind of relationship between the inserted and deleted tables that I could exploit since the key field is unavailable?
I am trying to avoid having to add a surrogate key to each of the children just for this activity (as there are many M rows in each and no other suitable unique column combinations that span all the child tables).
-DC
As far as I understood your question, no. But the deleted and the inserted tables will always have the full structure of the modified table, so the key column should be available for you ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||
Hi Jens,
This code should help clear up my question. Case 2 is what I am talking about.
create table tt (pk int primary key,col2 int,col3 int)
create table tg (pk int primary key,col2 int,col3 int)
insert tt values (1,2,3)
insert tg values (1,3,7)
go
create trigger tr1
on tt
instead of update
as
begin
select pk,col2,col3 from inserted
select pk,col2,col3 from deleted
end
go
set nocount on
print 'Case 1. Non-key field updated. Can join on pk'
print ' '
update tt set col2=9 where col2=2
print ' '
print 'Case 2. Key field updated. Cannot join on pk'
print ' '
update tt set pk=3 where pk=1
drop table tt
go
drop table tg
go
|||
OK, got it. YOu will have to join on all non key columns then. for performance reasons, you could check the updated column and use the update on the PK if the OK was not updated and the covering join when then PK was updated.
|||Much appreciated, Jens. I will try that. Thanks!
sqlMonday, March 26, 2012
Relation between reads and duration
I have a query that performs strangely under different conditions as
follows:
When ran normally, it uses a clustered index scan. From the Profiler and
the IO statistics, this makes ~250,000 reads. This takes about 4 minutes to
run.
When ran with an index hint, it uses a clusted index seek and makes a
bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, this
takes only 1 minute to run.
Why is it like that? I have tried defragging the clustered index, but to no
avail. Running another query that also makes a clustered index scan makes
~250,000 reads, and takes ~ 4 minutes too.
What else can I check? Why does a query that makes less reads takes longer
to run than another that makes 4 times as many reads? I would like to avoid
having to force the hint, if I can help it.
Thx in advance.
Regards
Ray MondRay,
Do the 250K reads from the clustered index involve wide rows, so they
would access many data pages (maybe even 250,000)? With the hint, maybe
most of the million reads are from a relatively small number of pages in
the nonclustered index, and therefore from memory, not disk.
There are lots of factors involved, and sometimes there just isn't
enough information for the optimizer to choose the best plan. If you
provide some more information about your query (such as create table
statements, indexes, the actual queries and maybe even the good and bad
plans you're seeing), maybe we can give more specific help.
SK
Ray Mond wrote:
>I have a query that performs strangely under different conditions as
>follows:
>When ran normally, it uses a clustered index scan. From the Profiler and
>the IO statistics, this makes ~250,000 reads. This takes about 4 minutes t
o
>run.
>When ran with an index hint, it uses a clusted index seek and makes a
>bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, thi
s
>takes only 1 minute to run.
>Why is it like that? I have tried defragging the clustered index, but to n
o
>avail. Running another query that also makes a clustered index scan makes
>~250,000 reads, and takes ~ 4 minutes too.
>What else can I check? Why does a query that makes less reads takes longer
>to run than another that makes 4 times as many reads? I would like to avoi
d
>having to force the hint, if I can help it.
>Thx in advance.
>Regards
>Ray Mond
>
>|||Steve,
Average row size is 1,248 bytes. You are probably right, in that the
execution plan using the non-clustered index is reading data from pages
already in memory, because the data rows requested by the query are bunched
up together and not randomly distributed. Is there a way to know the actual
number of unique pages actually looked up by a query, short of peeking into
the contents of each page?
Thanks.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:OBrtou$BEHA.2620@.TK2MSFTNGP12.phx.gbl...
> Ray,
> Do the 250K reads from the clustered index involve wide rows, so they
> would access many data pages (maybe even 250,000)? With the hint, maybe
> most of the million reads are from a relatively small number of pages in
> the nonclustered index, and therefore from memory, not disk.
> There are lots of factors involved, and sometimes there just isn't
> enough information for the optimizer to choose the best plan. If you
> provide some more information about your query (such as create table
> statements, indexes, the actual queries and maybe even the good and bad
> plans you're seeing), maybe we can give more specific help.
> SK
> Ray Mond wrote:
>
to
this
no
makes
longer
avoid
>|||I don't know of a way, though maybe there's something in profiler that
I've never seen. I don't recall the details now, but I think the beta 1
release of SQL Server 2005 had some extra show statistics io column -
maybe the situation will improve in future versions of SQL Server.
SK
Ray Mond wrote:
>Steve,
>Average row size is 1,248 bytes. You are probably right, in that the
>execution plan using the non-clustered index is reading data from pages
>already in memory, because the data rows requested by the query are bunched
>up together and not randomly distributed. Is there a way to know the actua
l
>number of unique pages actually looked up by a query, short of peeking into
>the contents of each page?
>Thanks.
>
>|||Steve,
Thx. I'm using the SET STATISTICS IO output to get a ballpark figure of the
number of unique pages read.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23RMkgJFCEHA.3064@.tk2msftngp13.phx.gbl...
> I don't know of a way, though maybe there's something in profiler that
> I've never seen. I don't recall the details now, but I think the beta 1
> release of SQL Server 2005 had some extra show statistics io column -
> maybe the situation will improve in future versions of SQL Server.
> SK
> Ray Mond wrote:
>
bunched
actual
into
>sql
follows:
When ran normally, it uses a clustered index scan. From the Profiler and
the IO statistics, this makes ~250,000 reads. This takes about 4 minutes to
run.
When ran with an index hint, it uses a clusted index seek and makes a
bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, this
takes only 1 minute to run.
Why is it like that? I have tried defragging the clustered index, but to no
avail. Running another query that also makes a clustered index scan makes
~250,000 reads, and takes ~ 4 minutes too.
What else can I check? Why does a query that makes less reads takes longer
to run than another that makes 4 times as many reads? I would like to avoid
having to force the hint, if I can help it.
Thx in advance.
Regards
Ray MondRay,
Do the 250K reads from the clustered index involve wide rows, so they
would access many data pages (maybe even 250,000)? With the hint, maybe
most of the million reads are from a relatively small number of pages in
the nonclustered index, and therefore from memory, not disk.
There are lots of factors involved, and sometimes there just isn't
enough information for the optimizer to choose the best plan. If you
provide some more information about your query (such as create table
statements, indexes, the actual queries and maybe even the good and bad
plans you're seeing), maybe we can give more specific help.
SK
Ray Mond wrote:
>I have a query that performs strangely under different conditions as
>follows:
>When ran normally, it uses a clustered index scan. From the Profiler and
>the IO statistics, this makes ~250,000 reads. This takes about 4 minutes t
o
>run.
>When ran with an index hint, it uses a clusted index seek and makes a
>bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, thi
s
>takes only 1 minute to run.
>Why is it like that? I have tried defragging the clustered index, but to n
o
>avail. Running another query that also makes a clustered index scan makes
>~250,000 reads, and takes ~ 4 minutes too.
>What else can I check? Why does a query that makes less reads takes longer
>to run than another that makes 4 times as many reads? I would like to avoi
d
>having to force the hint, if I can help it.
>Thx in advance.
>Regards
>Ray Mond
>
>|||Steve,
Average row size is 1,248 bytes. You are probably right, in that the
execution plan using the non-clustered index is reading data from pages
already in memory, because the data rows requested by the query are bunched
up together and not randomly distributed. Is there a way to know the actual
number of unique pages actually looked up by a query, short of peeking into
the contents of each page?
Thanks.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:OBrtou$BEHA.2620@.TK2MSFTNGP12.phx.gbl...
> Ray,
> Do the 250K reads from the clustered index involve wide rows, so they
> would access many data pages (maybe even 250,000)? With the hint, maybe
> most of the million reads are from a relatively small number of pages in
> the nonclustered index, and therefore from memory, not disk.
> There are lots of factors involved, and sometimes there just isn't
> enough information for the optimizer to choose the best plan. If you
> provide some more information about your query (such as create table
> statements, indexes, the actual queries and maybe even the good and bad
> plans you're seeing), maybe we can give more specific help.
> SK
> Ray Mond wrote:
>
to
this
no
makes
longer
avoid
>|||I don't know of a way, though maybe there's something in profiler that
I've never seen. I don't recall the details now, but I think the beta 1
release of SQL Server 2005 had some extra show statistics io column -
maybe the situation will improve in future versions of SQL Server.
SK
Ray Mond wrote:
>Steve,
>Average row size is 1,248 bytes. You are probably right, in that the
>execution plan using the non-clustered index is reading data from pages
>already in memory, because the data rows requested by the query are bunched
>up together and not randomly distributed. Is there a way to know the actua
l
>number of unique pages actually looked up by a query, short of peeking into
>the contents of each page?
>Thanks.
>
>|||Steve,
Thx. I'm using the SET STATISTICS IO output to get a ballpark figure of the
number of unique pages read.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23RMkgJFCEHA.3064@.tk2msftngp13.phx.gbl...
> I don't know of a way, though maybe there's something in profiler that
> I've never seen. I don't recall the details now, but I think the beta 1
> release of SQL Server 2005 had some extra show statistics io column -
> maybe the situation will improve in future versions of SQL Server.
> SK
> Ray Mond wrote:
>
bunched
actual
into
>sql
Monday, March 12, 2012
Reindex tables question
We reindexed all of our tables this weekend. We ran the showcontig for
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
--
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
--
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
Reindex tables question
We reindexed all of our tables this weekend. We ran the showcontig for
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thanks everyone for responding. I actually printed out that white paper
this morning and am going to wait to play with this anymore until I have
read the whole thing cover to cover.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thanks everyone for responding. I actually printed out that white paper
this morning and am going to wait to play with this anymore until I have
read the whole thing cover to cover.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Friday, March 9, 2012
Reindex tables question
We reindexed all of our tables this weekend. We ran the showcontig for
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!How many pages are the indexes? How much free space in the database? Cluster
ed or non-clustered indexes? Do
you have one or several data files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl
..
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks everyone for responding. I actually printed out that white paper
this morning and am going to wait to play with this anymore until I have
read the whole thing cover to cover.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!How many pages are the indexes? How much free space in the database? Cluster
ed or non-clustered indexes? Do
you have one or several data files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl
..
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks everyone for responding. I actually printed out that white paper
this morning and am going to wait to play with this anymore until I have
read the whole thing cover to cover.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Reindex HUGE table - production issue
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,
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...
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
partitioning. Break
partitioned view. It
reindexing.
--
indexing script, to
following weekend
about index defrag.
SPID associated with
rebuild but,
online and allow your
--
middle of
table before
doing anything or
users are
during business
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
partitioning. Break
partitioned view. It
reindexing.
--
indexing script, to
following weekend
about index defrag.
SPID associated with
rebuild but,
online and allow your
--
middle of
table before
doing anything or
users are
during business
, 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.
rolled back.
been running.
--
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
>
Reindex HUGE table - production issue
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,
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" wrote in =message news:4fdf650=7.0401120718.11feddb@.posting.google.com...Hi, I have a very large table (400 Gig) that I am in the middle ofreindexing, it has been 60 hours. I have reindexed this table beforeand it ran for 44 hours.Does anyone know how to tell =if it is still actually doing anything orif it is stuck?Please if =anyone has any suggestions, let me know. The users arecomplaining about this =table not being availiable during =businesshours!!!Thanks,LT
--=_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"= 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
=---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:4fdf650=7.0401120718.11feddb@.posting.google.com...Hi, I have a very large table (400 Gig) that I am in the middle ofreindexing, it has been 60 hours. I have reindexed this table beforeand it ran for 44 hours.Does anyone know how to tell =if it is still actually doing anything orif it is stuck?Please =if anyone has any suggestions, let me know. The users arecomplaining about =this table not being availiable during =businesshours!!!Thanks,LT
--=_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"= 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
=---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:4fdf650=7.0401120718.11feddb@.posting.google.com...Hi, I have a very large table (400 Gig) that I am in the middle ofreindexing, it has been 60 hours. I have reindexed this table beforeand it ran for 44 hours.Does anyone know how to tell =if it is still actually doing anything orif it is stuck?Please =if anyone has any suggestions, let me know. The users arecomplaining about =this table not being availiable during =businesshours!!!Thanks,LT
--=_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" wrote in =message news:0c4b01c3d92d$96=cc8f50$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>>>"Quen=tin Ran" 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" = 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>> -----&g=t; Thomas A. Moreau, BSc, PhD, MCSE, MCDBA> SQL Server MVP> Columnist, SQL Server Professional> =Toronto, ON Canada>http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql>>> "Laura" 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--|||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"= 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
=---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:4fdf650=7.0401120718.11feddb@.posting.google.com...Hi, I have a very large table (400 Gig) that I am in the middle ofreindexing, it has been 60 hours. I have reindexed this table beforeand it ran for 44 hours.Does anyone know how to =tell if it is still actually doing anything orif it is stuck?Please =if anyone has any suggestions, let me know. The users =arecomplaining about this table not being availiable during businesshours!!!Thanks,LT
--=_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" 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" 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"= 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
=---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:4fdf650=7.0401120718.11feddb@.posting.google.com...Hi, I have a very large table (400 Gig) that I am in the =middle ofreindexing, it has been 60 hours. I have reindexed this =table beforeand it ran for 44 hours.Does anyone know how to =tell if it is still actually doing anything orif it is =stuck?Please if anyone has any suggestions, let me know. The users =arecomplaining about this table not being availiable during businesshours!!!Thanks,LT
--=_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" =wrote in message>news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...>Than=ks 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>>--Origin=al 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>&=gt;>----->-->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Quentin Ran" wrote in message>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...>&g=t;... 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" = wrote in message>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...&=gt;> 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>http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql>>> "Laura" 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--
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--
Subscribe to:
Posts (Atom)