release database unused space

Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to have the database release the space. I see no reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with another method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXDEFRAG.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> > Hi,
> >
> > I have deleted 100K records from a table in a database. I ran the DBCC
shrinkfile & DBCC shrinkdatabase to
> have the database release the space. I see no reduction in the database
size.
> >
> > I have run the following;
> > DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> > GO
> > DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> > GO
> > BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> > GO
> > DBCC SHRICKDATABASE (VIRTEN, 20)
> > GO
> > Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> > GO
> >
> > Please can anyone tell me, where I am making a mistake, or provide me
with another method to release the
> space.
> >
> > Many thanks.
> >
> > Daniel
>

release database unused space

Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrin
kfile & DBCC shrinkdatabase to have the database release the space. I see no
reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with a
nother method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXD
EFRAG.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile
& DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.">
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another
method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
shrinkfile & DBCC shrinkdatabase to[vbcol=seagreen]
> have the database release the space. I see no reduction in the database
size.
with another method to release the[vbcol=seagreen]
> space.
>sql

ReIndexing while using replication Important

Does anyone know of a good set of guidelines to follow, after running DBCC
Showcontig on a publisher of replication and DBCC reindexing is called for?
What are the pitfalls and things to watch out for? I can't afford to make a
mistake since there are three servers involved and it's in production.
I can do the reindexing during night time hours. I've read a lot of material
but haven't found "replication considerations" and have not found anything
other than generalities when explaining how do analyze the Index fragmenting
statistics.
Matthew Mark
For merge replication it helps to reindex or defragment the msmerge tables.
You need to stop the merge agents which you are doing this. Ideally you
would stop all users while you are doing the reindexing.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:15C56BD5-DED8-41D2-9C16-9C87B664751C@.microsoft.com...
> Does anyone know of a good set of guidelines to follow, after running DBCC
> Showcontig on a publisher of replication and DBCC reindexing is called
> for?
> What are the pitfalls and things to watch out for? I can't afford to make
> a
> mistake since there are three servers involved and it's in production.
> I can do the reindexing during night time hours. I've read a lot of
> material
> but haven't found "replication considerations" and have not found anything
> other than generalities when explaining how do analyze the Index
> fragmenting
> statistics.
> --
> Matthew Mark
>
|||Here's what I understand from this.
Since I couldn't find a MSMerge table I assume you mean to reindex the
Article (tables) in MSarticles from the publisher.
If so, how does this fall into the procedure. For example, based on what I
know now, here's how I would do this.
1. Turn all merge agents off.
2. Put DB in restricted mode.
3. Run DBCC reindex, looping through all tables in this database.
4. Put dB back in Full access mode.
5. Turn merge agents back on.
So, how does your suggesting fit in or modify this plan?
Does the reindexing naturally replicate to the subscribers? or ?
thanks
Matthew Mark
"Hilary Cotter" wrote:

> For merge replication it helps to reindex or defragment the msmerge tables.
> You need to stop the merge agents which you are doing this. Ideally you
> would stop all users while you are doing the reindexing.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
> news:15C56BD5-DED8-41D2-9C16-9C87B664751C@.microsoft.com...
>
>
|||reindexing does not propagate to the subscribers, I meant that you should
reinindex msmerge_contents, and msmerge_tombstone.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:2CB7AB6E-8D3B-48D8-A092-E55A8A25540A@.microsoft.com...[vbcol=seagreen]
> Here's what I understand from this.
> Since I couldn't find a MSMerge table I assume you mean to reindex the
> Article (tables) in MSarticles from the publisher.
> If so, how does this fall into the procedure. For example, based on what I
> know now, here's how I would do this.
> 1. Turn all merge agents off.
> 2. Put DB in restricted mode.
> 3. Run DBCC reindex, looping through all tables in this database.
> 4. Put dB back in Full access mode.
> 5. Turn merge agents back on.
> So, how does your suggesting fit in or modify this plan?
> Does the reindexing naturally replicate to the subscribers? or ?
>
> thanks
>
> --
> Matthew Mark
>
> "Hilary Cotter" wrote:
|||When I do reindex, and loop through the tables, doesn't it include these
tabels too?
Matthew Mark
"Hilary Cotter" wrote:

