Hi,
I have one million records in one table, My data
contains all the records from for eg. January to December.
I use normal view to select out certain period of
records, for eg. January. then i query the records use
the remote view from foxpro to filter up the records from
this view. Does it make my data selection faster?
Or, do i need to separate out my data into few
tables and then use partitioned view to query the data
out?
Which one is faster?
Thanks.
regards,
florence
Hi
1'000'000 rows does not justify Partitioned Views. Once you get into the
1000's of millions it becomes justified (and then you nedd to put each table
on a seperate disk volume to make it perform). Rather make sure that you have
correct indexes and that you configure your hardware correctly.
Regards
Mike
"florencelee" wrote:
> Hi,
> I have one million records in one table, My data
> contains all the records from for eg. January to December.
> I use normal view to select out certain period of
> records, for eg. January. then i query the records use
> the remote view from foxpro to filter up the records from
> this view. Does it make my data selection faster?
> Or, do i need to separate out my data into few
> tables and then use partitioned view to query the data
> out?
> Which one is faster?
> Thanks.
> regards,
> florence
>
Showing posts with label million. Show all posts
Showing posts with label million. Show all posts
Monday, March 26, 2012
Normal view compare to partitioned view
Hi,
I have one million records in one table, My data
contains all the records from for eg. January to December.
I use normal view to select out certain period of
records, for eg. January. then i query the records use
the remote view from foxpro to filter up the records from
this view. Does it make my data selection faster?
Or, do i need to separate out my data into few
tables and then use partitioned view to query the data
out?
Which one is faster?
Thanks.
regards,
florenceHi
1'000'000 rows does not justify Partitioned Views. Once you get into the
1000's of millions it becomes justified (and then you nedd to put each table
on a seperate disk volume to make it perform). Rather make sure that you have
correct indexes and that you configure your hardware correctly.
Regards
Mike
"florencelee" wrote:
> Hi,
> I have one million records in one table, My data
> contains all the records from for eg. January to December.
> I use normal view to select out certain period of
> records, for eg. January. then i query the records use
> the remote view from foxpro to filter up the records from
> this view. Does it make my data selection faster?
> Or, do i need to separate out my data into few
> tables and then use partitioned view to query the data
> out?
> Which one is faster?
> Thanks.
> regards,
> florence
>
I have one million records in one table, My data
contains all the records from for eg. January to December.
I use normal view to select out certain period of
records, for eg. January. then i query the records use
the remote view from foxpro to filter up the records from
this view. Does it make my data selection faster?
Or, do i need to separate out my data into few
tables and then use partitioned view to query the data
out?
Which one is faster?
Thanks.
regards,
florenceHi
1'000'000 rows does not justify Partitioned Views. Once you get into the
1000's of millions it becomes justified (and then you nedd to put each table
on a seperate disk volume to make it perform). Rather make sure that you have
correct indexes and that you configure your hardware correctly.
Regards
Mike
"florencelee" wrote:
> Hi,
> I have one million records in one table, My data
> contains all the records from for eg. January to December.
> I use normal view to select out certain period of
> records, for eg. January. then i query the records use
> the remote view from foxpro to filter up the records from
> this view. Does it make my data selection faster?
> Or, do i need to separate out my data into few
> tables and then use partitioned view to query the data
> out?
> Which one is faster?
> Thanks.
> regards,
> florence
>
Wednesday, March 21, 2012
Non-logged delete of records??
I have a database in Simple mode from which I wish to delete several million rows of one table (not all rows so 'TRUNCATE TABLE' is no good), without making the transaction log file grow to several GB in size. Any ideas? I'm almost at the point where I might create a new table and copy the records I want to keep instead...
Thanks, Simon.Assuming the rows have a primary key and you can select the PK from the Primary table , you could setup a secondary table (PKToDelete) with, say, 10,000 PK , populate that table with a select, and then join the PKToDelete and Primary tables for the delete operation. Upon successful completion of the delete, truncate the PKToDelete table, and repeat at perodic intervals until all Primary table deletes are complete.
Since you are in simple mode, the transaction log will be checkpointed when your pulsed delete completes, thereby preventing major growth of the transaction log.|||declare @.batchsize int
set @.batchsize=10000
set rowcount @.batchsize
delete from bigtab where something='some value'
while @.@.rowcount=@.batchsize
begin
delete from bigtab where something='some value'
end
set rowcount 0
Edit:
PS. Can do the same for updates e.g.
declare @.batchsize int
set @.batchsize=10000
set rowcount @.batchsize
update bigtab set type='shrub'
where id between 2867127 and 1908917872
and type='tree'
while @.@.rowcount=@.batchsize
begin
update bigtab set type='shrub'
where id between 2867127 and 1908917872
and type='tree'
end
set rowcount 0
Thanks, Simon.Assuming the rows have a primary key and you can select the PK from the Primary table , you could setup a secondary table (PKToDelete) with, say, 10,000 PK , populate that table with a select, and then join the PKToDelete and Primary tables for the delete operation. Upon successful completion of the delete, truncate the PKToDelete table, and repeat at perodic intervals until all Primary table deletes are complete.
Since you are in simple mode, the transaction log will be checkpointed when your pulsed delete completes, thereby preventing major growth of the transaction log.|||declare @.batchsize int
set @.batchsize=10000
set rowcount @.batchsize
delete from bigtab where something='some value'
while @.@.rowcount=@.batchsize
begin
delete from bigtab where something='some value'
end
set rowcount 0
Edit:
PS. Can do the same for updates e.g.
declare @.batchsize int
set @.batchsize=10000
set rowcount @.batchsize
update bigtab set type='shrub'
where id between 2867127 and 1908917872
and type='tree'
while @.@.rowcount=@.batchsize
begin
update bigtab set type='shrub'
where id between 2867127 and 1908917872
and type='tree'
end
set rowcount 0
Monday, March 19, 2012
NonClustered Index Corruption
Hi,
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
Jerry
First off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>
|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>
|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>
|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>
|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>
|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>
|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>
|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
Jerry
First off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>
|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>
|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>
|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>
|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>
|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>
|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>
|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>
NonClustered Index Corruption
Hi,
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
JerryFirst off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
JerryFirst off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>
NonClustered Index Corruption
Hi,
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
JerryFirst off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
>> and DBCC CHECKDB (doing both to make sure something else isn't causing
>> the issue) - both hung again (over 5 hours yesterday). Dropping the NC
>> index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that mean?
>> Were they being blocked? Was the spid doing anything? Is the NCI on a
>> single column, computed etc? In any case if you have hardware corruption
>> going on it will usually manifest itself in the most active process.
>> Creating an index on a 150M row table gives a lot of opportunity for
>> corruption if the hardware is suspect. If you have bad disk sectors then
>> creating the index again might very well use the same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
>> the NC index is on a single column - no it is not a computed column.
>> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
>> never completes and has to be stopped manually. It could be a hardware
>> issue (RAID 5). What operations would you suggest to determine if this
>> could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that mean?
>> Were they being blocked? Was the spid doing anything? Is the NCI on a
>> single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original
>> post the NC index is on a single column - no it is not a computed
>> column. Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6
>> hours and never completes and has to be stopped manually. It could be a
>> hardware issue (RAID 5). What operations would you suggest to determine
>> if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the NC
>> index was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>> Jerry,
>> Sorry I didn't catch the single column. What level of DBCC did you run?
>> Try with the Physical_Only option first just to see if it goes through.
>> In any case unless it is blocked it should not just sit there doing
>> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
>> would have expected some errors if it were disk related. I would run
>> chkdisk to be sure anyway when you can. Next time you try it take a look
>> at the SPID in sysprocesses to see how much I/O CPU etc. it used before
>> it "hangs".
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat
>> DBCC CHECKDB includes DBCC CHECKTABLE. Again as I stated in the
>> original post the NC index is on a single column - no it is not a
>> computed column. Hung? A 20 minute operations (DBCC CHECKDB) takes
>> more than 6 hours and never completes and has to be stopped manually.
>> It could be a hardware issue (RAID 5). What operations would you
>> suggest to determine if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have
>> hardware corruption going on it will usually manifest itself in the
>> most active process. Creating an index on a 150M row table gives a lot
>> of opportunity for corruption if the hardware is suspect. If you have
>> bad disk sectors then creating the index again might very well use the
>> same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the
>> NC index was the cause, I removed the NC index and reran the DBCC
>> CHECKDB statement. The results were no errors after 20 minutes.
>> Today I recreated the NC index (single column). I reran both the
>> DBCC CHECKTABLE and DBCC CHECKDB (doing both to make sure something
>> else isn't causing the issue) - both hung again (over 5 hours
>> yesterday). Dropping the NC index again and the checks work fine.
>> Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>>
>|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> Andrew,
>> No worries. I was running DBCC CHECKDB (with the default options). I
>> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
>> statment. This time the statement returned in a timely manner with no
>> errors reported. I'm thinking the drive(s). Does the SQL Server service
>> deed to be stopped before I have the network guy run the chkdsk command?
>> Thanks again.
>> Jerry
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>> Jerry,
>> Sorry I didn't catch the single column. What level of DBCC did you run?
>> Try with the Physical_Only option first just to see if it goes through.
>> In any case unless it is blocked it should not just sit there doing
>> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
>> would have expected some errors if it were disk related. I would run
>> chkdisk to be sure anyway when you can. Next time you try it take a
>> look at the SPID in sysprocesses to see how much I/O CPU etc. it used
>> before it "hangs".
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat
>> DBCC CHECKDB includes DBCC CHECKTABLE. Again as I stated in the
>> original post the NC index is on a single column - no it is not a
>> computed column. Hung? A 20 minute operations (DBCC CHECKDB) takes
>> more than 6 hours and never completes and has to be stopped manually.
>> It could be a hardware issue (RAID 5). What operations would you
>> suggest to determine if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have
>> hardware corruption going on it will usually manifest itself in the
>> most active process. Creating an index on a 150M row table gives a
>> lot of opportunity for corruption if the hardware is suspect. If you
>> have bad disk sectors then creating the index again might very well
>> use the same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>>> Hi,
>>>
>>> Yesterday I had some corruption in a nonclustered index on a table
>>> that contains over 150 million records. After determining that the
>>> NC index was the cause, I removed the NC index and reran the DBCC
>>> CHECKDB statement. The results were no errors after 20 minutes.
>>> Today I recreated the NC index (single column). I reran both the
>>> DBCC CHECKTABLE and DBCC CHECKDB (doing both to make sure something
>>> else isn't causing the issue) - both hung again (over 5 hours
>>> yesterday). Dropping the NC index again and the checks work fine.
>>> Any ideas?
>>>
>>> Thanks
>>>
>>> Jerry
>>>
>>
>>
>>
>>
>>
>|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original
>> post the NC index is on a single column - no it is not a computed
>> column. Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6
>> hours and never completes and has to be stopped manually. It could be a
>> hardware issue (RAID 5). What operations would you suggest to determine
>> if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the NC
>> index was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
JerryFirst off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
>> and DBCC CHECKDB (doing both to make sure something else isn't causing
>> the issue) - both hung again (over 5 hours yesterday). Dropping the NC
>> index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that mean?
>> Were they being blocked? Was the spid doing anything? Is the NCI on a
>> single column, computed etc? In any case if you have hardware corruption
>> going on it will usually manifest itself in the most active process.
>> Creating an index on a 150M row table gives a lot of opportunity for
>> corruption if the hardware is suspect. If you have bad disk sectors then
>> creating the index again might very well use the same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
>> the NC index is on a single column - no it is not a computed column.
>> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
>> never completes and has to be stopped manually. It could be a hardware
>> issue (RAID 5). What operations would you suggest to determine if this
>> could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that mean?
>> Were they being blocked? Was the spid doing anything? Is the NCI on a
>> single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original
>> post the NC index is on a single column - no it is not a computed
>> column. Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6
>> hours and never completes and has to be stopped manually. It could be a
>> hardware issue (RAID 5). What operations would you suggest to determine
>> if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the NC
>> index was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>> Jerry,
>> Sorry I didn't catch the single column. What level of DBCC did you run?
>> Try with the Physical_Only option first just to see if it goes through.
>> In any case unless it is blocked it should not just sit there doing
>> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
>> would have expected some errors if it were disk related. I would run
>> chkdisk to be sure anyway when you can. Next time you try it take a look
>> at the SPID in sysprocesses to see how much I/O CPU etc. it used before
>> it "hangs".
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat
>> DBCC CHECKDB includes DBCC CHECKTABLE. Again as I stated in the
>> original post the NC index is on a single column - no it is not a
>> computed column. Hung? A 20 minute operations (DBCC CHECKDB) takes
>> more than 6 hours and never completes and has to be stopped manually.
>> It could be a hardware issue (RAID 5). What operations would you
>> suggest to determine if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have
>> hardware corruption going on it will usually manifest itself in the
>> most active process. Creating an index on a 150M row table gives a lot
>> of opportunity for corruption if the hardware is suspect. If you have
>> bad disk sectors then creating the index again might very well use the
>> same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the
>> NC index was the cause, I removed the NC index and reran the DBCC
>> CHECKDB statement. The results were no errors after 20 minutes.
>> Today I recreated the NC index (single column). I reran both the
>> DBCC CHECKTABLE and DBCC CHECKDB (doing both to make sure something
>> else isn't causing the issue) - both hung again (over 5 hours
>> yesterday). Dropping the NC index again and the checks work fine.
>> Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>>
>|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> Andrew,
>> No worries. I was running DBCC CHECKDB (with the default options). I
>> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
>> statment. This time the statement returned in a timely manner with no
>> errors reported. I'm thinking the drive(s). Does the SQL Server service
>> deed to be stopped before I have the network guy run the chkdsk command?
>> Thanks again.
>> Jerry
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>> Jerry,
>> Sorry I didn't catch the single column. What level of DBCC did you run?
>> Try with the Physical_Only option first just to see if it goes through.
>> In any case unless it is blocked it should not just sit there doing
>> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
>> would have expected some errors if it were disk related. I would run
>> chkdisk to be sure anyway when you can. Next time you try it take a
>> look at the SPID in sysprocesses to see how much I/O CPU etc. it used
>> before it "hangs".
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat
>> DBCC CHECKDB includes DBCC CHECKTABLE. Again as I stated in the
>> original post the NC index is on a single column - no it is not a
>> computed column. Hung? A 20 minute operations (DBCC CHECKDB) takes
>> more than 6 hours and never completes and has to be stopped manually.
>> It could be a hardware issue (RAID 5). What operations would you
>> suggest to determine if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have
>> hardware corruption going on it will usually manifest itself in the
>> most active process. Creating an index on a 150M row table gives a
>> lot of opportunity for corruption if the hardware is suspect. If you
>> have bad disk sectors then creating the index again might very well
>> use the same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>>> Hi,
>>>
>>> Yesterday I had some corruption in a nonclustered index on a table
>>> that contains over 150 million records. After determining that the
>>> NC index was the cause, I removed the NC index and reran the DBCC
>>> CHECKDB statement. The results were no errors after 20 minutes.
>>> Today I recreated the NC index (single column). I reran both the
>>> DBCC CHECKTABLE and DBCC CHECKDB (doing both to make sure something
>>> else isn't causing the issue) - both hung again (over 5 hours
>>> yesterday). Dropping the NC index again and the checks work fine.
>>> Any ideas?
>>>
>>> Thanks
>>>
>>> Jerry
>>>
>>
>>
>>
>>
>>
>|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original
>> post the NC index is on a single column - no it is not a computed
>> column. Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6
>> hours and never completes and has to be stopped manually. It could be a
>> hardware issue (RAID 5). What operations would you suggest to determine
>> if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the NC
>> index was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>
Subscribe to:
Posts (Atom)