sql2k sp3
How do I get the results of "dbcc opentran with
tableresults" into an actual table? Something along the
lines of;
create table #temp(c1 varchar(50),c2 varchar(50))
insert into #temp (c1,c2) values
(dbcc opentran with tableresults)
select * from #temp
drop table #temp
TIA, ChrisHi Chris
Once you have the table created, you can use the INSERT/EXEC syntax to
populate it:
insert into #temp
exec ('dbcc opentran(pubs) with tableresults')
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:09d701c3ce6b$37e9d640$a001280a@.phx.gbl...
> sql2k sp3
> How do I get the results of "dbcc opentran with
> tableresults" into an actual table? Something along the
> lines of;
> create table #temp(c1 varchar(50),c2 varchar(50))
> insert into #temp (c1,c2) values
> (dbcc opentran with tableresults)
> select * from #temp
> drop table #temp
> TIA, Chris|||create table #temp(c1 varchar(50),c2 varchar(50))
insert into #temp (c1,c2)
exec ('dbcc opentran with tableresults')
select * from #temp
drop table #temp
>--Original Message--
>sql2k sp3
>How do I get the results of "dbcc opentran with
>tableresults" into an actual table? Something along the
>lines of;
>create table #temp(c1 varchar(50),c2 varchar(50))
>insert into #temp (c1,c2) values
>(dbcc opentran with tableresults)
>select * from #temp
>drop table #temp
>TIA, Chris
>.
>
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
dbcc dbreindex problems
sql2k sp3
When running:
dbcc dbreindex ('dev.dbo.transdtl')
I get:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is '8216076'.
The statement has been terminated.
Now this doesn't make sense. TransDTLKey is the PK column for this table. No
other unique constraints are defined. So I:
select transdtlkey
from transdtl
group by transdtlkey
having count(transdtlkey) > 1
and get back 0 records.
Ideas?
TIA, ChrisRSmells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
have been dreaming, though), I'd search the newsgroups archive as well...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> sql2k sp3
> When running:
> dbcc dbreindex ('dev.dbo.transdtl')
> I get:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 1. Most significant primary key is '8216076'.
> The statement has been terminated.
> Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> other unique constraints are defined. So I:
> select transdtlkey
> from transdtl
> group by transdtlkey
> having count(transdtlkey) > 1
> and get back 0 records.
>
> Ideas?
> TIA, ChrisR|||You are correct:
Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
out of order on page (1:817776), slots 57 and 58.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
129.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in database 'DEV'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
repair level for the errors found by DBCC CHECKDB (DEV ).
The problem is though that I ran dbcc checkdb with repair_rebuild and it
errors out because of this issue. So the Checkdb cant fix it and neither can
the Reindex. Any other ideas? A few years back at another job we had to:
Put the data into new tables.
Drop the old table.
Rename the new table.
to get rid of other strange behavior and it seemed to do the trick. I may
try this again here.
"Tibor Karaszi" wrote:
> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
> have been dreaming, though), I'd search the newsgroups archive as well...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> > sql2k sp3
> >
> > When running:
> >
> > dbcc dbreindex ('dev.dbo.transdtl')
> >
> > I get:
> >
> > Server: Msg 1505, Level 16, State 1, Line 1
> > CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> > ID 1. Most significant primary key is '8216076'.
> > The statement has been terminated.
> >
> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> > other unique constraints are defined. So I:
> >
> > select transdtlkey
> > from transdtl
> > group by transdtlkey
> > having count(transdtlkey) > 1
> >
> > and get back 0 records.
> >
> >
> > Ideas?
> >
> > TIA, ChrisR
>
>|||ChrisR wrote:
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> error: Object ID 1945773989, Index ID 16. Keys out of order on page
> (1:637458), slots 128 and 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> allocation errors and 2 consistency errors in table 'TransDtl'
> (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> Server]repair_rebuild is the minimum repair level for the errors
> found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and
> it errors out because of this issue. So the Checkdb cant fix it and
> neither can the Reindex. Any other ideas? A few years back at another
> job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I
> may try this again here.
>
If you script out the table and data, can you try using the
REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
user transaction to examine the differences (or data loss) when compared
to the copy of the table. If you find the repairs sufficient, you can
update rows from the table copy and then insert missing rows from the
table copy to get the tables in sync.
You obviously want to script out this whole procedure before you commit
to tying up production data for too long. OTOH, if your production table
is corrupt, the sooner you fix it, the better.
--
David Gugick
Imceda Software
www.imceda.com|||Im lucky in that this is a development db.
"David Gugick" wrote:
> ChrisR wrote:
> > You are correct:
> >
> > Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> > 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> > 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> > error: Object ID 1945773989, Index ID 16. Keys out of order on page
> > (1:637458), slots 128 and 129.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> > allocation errors and 2 consistency errors in table 'TransDtl'
> > (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> > Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> > database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> > Server]repair_rebuild is the minimum repair level for the errors
> > found by DBCC CHECKDB (DEV ).
> >
> >
> > The problem is though that I ran dbcc checkdb with repair_rebuild and
> > it errors out because of this issue. So the Checkdb cant fix it and
> > neither can the Reindex. Any other ideas? A few years back at another
> > job we had to:
> >
> > Put the data into new tables.
> > Drop the old table.
> > Rename the new table.
> >
> > to get rid of other strange behavior and it seemed to do the trick. I
> > may try this again here.
> >
> >
> If you script out the table and data, can you try using the
> REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
> user transaction to examine the differences (or data loss) when compared
> to the copy of the table. If you find the repairs sufficient, you can
> update rows from the table copy and then insert missing rows from the
> table copy to get the tables in sync.
> You obviously want to script out this whole procedure before you commit
> to tying up production data for too long. OTOH, if your production table
> is corrupt, the sooner you fix it, the better.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||1. Read about the specific error numbers in the updated Books Online. The update includes detailed
explanations for all corruption types of errors, including suggested actions. Execute the DBCC CHECK
command from QA so you get all error numbers back (Maint Wiz only return error number from first
error that a command generates).
2. Generic recommendations:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE9354B5-AB7D-407E-ABCB-60E8A46233F2@.microsoft.com...
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
> out of order on page (1:817776), slots 57 and 58.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
> 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in database 'DEV'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
> repair level for the errors found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and it
> errors out because of this issue. So the Checkdb cant fix it and neither can
> the Reindex. Any other ideas? A few years back at another job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I may
> try this again here.
>
> "Tibor Karaszi" wrote:
>> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might
>> be
>> have been dreaming, though), I'd search the newsgroups archive as well...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
>> > sql2k sp3
>> >
>> > When running:
>> >
>> > dbcc dbreindex ('dev.dbo.transdtl')
>> >
>> > I get:
>> >
>> > Server: Msg 1505, Level 16, State 1, Line 1
>> > CREATE UNIQUE INDEX terminated because a duplicate key was found for index
>> > ID 1. Most significant primary key is '8216076'.
>> > The statement has been terminated.
>> >
>> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
>> > other unique constraints are defined. So I:
>> >
>> > select transdtlkey
>> > from transdtl
>> > group by transdtlkey
>> > having count(transdtlkey) > 1
>> >
>> > and get back 0 records.
>> >
>> >
>> > Ideas?
>> >
>> > TIA, ChrisR
>>
When running:
dbcc dbreindex ('dev.dbo.transdtl')
I get:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is '8216076'.
The statement has been terminated.
Now this doesn't make sense. TransDTLKey is the PK column for this table. No
other unique constraints are defined. So I:
select transdtlkey
from transdtl
group by transdtlkey
having count(transdtlkey) > 1
and get back 0 records.
Ideas?
TIA, ChrisRSmells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
have been dreaming, though), I'd search the newsgroups archive as well...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> sql2k sp3
> When running:
> dbcc dbreindex ('dev.dbo.transdtl')
> I get:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 1. Most significant primary key is '8216076'.
> The statement has been terminated.
> Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> other unique constraints are defined. So I:
> select transdtlkey
> from transdtl
> group by transdtlkey
> having count(transdtlkey) > 1
> and get back 0 records.
>
> Ideas?
> TIA, ChrisR|||You are correct:
Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
out of order on page (1:817776), slots 57 and 58.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
129.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in database 'DEV'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
repair level for the errors found by DBCC CHECKDB (DEV ).
The problem is though that I ran dbcc checkdb with repair_rebuild and it
errors out because of this issue. So the Checkdb cant fix it and neither can
the Reindex. Any other ideas? A few years back at another job we had to:
Put the data into new tables.
Drop the old table.
Rename the new table.
to get rid of other strange behavior and it seemed to do the trick. I may
try this again here.
"Tibor Karaszi" wrote:
> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
> have been dreaming, though), I'd search the newsgroups archive as well...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> > sql2k sp3
> >
> > When running:
> >
> > dbcc dbreindex ('dev.dbo.transdtl')
> >
> > I get:
> >
> > Server: Msg 1505, Level 16, State 1, Line 1
> > CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> > ID 1. Most significant primary key is '8216076'.
> > The statement has been terminated.
> >
> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> > other unique constraints are defined. So I:
> >
> > select transdtlkey
> > from transdtl
> > group by transdtlkey
> > having count(transdtlkey) > 1
> >
> > and get back 0 records.
> >
> >
> > Ideas?
> >
> > TIA, ChrisR
>
>|||ChrisR wrote:
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> error: Object ID 1945773989, Index ID 16. Keys out of order on page
> (1:637458), slots 128 and 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> allocation errors and 2 consistency errors in table 'TransDtl'
> (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> Server]repair_rebuild is the minimum repair level for the errors
> found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and
> it errors out because of this issue. So the Checkdb cant fix it and
> neither can the Reindex. Any other ideas? A few years back at another
> job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I
> may try this again here.
>
If you script out the table and data, can you try using the
REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
user transaction to examine the differences (or data loss) when compared
to the copy of the table. If you find the repairs sufficient, you can
update rows from the table copy and then insert missing rows from the
table copy to get the tables in sync.
You obviously want to script out this whole procedure before you commit
to tying up production data for too long. OTOH, if your production table
is corrupt, the sooner you fix it, the better.
--
David Gugick
Imceda Software
www.imceda.com|||Im lucky in that this is a development db.
"David Gugick" wrote:
> ChrisR wrote:
> > You are correct:
> >
> > Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> > 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> > 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> > error: Object ID 1945773989, Index ID 16. Keys out of order on page
> > (1:637458), slots 128 and 129.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> > allocation errors and 2 consistency errors in table 'TransDtl'
> > (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> > Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> > database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> > Server]repair_rebuild is the minimum repair level for the errors
> > found by DBCC CHECKDB (DEV ).
> >
> >
> > The problem is though that I ran dbcc checkdb with repair_rebuild and
> > it errors out because of this issue. So the Checkdb cant fix it and
> > neither can the Reindex. Any other ideas? A few years back at another
> > job we had to:
> >
> > Put the data into new tables.
> > Drop the old table.
> > Rename the new table.
> >
> > to get rid of other strange behavior and it seemed to do the trick. I
> > may try this again here.
> >
> >
> If you script out the table and data, can you try using the
> REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
> user transaction to examine the differences (or data loss) when compared
> to the copy of the table. If you find the repairs sufficient, you can
> update rows from the table copy and then insert missing rows from the
> table copy to get the tables in sync.
> You obviously want to script out this whole procedure before you commit
> to tying up production data for too long. OTOH, if your production table
> is corrupt, the sooner you fix it, the better.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||1. Read about the specific error numbers in the updated Books Online. The update includes detailed
explanations for all corruption types of errors, including suggested actions. Execute the DBCC CHECK
command from QA so you get all error numbers back (Maint Wiz only return error number from first
error that a command generates).
2. Generic recommendations:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE9354B5-AB7D-407E-ABCB-60E8A46233F2@.microsoft.com...
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
> out of order on page (1:817776), slots 57 and 58.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
> 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in database 'DEV'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
> repair level for the errors found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and it
> errors out because of this issue. So the Checkdb cant fix it and neither can
> the Reindex. Any other ideas? A few years back at another job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I may
> try this again here.
>
> "Tibor Karaszi" wrote:
>> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might
>> be
>> have been dreaming, though), I'd search the newsgroups archive as well...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
>> > sql2k sp3
>> >
>> > When running:
>> >
>> > dbcc dbreindex ('dev.dbo.transdtl')
>> >
>> > I get:
>> >
>> > Server: Msg 1505, Level 16, State 1, Line 1
>> > CREATE UNIQUE INDEX terminated because a duplicate key was found for index
>> > ID 1. Most significant primary key is '8216076'.
>> > The statement has been terminated.
>> >
>> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
>> > other unique constraints are defined. So I:
>> >
>> > select transdtlkey
>> > from transdtl
>> > group by transdtlkey
>> > having count(transdtlkey) > 1
>> >
>> > and get back 0 records.
>> >
>> >
>> > Ideas?
>> >
>> > TIA, ChrisR
>>
DBCC DBREINDEX failure
Rob,
The reindex function will need data + (2 x Index space) to create an index.
You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
statement, and look at dropping and recreating the indexes instead of the
DBCC DBREINDEX statement.
-- Bill
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:6F079FC8-012F-4E24-8E07-645F1A197B25@.microsoft.com...[vbcol=seagreen]
> Repeat post. My apologies... it seemed that my original post had gone into
> limbo (timeout issue) so I had reposted.
> "Rob" wrote:
Thanks Bill.
I've been wondering if reindexing would do any good at all given that most
of my tables only have one clustered index. If it's clustered then there
shouldn't be any reason to drop and recreate the index, right... as data and
index pages are always kept in a sorted order.
"AlterEgo" wrote:
> Rob,
> The reindex function will need data + (2 x Index space) to create an index.
> You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
> statement, and look at dropping and recreating the indexes instead of the
> DBCC DBREINDEX statement.
> -- Bill
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:6F079FC8-012F-4E24-8E07-645F1A197B25@.microsoft.com...
>
>
The reindex function will need data + (2 x Index space) to create an index.
You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
statement, and look at dropping and recreating the indexes instead of the
DBCC DBREINDEX statement.
-- Bill
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:6F079FC8-012F-4E24-8E07-645F1A197B25@.microsoft.com...[vbcol=seagreen]
> Repeat post. My apologies... it seemed that my original post had gone into
> limbo (timeout issue) so I had reposted.
> "Rob" wrote:
Thanks Bill.
I've been wondering if reindexing would do any good at all given that most
of my tables only have one clustered index. If it's clustered then there
shouldn't be any reason to drop and recreate the index, right... as data and
index pages are always kept in a sorted order.
"AlterEgo" wrote:
> Rob,
> The reindex function will need data + (2 x Index space) to create an index.
> You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
> statement, and look at dropping and recreating the indexes instead of the
> DBCC DBREINDEX statement.
> -- Bill
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:6F079FC8-012F-4E24-8E07-645F1A197B25@.microsoft.com...
>
>
Saturday, February 25, 2012
DBCC DBREINDEX and Statistics
In SQL Server 2000 when you create an Index, statistics are created with fullscan. My question is when you issue the DBCC DBREINDEX command, are statistics updated with a fullscan or the default sample size? If the answer is fullscan, then I am curious
as to why the DBCC DBREINDEX runs faster than an UPDATE STATISTICS WITH FULLSCAN?
Thanks
Donnie
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?
> Thanks
> Donnie
|||Yes, The reindex takes about 4 hours on the table, the update statistics with a fullscan was killed after 11 hours. The size of the table is about 200 million rows.
-- Paul S Randal [MS] wrote: --
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?[vbcol=seagreen]
|||Hi Donnie,
An index rebuild always has a stats recreate piggy-backed on top of it.
I can only guess that you have a concurrent workload while you're doing the
update statistics (which is an online operation), whereas the index rebuild
is effectively offline (as it takes a table S or X lock).
Also, an index rebuild will do an allocation-order scan of the data whereas
an update statistics is probably doing a logical-order scan. Does the index
have high logical scan fragmentation?
According to a colleague in the query optimizer team, a full scan update of
such a large index is a highly unusual operation. Are you certain that a
lower sampling rate is producing bad statistics?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:379598EC-0F22-4BC0-A070-B30B860090A3@.microsoft.com...
> Yes, The reindex takes about 4 hours on the table, the update statistics
with a fullscan was killed after 11 hours. The size of the table is about
200 million rows.
> -- Paul S Randal [MS] wrote: --
> Can you provide some timing details along with index size etc?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Donnie" <anonymous@.discussions.microsoft.com> wrote in message
> news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
with
> fullscan. My question is when you issue the DBCC DBREINDEX command,
are
> statistics updated with a fullscan or the default sample size? If
the
> answer is fullscan, then I am curious as to why the DBCC DBREINDEX
runs
> faster than an UPDATE STATISTICS WITH FULLSCAN?
>
>
|||Ok, That makes since as to why the Update Statistics takes longer.
Just so I understand, when a reindex is run, the statistics are re-created for the table that is being reindexed? Is that correct?
Thanks for your help. It is much appreciated.
Thanks
Donnie
|||Yes, correct. And you're most welcome.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:EB8918BF-B938-47A8-8D88-7E308D748340@.microsoft.com...
> Ok, That makes since as to why the Update Statistics takes longer.
> Just so I understand, when a reindex is run, the statistics are re-created
for the table that is being reindexed? Is that correct?
>
> Thanks for your help. It is much appreciated.
> Thanks
> Donnie
as to why the DBCC DBREINDEX runs faster than an UPDATE STATISTICS WITH FULLSCAN?
Thanks
Donnie
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?
> Thanks
> Donnie
|||Yes, The reindex takes about 4 hours on the table, the update statistics with a fullscan was killed after 11 hours. The size of the table is about 200 million rows.
-- Paul S Randal [MS] wrote: --
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?[vbcol=seagreen]
|||Hi Donnie,
An index rebuild always has a stats recreate piggy-backed on top of it.
I can only guess that you have a concurrent workload while you're doing the
update statistics (which is an online operation), whereas the index rebuild
is effectively offline (as it takes a table S or X lock).
Also, an index rebuild will do an allocation-order scan of the data whereas
an update statistics is probably doing a logical-order scan. Does the index
have high logical scan fragmentation?
According to a colleague in the query optimizer team, a full scan update of
such a large index is a highly unusual operation. Are you certain that a
lower sampling rate is producing bad statistics?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:379598EC-0F22-4BC0-A070-B30B860090A3@.microsoft.com...
> Yes, The reindex takes about 4 hours on the table, the update statistics
with a fullscan was killed after 11 hours. The size of the table is about
200 million rows.
> -- Paul S Randal [MS] wrote: --
> Can you provide some timing details along with index size etc?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Donnie" <anonymous@.discussions.microsoft.com> wrote in message
> news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
with
> fullscan. My question is when you issue the DBCC DBREINDEX command,
are
> statistics updated with a fullscan or the default sample size? If
the
> answer is fullscan, then I am curious as to why the DBCC DBREINDEX
runs
> faster than an UPDATE STATISTICS WITH FULLSCAN?
>
>
|||Ok, That makes since as to why the Update Statistics takes longer.
Just so I understand, when a reindex is run, the statistics are re-created for the table that is being reindexed? Is that correct?
Thanks for your help. It is much appreciated.
Thanks
Donnie
|||Yes, correct. And you're most welcome.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:EB8918BF-B938-47A8-8D88-7E308D748340@.microsoft.com...
> Ok, That makes since as to why the Update Statistics takes longer.
> Just so I understand, when a reindex is run, the statistics are re-created
for the table that is being reindexed? Is that correct?
>
> Thanks for your help. It is much appreciated.
> Thanks
> Donnie
DBCC DBREINDEX and Statistics
In SQL Server 2000 when you create an Index, statistics are created with ful
lscan. My question is when you issue the DBCC DBREINDEX command, are statis
tics updated with a fullscan or the default sample size? If the answer is f
ullscan, then I am curious
as to why the DBCC DBREINDEX runs faster than an UPDATE STATISTICS WITH FULL
SCAN?
Thanks
DonnieCan you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?
> Thanks
> Donnie|||Yes, The reindex takes about 4 hours on the table, the update statistics wit
h a fullscan was killed after 11 hours. The size of the table is about 200
million rows.
-- Paul S Randal [MS] wrote: --
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?[vbcol=seagreen]|||Hi Donnie,
An index rebuild always has a stats recreate piggy-backed on top of it.
I can only guess that you have a concurrent workload while you're doing the
update statistics (which is an online operation), whereas the index rebuild
is effectively offline (as it takes a table S or X lock).
Also, an index rebuild will do an allocation-order scan of the data whereas
an update statistics is probably doing a logical-order scan. Does the index
have high logical scan fragmentation?
According to a colleague in the query optimizer team, a full scan update of
such a large index is a highly unusual operation. Are you certain that a
lower sampling rate is producing bad statistics?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:379598EC-0F22-4BC0-A070-B30B860090A3@.microsoft.com...
> Yes, The reindex takes about 4 hours on the table, the update statistics
with a fullscan was killed after 11 hours. The size of the table is about
200 million rows.
> -- Paul S Randal [MS] wrote: --
> Can you provide some timing details along with index size etc?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Donnie" <anonymous@.discussions.microsoft.com> wrote in message
> news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
with[vbcol=seagreen]
> fullscan. My question is when you issue the DBCC DBREINDEX command,
are
> statistics updated with a fullscan or the default sample size? If
the
> answer is fullscan, then I am curious as to why the DBCC DBREINDEX
runs
> faster than an UPDATE STATISTICS WITH FULLSCAN?
>
>|||Ok, That makes since as to why the Update Statistics takes longer.
Just so I understand, when a reindex is run, the statistics are re-created f
or the table that is being reindexed? Is that correct?
Thanks for your help. It is much appreciated.
Thanks
Donnie|||Yes, correct. And you're most welcome.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:EB8918BF-B938-47A8-8D88-7E308D748340@.microsoft.com...
> Ok, That makes since as to why the Update Statistics takes longer.
> Just so I understand, when a reindex is run, the statistics are re-created
for the table that is being reindexed? Is that correct?
>
> Thanks for your help. It is much appreciated.
> Thanks
> Donnie
lscan. My question is when you issue the DBCC DBREINDEX command, are statis
tics updated with a fullscan or the default sample size? If the answer is f
ullscan, then I am curious
as to why the DBCC DBREINDEX runs faster than an UPDATE STATISTICS WITH FULL
SCAN?
Thanks
DonnieCan you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?
> Thanks
> Donnie|||Yes, The reindex takes about 4 hours on the table, the update statistics wit
h a fullscan was killed after 11 hours. The size of the table is about 200
million rows.
-- Paul S Randal [MS] wrote: --
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?[vbcol=seagreen]|||Hi Donnie,
An index rebuild always has a stats recreate piggy-backed on top of it.
I can only guess that you have a concurrent workload while you're doing the
update statistics (which is an online operation), whereas the index rebuild
is effectively offline (as it takes a table S or X lock).
Also, an index rebuild will do an allocation-order scan of the data whereas
an update statistics is probably doing a logical-order scan. Does the index
have high logical scan fragmentation?
According to a colleague in the query optimizer team, a full scan update of
such a large index is a highly unusual operation. Are you certain that a
lower sampling rate is producing bad statistics?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:379598EC-0F22-4BC0-A070-B30B860090A3@.microsoft.com...
> Yes, The reindex takes about 4 hours on the table, the update statistics
with a fullscan was killed after 11 hours. The size of the table is about
200 million rows.
> -- Paul S Randal [MS] wrote: --
> Can you provide some timing details along with index size etc?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Donnie" <anonymous@.discussions.microsoft.com> wrote in message
> news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
with[vbcol=seagreen]
> fullscan. My question is when you issue the DBCC DBREINDEX command,
are
> statistics updated with a fullscan or the default sample size? If
the
> answer is fullscan, then I am curious as to why the DBCC DBREINDEX
runs
> faster than an UPDATE STATISTICS WITH FULLSCAN?
>
>|||Ok, That makes since as to why the Update Statistics takes longer.
Just so I understand, when a reindex is run, the statistics are re-created f
or the table that is being reindexed? Is that correct?
Thanks for your help. It is much appreciated.
Thanks
Donnie|||Yes, correct. And you're most welcome.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:EB8918BF-B938-47A8-8D88-7E308D748340@.microsoft.com...
> Ok, That makes since as to why the Update Statistics takes longer.
> Just so I understand, when a reindex is run, the statistics are re-created
for the table that is being reindexed? Is that correct?
>
> Thanks for your help. It is much appreciated.
> Thanks
> Donnie
DBCC DBREINDEX - Update or Create Statistics
Hello All,
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexes
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data file
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
Mitra
Mitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexes
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data file
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
Mitra
Mitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
DBCC DBREINDEX - Update or Create Statistics
Hello All,
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our Java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexe
s
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data fil
e
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
MitraMitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our Java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our Java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexe
s
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data fil
e
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
MitraMitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our Java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
DBCC DBREINDEX - Update or Create Statistics
Hello All,
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexes
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data file
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
MitraMitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexes
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data file
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
MitraMitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
Friday, February 24, 2012
Dbcc Checkident
Dear All,
I'm trying to run the DBCC CHECKIDENT procedure to reset identity seed to 1 as I need it in a temp table I create in a stored procedure.
Is there a way of running this procedure or one which resets identity seed under an account which is not part of the sys-admin group?From BOL about DBCC CHECKIDENT:
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
Meaning that the user that created the temporary table also has permission to execute DBCC CHECKIDENT on it.|||Thanks for that. It works when I'm logged on as Administrator on the test server and also with an admin account on the live server.
However I get the following:
User '______.Gorgenyi' does not have permission to run DBCC CHECKIDENT for object '#tblRsWeek'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
All users get the same message. This is an ASP.NET web app.
Any suggestions?|||It seems like DBCC CHECKIDENT behaves differently on temporary tables. I haven't tested it myself so I can't give you any details on how and why.
A workaround to reset the identity to 1 is to truncate the temprary table.|||Thanks, TRUNCATE TABLE sorted the problem, no need to use DBCC Checkident
I'm trying to run the DBCC CHECKIDENT procedure to reset identity seed to 1 as I need it in a temp table I create in a stored procedure.
Is there a way of running this procedure or one which resets identity seed under an account which is not part of the sys-admin group?From BOL about DBCC CHECKIDENT:
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
Meaning that the user that created the temporary table also has permission to execute DBCC CHECKIDENT on it.|||Thanks for that. It works when I'm logged on as Administrator on the test server and also with an admin account on the live server.
However I get the following:
User '______.Gorgenyi' does not have permission to run DBCC CHECKIDENT for object '#tblRsWeek'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
All users get the same message. This is an ASP.NET web app.
Any suggestions?|||It seems like DBCC CHECKIDENT behaves differently on temporary tables. I haven't tested it myself so I can't give you any details on how and why.
A workaround to reset the identity to 1 is to truncate the temprary table.|||Thanks, TRUNCATE TABLE sorted the problem, no need to use DBCC Checkident
Subscribe to:
Posts (Atom)