Sql Server Alignment problem: dbcc

Showing posts with label dbcc. Show all posts

Thursday, March 29, 2012

alternate to DBCC command

Hi ,
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARR
All documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>
|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
not
>
|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you[vbcol=seagreen]
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
deprecated[vbcol=seagreen]
> As
> what
> rights.
> not
..
>

alternate to DBCC command

Hi ,
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARRAll documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> > Hi ,
> > Is Yukon not supporting all dbcc commands or if they are used they are
not
> > reliable .
> > If its not supporting where do I find the alternate DMVs for the same .
> >
> >
> > Thanks
> > ARR
> >
> >
>|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> > All documented DBCC commands in Yukon are backwards-compatible and are
> > supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been
deprecated
> > and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> > INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
> As
> > always, undocumented DBCC commands are not supported and we reserve the
> > right to alter or completely remove them at any time. Can you tell us
> what
> > piece of information you're looking for specifically?
> >
> > Thanks,
> > Ryan Stonecipher
> > Microsoft SQL Server Storage Engine, DBCC
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Aju" <ajuonline@.yahoo.com> wrote in message
> > news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> > > Hi ,
> > > Is Yukon not supporting all dbcc commands or if they are used they are
> not
> > > reliable .
> > > If its not supporting where do I find the alternate DMVs for the same
.
> > >
> > >
> > > Thanks
> > > ARR
> > >
> > >
> >
> >
>

alternate to DBCC command

Hi ,
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARRAll documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
not[vbcol=seagreen]
>|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
deprecated[vbcol=seagreen]
> As
> what
> rights.
> not
.[vbcol=seagreen]
>

Tuesday, March 20, 2012

alter table drop column and dbcc cleantable

All,
I just want to make sure my understanding is correct:
1) alter table drop column does not release space of varchar columns and
other columns of variable length.
2) dbcc cleantable frees this space
Open questions:
3) What about the space used by fixed size columns? Is it reclaimed
during drop (timing lets me suspect that it's not)? dbcc cleantable "does
not reclaim space after a fixed length column is dropped."
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
Are rows inserted after a DROP COLUMN smaller?
Thanks a lot!
Kind regards
robert
Robert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert
|||Well, its actually two questions.
1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does the
ALTER TABLE ... DROP COLUMN statement. Correct.
2. Are the rows inserted after the ALTER TABLE statement has been issued?
Yes, new rows will only occupy the storage required for the new data
definition contingent on the values of PAD_INDEX and FILLFACTOR for the
Clustered Index.
Now, the next logical question would be: how to I reclaim the space after
issue the ALTER TABLE ... DROP COLUMN statement with a fixed-length column?
Rebuild the Clustered Index.
Sincerely,
Anthony Thomas

"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
Robert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert
|||Anthony Thomas wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Robert Klemme wrote:
>
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
> No one wants to answer this one? Come on... I can even offer a
> virtual hug. :-)

> Well, its actually two questions.
> 1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does
> the ALTER TABLE ... DROP COLUMN statement. Correct.
Ok.

> 2. Are the rows inserted after the ALTER TABLE statement has been
> issued? Yes, new rows will only occupy the storage required for the
> new data definition contingent on the values of PAD_INDEX and
> FILLFACTOR for the Clustered Index.
Ok.

> Now, the next logical question would be: how to I reclaim the space
> after issue the ALTER TABLE ... DROP COLUMN statement with a
> fixed-length column?
> Rebuild the Clustered Index.
Ok, so basically the space is not reclaimed until either the complete
table is rebuild or old records are deleted.
Thanks a lot! You get the virtual hug: *hug*
:-)
Kind regards
robert

alter table drop column and dbcc cleantable

All,
I just want to make sure my understanding is correct:
1) alter table drop column does not release space of varchar columns and
other columns of variable length.
2) dbcc cleantable frees this space
Open questions:
3) What about the space used by fixed size columns? Is it reclaimed
during drop (timing lets me suspect that it's not)? dbcc cleantable "does
not reclaim space after a fixed length column is dropped."
bah.asp" target="_blank">http://msdn.microsoft.com/library/d...
bah.asp
Are rows inserted after a DROP COLUMN smaller?
Thanks a lot!
Kind regards
robertRobert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/d...s_dbcc_4bah.asp[vb
col=seagreen]
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert[/vbcol]
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert|||Well, its actually two questions.
1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does the
ALTER TABLE ... DROP COLUMN statement. Correct.
2. Are the rows inserted after the ALTER TABLE statement has been issued?
Yes, new rows will only occupy the storage required for the new data
definition contingent on the values of PAD_INDEX and FILLFACTOR for the
Clustered Index.
Now, the next logical question would be: how to I reclaim the space after
issue the ALTER TABLE ... DROP COLUMN statement with a fixed-length column?
Rebuild the Clustered Index.
Sincerely,
Anthony Thomas
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
Robert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/d...s_dbcc_4bah.asp[vb
col=seagreen]
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert[/vbcol]
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert|||Anthony Thomas wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Robert Klemme wrote:
>
http://msdn.microsoft.com/library/d...s_dbcc_4bah.asp[vb
col=seagreen]
> No one wants to answer this one? Come on... I can even offer a
> virtual hug. :-)[/vbcol]

