I have a table with 1.5 million rows. Each field has a text qualifier of " ". Could you please tell me if and how I can write a script to remove the quotes from each field?
Thank you. ;)Using which database engine? The string operations aren't very standard between engines, so which engine makes a considerable difference.
-PatP|||Using MS SQL Server 2000|||Hi mary10k, try this
update tablename set filedname=replace(filedname,""" ""","")
Madhivanan
Showing posts with label million. Show all posts
Showing posts with label million. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
cleaning different formats
I have a column "currentDate" with approx 4 million rows.
The rows are composed of dates , but with a variety of formats.
For example:
"14-05-2005 14:56:32"
"20/07/2005 10:26:43"
"2006-03-26 11:21:42"
I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
hh:mm:ss, and then convert the column into a datetime
The problem I have is that I am using DATEPART , which is fine , except that
I am losing the leading zero.
Taking "02/03/2005 10:26:43" as an example ,
when I do the following:
CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to "02"
,
How can I retain the leading "0" , for any DATEPART.?>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
Bravo! But that is very far from a standard format!
Why wasn't this a datetime in the first place? What application is storing
all these different formats? Are you allowing end users to just enter
whatever format they want?
Assuming these are the only three formats present, this might give you some
ideas:
SELECT CONVERT(CHAR(10), d, 120)+'T'+CONVERT(CHAR(8), d, 108)
FROM
(
SELECT d = CASE WHEN ISDate(d)=1 THEN CONVERT(DATETIME, d)
ELSE CONVERT(DATETIME, d, 103) END
FROM
(
SELECT d = '14-05-2005 14:56:32'
UNION ALL SELECT '20/07/2005 10:26:43'
UNION ALL SELECT '2006-03-26 11:21:42'
) a
) b
However, I am betting there are twenty other formats you're not mentioning.
A|||You may try this:
select right('0' + CAST(DATEPART(dd, myday) AS VARCHAR(2)), 2)
Perayu
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
> The problem I have is that I am using DATEPART , which is fine , except
> that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02"
> ,
> How can I retain the leading "0" , for any DATEPART.?
>|||Jack Vamvas (delete_this_bit_jack@.ciquery.com_delete) writes:
> I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e
> dd-mm-yyyy hh:mm:ss, and then convert the column into a datetime The
> problem I have is that I am using DATEPART , which is fine , except that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02" ,
> How can I retain the leading "0" , for any DATEPART.?
There are a couple of variations on that theme, but it does not seem to
address your real issue anyway.
Obviously you have dateformat of dmy, in which case the format 2006-03-26
is not likely to convert to datetime.
I would suggest that you add a new column to the table, nullable. Then
you would do something like:
UPDATE tbl
SET newcol = convert(datetime, oldcol)
WHERE isdate(oldcol) = 1
go
SET DATEFORMAT ymd
go
UPDATE tbl
SET newcol = convert(datetime, oldcol)
WHERE isdate(oldcol) = 1
AND newcol IS NULL
go
SELECT * FROM tbl WHERE newcol IS NULL
-- Manuallly fix the rest?
go
ALTER TABLE tbl DROP oldcol
go
-- If column should not permit NULL.
ALTER TABLE col ALTER newcol datetime NOT NULL
One thing you would have to make an extra check for are completely far-out
date formats like MM/DD/YY (yes, there are odd corners of the world where
they use this). Not talking of a string like 05/03/02 that has a number of
interpretations.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The Convert function will do the conversion to DateTime for you. However,
depending on the format, you will need to specify the Style parameter.
Lookup the Convert function in SQL Server Books Online. For example:
-- Italian dd-mm-yy
print convert(datetime,'14-05-2005 14:56:32',105)
May 14 2005 2:56PM
print convert(datetime,'14/05/2005 14:56:32',105)
May 14 2005 2:56PM
-- ANSI
print convert(datetime,'2005-05-14 14:56:32',102)
May 14 2005 2:56PM
Also, going forward, you will need to constrain user input into the
application to a consistent format.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
> The problem I have is that I am using DATEPART , which is fine , except
> that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02"
> ,
> How can I retain the leading "0" , for any DATEPART.?
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#yCoM86OGHA.2912@.tk2msftngp13.phx.gbl...
dd-mm-yyyy
> Bravo! But that is very far from a standard format!
> Why wasn't this a datetime in the first place? What application is
storing
> all these different formats? Are you allowing end users to just enter
> whatever format they want?
> Assuming these are the only three formats present, this might give you
some
> ideas:
> SELECT CONVERT(CHAR(10), d, 120)+'T'+CONVERT(CHAR(8), d, 108)
> FROM
> (
> SELECT d = CASE WHEN ISDate(d)=1 THEN CONVERT(DATETIME, d)
> ELSE CONVERT(DATETIME, d, 103) END
> FROM
> (
> SELECT d = '14-05-2005 14:56:32'
> UNION ALL SELECT '20/07/2005 10:26:43'
> UNION ALL SELECT '2006-03-26 11:21:42'
> ) a
> ) b
> However, I am betting there are twenty other formats you're not
mentioning.
> A
>
>
>
Thanks for the response.
Just to clarify , this data is inherited from a client . It is historical
data , and the different formats represent different pahases of their data
recording.
They dumped everything into a varchar column and now are requesting
different queries based on the dates.Hence the need to sort this problem
out.
The 3 formats I've presented are the only formats. The code you've presented
has givem me some ideas.|||> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
I think you can eliminate this intermediate step. Just convert from the
existing values into DateTime.|||Thanks
That sorted the problem out
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uZ#VY86OGHA.1132@.TK2MSFTNGP10.phx.gbl...
> You may try this:
> select right('0' + CAST(DATEPART(dd, myday) AS VARCHAR(2)), 2)
> Perayu
> "Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
> news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
dd-mm-yyyy
>|||I was having a similar issue and this fixed my problem as well! Good tip!
The rows are composed of dates , but with a variety of formats.
For example:
"14-05-2005 14:56:32"
"20/07/2005 10:26:43"
"2006-03-26 11:21:42"
I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
hh:mm:ss, and then convert the column into a datetime
The problem I have is that I am using DATEPART , which is fine , except that
I am losing the leading zero.
Taking "02/03/2005 10:26:43" as an example ,
when I do the following:
CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to "02"
,
How can I retain the leading "0" , for any DATEPART.?>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
Bravo! But that is very far from a standard format!
Why wasn't this a datetime in the first place? What application is storing
all these different formats? Are you allowing end users to just enter
whatever format they want?
Assuming these are the only three formats present, this might give you some
ideas:
SELECT CONVERT(CHAR(10), d, 120)+'T'+CONVERT(CHAR(8), d, 108)
FROM
(
SELECT d = CASE WHEN ISDate(d)=1 THEN CONVERT(DATETIME, d)
ELSE CONVERT(DATETIME, d, 103) END
FROM
(
SELECT d = '14-05-2005 14:56:32'
UNION ALL SELECT '20/07/2005 10:26:43'
UNION ALL SELECT '2006-03-26 11:21:42'
) a
) b
However, I am betting there are twenty other formats you're not mentioning.
A|||You may try this:
select right('0' + CAST(DATEPART(dd, myday) AS VARCHAR(2)), 2)
Perayu
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
> The problem I have is that I am using DATEPART , which is fine , except
> that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02"
> ,
> How can I retain the leading "0" , for any DATEPART.?
>|||Jack Vamvas (delete_this_bit_jack@.ciquery.com_delete) writes:
> I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e
> dd-mm-yyyy hh:mm:ss, and then convert the column into a datetime The
> problem I have is that I am using DATEPART , which is fine , except that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02" ,
> How can I retain the leading "0" , for any DATEPART.?
There are a couple of variations on that theme, but it does not seem to
address your real issue anyway.
Obviously you have dateformat of dmy, in which case the format 2006-03-26
is not likely to convert to datetime.
I would suggest that you add a new column to the table, nullable. Then
you would do something like:
UPDATE tbl
SET newcol = convert(datetime, oldcol)
WHERE isdate(oldcol) = 1
go
SET DATEFORMAT ymd
go
UPDATE tbl
SET newcol = convert(datetime, oldcol)
WHERE isdate(oldcol) = 1
AND newcol IS NULL
go
SELECT * FROM tbl WHERE newcol IS NULL
-- Manuallly fix the rest?
go
ALTER TABLE tbl DROP oldcol
go
-- If column should not permit NULL.
ALTER TABLE col ALTER newcol datetime NOT NULL
One thing you would have to make an extra check for are completely far-out
date formats like MM/DD/YY (yes, there are odd corners of the world where
they use this). Not talking of a string like 05/03/02 that has a number of
interpretations.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The Convert function will do the conversion to DateTime for you. However,
depending on the format, you will need to specify the Style parameter.
Lookup the Convert function in SQL Server Books Online. For example:
-- Italian dd-mm-yy
print convert(datetime,'14-05-2005 14:56:32',105)
May 14 2005 2:56PM
print convert(datetime,'14/05/2005 14:56:32',105)
May 14 2005 2:56PM
-- ANSI
print convert(datetime,'2005-05-14 14:56:32',102)
May 14 2005 2:56PM
Also, going forward, you will need to constrain user input into the
application to a consistent format.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
> The problem I have is that I am using DATEPART , which is fine , except
> that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02"
> ,
> How can I retain the leading "0" , for any DATEPART.?
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#yCoM86OGHA.2912@.tk2msftngp13.phx.gbl...
dd-mm-yyyy
> Bravo! But that is very far from a standard format!
> Why wasn't this a datetime in the first place? What application is
storing
> all these different formats? Are you allowing end users to just enter
> whatever format they want?
> Assuming these are the only three formats present, this might give you
some
> ideas:
> SELECT CONVERT(CHAR(10), d, 120)+'T'+CONVERT(CHAR(8), d, 108)
> FROM
> (
> SELECT d = CASE WHEN ISDate(d)=1 THEN CONVERT(DATETIME, d)
> ELSE CONVERT(DATETIME, d, 103) END
> FROM
> (
> SELECT d = '14-05-2005 14:56:32'
> UNION ALL SELECT '20/07/2005 10:26:43'
> UNION ALL SELECT '2006-03-26 11:21:42'
> ) a
> ) b
> However, I am betting there are twenty other formats you're not
mentioning.
> A
>
>
>
Thanks for the response.
Just to clarify , this data is inherited from a client . It is historical
data , and the different formats represent different pahases of their data
recording.
They dumped everything into a varchar column and now are requesting
different queries based on the dates.Hence the need to sort this problem
out.
The 3 formats I've presented are the only formats. The code you've presented
has givem me some ideas.|||> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
I think you can eliminate this intermediate step. Just convert from the
existing values into DateTime.|||Thanks
That sorted the problem out
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uZ#VY86OGHA.1132@.TK2MSFTNGP10.phx.gbl...
> You may try this:
> select right('0' + CAST(DATEPART(dd, myday) AS VARCHAR(2)), 2)
> Perayu
> "Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
> news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
dd-mm-yyyy
>|||I was having a similar issue and this fixed my problem as well! Good tip!
Thursday, March 8, 2012
checktable with repair_rebuild
Hi! Could anyone tell me how much free space is needed to run DBCC
Checktable with repair_rebuild option. I have a table with more than 100
million record with size around 120 G and when I ran the command it failed
because of space issue. I only had around 50 G. of free space left on that
drive.
Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is there
any command that I can use to use Tempdb for the rebuild process instead of
using the database space?
I appreciate your answer.If your running 2000 then you can specify the ESTIMATE_ONLY option to see
how much space you need in tempdb. Check out BOL for more details.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> Hi! Could anyone tell me how much free space is needed to run DBCC
> Checktable with repair_rebuild option. I have a table with more than 100
> million record with size around 120 G and when I ran the command it failed
> because of space issue. I only had around 50 G. of free space left on that
> drive.
> Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
there
> any command that I can use to use Tempdb for the rebuild process instead
of
> using the database space?
> I appreciate your answer.
>
>|||However the ESTIMATEONLY option only works out how much space is required to
run the check - it does not know what repairs may be necessary and how much
space they will require. For an index rebuild, you will need the same amount
of free space as if you were running DBCC DBREINDEX on the same index.
BTW, if you have determined that there is an integrity problem, it is in
your best interestes to do root-cause analysis of the problem and see why it
happened (almost certainly hardware). Examine your event logs, the SQL
Server error logs and run any hardware diagnostics you can - the odds are it
will happen again.
Regards.
--
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" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> If your running 2000 then you can specify the ESTIMATE_ONLY option to see
> how much space you need in tempdb. Check out BOL for more details.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > Hi! Could anyone tell me how much free space is needed to run DBCC
> > Checktable with repair_rebuild option. I have a table with more than 100
> > million record with size around 120 G and when I ran the command it
failed
> > because of space issue. I only had around 50 G. of free space left on
that
> > drive.
> > Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
> there
> > any command that I can use to use Tempdb for the rebuild process instead
> of
> > using the database space?
> >
> > I appreciate your answer.
> >
> >
> >
>|||Thanks for the answer.
In order to find what may have caused this, our Network guys did some online
hardware diognistic and didn't see any problems. Now we are planning to do
offline diognistic. I have one question on this:
Is there any specifc hardware (disk i/o, cpu, memory etc) do we need to
focus on? In other words what has been the main culprit from hardware side
causing database corruption, in your experience?
I appreciate your answer.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eYh248UJEHA.3436@.tk2msftngp13.phx.gbl...
> However the ESTIMATEONLY option only works out how much space is required
to
> run the check - it does not know what repairs may be necessary and how
much
> space they will require. For an index rebuild, you will need the same
amount
> of free space as if you were running DBCC DBREINDEX on the same index.
> BTW, if you have determined that there is an integrity problem, it is in
> your best interestes to do root-cause analysis of the problem and see why
it
> happened (almost certainly hardware). Examine your event logs, the SQL
> Server error logs and run any hardware diagnostics you can - the odds are
it
> will happen again.
> Regards.
> --
> 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" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> > If your running 2000 then you can specify the ESTIMATE_ONLY option to
see
> > how much space you need in tempdb. Check out BOL for more details.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "james" <kush@.brandes.com> wrote in message
> > news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > > Hi! Could anyone tell me how much free space is needed to run DBCC
> > > Checktable with repair_rebuild option. I have a table with more than
100
> > > million record with size around 120 G and when I ran the command it
> failed
> > > because of space issue. I only had around 50 G. of free space left on
> that
> > > drive.
> > > Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
> > there
> > > any command that I can use to use Tempdb for the rebuild process
instead
> > of
> > > using the database space?
> > >
> > > I appreciate your answer.
> > >
> > >
> > >
> >
> >
>|||In my experience, the major culprits have been bad drives, cables and
occasional problems with controllers. Make sure you're on the latest
software rev for all your controllers etc.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:uSAp727JEHA.2452@.TK2MSFTNGP09.phx.gbl...
> Thanks for the answer.
> In order to find what may have caused this, our Network guys did some
online
> hardware diognistic and didn't see any problems. Now we are planning to do
> offline diognistic. I have one question on this:
> Is there any specifc hardware (disk i/o, cpu, memory etc) do we need to
> focus on? In other words what has been the main culprit from hardware side
> causing database corruption, in your experience?
> I appreciate your answer.
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eYh248UJEHA.3436@.tk2msftngp13.phx.gbl...
> > However the ESTIMATEONLY option only works out how much space is
required
> to
> > run the check - it does not know what repairs may be necessary and how
> much
> > space they will require. For an index rebuild, you will need the same
> amount
> > of free space as if you were running DBCC DBREINDEX on the same index.
> >
> > BTW, if you have determined that there is an integrity problem, it is in
> > your best interestes to do root-cause analysis of the problem and see
why
> it
> > happened (almost certainly hardware). Examine your event logs, the SQL
> > Server error logs and run any hardware diagnostics you can - the odds
are
> it
> > will happen again.
> >
> > Regards.
> >
> > --
> > 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" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> > > If your running 2000 then you can specify the ESTIMATE_ONLY option to
> see
> > > how much space you need in tempdb. Check out BOL for more details.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "james" <kush@.brandes.com> wrote in message
> > > news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > > > Hi! Could anyone tell me how much free space is needed to run DBCC
> > > > Checktable with repair_rebuild option. I have a table with more than
> 100
> > > > million record with size around 120 G and when I ran the command it
> > failed
> > > > because of space issue. I only had around 50 G. of free space left
on
> > that
> > > > drive.
> > > > Does it require same free space as in DBCC DBReindex (1.2 * Table)?
Is
> > > there
> > > > any command that I can use to use Tempdb for the rebuild process
> instead
> > > of
> > > > using the database space?
> > > >
> > > > I appreciate your answer.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> In order to find what may have caused this, our Network guys did some
online
> hardware diognistic and didn't see any problems. Now we are planning to do
> offline diognistic.
--
Hi James,
You should also consider SQLIOStress tool in your battery of tests:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional
Checktable with repair_rebuild option. I have a table with more than 100
million record with size around 120 G and when I ran the command it failed
because of space issue. I only had around 50 G. of free space left on that
drive.
Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is there
any command that I can use to use Tempdb for the rebuild process instead of
using the database space?
I appreciate your answer.If your running 2000 then you can specify the ESTIMATE_ONLY option to see
how much space you need in tempdb. Check out BOL for more details.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> Hi! Could anyone tell me how much free space is needed to run DBCC
> Checktable with repair_rebuild option. I have a table with more than 100
> million record with size around 120 G and when I ran the command it failed
> because of space issue. I only had around 50 G. of free space left on that
> drive.
> Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
there
> any command that I can use to use Tempdb for the rebuild process instead
of
> using the database space?
> I appreciate your answer.
>
>|||However the ESTIMATEONLY option only works out how much space is required to
run the check - it does not know what repairs may be necessary and how much
space they will require. For an index rebuild, you will need the same amount
of free space as if you were running DBCC DBREINDEX on the same index.
BTW, if you have determined that there is an integrity problem, it is in
your best interestes to do root-cause analysis of the problem and see why it
happened (almost certainly hardware). Examine your event logs, the SQL
Server error logs and run any hardware diagnostics you can - the odds are it
will happen again.
Regards.
--
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" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> If your running 2000 then you can specify the ESTIMATE_ONLY option to see
> how much space you need in tempdb. Check out BOL for more details.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > Hi! Could anyone tell me how much free space is needed to run DBCC
> > Checktable with repair_rebuild option. I have a table with more than 100
> > million record with size around 120 G and when I ran the command it
failed
> > because of space issue. I only had around 50 G. of free space left on
that
> > drive.
> > Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
> there
> > any command that I can use to use Tempdb for the rebuild process instead
> of
> > using the database space?
> >
> > I appreciate your answer.
> >
> >
> >
>|||Thanks for the answer.
In order to find what may have caused this, our Network guys did some online
hardware diognistic and didn't see any problems. Now we are planning to do
offline diognistic. I have one question on this:
Is there any specifc hardware (disk i/o, cpu, memory etc) do we need to
focus on? In other words what has been the main culprit from hardware side
causing database corruption, in your experience?
I appreciate your answer.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eYh248UJEHA.3436@.tk2msftngp13.phx.gbl...
> However the ESTIMATEONLY option only works out how much space is required
to
> run the check - it does not know what repairs may be necessary and how
much
> space they will require. For an index rebuild, you will need the same
amount
> of free space as if you were running DBCC DBREINDEX on the same index.
> BTW, if you have determined that there is an integrity problem, it is in
> your best interestes to do root-cause analysis of the problem and see why
it
> happened (almost certainly hardware). Examine your event logs, the SQL
> Server error logs and run any hardware diagnostics you can - the odds are
it
> will happen again.
> Regards.
> --
> 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" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> > If your running 2000 then you can specify the ESTIMATE_ONLY option to
see
> > how much space you need in tempdb. Check out BOL for more details.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "james" <kush@.brandes.com> wrote in message
> > news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > > Hi! Could anyone tell me how much free space is needed to run DBCC
> > > Checktable with repair_rebuild option. I have a table with more than
100
> > > million record with size around 120 G and when I ran the command it
> failed
> > > because of space issue. I only had around 50 G. of free space left on
> that
> > > drive.
> > > Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
> > there
> > > any command that I can use to use Tempdb for the rebuild process
instead
> > of
> > > using the database space?
> > >
> > > I appreciate your answer.
> > >
> > >
> > >
> >
> >
>|||In my experience, the major culprits have been bad drives, cables and
occasional problems with controllers. Make sure you're on the latest
software rev for all your controllers etc.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:uSAp727JEHA.2452@.TK2MSFTNGP09.phx.gbl...
> Thanks for the answer.
> In order to find what may have caused this, our Network guys did some
online
> hardware diognistic and didn't see any problems. Now we are planning to do
> offline diognistic. I have one question on this:
> Is there any specifc hardware (disk i/o, cpu, memory etc) do we need to
> focus on? In other words what has been the main culprit from hardware side
> causing database corruption, in your experience?
> I appreciate your answer.
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eYh248UJEHA.3436@.tk2msftngp13.phx.gbl...
> > However the ESTIMATEONLY option only works out how much space is
required
> to
> > run the check - it does not know what repairs may be necessary and how
> much
> > space they will require. For an index rebuild, you will need the same
> amount
> > of free space as if you were running DBCC DBREINDEX on the same index.
> >
> > BTW, if you have determined that there is an integrity problem, it is in
> > your best interestes to do root-cause analysis of the problem and see
why
> it
> > happened (almost certainly hardware). Examine your event logs, the SQL
> > Server error logs and run any hardware diagnostics you can - the odds
are
> it
> > will happen again.
> >
> > Regards.
> >
> > --
> > 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" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> > > If your running 2000 then you can specify the ESTIMATE_ONLY option to
> see
> > > how much space you need in tempdb. Check out BOL for more details.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "james" <kush@.brandes.com> wrote in message
> > > news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > > > Hi! Could anyone tell me how much free space is needed to run DBCC
> > > > Checktable with repair_rebuild option. I have a table with more than
> 100
> > > > million record with size around 120 G and when I ran the command it
> > failed
> > > > because of space issue. I only had around 50 G. of free space left
on
> > that
> > > > drive.
> > > > Does it require same free space as in DBCC DBReindex (1.2 * Table)?
Is
> > > there
> > > > any command that I can use to use Tempdb for the rebuild process
> instead
> > > of
> > > > using the database space?
> > > >
> > > > I appreciate your answer.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> In order to find what may have caused this, our Network guys did some
online
> hardware diognistic and didn't see any problems. Now we are planning to do
> offline diognistic.
--
Hi James,
You should also consider SQLIOStress tool in your battery of tests:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional
checktable repair_rebuild taking long time
Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.
I would check out Kalen Delaney's article on the sysprocesses table at www.sqlmag.com.
Check the delta of the CPU usage in sysprocesses to determine how much progress the check is making.
|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>
|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
> for
(how
>
|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and[vbcol=seagreen]
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
long.[vbcol=seagreen]
> You'd
> rights.
table[vbcol=seagreen]
> of
74[vbcol=seagreen]
(1:11667248),[vbcol=seagreen]
command
> (how
>
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.
I would check out Kalen Delaney's article on the sysprocesses table at www.sqlmag.com.
Check the delta of the CPU usage in sysprocesses to determine how much progress the check is making.
|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>
|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
> for
(how
>
|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and[vbcol=seagreen]
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
long.[vbcol=seagreen]
> You'd
> rights.
table[vbcol=seagreen]
> of
74[vbcol=seagreen]
(1:11667248),[vbcol=seagreen]
command
> (how
>
checktable repair_rebuild taking long time
Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.I would check out Kalen Delaney's article on the sysprocesses table at com." target="_blank">www.sqlmag.
com.
Check the delta of the CPU usage in sysprocesses to determine how much progr
ess the check is making.|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
> for
(how[vbcol=seagreen]
>|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
long.[vbcol=seagreen]
> You'd
> rights.
table[vbcol=seagreen]
> of
74[vbcol=seagreen]
(1:11667248),[vbcol=seagreen]
command[vbcol=seagreen]
> (how
>
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.I would check out Kalen Delaney's article on the sysprocesses table at com." target="_blank">www.sqlmag.
com.
Check the delta of the CPU usage in sysprocesses to determine how much progr
ess the check is making.|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
> for
(how[vbcol=seagreen]
>|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
long.[vbcol=seagreen]
> You'd
> rights.
table[vbcol=seagreen]
> of
74[vbcol=seagreen]
(1:11667248),[vbcol=seagreen]
command[vbcol=seagreen]
> (how
>
checktable repair_rebuild taking long time
Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.I would check out Kalen Delaney's article on the sysprocesses table at www.sqlmag.com
Check the delta of the CPU usage in sysprocesses to determine how much progress the check is making.|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> > Hi! I am running dbcc checktable with repair_rebuild option for a table
of
> > 121 Million record (about 150 GB) in size and its already running for 74
> > hours and still going. Table had Keys out of order on page (1:11667248),
> > slots 5 and 6 (Which was clustered Index).
> > Could anyone tell me how long does it normally take to run this command
> for
> > table of this size? Is there any way we can see the status of process
(how
> > far it has gone percentage wise)?
> > Environment:
> > Sql 2k SP2 running on Wi2k Advanced server
> > 8 CPU 2.7 GH and 8 GB RAM.
> >
> >
>|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> > It's rebuilding the clustered index and all the non-clustered indexes as
> > part of the repair - depending on how much and the distribution of free
> > space this could take a while but I wouldn't expect it to take that
long.
> > What was the exact output from checkdb before you re-ran with repair?
> You'd
> > have been much better off restoring from your backups (which is the
> > recommeneded strategy)
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "james" <kush@.brandes.com> wrote in message
> > news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> > > Hi! I am running dbcc checktable with repair_rebuild option for a
table
> of
> > > 121 Million record (about 150 GB) in size and its already running for
74
> > > hours and still going. Table had Keys out of order on page
(1:11667248),
> > > slots 5 and 6 (Which was clustered Index).
> > > Could anyone tell me how long does it normally take to run this
command
> > for
> > > table of this size? Is there any way we can see the status of process
> (how
> > > far it has gone percentage wise)?
> > > Environment:
> > > Sql 2k SP2 running on Wi2k Advanced server
> > > 8 CPU 2.7 GH and 8 GB RAM.
> > >
> > >
> >
> >
>
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.I would check out Kalen Delaney's article on the sysprocesses table at www.sqlmag.com
Check the delta of the CPU usage in sysprocesses to determine how much progress the check is making.|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> > Hi! I am running dbcc checktable with repair_rebuild option for a table
of
> > 121 Million record (about 150 GB) in size and its already running for 74
> > hours and still going. Table had Keys out of order on page (1:11667248),
> > slots 5 and 6 (Which was clustered Index).
> > Could anyone tell me how long does it normally take to run this command
> for
> > table of this size? Is there any way we can see the status of process
(how
> > far it has gone percentage wise)?
> > Environment:
> > Sql 2k SP2 running on Wi2k Advanced server
> > 8 CPU 2.7 GH and 8 GB RAM.
> >
> >
>|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> > It's rebuilding the clustered index and all the non-clustered indexes as
> > part of the repair - depending on how much and the distribution of free
> > space this could take a while but I wouldn't expect it to take that
long.
> > What was the exact output from checkdb before you re-ran with repair?
> You'd
> > have been much better off restoring from your backups (which is the
> > recommeneded strategy)
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "james" <kush@.brandes.com> wrote in message
> > news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> > > Hi! I am running dbcc checktable with repair_rebuild option for a
table
> of
> > > 121 Million record (about 150 GB) in size and its already running for
74
> > > hours and still going. Table had Keys out of order on page
(1:11667248),
> > > slots 5 and 6 (Which was clustered Index).
> > > Could anyone tell me how long does it normally take to run this
command
> > for
> > > table of this size? Is there any way we can see the status of process
> (how
> > > far it has gone percentage wise)?
> > > Environment:
> > > Sql 2k SP2 running on Wi2k Advanced server
> > > 8 CPU 2.7 GH and 8 GB RAM.
> > >
> > >
> >
> >
>
Friday, February 24, 2012
Checking to see if a records exists before inserting - 3 million + rows
I have 1+ CSV files (using a foreach loop) which I'm doing a lot of transform work on and then inserting into a SQL database table.
Each CSV file usually contains about 2 days worth of data (contains date stamps) - somewhere in the region of 60k records per day.
The destination table currently contains 3 million+ rows and will get bigger.
I need to make sure that before inserting into the destination table, the data doesn't already exist.
Each CSV file usually contains about 2 days worth of data (contains date stamps) - somewhere in the region of 60k records per day.
The destination table currently contains 3 million+ rows and will get bigger.
I need to make sure that before inserting into the destination table, the data doesn't already exist.
I've read the following article: http://www.sqlis.com/311.aspx
While the lookup method works, it takes ages and eats up memory as it caches the 3m+ records before running for each CSV. Obviously this will only get worse as the table grows in size.
To make things a little more efficient what I'd like to do, is first derive the dates I'm dealing with in the current file - essentially storing the max(date) and min(date) in variables. Then in the lookup SQL use those vars, to reduce the amount of data that needs to be brought into the transformation to check against before inserting into the destination table.
Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.
Ideally I'd use an aggregate transformation and then use the subsequent output from that either in the lookup query or store the output in vars, but I don't think you can do that and I get the feeling I'm approaching this with the wrong mindset.
Any thoughts would be great!
David Wynne wrote:
Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.
You aren't doing a select * against the lookup table, are you?|||Of course not - just pseudo code to get across what I think I'd like to achieve.
|||
Do you have the ability to push your data into a second table for comparison? You'd then be able to do an outer join based on whatever criteria and then you could pipe those results into your destination component and be far kinder on memory requirements.
I know there are some best practices with regard to using the lookup component to minimize impact but I don't recall those off the top of my head.
|||
Do you have the ability to push your data into a second table for comparison? You'd then be able to do an outer join based on whatever criteria and then you could pipe those results into your destination component and be far kinder on memory requirements.
I know there are some best practices with regard to using the lookup component to minimize impact but I don't recall those off the top of my head.
Yep, the OP could use an Execute SQL task to load up a temporary table with the keys of the lookup table. Then, in the data flow, you can use the mentioned outer join to capture new records. That is, unless you need to repopulate the lookup table with the incoming "new" records before the next lookup.|||
David,
How many columns do you need to put in the lookup transformation to determine if a record exists? Unless we are taking about too many/wide columns; I don't see several millions to be a problem. Another option is to use partial cache with decent amount of memory; so the chances a row is not in cache is low.
Remember, always provide a query with only the columns that are strictly required for the join
Subscribe to:
Posts (Atom)