> reindexing does not propagate to the subscribers, I meant that you should
> reinindex msmerge_contents, and msmerge_tombstone.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
> news:2CB7AB6E-8D3B-48D8-A092-E55A8A25540A@.microsoft.com...
>
>
|||Matthew,
this partly depends on what you are reindexing - system tables or user
tables. Generally speaking though, I ensure synchronization isn't occurring
while reindexing takes place, otherwise there tend to be job failures
because of blocking.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
I have never run reindexing on a replicated db. I have ,though, run a script
that runs the reindexing on all user tables. Are these MSarticles etc tables
system tables? I'd assume they are since the replication process creates it.
To avoid asking you a lot of specific questions, is there a comprehensive
guide I can read? The microsoft info online is usually good, only if you
already know the pitfalls. I don't.
If I make a mistake here because I didn't do my homework..., well, I'm sure
you understand. We don't replicate on our development server, so I can't
exactly practice ahead of time.
Matthew Mark
"Paul Ibison" wrote:

> Matthew,
> this partly depends on what you are reindexing - system tables or user
> tables. Generally speaking though, I ensure synchronization isn't occurring
> while reindexing takes place, otherwise there tend to be job failures
> because of blocking.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||I am unsure what you mean here. Can you clarify it? I normally issue the
following dbcc dbreindex('msmerge_contents')
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:9038947F-DAA7-476C-92ED-729E905E77E3@.microsoft.com...[vbcol=seagreen]
> When I do reindex, and loop through the tables, doesn't it include these
> tabels too?
> --
> Matthew Mark
>
> "Hilary Cotter" wrote:
|||Matthew,
I don't know of any guide about reindexing and replication. However I'm not
too sure there's much to it. If you know the difference between DBCC
DBREINDEX and DBCC INDEXDEFRAG, understand locking and blocking and can see
what the agents require access to in order to work, these are the basic
facts. All I can sey is that a simple guideline is to run DBCC DBREINDEX not
overly often and when you do, try to ensure that synchronization isn't
occurring.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables?
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***
Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/de...es_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default...b;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default...b;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/d...>
_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/defaul...kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as
maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you sho
uld use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.
gbl...
> If you're using a non-named instance and are running SP4, you can use a -s
upportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named
instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/defaul...kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job wit
h two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you t
he script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2M
SFTNGP15.phx.gbl...
>

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.developersdex.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>>I need to reindex a table with a computed column. The column is not
>> included in any indexes, but when I run the DBCC it crashes with the
>> following error:
>> DBCC failed because the following SET options have incorrect settings:
>> 'QUOTED_IDENTIFIER'.
>> Any ideas on how I can reindex these tables'
>> Thanks!
>> Richard
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>

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
> >
> >
>

Reindexation

I have a very large database, in wich the indexes are constantly "getting lost". I execute DBCC Reindex and everything goes back to normal. Still, I don't know why this happens. I have to run this process once or twice a week, sometimes as a scheduled task, some others manually.
Why are the indexes 'getting lost'?
What could be wrong with the database?
Thanks in advance

Can you elaborate on what you mean by "indexes are getting lost"...are you saying the index no longer exists? That cannot be the case because dbcc reindex implies you have an index that is being reindexed.

Thx

Ajay

|||

I'm deeply sorry for not being able to put this in other way. It's just that i do not understand what is happening, even less how to ask it or explain it... someone told me that "the indexes get corrupted". Still, I don't know what this means, or why...

Thans again

Eduardo

|||

You might find some help with the DBCC commands. They will help you with physical and logical errors. Here is a good link for an introduction to those:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=30&rl=1

And here is a more in-depth explanation of the DBCC CHECKDB command:

http://msdn2.microsoft.com/en-us/library/aa258278(SQL.80).aspx

reindex and shrinking question