> Well, its actually two questions.
> 1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does
> the ALTER TABLE ... DROP COLUMN statement. Correct.
Ok.

> 2. Are the rows inserted after the ALTER TABLE statement has been
> issued? Yes, new rows will only occupy the storage required for the
> new data definition contingent on the values of PAD_INDEX and
> FILLFACTOR for the Clustered Index.
Ok.

> Now, the next logical question would be: how to I reclaim the space
> after issue the ALTER TABLE ... DROP COLUMN statement with a
> fixed-length column?
> Rebuild the Clustered Index.
Ok, so basically the space is not reclaimed until either the complete
table is rebuild or old records are deleted.
Thanks a lot! You get the virtual hug: *hug*
:-)
Kind regards
robert

alter table drop column and dbcc cleantable

All,
I just want to make sure my understanding is correct:
1) alter table drop column does not release space of varchar columns and
other columns of variable length.
2) dbcc cleantable frees this space
Open questions:
3) What about the space used by fixed size columns? Is it reclaimed
during drop (timing lets me suspect that it's not)? dbcc cleantable "does
not reclaim space after a fixed length column is dropped."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp
Are rows inserted after a DROP COLUMN smaller?
Thanks a lot!
Kind regards
robertRobert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert|||Well, its actually two questions.
1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does the
ALTER TABLE ... DROP COLUMN statement. Correct.
2. Are the rows inserted after the ALTER TABLE statement has been issued?
Yes, new rows will only occupy the storage required for the new data
definition contingent on the values of PAD_INDEX and FILLFACTOR for the
Clustered Index.
Now, the next logical question would be: how to I reclaim the space after
issue the ALTER TABLE ... DROP COLUMN statement with a fixed-length column?
Rebuild the Clustered Index.
Sincerely,
Anthony Thomas
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
Robert Klemme wrote:
> All,
> I just want to make sure my understanding is correct:
> 1) alter table drop column does not release space of varchar columns
> and other columns of variable length.
> 2) dbcc cleantable frees this space
> Open questions:
> 3) What about the space used by fixed size columns? Is it reclaimed
> during drop (timing lets me suspect that it's not)? dbcc cleantable
> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert|||Anthony Thomas wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Robert Klemme wrote:
>> All,
>> I just want to make sure my understanding is correct:
>> 1) alter table drop column does not release space of varchar columns
>> and other columns of variable length.
>> 2) dbcc cleantable frees this space
>> Open questions:
>> 3) What about the space used by fixed size columns? Is it reclaimed
>> during drop (timing lets me suspect that it's not)? dbcc cleantable
>> "does not reclaim space after a fixed length column is dropped."
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp
>> Are rows inserted after a DROP COLUMN smaller?
>> Thanks a lot!
>> Kind regards
>> robert
> No one wants to answer this one? Come on... I can even offer a
> virtual hug. :-)
> Well, its actually two questions.
> 1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does
> the ALTER TABLE ... DROP COLUMN statement. Correct.
Ok.
> 2. Are the rows inserted after the ALTER TABLE statement has been
> issued? Yes, new rows will only occupy the storage required for the
> new data definition contingent on the values of PAD_INDEX and
> FILLFACTOR for the Clustered Index.
Ok.
> Now, the next logical question would be: how to I reclaim the space
> after issue the ALTER TABLE ... DROP COLUMN statement with a
> fixed-length column?
> Rebuild the Clustered Index.
Ok, so basically the space is not reclaimed until either the complete
table is rebuild or old records are deleted.
Thanks a lot! You get the virtual hug: *hug*
:-)
Kind regards
robert

Thursday, March 8, 2012

Alter Index...Rebuild

Running SQL 2005, SP1
In migrating over to SQL 2005, we also took on a different indexing strategy,
going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
There are a couple of things that are worrisome. A sample of the ALTER INDEX
statement I use is included at the end of this message. I only run this on
indexes that are fragmented as per sys.dm_db_index_physical_stats. The
following are the two items of concern.
1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little growth,
if any to TempDB. So I assume it may be done in memory. TempDB is on drive C,
a set of internal SCSI disks. The data and log files are on the SAN, spread
across 26 disks.
2. The data file growth is significant (sometimes the log files, but mostly
the data files) when running ALTER INDEX. As noted in concern 1,
SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the data
files?
SAMPLE:
ALTER INDEX [name of index] ON [name of database].dbo.[name of table]
REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
Message posted via http://www.droptable.com
When you rebuild an index with either DBREINDEX or ALTER INDEX REBUILD it
completely rebuilds the indexes (this includes the table if there is a
clustered index) by creating a new copy of them in the data file(s). This
means you need free space in the data files to hold the old and new indexes
at the same time. Only when it is completed does it drop the original
indexes. So you always need plenty of free space in the data files for
operations such as these. You have been around here long enough to know by
know you should ALWAYS have plenty of free space in the database files both
data & Log. If you are getting growth in the data files it sounds like you
shrunk it at some point. This is also a bad idea to shrink the files. The
SORT_IN_TEMPDB can help reduce time and some of the space needed in the data
files as it can serve as a work space to sort the indexes etc during the
processing. But it does not remove the need for the place to rebuild the
index itself. You say there is little growth to Tempdb during this process.
Well again if there is any growth you didn't size the files correctly to
begin with. Lack of growth of the files has nothing to do with how much work
they are performing. As long as there is room in the data files for what it
has to do there should be no growth. That is the proper and expected
behavior.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7675a0e8fcb67@.uwe...
> Running SQL 2005, SP1
> In migrating over to SQL 2005, we also took on a different indexing
> strategy,
> going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
> There are a couple of things that are worrisome. A sample of the ALTER
> INDEX
> statement I use is included at the end of this message. I only run this on
> indexes that are fragmented as per sys.dm_db_index_physical_stats. The
> following are the two items of concern.
> 1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
> DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little
> growth,
> if any to TempDB. So I assume it may be done in memory. TempDB is on drive
> C,
> a set of internal SCSI disks. The data and log files are on the SAN,
> spread
> across 26 disks.
> 2. The data file growth is significant (sometimes the log files, but
> mostly
> the data files) when running ALTER INDEX. As noted in concern 1,
> SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the
> data
> files?
> SAMPLE:
> ALTER INDEX [name of index] ON [name of database].dbo.[name of table]
> REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
> --
> Message posted via http://www.droptable.com
>

