What if i update a dbid from 14 to 24 say for example, is there a reference
to it in any system tables in the corresponding user database itself that i
would also need to update. Just curious.
Will the db go into suspect mode or will it just continue to function as
normal ?I don't think there are references in the database itself to the dbid, but
there are plenty of references in master to the database id, including in
sysxlogins, which you also asked about. Some of the tables are
pseudo-tables, so they would probably be fine, but many are real tables what
could be corrupted if you updated a dbid.
I have never done this, so I can't tell you for sure what might break.
This query will show you all the columns in tables in master that reference
dbid:
use master
select name, object_name(id), objectproperty(id, 'tableisfake') from
syscolumns where name = 'dbid'
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ORSqfECdDHA.372@.TK2MSFTNGP11.phx.gbl...
> What if i update a dbid from 14 to 24 say for example, is there a
reference
> to it in any system tables in the corresponding user database itself that
i
> would also need to update. Just curious.
> Will the db go into suspect mode or will it just continue to function as
> normal ?
>|||Hi Hassan,
I've never tried this, a lot of the system uses the database name as it's
key, so maybe you would get away without breaking too much
......but any update to system tables is unsupported..........
Why would you want to do this ?
Regards,
Clive Challinor [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label itself. Show all posts
Showing posts with label itself. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Reinstalling database devices
Hello,
I am experiencing the following problem: The SAs were
upgrading apps on the server and the server literally ate
itself. Thus requiring a reinstall of SQL Server.
However, the data and log files are still in tact on the
box. The backup tapes are empty and I am trying to
recreate the devices and databases using the existing
files. Is that possible? If yes, how would I proceed?
Thanks.You can hopefully attach the database file using sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Albert H. Offer" <ahoffer@.magellanhealth.com> wrote in message
news:0ebe01c38f34$df72f430$a101280a@.phx.gbl...
> Hello,
> I am experiencing the following problem: The SAs were
> upgrading apps on the server and the server literally ate
> itself. Thus requiring a reinstall of SQL Server.
> However, the data and log files are still in tact on the
> box. The backup tapes are empty and I am trying to
> recreate the devices and databases using the existing
> files. Is that possible? If yes, how would I proceed?
> Thanks.|||Hi Albert, if the old directories are still valid, replacing the new master
with old one will be enough: it will point to your data files.
Another option (if the old master is not available) would be using
sp_attach_db to fill the new master with the information about your old db
data files.
The first option is better because the old master will be on-line with all
of your configuration options, logins, etc.
Syntax about sp_attach_db is bellow (from books on line):
sp_attach_db
Attaches a database to a server.
Syntax
sp_attach_db [ @.dbname = ] 'dbname'
, [ @.filename1 = ] 'filename_n' [ ,...16 ]
Arguments
[@.dbname =] 'dbname'
Is the name of the database to be attached to the server. The name must be
unique. dbname is sysname, with a default of NULL.
[@.filename1 =] 'filename_n'
Is the physical name, including path, of a database file. filename_n is
nvarchar(260), with a default of NULL. There can be up to 16 file names
specified. The parameter names start at @.filename1 and increment to
@.filename16. The file name list must include at least the primary file,
which contains the system tables that point to other files in the database.
The list must also include any files that were moved after the database was
detached.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
sp_attach_db should only be executed on databases that were previously
detached from the database server using an explicit sp_detach_db operation.
If more than 16 files must be specified, use CREATE DATABASE with the FOR
ATTACH clause.
If you attach a database to a server other than the server from which the
database was detached, and the detached database was enabled for
replication, you should run sp_removedbreplication to remove replication
from the database.
Permissions
Only members of the sysadmin and dbcreator fixed server roles can execute
this procedure.
Examples
This example attaches two files from pubs to the current server.
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
hth,
Roberto de Souza Santos.
"Albert H. Offer" <ahoffer@.magellanhealth.com> wrote in message
news:0ebe01c38f34$df72f430$a101280a@.phx.gbl...
> Hello,
> I am experiencing the following problem: The SAs were
> upgrading apps on the server and the server literally ate
> itself. Thus requiring a reinstall of SQL Server.
> However, the data and log files are still in tact on the
> box. The backup tapes are empty and I am trying to
> recreate the devices and databases using the existing
> files. Is that possible? If yes, how would I proceed?
> Thanks.|||Thanks Tibor. I forgot to mention that the box is running SQL Server
6.5.. The backup tapes are no good, so I don't have a good backup of
Master.
Albert H. Offer
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Then you need to use DISK REINIT and DISK REFIT. Make sure you read all you can find about these
commands. It is not even closely as easy as in 7.0 or 2000 as you need to get the database fragments
right with DISK REINIT.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Albert Offer" <ahoffer@.magellanhealth.com> wrote in message
news:eFLcGN0jDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Thanks Tibor. I forgot to mention that the box is running SQL Server
> 6.5.. The backup tapes are no good, so I don't have a good backup of
> Master.
> Albert H. Offer
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
I am experiencing the following problem: The SAs were
upgrading apps on the server and the server literally ate
itself. Thus requiring a reinstall of SQL Server.
However, the data and log files are still in tact on the
box. The backup tapes are empty and I am trying to
recreate the devices and databases using the existing
files. Is that possible? If yes, how would I proceed?
Thanks.You can hopefully attach the database file using sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Albert H. Offer" <ahoffer@.magellanhealth.com> wrote in message
news:0ebe01c38f34$df72f430$a101280a@.phx.gbl...
> Hello,
> I am experiencing the following problem: The SAs were
> upgrading apps on the server and the server literally ate
> itself. Thus requiring a reinstall of SQL Server.
> However, the data and log files are still in tact on the
> box. The backup tapes are empty and I am trying to
> recreate the devices and databases using the existing
> files. Is that possible? If yes, how would I proceed?
> Thanks.|||Hi Albert, if the old directories are still valid, replacing the new master
with old one will be enough: it will point to your data files.
Another option (if the old master is not available) would be using
sp_attach_db to fill the new master with the information about your old db
data files.
The first option is better because the old master will be on-line with all
of your configuration options, logins, etc.
Syntax about sp_attach_db is bellow (from books on line):
sp_attach_db
Attaches a database to a server.
Syntax
sp_attach_db [ @.dbname = ] 'dbname'
, [ @.filename1 = ] 'filename_n' [ ,...16 ]
Arguments
[@.dbname =] 'dbname'
Is the name of the database to be attached to the server. The name must be
unique. dbname is sysname, with a default of NULL.
[@.filename1 =] 'filename_n'
Is the physical name, including path, of a database file. filename_n is
nvarchar(260), with a default of NULL. There can be up to 16 file names
specified. The parameter names start at @.filename1 and increment to
@.filename16. The file name list must include at least the primary file,
which contains the system tables that point to other files in the database.
The list must also include any files that were moved after the database was
detached.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
sp_attach_db should only be executed on databases that were previously
detached from the database server using an explicit sp_detach_db operation.
If more than 16 files must be specified, use CREATE DATABASE with the FOR
ATTACH clause.
If you attach a database to a server other than the server from which the
database was detached, and the detached database was enabled for
replication, you should run sp_removedbreplication to remove replication
from the database.
Permissions
Only members of the sysadmin and dbcreator fixed server roles can execute
this procedure.
Examples
This example attaches two files from pubs to the current server.
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
hth,
Roberto de Souza Santos.
"Albert H. Offer" <ahoffer@.magellanhealth.com> wrote in message
news:0ebe01c38f34$df72f430$a101280a@.phx.gbl...
> Hello,
> I am experiencing the following problem: The SAs were
> upgrading apps on the server and the server literally ate
> itself. Thus requiring a reinstall of SQL Server.
> However, the data and log files are still in tact on the
> box. The backup tapes are empty and I am trying to
> recreate the devices and databases using the existing
> files. Is that possible? If yes, how would I proceed?
> Thanks.|||Thanks Tibor. I forgot to mention that the box is running SQL Server
6.5.. The backup tapes are no good, so I don't have a good backup of
Master.
Albert H. Offer
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Then you need to use DISK REINIT and DISK REFIT. Make sure you read all you can find about these
commands. It is not even closely as easy as in 7.0 or 2000 as you need to get the database fragments
right with DISK REINIT.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Albert Offer" <ahoffer@.magellanhealth.com> wrote in message
news:eFLcGN0jDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Thanks Tibor. I forgot to mention that the box is running SQL Server
> 6.5.. The backup tapes are no good, so I don't have a good backup of
> Master.
> Albert H. Offer
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Monday, March 12, 2012
reindexing table with blob data type
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advanceUnfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> > Just to clarify, what Tom means is to bcp out/in the data, not to run
DBCC
> > DBREINDEX on the table.
> >
> > We have this fixed in SQL Server 2005.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > > Unfortunately, the only way to defrag tables with BLOB columns is to
> > rebuild
> > > the table.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Columnist, SQL Server Professional
> > > Toronto, ON Canada
> > > www.pinpub.com
> > > .
> > > "james" <kush@.brandes.com> wrote in message
> > > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > > Hi! Is there a way to defrag image data type column in a table? I use
> dbcc
> > > dbreindex on the table itself during maintenance period, but I don't
> know
> > if
> > > that is sufficient to defrag those pages which actually stores image
> data
> > > type. We use third party application and they designed to put some
> > > transaction information on image data type column. Now we are
> experiencing
> > > gradual degradation on performance and wondering if it is related to
> image
> > > data type and its growth.
> > >
> > > Sql server 2000 sp3a
> > >
> > > thanks in advance
> > >
> > >
> >
> >
>|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advanceUnfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> > Just to clarify, what Tom means is to bcp out/in the data, not to run
DBCC
> > DBREINDEX on the table.
> >
> > We have this fixed in SQL Server 2005.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > > Unfortunately, the only way to defrag tables with BLOB columns is to
> > rebuild
> > > the table.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Columnist, SQL Server Professional
> > > Toronto, ON Canada
> > > www.pinpub.com
> > > .
> > > "james" <kush@.brandes.com> wrote in message
> > > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > > Hi! Is there a way to defrag image data type column in a table? I use
> dbcc
> > > dbreindex on the table itself during maintenance period, but I don't
> know
> > if
> > > that is sufficient to defrag those pages which actually stores image
> data
> > > type. We use third party application and they designed to put some
> > > transaction information on image data type column. Now we are
> experiencing
> > > gradual degradation on performance and wondering if it is related to
> image
> > > data type and its growth.
> > >
> > > Sql server 2000 sp3a
> > >
> > > thanks in advance
> > >
> > >
> >
> >
>|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>
reindexing table with blob data type
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance
Unfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance
|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>
|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>
|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>
|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
DBCC
> rights.
> dbcc
> know
> data
> experiencing
> image
>
|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance
Unfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance
|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>
|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>
|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>
|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
DBCC
> rights.
> dbcc
> know
> data
> experiencing
> image
>
|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>
reindexing table with blob data type
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advanceUnfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
DBCC[vbcol=seagreen]
> rights.
> dbcc
> know
> data
> experiencing
> image
>|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advanceUnfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
DBCC[vbcol=seagreen]
> rights.
> dbcc
> know
> data
> experiencing
> image
>|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>
Subscribe to:
Posts (Atom)