Hi,
I have a large table and would like to run dbcc dbreindex
on the table and also shrink the database. As I understand
it, both of these can be used to remove fragmentation. My
question is what is the correct order? Reindex first then
shrink or vice-versa?
TIA,
JBShrink then reindex. If you do it the other way, the shrink will scatter
your index into the available spaces and you work will have been for naught.
Russell Fields
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:5f5301c49281$20d2aa10$a601280a@.phx.gbl...
> Hi,
> I have a large table and would like to run dbcc dbreindex
> on the table and also shrink the database. As I understand
> it, both of these can be used to remove fragmentation. My
> question is what is the correct order? Reindex first then
> shrink or vice-versa?
> TIA,
> JB|||Thanks again Russell,
final question - do you need to do shrink, reindex
iteratively until nothing happens, as the reindex will
presumably free up some pages, or are the pages it frees
up already at the end of the file.
TIA,
JB|||Actually ideally you don't want to shrink at all. Shrinking will fragment
your tables and indexes and you need lots of free space in the database to
do operate properly for such things as Reindexing. If you shrink the DB and
remove most or all of the free space then reindex it will just have to grow
the database all over again. If the database grew to that size before it
will most likely need that much room again. The best thing to do is usually
leave it at that size. You can then reindex the tables and get the best
chance of optimized indexes and won't have to worry about the files growing
when you do so.
--
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:607501c492a9$c0a343b0$a501280a@.phx.gbl...
> Thanks again Russell,
> final question - do you need to do shrink, reindex
> iteratively until nothing happens, as the reindex will
> presumably free up some pages, or are the pages it frees
> up already at the end of the file.
> TIA,
> JB
>|||Thanks Andrew,
so the number of page reads would be the same if you do
shrink followed by reindex compared to reindex on its own?
TIA,
JB|||Well the number of pages in the index will be the same given they both use
the same fill factor. But the order in which they get stored on disk may
not be the same.
--
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:68e701c493e7$783bcd10$a501280a@.phx.gbl...
> Thanks Andrew,
> so the number of page reads would be the same if you do
> shrink followed by reindex compared to reindex on its own?
> TIA,
> JB

reindex and shrinking question

Hi,
I have a large table and would like to run dbcc dbreindex
on the table and also shrink the database. As I understand
it, both of these can be used to remove fragmentation. My
question is what is the correct order? Reindex first then
shrink or vice-versa?
TIA,
JB
Shrink then reindex. If you do it the other way, the shrink will scatter
your index into the available spaces and you work will have been for naught.
Russell Fields
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:5f5301c49281$20d2aa10$a601280a@.phx.gbl...
> Hi,
> I have a large table and would like to run dbcc dbreindex
> on the table and also shrink the database. As I understand
> it, both of these can be used to remove fragmentation. My
> question is what is the correct order? Reindex first then
> shrink or vice-versa?
> TIA,
> JB
|||Thanks again Russell,
final question - do you need to do shrink, reindex
iteratively until nothing happens, as the reindex will
presumably free up some pages, or are the pages it frees
up already at the end of the file.
TIA,
JB
|||Actually ideally you don't want to shrink at all. Shrinking will fragment
your tables and indexes and you need lots of free space in the database to
do operate properly for such things as Reindexing. If you shrink the DB and
remove most or all of the free space then reindex it will just have to grow
the database all over again. If the database grew to that size before it
will most likely need that much room again. The best thing to do is usually
leave it at that size. You can then reindex the tables and get the best
chance of optimized indexes and won't have to worry about the files growing
when you do so.
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:607501c492a9$c0a343b0$a501280a@.phx.gbl...
> Thanks again Russell,
> final question - do you need to do shrink, reindex
> iteratively until nothing happens, as the reindex will
> presumably free up some pages, or are the pages it frees
> up already at the end of the file.
> TIA,
> JB
>
|||Thanks Andrew,
so the number of page reads would be the same if you do
shrink followed by reindex compared to reindex on its own?
TIA,
JB
|||Well the number of pages in the index will be the same given they both use
the same fill factor. But the order in which they get stored on disk may
not be the same.
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:68e701c493e7$783bcd10$a501280a@.phx.gbl...
> Thanks Andrew,
> so the number of page reads would be the same if you do
> shrink followed by reindex compared to reindex on its own?
> TIA,
> JB

re-index

Take a look at dbcc dbreindex on Books Online.
Ben Nevarez, MCDBA, OCP
Database Administrator
"00KobeBrian" wrote:

> How do you re-index in SQL 2000? Thanks.
>
>Hi,
Check following two articles:
http://msdn2.microsoft.com/en-us/library/ms181671.aspx
http://msdn2.microsoft.com/en-us/library/ms188388.aspx
They apply to SQL 2000 as well.
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||Hi Vincent,
Looks like alter index does not apply to SQL Server 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vincent Xu [MSFT]" wrote:

> Hi,
> Check following two articles:
> http://msdn2.microsoft.com/en-us/library/ms181671.aspx
> http://msdn2.microsoft.com/en-us/library/ms188388.aspx
> They apply to SQL 2000 as well.
> Thanks.
>
> Best regards,
> Vincent Xu
> Microsoft Online Partner Support
> ========================================
==============
> Get Secure! - www.microsoft.com/security
> ========================================
==============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties,and confers no rights.
> ========================================
==============
>
> --
>|||How do you re-index in SQL 2000? Thanks.|||Take a look at dbcc dbreindex on Books Online.
Ben Nevarez, MCDBA, OCP
Database Administrator
"00KobeBrian" wrote:

> How do you re-index in SQL 2000? Thanks.
>
>|||Hi,
Check following two articles:
http://msdn2.microsoft.com/en-us/library/ms181671.aspx
http://msdn2.microsoft.com/en-us/library/ms188388.aspx
They apply to SQL 2000 as well.
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||Hi Vincent,
Looks like alter index does not apply to SQL Server 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vincent Xu [MSFT]" wrote:

> Hi,
> Check following two articles:
> http://msdn2.microsoft.com/en-us/library/ms181671.aspx
> http://msdn2.microsoft.com/en-us/library/ms188388.aspx
> They apply to SQL 2000 as well.
> Thanks.
>
> Best regards,
> Vincent Xu
> Microsoft Online Partner Support
> ========================================
==============
> Get Secure! - www.microsoft.com/security
> ========================================
==============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties,and confers no rights.
> ========================================
==============
>
> --
>|||Hi All,
Sorry, my mistake.I assume this as a matter of course
We have to use DBCC
Check following article:
<http://www.microsoft.com/technet/pr...in/ss2kidbp.msp
x>
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<DD275389-46FA-4FA8-AD04-C9AB9FE75B21@.microsoft.com>
<rJ$Z6gAnGHA.2028@.TK2MSFTNGXA01.phx.gbl>[vbcol=seagreen]
so[vbcol=seagreen]
rights.[vbcol=seagreen]|||Hi All,
Sorry, my mistake.I assume this as a matter of course
We have to use DBCC
Check following article:
<http://www.microsoft.com/technet/pr...in/ss2kidbp.msp
x>
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<DD275389-46FA-4FA8-AD04-C9AB9FE75B21@.microsoft.com>
<rJ$Z6gAnGHA.2028@.TK2MSFTNGXA01.phx.gbl>[vbcol=seagreen]
so[vbcol=seagreen]
rights.[vbcol=seagreen]

Reindex

The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
takes about 1hr and 20 minutes in a 64 bit server. The same set of databases
exist on both servers and the file structure also is the same with the drives
for the database files for both being SAN LUNs. The activity on SQL Server is
also the same for both servers when the reindex job is run , there is no one
else on the system. The 32 bit server has only 2G available to SQL whereas
the 64 bit has 8G available to SQL.
How many processors on the 32 bit and the 64bit? What is your MAXDOP set
to? Just because the 64bit is connected to a SAN does not mean you will get
the same performance as another LUN.
Andrew J. Kelly SQL MVP
"MANCHO" <MANCHO@.discussions.microsoft.com> wrote in message
news:3EC0BE26-484E-4714-A25D-35A038E2D7A7@.microsoft.com...
> The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
> takes about 1hr and 20 minutes in a 64 bit server. The same set of
> databases
> exist on both servers and the file structure also is the same with the
> drives
> for the database files for both being SAN LUNs. The activity on SQL Server
> is
> also the same for both servers when the reindex job is run , there is no
> one
> else on the system. The 32 bit server has only 2G available to SQL whereas
> the 64 bit has 8G available to SQL.

Reindex