Alter Index...Rebuild

Running SQL 2005, SP1
In migrating over to SQL 2005, we also took on a different indexing strategy
,
going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
There are a couple of things that are worrisome. A sample of the ALTER INDEX
statement I use is included at the end of this message. I only run this on
indexes that are fragmented as per sys.dm_db_index_physical_stats. The
following are the two items of concern.
1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little growth
,
if any to TempDB. So I assume it may be done in memory. TempDB is on drive C
,
a set of internal SCSI disks. The data and log files are on the SAN, spread
across 26 disks.
2. The data file growth is significant (sometimes the log files, but mostly
the data files) when running ALTER INDEX. As noted in concern 1,
SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the data
files?
SAMPLE:
ALTER INDEX [name of index] ON [name of database].dbo.[name of t
able]
REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
Message posted via http://www.droptable.comWhen you rebuild an index with either DBREINDEX or ALTER INDEX REBUILD it
completely rebuilds the indexes (this includes the table if there is a
clustered index) by creating a new copy of them in the data file(s). This
means you need free space in the data files to hold the old and new indexes
at the same time. Only when it is completed does it drop the original
indexes. So you always need plenty of free space in the data files for
operations such as these. You have been around here long enough to know by
know you should ALWAYS have plenty of free space in the database files both
data & Log. If you are getting growth in the data files it sounds like you
shrunk it at some point. This is also a bad idea to shrink the files. The
SORT_IN_TEMPDB can help reduce time and some of the space needed in the data
files as it can serve as a work space to sort the indexes etc during the
processing. But it does not remove the need for the place to rebuild the
index itself. You say there is little growth to Tempdb during this process.
Well again if there is any growth you didn't size the files correctly to
begin with. Lack of growth of the files has nothing to do with how much work
they are performing. As long as there is room in the data files for what it
has to do there should be no growth. That is the proper and expected
behavior.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7675a0e8fcb67@.uwe...
> Running SQL 2005, SP1
> In migrating over to SQL 2005, we also took on a different indexing
> strategy,
> going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
> There are a couple of things that are worrisome. A sample of the ALTER
> INDEX
> statement I use is included at the end of this message. I only run this on
> indexes that are fragmented as per sys.dm_db_index_physical_stats. The
> following are the two items of concern.
> 1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
> DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little
> growth,
> if any to TempDB. So I assume it may be done in memory. TempDB is on drive
> C,
> a set of internal SCSI disks. The data and log files are on the SAN,
> spread
> across 26 disks.
> 2. The data file growth is significant (sometimes the log files, but
> mostly
> the data files) when running ALTER INDEX. As noted in concern 1,
> SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the
> data
> files?
> SAMPLE:
> ALTER INDEX [name of index] ON [name of database].dbo.[name of
table]
> REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
> --
> Message posted via http://www.droptable.com
>

Alter Index...Rebuild

Running SQL 2005, SP1
In migrating over to SQL 2005, we also took on a different indexing strategy,
going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
There are a couple of things that are worrisome. A sample of the ALTER INDEX
statement I use is included at the end of this message. I only run this on
indexes that are fragmented as per sys.dm_db_index_physical_stats. The
following are the two items of concern.
1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little growth,
if any to TempDB. So I assume it may be done in memory. TempDB is on drive C,
a set of internal SCSI disks. The data and log files are on the SAN, spread
across 26 disks.
2. The data file growth is significant (sometimes the log files, but mostly
the data files) when running ALTER INDEX. As noted in concern 1,
SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the data
files?
SAMPLE:
ALTER INDEX [name of index] ON [name of database].dbo.[name of table]
REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
--
Message posted via http://www.sqlmonster.comWhen you rebuild an index with either DBREINDEX or ALTER INDEX REBUILD it
completely rebuilds the indexes (this includes the table if there is a
clustered index) by creating a new copy of them in the data file(s). This
means you need free space in the data files to hold the old and new indexes
at the same time. Only when it is completed does it drop the original
indexes. So you always need plenty of free space in the data files for
operations such as these. You have been around here long enough to know by
know you should ALWAYS have plenty of free space in the database files both
data & Log. If you are getting growth in the data files it sounds like you
shrunk it at some point. This is also a bad idea to shrink the files. The
SORT_IN_TEMPDB can help reduce time and some of the space needed in the data
files as it can serve as a work space to sort the indexes etc during the
processing. But it does not remove the need for the place to rebuild the
index itself. You say there is little growth to Tempdb during this process.
Well again if there is any growth you didn't size the files correctly to
begin with. Lack of growth of the files has nothing to do with how much work
they are performing. As long as there is room in the data files for what it
has to do there should be no growth. That is the proper and expected
behavior.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7675a0e8fcb67@.uwe...
> Running SQL 2005, SP1
> In migrating over to SQL 2005, we also took on a different indexing
> strategy,
> going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
> There are a couple of things that are worrisome. A sample of the ALTER
> INDEX
> statement I use is included at the end of this message. I only run this on
> indexes that are fragmented as per sys.dm_db_index_physical_stats. The
> following are the two items of concern.
> 1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
> DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little
> growth,
> if any to TempDB. So I assume it may be done in memory. TempDB is on drive
> C,
> a set of internal SCSI disks. The data and log files are on the SAN,
> spread
> across 26 disks.
> 2. The data file growth is significant (sometimes the log files, but
> mostly
> the data files) when running ALTER INDEX. As noted in concern 1,
> SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the
> data
> files?
> SAMPLE:
> ALTER INDEX [name of index] ON [name of database].dbo.[name of table]
> REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
> --
> Message posted via http://www.sqlmonster.com
>

