Defragment tables that have no clustered index

Hi!
We have several SQL Servers in a system that replicates
information in non-realtime between them using MSMQ and
Biztalk and to guarantee uniqueness it uses GUID:s as
primary and foreign keys. When planning this solution we
were recommended by Microsoft to use only non-clustered
indexes on these tables.
Since there are a lot of inserts and updates to the data
in this system we have now got a lot of really
fragmentated tables but since we have no clustered indexes
DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
solve this problem?
I have searched the newsgroups (and of course "Inside sQL
Server 2000", Hi Kalen! Any suggestions..?) but found no
answers that works. After reading a post here I tried both
DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
hardly effects the terrible scan density (DBCC SHOWCONTIG)
for these tables. The only other solution I have read
about is to use BCP to export and import the tables but
this quite complicated solution can't really be included
in our weekly maintenance job which is what we want.
Please, anyone, suggestions? I can't be the only one with
this problem..?
- AllanRead in "BOL - DBCC DBREINDEX" after reindexing, update
your statistics manually after the reindex.
Greg
>--Original Message--
>Hi!
>We have several SQL Servers in a system that replicates
>information in non-realtime between them using MSMQ and
>Biztalk and to guarantee uniqueness it uses GUID:s as
>primary and foreign keys. When planning this solution we
>were recommended by Microsoft to use only non-clustered
>indexes on these tables.
>Since there are a lot of inserts and updates to the data
>in this system we have now got a lot of really
>fragmentated tables but since we have no clustered
indexes
>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
>solve this problem?
>I have searched the newsgroups (and of course "Inside sQL
>Server 2000", Hi Kalen! Any suggestions..?) but found no
>answers that works. After reading a post here I tried
both
>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
>hardly effects the terrible scan density (DBCC
SHOWCONTIG)
>for these tables. The only other solution I have read
>about is to use BCP to export and import the tables but
>this quite complicated solution can't really be included
>in our weekly maintenance job which is what we want.
>Please, anyone, suggestions? I can't be the only one with
>this problem..?
>- Allan
>.
>|||There's no easy way to reorg (or perhaps "compact" is a better word as the data isn't sorted in any
way) for a heap. Two ways I can think of:
Create a clustered index and drop it.
Export/import of the data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
news:03d101c38386$33166d70$a301280a@.phx.gbl...
> As far as I have understood (and tested) neither DBCC
> DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> have clustered indexes on these tables the data is not
> stored on the leaf level of the index but in a heap. What
> I want to know is how to defragment this heap..
> >--Original Message--
> >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> >your statistics manually after the reindex.
> >
> >Greg
> >
> >>--Original Message--
> >>Hi!
> >>
> >>We have several SQL Servers in a system that replicates
> >>information in non-realtime between them using MSMQ and
> >>Biztalk and to guarantee uniqueness it uses GUID:s as
> >>primary and foreign keys. When planning this solution we
> >>were recommended by Microsoft to use only non-clustered
> >>indexes on these tables.
> >>
> >>Since there are a lot of inserts and updates to the data
> >>in this system we have now got a lot of really
> >>fragmentated tables but since we have no clustered
> >indexes
> >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> to
> >>solve this problem?
> >>
> >>I have searched the newsgroups (and of course "Inside
> sQL
> >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> >>answers that works. After reading a post here I tried
> >both
> >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> it
> >>hardly effects the terrible scan density (DBCC
> >SHOWCONTIG)
> >>for these tables. The only other solution I have read
> >>about is to use BCP to export and import the tables but
> >>this quite complicated solution can't really be included
> >>in our weekly maintenance job which is what we want.
> >>
> >>Please, anyone, suggestions? I can't be the only one
> with
> >>this problem..?
> >>
> >>- Allan
> >>
> >>.
> >>
> >.
> >|||Yes Tibor has it correct. That's one of the reasons I suggest that most
tables have a clustered index.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23W979hBhDHA.616@.TK2MSFTNGP11.phx.gbl...
> There's no easy way to reorg (or perhaps "compact" is a better word as the
data isn't sorted in any
> way) for a heap. Two ways I can think of:
> Create a clustered index and drop it.
> Export/import of the data.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
> news:03d101c38386$33166d70$a301280a@.phx.gbl...
> > As far as I have understood (and tested) neither DBCC
> > DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> > have clustered indexes on these tables the data is not
> > stored on the leaf level of the index but in a heap. What
> > I want to know is how to defragment this heap..
> >
> > >--Original Message--
> > >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> > >your statistics manually after the reindex.
> > >
> > >Greg
> > >
> > >>--Original Message--
> > >>Hi!
> > >>
> > >>We have several SQL Servers in a system that replicates
> > >>information in non-realtime between them using MSMQ and
> > >>Biztalk and to guarantee uniqueness it uses GUID:s as
> > >>primary and foreign keys. When planning this solution we
> > >>were recommended by Microsoft to use only non-clustered
> > >>indexes on these tables.
> > >>
> > >>Since there are a lot of inserts and updates to the data
> > >>in this system we have now got a lot of really
> > >>fragmentated tables but since we have no clustered
> > >indexes
> > >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> > to
> > >>solve this problem?
> > >>
> > >>I have searched the newsgroups (and of course "Inside
> > sQL
> > >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> > >>answers that works. After reading a post here I tried
> > >both
> > >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> > it
> > >>hardly effects the terrible scan density (DBCC
> > >SHOWCONTIG)
> > >>for these tables. The only other solution I have read
> > >>about is to use BCP to export and import the tables but
> > >>this quite complicated solution can't really be included
> > >>in our weekly maintenance job which is what we want.
> > >>
> > >>Please, anyone, suggestions? I can't be the only one
> > with
> > >>this problem..?
> > >>
> > >>- Allan
> > >>
> > >>.
> > >>
> > >.
> > >
>

Defragment SQL Database File

I am trying to defragment our sql database file but windows defragmentation
tool defragments everything but the database file. I have tried detaching the
database and stopping the services but with no luck. Drive size is 82 Gb and
file size is about 45 Gb. The file is in 2 fragments and records a 99% file
fragmentation level. Does anyone have any ideas how I can defrag this file?
Thank you
Clint ColefaxClint
Look at DBCC SHRINKFILE command in the BOL.
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:B7892BF8-05E9-459A-B70E-844612937AC7@.microsoft.com...
> I am trying to defragment our sql database file but windows
defragmentation
> tool defragments everything but the database file. I have tried detaching
the
> database and stopping the services but with no luck. Drive size is 82 Gb
and
> file size is about 45 Gb. The file is in 2 fragments and records a 99%
file
> fragmentation level. Does anyone have any ideas how I can defrag this
file?
> Thank you
> Clint Colefax|||And to avoid such fragmentation in the future, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23Zy%23K7NDFHA.2764@.TK2MSFTNGP10.phx.gbl...
> Clint
> Look at DBCC SHRINKFILE command in the BOL.
>
> "Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
> news:B7892BF8-05E9-459A-B70E-844612937AC7@.microsoft.com...
>> I am trying to defragment our sql database file but windows
> defragmentation
>> tool defragments everything but the database file. I have tried detaching
> the
>> database and stopping the services but with no luck. Drive size is 82 Gb
> and
>> file size is about 45 Gb. The file is in 2 fragments and records a 99%
> file
>> fragmentation level. Does anyone have any ideas how I can defrag this
> file?
>> Thank you
>> Clint Colefax
>|||Tibor
You are not added yet "Search" engine on your web site :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unTZdvQDFHA.328@.tk2msftngp13.phx.gbl...
> And to avoid such fragmentation in the future, check out:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Zy%23K7NDFHA.2764@.TK2MSFTNGP10.phx.gbl...
> > Clint
> > Look at DBCC SHRINKFILE command in the BOL.
> >
> >
> > "Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in
message
> > news:B7892BF8-05E9-459A-B70E-844612937AC7@.microsoft.com...
> >> I am trying to defragment our sql database file but windows
> > defragmentation
> >> tool defragments everything but the database file. I have tried
detaching
> > the
> >> database and stopping the services but with no luck. Drive size is 82
Gb
> > and
> >> file size is about 45 Gb. The file is in 2 fragments and records a 99%
> > file
> >> fragmentation level. Does anyone have any ideas how I can defrag this
> > file?
> >>
> >> Thank you
> >> Clint Colefax
> >
> >
>|||Thanks for the reminder Uri. It is still on the to-do list, but other things keep getting popped on
the stack before I get to do this one. ;-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23mkEydRDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> Tibor
> You are not added yet "Search" engine on your web site :)
>

Defragment SQL Database File

I am trying to defragment our sql database file but windows defragmentation
tool defragments everything but the database file. I have tried detaching the
database and stopping the services but with no luck. Drive size is 82 Gb and
file size is about 45 Gb. The file is in 2 fragments and records a 99% file
fragmentation level. Does anyone have any ideas how I can defrag this file?
Thank you
Clint Colefax
Clint
Look at DBCC SHRINKFILE command in the BOL.
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:B7892BF8-05E9-459A-B70E-844612937AC7@.microsoft.com...
> I am trying to defragment our sql database file but windows
defragmentation
> tool defragments everything but the database file. I have tried detaching
the
> database and stopping the services but with no luck. Drive size is 82 Gb
and
> file size is about 45 Gb. The file is in 2 fragments and records a 99%
file
> fragmentation level. Does anyone have any ideas how I can defrag this
file?
> Thank you
> Clint Colefax
|||And to avoid such fragmentation in the future, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23Zy%23K7NDFHA.2764@.TK2MSFTNGP10.phx.gbl...
> Clint
> Look at DBCC SHRINKFILE command in the BOL.
>
> "Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
> news:B7892BF8-05E9-459A-B70E-844612937AC7@.microsoft.com...
> defragmentation
> the
> and
> file
> file?
>
|||Tibor
You are not added yet "Search" engine on your web site
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unTZdvQDFHA.328@.tk2msftngp13.phx.gbl...
> And to avoid such fragmentation in the future, check out:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Zy%23K7NDFHA.2764@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
message[vbcol=seagreen]
detaching[vbcol=seagreen]
Gb
>
|||Thanks for the reminder Uri. It is still on the to-do list, but other things keep getting popped on
the stack before I get to do this one. ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23mkEydRDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> Tibor
> You are not added yet "Search" engine on your web site
>

Defragment SQL Database File

I am trying to defragment our sql database file but windows defragmentation
tool defragments everything but the database file. I have tried detaching th
e
database and stopping the services but with no luck. Drive size is 82 Gb and
file size is about 45 Gb. The file is in 2 fragments and records a 99% file
fragmentation level. Does anyone have any ideas how I can defrag this file?
Thank you
Clint ColefaxClint
Look at DBCC SHRINKFILE command in the BOL.
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:B7892BF8-05E9-459A-B70E-844612937AC7@.microsoft.com...
> I am trying to defragment our sql database file but windows
defragmentation
> tool defragments everything but the database file. I have tried detaching
the
> database and stopping the services but with no luck. Drive size is 82 Gb
and
> file size is about 45 Gb. The file is in 2 fragments and records a 99%
file
> fragmentation level. Does anyone have any ideas how I can defrag this
file?
> Thank you
> Clint Colefax|||And to avoid such fragmentation in the future, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23Zy%23K7NDFHA.2764@.TK2MSFTNGP10.phx.
gbl...
> Clint
> Look at DBCC SHRINKFILE command in the BOL.
>
> "Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
> news:B7892BF8-05E9-459A-B70E-844612937AC7@.microsoft.com...
> defragmentation
> the
> and
> file
> file?
>|||Tibor
You are not added yet "Search" engine on your web site
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unTZdvQDFHA.328@.tk2msftngp13.phx.gbl...
> And to avoid such fragmentation in the future, check out:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Zy%23K7NDFHA.2764@.TK2MSFTNGP10.phx.gbl...
message[vbcol=seagreen]
detaching[vbcol=seagreen]
Gb[vbcol=seagreen]
>|||Thanks for the reminder Uri. It is still on the to-do list, but other things
keep getting popped on
the stack before I get to do this one. ;-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23mkEydRDFHA.1280@.TK2MSFTNGP10.phx.gb
l...
> Tibor
> You are not added yet "Search" engine on your web site
>

defragment os and dbs

Hi,
Could someone help me what the best practice is for defragging sql server?
Do i need to defrag the OS first and then the databaseserver (with DBCC
REINDEX or DBCC DEFRAGINDEX). For the DBCC statements i could only do it for
a table, it would be nice if it could do for the whole database.
Thnx
dbcc checkdb
Message posted via http://www.sqlmonster.com
|||See this white paper
(http://www.microsoft.com/technet/pro.../ss2kidbp.mspx)
for best practice information on defragging.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23BHXsDx$EHA.2032@.tk2msftngp13.phx.gbl...
> Hi,
> Could someone help me what the best practice is for defragging sql server?
> Do i need to defrag the OS first and then the databaseserver (with DBCC
> REINDEX or DBCC DEFRAGINDEX). For the DBCC statements i could only do it
> for
> a table, it would be nice if it could do for the whole database.
> Thnx
>

defragment os and dbs

Hi,
Could someone help me what the best practice is for defragging sql server?
Do i need to defrag the OS first and then the databaseserver (with DBCC
REINDEX or DBCC DEFRAGINDEX). For the DBCC statements i could only do it for
a table, it would be nice if it could do for the whole database.
Thnxdbcc checkdb
Message posted via http://www.droptable.com|||See this white paper
([url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/url
])
for best practice information on defragging.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23BHXsDx$EHA.2032@.tk2msftngp13.phx.gbl...
> Hi,
> Could someone help me what the best practice is for defragging sql server?
> Do i need to defrag the OS first and then the databaseserver (with DBCC
> REINDEX or DBCC DEFRAGINDEX). For the DBCC statements i could only do it
> for
> a table, it would be nice if it could do for the whole database.
> Thnx
>

defragment os and dbs

Hi,
Could someone help me what the best practice is for defragging sql server?
Do i need to defrag the OS first and then the databaseserver (with DBCC
REINDEX or DBCC DEFRAGINDEX). For the DBCC statements i could only do it for
a table, it would be nice if it could do for the whole database.
Thnxdbcc checkdb
--
Message posted via http://www.sqlmonster.com|||See this white paper
(http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx)
for best practice information on defragging.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23BHXsDx$EHA.2032@.tk2msftngp13.phx.gbl...
> Hi,
> Could someone help me what the best practice is for defragging sql server?
> Do i need to defrag the OS first and then the databaseserver (with DBCC
> REINDEX or DBCC DEFRAGINDEX). For the DBCC statements i could only do it
> for
> a table, it would be nice if it could do for the whole database.
> Thnx
>

Defragment Heap Tables

Hi guru,

I've been new company for only a month and started analysing Index
Fragmentation.

After I ran DBCC DBREINDEX and capture data into permanent table, I 've
seen lots of tables with no indexes. These tables showed:

Very low scan density,
High extent fragmentation
High Avg. Bytes Free per Page

What are the best strategies to defragment tables with no indexes?

I'm planning to make a rule that each table must have a clustered index
and this index must be created on the best column (highest
selectivity).

Please help.

Thanks,
Silaphet,kmounkhaty@.yahoo.com (smounkhaty@.bremer.com) writes:
> I've been new company for only a month and started analysing Index
> Fragmentation.
> After I ran DBCC DBREINDEX and capture data into permanent table, I 've
> seen lots of tables with no indexes. These tables showed:
> Very low scan density,
> High extent fragmentation
> High Avg. Bytes Free per Page
> What are the best strategies to defragment tables with no indexes?

Create a clustered index on them. If the index is absolute undesired,
drop the index once your done.

> I'm planning to make a rule that each table must have a clustered index
> and this index must be created on the best column (highest
> selectivity).

Yes, that is a good rule. Heap tables with deletions can easily become
very fragmented.

As for which column to cluster on, you may want to find a column
that grows monotonically, if all you want to do is avoid fragmentation.
Of course, adding indexes to improve queries is a good idea too!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Defragment disk

Hi
Is it acceptable practice to run Disk Defragmenter on a Disk when SQLServer
is installed and running?
(using Win2000, SQLServer 2000)
ThanksSure, as long as you exclude the SQL Server database files (perhaps the tool
will not even try on open files). If it is a dedicated SQL Server and you
don't overuse autogrow, then there shouldn't really be any need for this.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:eQ0ykPfoDHA.1632@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is it acceptable practice to run Disk Defragmenter on a Disk when
SQLServer
> is installed and running?
> (using Win2000, SQLServer 2000)
> Thanks
>|||How does one exclude these from the defragmenter?
I guess what I need to know is will anything bad happen if the data & log
files are defragmented? Is it best to switch off SQLServer first?|||> How does one exclude these from the defragmenter?
You have to check that with your defrag tool. If you are using the built-in
defrag tool, I suggest you check this in a Windows group.
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
Probably, until you got info from the defrag tool vendor that the tool does
not touch locked files. SQL Server does not itself have a problem with the
files being defragged, but you don't want the risk of having two tools
working against the same file(s) at the same time.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> How does one exclude these from the defragmenter?
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
>|||There is no requirement that SQL database devices be excluded from being
defragmented. There is also no requirement that SQL services first be
stopped prior to defragmenting. Microsoft's defrag APIs fully support
defragmenting SQL database devices online.
- Greg/Raxco Software
Microsoft MVP - Windows File System
Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
commercial defrag utility, as a systems engineer in the support department.
Want to email me? Delete ntloader.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > How does one exclude these from the defragmenter?
> You have to check that with your defrag tool. If you are using the
built-in
> defrag tool, I suggest you check this in a Windows group.
>
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> Probably, until you got info from the defrag tool vendor that the tool
does
> not touch locked files. SQL Server does not itself have a problem with the
> files being defragged, but you don't want the risk of having two tools
> working against the same file(s) at the same time.
> --
> Tibor Karaszi
>
> "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > How does one exclude these from the defragmenter?
> >
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> >
> >
>|||Thanks for the info, Greg! Good to know.
I was trying to get that kind of info from another defrag manufacturer, but
never got a response to my question. Hence my careful approach. :-)
--
Tibor Karaszi
"Greg Hayes/Raxco Software" <ghayesntloader@.raxco.com> wrote in message
news:%23EXhy9uoDHA.2216@.TK2MSFTNGP12.phx.gbl...
> There is no requirement that SQL database devices be excluded from being
> defragmented. There is also no requirement that SQL services first be
> stopped prior to defragmenting. Microsoft's defrag APIs fully support
> defragmenting SQL database devices online.
> - Greg/Raxco Software
> Microsoft MVP - Windows File System
> Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
> commercial defrag utility, as a systems engineer in the support
department.
> Want to email me? Delete ntloader.
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > > How does one exclude these from the defragmenter?
> >
> > You have to check that with your defrag tool. If you are using the
> built-in
> > defrag tool, I suggest you check this in a Windows group.
> >
> >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> >
> > Probably, until you got info from the defrag tool vendor that the tool
> does
> > not touch locked files. SQL Server does not itself have a problem with
the
> > files being defragged, but you don't want the risk of having two tools
> > working against the same file(s) at the same time.
> > --
> > Tibor Karaszi
> >
> >
> > "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> > news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > > How does one exclude these from the defragmenter?
> > >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> > >
> > >
> >
> >
>|||Thanks everyone...
Griff

Defragment all indexes in a database?

How can I defragment all the indexes in my databases using dbcc indexdefrag?Have you read this?
http://msdn.microsoft.com/library/d...
0o9.asp
ML|||This defrags only 1 index at a time. I would like to defrag all the indexes
in the database with one command
"ML" wrote:

> Have you read this?
> http://msdn.microsoft.com/library/d...r />
_30o9.asp
>
> ML|||Google the usage for the undocumented SP called sp_MSforeachtable. It can be
used to execute a command for each table in a database.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:039DB153-5857-483C-8BDF-97DB1AD6B3AE@.microsoft.com...
> This defrags only 1 index at a time. I would like to defrag all the
> indexes
> in the database with one command
> "ML" wrote:
>|||Here, I hope this helps.
http://milambda.blogspot.com/2005/0...in-current.html
ML|||I work for a software house which has developed database defragmentation sof
tware for SQL Server. It can defrag more than one index at a time and has sc
heduling facilities built in.
If anyone is interested in beta-testing it, I would like to hear from you as
it is close to launch. My company is willing to offer a free copy of the so
ftware to anyone who can provide genuine and useful feedback. Please send me
a private message from the members' section if you are interested.
Regards,
Martin
[QUOTE]Originally posted by Mike
How can I defragment all the indexes in my databases using dbcc indexdefrag? [/QUOTE
]

Defragment

I am currently running Vista Ultimate with SP1. When I was running XP PRO I could defragment my disks myself. Now I can not find the exe. Vista wants to do it its self, I used to run a defrag several time to get back for usable disk space now it can't even see how fragmented my disks are. Does anyone know where the exe for defraging is hidding?

Hi,

The link that you are looking for is:

http://windowshelp.microsoft.com/Windows/en-US/Help/1399f42d-c6d4-4716-97a7-612a1f0598e31033.mspx

The Windows Vista forum would be better adept at answering your queries regarding Windows Vista:

http://forums.microsoft.com/TechNet/default.aspx?ForumGroupID=204&SiteID=17

HTH

|||

Amit,

That is the normal defrag for Vista. The one I was looking for one could watch the defrager working. thanks for your post.

Curtis

Defragment

I am currently running Vista Ultimate with SP1. When I was running XP PRO I could defragment my disks myself. Now I can not find the exe. Vista wants to do it its self, I used to run a defrag several time to get back for usable disk space now it can't even see how fragmented my disks are. Does anyone know where the exe for defraging is hidding?

Hi,

The link that you are looking for is:

http://windowshelp.microsoft.com/Windows/en-US/Help/1399f42d-c6d4-4716-97a7-612a1f0598e31033.mspx

The Windows Vista forum would be better adept at answering your queries regarding Windows Vista:

http://forums.microsoft.com/TechNet/default.aspx?ForumGroupID=204&SiteID=17

HTH

|||

Amit,

That is the normal defrag for Vista. The one I was looking for one could watch the defrager working. thanks for your post.

Curtis

Defragment

If I want defragment the hard disk.
I will stop the service of SQL server.
Is it OK?Yes, that is fine. But please do a backup before just in case.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vincent" <aaa@.aaa.com> wrote in message news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...[col
or=darkred]
> If I want defragment the hard disk.
> I will stop the service of SQL server.
> Is it OK?
>
>[/color]|||I found that the defragment has completed but the database file remain
fragment.
Any missing step?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uKSTMQkGFHA.1948@.TK2MSFTNGP10.phx.gbl...
> Yes, that is fine. But please do a backup before just in case.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Vincent" <aaa@.aaa.com> wrote in message
news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...
>|||Assuming you stopped SQL Server during the defrag: Perhaps there wasn't enou
gh free space on the
disk in order to defrag your potential large database files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vincent" <aaa@.aaa.com> wrote in message news:OjZJfflGFHA.1948@.TK2MSFTNGP10.phx.gbl...[colo
r=darkred]
>I found that the defragment has completed but the database file remain
> fragment.
> Any missing step?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uKSTMQkGFHA.1948@.TK2MSFTNGP10.phx.gbl...
> news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...
>[/color]|||How are you determining the file is fragmented?
Andrew J. Kelly SQL MVP
"Vincent" <aaa@.aaa.com> wrote in message
news:OjZJfflGFHA.1948@.TK2MSFTNGP10.phx.gbl...
>I found that the defragment has completed but the database file remain
> fragment.
> Any missing step?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uKSTMQkGFHA.1948@.TK2MSFTNGP10.phx.gbl...
> news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...
>