The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
takes about 1hr and 20 minutes in a 64 bit server. The same set of databases
exist on both servers and the file structure also is the same with the drive
s
for the database files for both being SAN LUNs. The activity on SQL Server i
s
also the same for both servers when the reindex job is run , there is no one
else on the system. The 32 bit server has only 2G available to SQL whereas
the 64 bit has 8G available to SQL.How many processors on the 32 bit and the 64bit? What is your MAXDOP set
to? Just because the 64bit is connected to a SAN does not mean you will get
the same performance as another LUN.
Andrew J. Kelly SQL MVP
"MANCHO" <MANCHO@.discussions.microsoft.com> wrote in message
news:3EC0BE26-484E-4714-A25D-35A038E2D7A7@.microsoft.com...
> The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
> takes about 1hr and 20 minutes in a 64 bit server. The same set of
> databases
> exist on both servers and the file structure also is the same with the
> drives
> for the database files for both being SAN LUNs. The activity on SQL Server
> is
> also the same for both servers when the reindex job is run , there is no
> one
> else on the system. The 32 bit server has only 2G available to SQL whereas
> the 64 bit has 8G available to SQL.

Reindex

How do you keep a system running optimum if you can't run
dbcc reindex because it locks the tables? The indexdefrag
has no perceptible performance increase, or very little
compared to rebuilding the indexes.
The db is about 60G. If we cant run the reindex because
of other jobs running all night that would get killed by
it, the speed the next day is affected, searches take
longer, concurrency gets affected, etc.
MichelIt's hard to believe that indexdefrag has little effect. If one nights
worth of work fragments the tables that badly then you might want to change
the fill factor or possibly rethink the clustered index. How fragmented do
the tables get after just one night of work?
--
Andrew J. Kelly
SQL Server MVP
"Michel" <michel@.micro-world.ca> wrote in message
news:081501c37735$a9c52ef0$a101280a@.phx.gbl...
> How do you keep a system running optimum if you can't run
> dbcc reindex because it locks the tables? The indexdefrag
> has no perceptible performance increase, or very little
> compared to rebuilding the indexes.
> The db is about 60G. If we cant run the reindex because
> of other jobs running all night that would get killed by
> it, the speed the next day is affected, searches take
> longer, concurrency gets affected, etc.
> Michel|||dbcc showcontig shows between 10 - 50% fragmentation on
the tables.
Some has high as 100% (log tables with lots of inserts).
The fill factor is 80% on tables. Its really not that much
new data.
All tables have a decent clustered index. It's just a
matter of one or
2 tables. This DB is fully relational with about 300
related tables.
It just seems that when data is inserted, the index just
dosent work
well anymore until I reindex. I may be missing something?
80% fill
factor on a table with 10M rows, mostly queried all day,
maybe 20-50 new
records added not much more. All the related tables get a
few records
here and there inserted. I can give more details but not
sure wich
details to give to help.
Michel
>--Original Message--
>It's hard to believe that indexdefrag has little
effect. If one nights
>worth of work fragments the tables that badly then you
might want to change
>the fill factor or possibly rethink the clustered
index. How fragmented do
>the tables get after just one night of work?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Michel" <michel@.micro-world.ca> wrote in message
>news:081501c37735$a9c52ef0$a101280a@.phx.gbl...
>> How do you keep a system running optimum if you can't
run
>> dbcc reindex because it locks the tables? The
indexdefrag
>> has no perceptible performance increase, or very little
>> compared to rebuilding the indexes.
>> The db is about 60G. If we cant run the reindex because
>> of other jobs running all night that would get killed by
>> it, the speed the next day is affected, searches take
>> longer, concurrency gets affected, etc.
>> Michel
>
>.
>

Reindex

The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
takes about 1hr and 20 minutes in a 64 bit server. The same set of databases
exist on both servers and the file structure also is the same with the drives
for the database files for both being SAN LUNs. The activity on SQL Server is
also the same for both servers when the reindex job is run , there is no one
else on the system. The 32 bit server has only 2G available to SQL whereas
the 64 bit has 8G available to SQL.How many processors on the 32 bit and the 64bit? What is your MAXDOP set
to? Just because the 64bit is connected to a SAN does not mean you will get
the same performance as another LUN.
--
Andrew J. Kelly SQL MVP
"MANCHO" <MANCHO@.discussions.microsoft.com> wrote in message
news:3EC0BE26-484E-4714-A25D-35A038E2D7A7@.microsoft.com...
> The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
> takes about 1hr and 20 minutes in a 64 bit server. The same set of
> databases
> exist on both servers and the file structure also is the same with the
> drives
> for the database files for both being SAN LUNs. The activity on SQL Server
> is
> also the same for both servers when the reindex job is run , there is no
> one
> else on the system. The 32 bit server has only 2G available to SQL whereas
> the 64 bit has 8G available to SQL.