alter index syntax

I'm using sql server 2005 sp1 on win2003 server
I want to reorganize all indices in my database.
Online-docu says that dbcc indexdefrag should not be used anymore.
Instead, 'alter index' should be used
My syntax for one table is like this:
alter index all on bew reorganize
sql server says:
Meldung 156, Ebene 15, Status 1, Zeile 1
Incorrect syntax near the keyword 'index'.
What's wrong ?
Furthermore I would like to know, what the sql-command looks like for
reorganizing indices for
ALL the tables in the database?My guess database isn't in 90 compatibility mode. See sp_dbcmptlevel. Also, see Books Online for
sample code on how to reorg all indexes for a database:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1154958955.385435.160610@.n13g2000cwa.googlegroups.com...
> I'm using sql server 2005 sp1 on win2003 server
> I want to reorganize all indices in my database.
> Online-docu says that dbcc indexdefrag should not be used anymore.
> Instead, 'alter index' should be used
> My syntax for one table is like this:
> alter index all on bew reorganize
> sql server says:
> Meldung 156, Ebene 15, Status 1, Zeile 1
> Incorrect syntax near the keyword 'index'.
> What's wrong ?
> Furthermore I would like to know, what the sql-command looks like for
> reorganizing indices for
> ALL the tables in the database?
>|||Hi Tibor,
that might be the reason.
So I tried to use this command
sp_dbcmptlevel mydb, 90
I get this error:
Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Inside the procedure I can see that only the levels 60, 65, 70, 80 are
allowed.
Also using object explorer (db options) I only can see 70 and 80.
What's the matter?
I'm testing with a new installed 2005 instance,
The database 'comes' from SQL 7.0 and I did a restore of the .bak file
on the 2005 server
Thank you for more help|||This sounds like you have connected to a SQL 2000 instance.
Try this:
SELECT serverproperty('ProductVersion')
--
HTH
Kalen Delaney, SQL Server MVP
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1155022695.236345.244990@.b28g2000cwb.googlegroups.com...
> Hi Tibor,
> that might be the reason.
> So I tried to use this command
> sp_dbcmptlevel mydb, 90
> I get this error:
> Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
> Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
> Inside the procedure I can see that only the levels 60, 65, 70, 80 are
> allowed.
> Also using object explorer (db options) I only can see 70 and 80.
> What's the matter?
> I'm testing with a new installed 2005 instance,
> The database 'comes' from SQL 7.0 and I did a restore of the .bak file
> on the 2005 server
> Thank you for more help
>

alter index syntax

I'm using sql server 2005 sp1 on win2003 server
I want to reorganize all indices in my database.
Online-docu says that dbcc indexdefrag should not be used anymore.
Instead, 'alter index' should be used
My syntax for one table is like this:
alter index all on bew reorganize
sql server says:
Meldung 156, Ebene 15, Status 1, Zeile 1
Incorrect syntax near the keyword 'index'.
What's wrong ?
Furthermore I would like to know, what the sql-command looks like for
reorganizing indices for
ALL the tables in the database?My guess database isn't in 90 compatibility mode. See sp_dbcmptlevel. Also,
see Books Online for
sample code on how to reorg all indexes for a database:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-
e57702230613.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1154958955.385435.160610@.n13g2000cwa.googlegroups.com...
> I'm using sql server 2005 sp1 on win2003 server
> I want to reorganize all indices in my database.
> Online-docu says that dbcc indexdefrag should not be used anymore.
> Instead, 'alter index' should be used
> My syntax for one table is like this:
> alter index all on bew reorganize
> sql server says:
> Meldung 156, Ebene 15, Status 1, Zeile 1
> Incorrect syntax near the keyword 'index'.
> What's wrong ?
> Furthermore I would like to know, what the sql-command looks like for
> reorganizing indices for
> ALL the tables in the database?
>|||Hi Tibor,
that might be the reason.
So I tried to use this command
sp_dbcmptlevel mydb, 90
I get this error:
Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Inside the procedure I can see that only the levels 60, 65, 70, 80 are
allowed.
Also using object explorer (db options) I only can see 70 and 80.
What's the matter?
I'm testing with a new installed 2005 instance,
The database 'comes' from SQL 7.0 and I did a restore of the .bak file
on the 2005 server
Thank you for more help|||This sounds like you have connected to a SQL 2000 instance.
Try this:
SELECT serverproperty('ProductVersion')
HTH
Kalen Delaney, SQL Server MVP
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1155022695.236345.244990@.b28g2000cwb.googlegroups.com...
> Hi Tibor,
> that might be the reason.
> So I tried to use this command
> sp_dbcmptlevel mydb, 90
> I get this error:
> Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
> Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
> Inside the procedure I can see that only the levels 60, 65, 70, 80 are
> allowed.
> Also using object explorer (db options) I only can see 70 and 80.
> What's the matter?
> I'm testing with a new installed 2005 instance,
> The database 'comes' from SQL 7.0 and I did a restore of the .bak file
> on the 2005 server
> Thank you for more help
>

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
Reorganizing a specified clustered index will compact all LOB columns that
are contained in the leaf level (data rows) of the clustered index" .
According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
" In SQL Server 2000, the only way you can compact LOBs in a table is to
unload and reload the LOB data"
This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
Is this correct?Mladen Andrijasevic wrote:
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> Is this correct?
from BOL:
ALTER INDEX
...
...
WITH ( LOB_COMPACTION = { ON | OFF } )
Specifies that all pages that contain large object (LOB) data are
compacted. The LOB data types are image, text, ntext, varchar(max),
nvarchar(max), varbinary(max), and xml. Compacting this data can improve
disk space use. The default is ON.|||Hi, according BOL (Jul 2007), ALTER INDEX ... REORGANIZE is Equivalent to
DBCC INDEXDEFRAG. is the same.
Regards,
"Mladen Andrijasevic" wrote:
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> Is this correct?|||I believe the following white has the info you need. It covers SQL2000, but
most materials should still be relevant.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Linchi
"Mladen Andrijasevic" wrote:
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> Is this correct?|||Zarko,
I am aware that LOB_COMPACTION default is ON . It was the point of my
question. From this it would fiollow that saying that "ALTER INDEX ...
REORGANIZE is Equivalent to DBCC INDEXDEFRAG " is wrong . But I have not come
across a statement saying so , or saying that ALTER INDEX ... REORGANIZE
should be immediately implemented on sql 2005 instead of DBCC INDEXDEFRAG
because it does more than DBCC INDEXDEFRAG , i.e. it can compact LOBs
Thanks
Mladen
"Zarko Jovanovic" wrote:
> Mladen Andrijasevic wrote:
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> > Is this correct?
> from BOL:
> ALTER INDEX
> ...
> ...
> WITH ( LOB_COMPACTION = { ON | OFF } )
> Specifies that all pages that contain large object (LOB) data are
> compacted. The LOB data types are image, text, ntext, varchar(max),
> nvarchar(max), varbinary(max), and xml. Compacting this data can improve
> disk space use. The default is ON.
>|||Carlos ,
Well, the purpose of my question was precisely to clarify how to reconcile
what is written in BOL in one place , i.e. that ALTER INDEX ...
REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
another place in SQL Server 2005 Books Online (September 2007) i.e. that
ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent if
ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
Thanks
Mladen
"Carlos A." wrote:
> Hi, according BOL (Jul 2007), ALTER INDEX ... REORGANIZE is Equivalent to
> DBCC INDEXDEFRAG. is the same.
> Regards,
>
> "Mladen Andrijasevic" wrote:
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> > Is this correct?|||Linchi,
I 've read Microsoft SQL Server 2000 Index Defragmentation Best Practices
and implemented its recommendations a few years ago . However, it cannot
provide an answer to my question since my question has todo with the
comparsion of DBCC INDEXDEFRAG of sql 2000 . to ALTER INDEX ... REORGANIZE of
sql 2005, which is not mentioned in the Server 2000 Index Defragmentation
Best Practices document since it appeared only in SQL 2005
Thanks
Mladen
"Linchi Shea" wrote:
> I believe the following white has the info you need. It covers SQL2000, but
> most materials should still be relevant.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Linchi
> "Mladen Andrijasevic" wrote:
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> > Is this correct?|||> Well, the purpose of my question was precisely to clarify how to
> reconcile
> what is written in BOL in one place , i.e. that ALTER INDEX ...
> REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> another place in SQL Server 2005 Books Online (September 2007) i.e. that
> ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent
> if
> ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
I think using the default behavior they are equivalent. Just because one
has some different *optional* commands does not make them completely
different animals.
Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
and COALESCE()?
In any case, I do agree that perhaps the wording could be a little less
ambiguous. Maybe you should click on the feedback item on that page in
Books Online, and voice your concerns? That feedback will make its way
directly to the writer of the topic.
A|||Aaron ,
I am not such a stickler over the wording at all. I just need to find out
whether it makes sense to implement ALTER INDEX . REORGANIZE over DBCC
INDEXDEFRAG on our SQL 2005 systems. If they were equivalent I would not
bother to do it right away . So would you say that ALTER INDEX . REORGANIZE
would better defrgarment than DBCC INDEXDEFRAG since ALTER INDEX .
REORGANIZE would compact LOBs and DBCC INDEXDEFRAG would not?
Thanks
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Well, the purpose of my question was precisely to clarify how to
> > reconcile
> > what is written in BOL in one place , i.e. that ALTER INDEX ...
> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> > another place in SQL Server 2005 Books Online (September 2007) i.e. that
> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent
> > if
> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
> I think using the default behavior they are equivalent. Just because one
> has some different *optional* commands does not make them completely
> different animals.
> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
> and COALESCE()?
> In any case, I do agree that perhaps the wording could be a little less
> ambiguous. Maybe you should click on the feedback item on that page in
> Books Online, and voice your concerns? That feedback will make its way
> directly to the writer of the topic.
> A
>
>|||Aaron,
Just noticed the " I think using the default behavior they are equivalent"
I do not think this is true either since LOB_COMPACTION = ON is the default
and that is precisely where they differ!
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Well, the purpose of my question was precisely to clarify how to
> > reconcile
> > what is written in BOL in one place , i.e. that ALTER INDEX ...
> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> > another place in SQL Server 2005 Books Online (September 2007) i.e. that
> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent
> > if
> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
> I think using the default behavior they are equivalent. Just because one
> has some different *optional* commands does not make them completely
> different animals.
> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
> and COALESCE()?
> In any case, I do agree that perhaps the wording could be a little less
> ambiguous. Maybe you should click on the feedback item on that page in
> Books Online, and voice your concerns? That feedback will make its way
> directly to the writer of the topic.
> A
>
>|||Sorry, my (clearly wrong) recollection was that LOB_COMPACTION defaulted to
OFF.
But still, I think it is weird for you to be asking us whether you should
use ALTER INDEX instead of DBCC. Isn't that really your call? Do you want
your LOBs compacted, or not? If not, then you can use ALTER INDEX with that
setting to OFF, no? Do you want your code to be forward compatible? I
envision that someday they will deprecate the DBCC command completely.
In any case, not really our decision.
"Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
in message news:BA1DD97B-3DB5-43A2-AAE0-07C8AC1674D2@.microsoft.com...
> Aaron,
> Just noticed the " I think using the default behavior they are equivalent"
> I do not think this is true either since LOB_COMPACTION = ON is the
> default
> and that is precisely where they differ!
> Mladen
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> > Well, the purpose of my question was precisely to clarify how to
>> > reconcile
>> > what is written in BOL in one place , i.e. that ALTER INDEX ...
>> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
>> > another place in SQL Server 2005 Books Online (September 2007) i.e.
>> > that
>> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be
>> > equivalent
>> > if
>> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
>> I think using the default behavior they are equivalent. Just because one
>> has some different *optional* commands does not make them completely
>> different animals.
>> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
>> and COALESCE()?
>> In any case, I do agree that perhaps the wording could be a little less
>> ambiguous. Maybe you should click on the feedback item on that page in
>> Books Online, and voice your concerns? That feedback will make its way
>> directly to the writer of the topic.
>> A
>>|||Hi Mladen
Internally DBCC INDEXDEFRAG and ALTER INDEX REORGANIZE use the same
algorithm, so the only 'improvements' in the REORGANIZE option are that
there are additional features you can control, such as the LOB compaction.
As Aaron states, whether or not you see the new syntax as an improvement
depends on whether you want to control LOB compaction or not. Going
forward, it's a good idea to use the ALTER INDEX because the DBCC option
will be eventually going away, but there is no word yet on when that will
be.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
in message news:91BF82DD-6C5A-4702-972A-EFD6A841A8FF@.microsoft.com...
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p
> 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to
> DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG
> cannot.
> Is this correct?|||Apparently there is a misunderstanding here. First the documentation
stated that the two (ALTER INDEX REORGANIZE and DBCC INDEXDEFRAG) were
equivalent, which they obviously are not. Next, you mistook the default
value for LOB_COMPACTION. This gives me the impression that not many have
been using ALTER INDEX REORGANIZE yet, else things would have been clarified
by now.
I do not expect you to make a decision for me, but to possibly point to a
study, white paper, where the performance benefits of LOB_COMPACTION
through ALTER INDEX REORGANIZE are quantified. Something on the line of a
sequel to Server 2000 Index Defragmentation Best Practices document . Is
there a Server 2005 Index Defragmentation Best Practices document planned?
I have not done LOB compaction through unload and reload of LOB data in SQL
2000 that Kalen Delaney mentioned in her book. Are the benefits of compaction
comparable to compacting of non LOB data? Any idiosyncrasies? If there are
documents discussing this topic in somewhat more detail I would definitely
switch to ALTER INDEX, once convinced of its benefits, even if it is not
backward compatible.
tks
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> Sorry, my (clearly wrong) recollection was that LOB_COMPACTION defaulted to
> OFF.
> But still, I think it is weird for you to be asking us whether you should
> use ALTER INDEX instead of DBCC. Isn't that really your call? Do you want
> your LOBs compacted, or not? If not, then you can use ALTER INDEX with that
> setting to OFF, no? Do you want your code to be forward compatible? I
> envision that someday they will deprecate the DBCC command completely.
> In any case, not really our decision.
>
> "Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
> in message news:BA1DD97B-3DB5-43A2-AAE0-07C8AC1674D2@.microsoft.com...
> >
> > Aaron,
> >
> > Just noticed the " I think using the default behavior they are equivalent"
> >
> > I do not think this is true either since LOB_COMPACTION = ON is the
> > default
> > and that is precisely where they differ!
> >
> > Mladen
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> > Well, the purpose of my question was precisely to clarify how to
> >> > reconcile
> >> > what is written in BOL in one place , i.e. that ALTER INDEX ...
> >> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> >> > another place in SQL Server 2005 Books Online (September 2007) i.e.
> >> > that
> >> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be
> >> > equivalent
> >> > if
> >> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
> >>
> >> I think using the default behavior they are equivalent. Just because one
> >> has some different *optional* commands does not make them completely
> >> different animals.
> >>
> >> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
> >> and COALESCE()?
> >>
> >> In any case, I do agree that perhaps the wording could be a little less
> >> ambiguous. Maybe you should click on the feedback item on that page in
> >> Books Online, and voice your concerns? That feedback will make its way
> >> directly to the writer of the topic.
> >>
> >> A
> >>
> >>
> >>
>
>|||> Apparently there is a misunderstanding here. First the documentation
> stated that the two (ALTER INDEX REORGANIZE and DBCC INDEXDEFRAG) were
> equivalent, which they obviously are not. Next, you mistook the default
> value for LOB_COMPACTION. This gives me the impression that not many have
> been using ALTER INDEX REORGANIZE yet, else things would have been
> clarified
> by now.
Or maybe we are using it in places where we don't have LOBs?
> I do not expect you to make a decision for me, but to possibly point to a
> study, white paper, where the performance benefits of LOB_COMPACTION
> through ALTER INDEX REORGANIZE are quantified.
<shrug>
I don't know of any. I could Google, but of course, so could you.
A|||Thanks Kalen,
I was responding to Aaronâ's post and did not notice your answer.
Is there a white paper, where the performance benefits of LOB_COMPACTION
through ALTER INDEX REORGANIZE are quantified? Something on the line of a
sequel to Server 2000 Index Defragmentation Best Practices document . Is
there a Server 2005 Index Defragmentation Best Practices document planned?
I have not used LOB compaction through unload and reload of LOB data that
you mentioned in your book. I do not think we could have done it (even if I
had known about it) for the same reason that we used DBCC INDEXDEFRAG in the
first place â' we need the system to be up 24/7 and the defragmenting
operation must be an online one. But I would definitely switch to ALTER
INDEX REORGANIZE if I could find some more documentation on LOB_COMPACTION .
Thank you for posting the new_helpindex command. Will definitely use it.
Mladen
"Kalen Delaney" wrote:
> Hi Mladen
> Internally DBCC INDEXDEFRAG and ALTER INDEX REORGANIZE use the same
> algorithm, so the only 'improvements' in the REORGANIZE option are that
> there are additional features you can control, such as the LOB compaction.
> As Aaron states, whether or not you see the new syntax as an improvement
> depends on whether you want to control LOB compaction or not. Going
> forward, it's a good idea to use the ALTER INDEX because the DBCC option
> will be eventually going away, but there is no word yet on when that will
> be.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
> in message news:91BF82DD-6C5A-4702-972A-EFD6A841A8FF@.microsoft.com...
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p
> > 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to
> > DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG
> > cannot.
> > Is this correct?
>
>|||>>Or maybe we are using it in places where we don't have LOBs?
Indeed. Sorry. Should have been more precise: â'This gives me the
impression that not many have been using ALTER INDEX REORGANIZE , in the
context of knowingly, deliberately compacting LOBs yet, else things would
have been clarified by nowâ'. I assumed the LOBs context from my initial
post.
Thanks for the answers.
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Apparently there is a misunderstanding here. First the documentation
> > stated that the two (ALTER INDEX REORGANIZE and DBCC INDEXDEFRAG) were
> > equivalent, which they obviously are not. Next, you mistook the default
> > value for LOB_COMPACTION. This gives me the impression that not many have
> > been using ALTER INDEX REORGANIZE yet, else things would have been
> > clarified
> > by now.
> Or maybe we are using it in places where we don't have LOBs?
> > I do not expect you to make a decision for me, but to possibly point to a
> > study, white paper, where the performance benefits of LOB_COMPACTION
> > through ALTER INDEX REORGANIZE are quantified.
> <shrug>
> I don't know of any. I could Google, but of course, so could you.
> A
>|||I'm not aware of any paper, but as Aaron suggests, you can use google as
well as any of us. My guess is that the impact would completely depend on
your application, and what you were doing with the lob data. It should be
very straightforward for you to run your own tests with and without lob
compaction, and check the performance difference.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
in message news:1017B786-D603-4252-A0FC-63E4AAC94E9A@.microsoft.com...
> Thanks Kalen,
> I was responding to Aaron's post and did not notice your answer.
> Is there a white paper, where the performance benefits of LOB_COMPACTION
> through ALTER INDEX REORGANIZE are quantified? Something on the line of a
> sequel to Server 2000 Index Defragmentation Best Practices document . Is
> there a Server 2005 Index Defragmentation Best Practices document planned?
> I have not used LOB compaction through unload and reload of LOB data that
> you mentioned in your book. I do not think we could have done it (even if
> I
> had known about it) for the same reason that we used DBCC INDEXDEFRAG in
> the
> first place - we need the system to be up 24/7 and the defragmenting
> operation must be an online one. But I would definitely switch to ALTER
> INDEX REORGANIZE if I could find some more documentation on LOB_COMPACTION
> .
> Thank you for posting the new_helpindex command. Will definitely use it.
> Mladen
>
> "Kalen Delaney" wrote:
>> Hi Mladen
>> Internally DBCC INDEXDEFRAG and ALTER INDEX REORGANIZE use the same
>> algorithm, so the only 'improvements' in the REORGANIZE option are that
>> there are additional features you can control, such as the LOB
>> compaction.
>> As Aaron states, whether or not you see the new syntax as an improvement
>> depends on whether you want to control LOB compaction or not. Going
>> forward, it's a good idea to use the ALTER INDEX because the DBCC option
>> will be eventually going away, but there is no word yet on when that will
>> be.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com>
>> wrote
>> in message news:91BF82DD-6C5A-4702-972A-EFD6A841A8FF@.microsoft.com...
>> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
>> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
>> > Reorganizing a specified clustered index will compact all LOB columns
>> > that
>> > are contained in the leaf level (data rows) of the clustered index" .
>> >
>> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine
>> > p
>> > 324
>> > " In SQL Server 2000, the only way you can compact LOBs in a table is
>> > to
>> > unload and reload the LOB data"
>> >
>> > This would make ALTER INDEX REORGANIZE not equivalent but superior to
>> > DBCC
>> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG
>> > cannot.
>> > Is this correct?
>>

Alter Database statement

Is it possible to issue an alter database statement across servers?
For example
Alter Database Server.Database
Also, the same question with dbcc shrinkdatabase.
Can you do dbcc shrinkdatabase('Server.Database')?
Thanks
Not directly. But if the linked server is configured so that you can execute remote stored
procedures, you can do:
EXEC Server.master.dbo.sp_executesql('ALTER DATABASE dbname...')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:73D8641F-7DED-48CA-80C9-762AC78D20C2@.microsoft.com...
> Is it possible to issue an alter database statement across servers?
> For example
> Alter Database Server.Database
> Also, the same question with dbcc shrinkdatabase.
> Can you do dbcc shrinkdatabase('Server.Database')?
> Thanks

Alter Database statement

Is it possible to issue an alter database statement across servers?
For example
Alter Database Server.Database
Also, the same question with dbcc shrinkdatabase.
Can you do dbcc shrinkdatabase('Server.Database')?
ThanksNot directly. But if the linked server is configured so that you can execute remote stored
procedures, you can do:
EXEC Server.master.dbo.sp_executesql('ALTER DATABASE dbname...')
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:73D8641F-7DED-48CA-80C9-762AC78D20C2@.microsoft.com...
> Is it possible to issue an alter database statement across servers?
> For example
> Alter Database Server.Database
> Also, the same question with dbcc shrinkdatabase.
> Can you do dbcc shrinkdatabase('Server.Database')?
> Thanks

Alter Database statement

Is it possible to issue an alter database statement across servers?
For example
Alter Database Server.Database
Also, the same question with dbcc shrinkdatabase.
Can you do dbcc shrinkdatabase('Server.Database')?
ThanksNot directly. But if the linked server is configured so that you can execute
remote stored
procedures, you can do:
EXEC Server.master.dbo.sp_executesql('ALTER DATABASE dbname...')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:73D8641F-7DED-48CA-80C9-762AC78D20C2@.microsoft.com...
> Is it possible to issue an alter database statement across servers?
> For example
> Alter Database Server.Database
> Also, the same question with dbcc shrinkdatabase.
> Can you do dbcc shrinkdatabase('Server.Database')?
> Thanks

Monday, February 13, 2012

Allocation Error in Master DB

Hi,
I wanted to find out if there is a way to fix Master DB. I ran DBCC and it
shows some allocation errors. I understand I can run Repaid_Allow_Data_Loss
with other databases but not with Master, Is this correct? Is there another
way to fix the DB rather than having to restore it?
Thank you.1. change sql server to single mode
2.stop sql server service
3.restore database
: RESTORE DATABASE master FROM DISK='C:\SQLDATA\...\...'
cheers|||Thank you but all my backups have the same errors as well so I need fix it
somehow.
"jongwoo" <jongwoo@.discussions.microsoft.com> wrote in message
news:DE879B08-EDAC-4B05-BDFA-0CBAFC9E5F28@.microsoft.com...
> 1. change sql server to single mode
> 2.stop sql server service
> 3.restore database
> : RESTORE DATABASE master FROM DISK='C:\SQLDATA\...\...'
> cheers|||If you give as much error message info as possible, we should be more able t
o
help out.

Thursday, March 29, 2012

alternate to DBCC command

alternate to DBCC command

alternate to DBCC command

Tuesday, March 20, 2012

alter table drop column and dbcc cleantable

alter table drop column and dbcc cleantable

alter table drop column and dbcc cleantable

Thursday, March 8, 2012

Alter Index...Rebuild

Alter Index...Rebuild

Alter Index...Rebuild

alter index syntax

alter index syntax

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

Alter Database statement

Alter Database statement

Alter Database statement

Monday, February 13, 2012

Allocation Error in Master DB

Allocation Error in Master DB

Sql Server Alignment problem

Blog Archive

About Me