Defragmenting issues

I would like to know how many DBAs take care of their file systems
defrag status's server.
I would like to know also if someone here is using some commercial tool
(diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
when they are in use.
Any feedback appreciated.
Regards.
--
Fabri
"Eat just one little baby and everyone remembers your name!"
- CELKODiskkeeper states that their tool is safe to use while sql server is
running. There will obviously be a performance hit while this is running
but it is up to you if you want to do it live or not.
--
Andrew J. Kelly SQL MVP
"Fabri" <no@.sp.am> wrote in message
news:OlOLe.72251$fm.4850956@.news4.tin.it...
>I would like to know how many DBAs take care of their file systems defrag
>status's server.
> I would like to know also if someone here is using some commercial tool
> (diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
> when they are in use.
> Any feedback appreciated.
> Regards.
> --
> Fabri
> "Eat just one little baby and everyone remembers your name!"
> - CELKO|||"Fabri" <no@.sp.am> wrote in message
news:OlOLe.72251$fm.4850956@.news4.tin.it...
>I would like to know how many DBAs take care of their file systems defrag
>status's server.
> I would like to know also if someone here is using some commercial tool
> (diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
> when they are in use.
> Any feedback appreciated.
>
I don't generally worry about fragmentation of database files. Regular best
practices like having extra space, pre-sizing data files and using large
auto-grow %, minimize both the fragmentation and the impact of
fragmentation.
A data file, or especially a log file, in a few large fragments has a
negligible impact on performance.
David

Defragmenting Data Files

Hello all,
Will data/log files be defragmented by Windows Defrag even if they are still
attached?
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> Will data/log files be defragmented by Windows Defrag even if they are sti
ll attached?
>
Nope... You have to stop SQL Server, or detach the database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
Thanks for the help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44EDEAF8.80705@.realsqlguy.com.
.
> Conan Kelly wrote:
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know for sure if that is true for the Windows defragger but
certainly not for things like DiskKeeper. I am pretty sure that the windows
defragger was based on DiskKeepers technology so I suspect it may be true
for that as well. The makers of DiskKeeper vividly state that their tool
will defrag sql server files even if the server is live. Not that I am
recommending that practice but it is certainly possible.
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Andrew J. Kelly wrote:
> I don't know for sure if that is true for the Windows defragger but
> certainly not for things like DiskKeeper. I am pretty sure that the windo
ws
> defragger was based on DiskKeepers technology so I suspect it may be true
> for that as well. The makers of DiskKeeper vividly state that their tool
> will defrag sql server files even if the server is live. Not that I am
> recommending that practice but it is certainly possible.
>
Yeah, I know they claim that, but the thought of something manipulating
my database files WHILE THEY'RE BEING USED, just scares me a bit. Seems
very risky to me.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I hear you man.
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2EBEB.10703@.realsqlguy.com...
> Andrew J. Kelly wrote:
> Yeah, I know they claim that, but the thought of something manipulating my
> database files WHILE THEY'RE BEING USED, just scares me a bit. Seems very
> risky to me.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Defragmenting Data Files

Hello all,
Will data/log files be defragmented by Windows Defrag even if they are still attached?
--
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> Will data/log files be defragmented by Windows Defrag even if they are still attached?
>
Nope... You have to stop SQL Server, or detach the database.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
Thanks for the help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> Will data/log files be defragmented by Windows Defrag even if they are still attached?
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know for sure if that is true for the Windows defragger but
certainly not for things like DiskKeeper. I am pretty sure that the windows
defragger was based on DiskKeepers technology so I suspect it may be true
for that as well. The makers of DiskKeeper vividly state that their tool
will defrag sql server files even if the server is live. Not that I am
recommending that practice but it is certainly possible.
--
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> Will data/log files be defragmented by Windows Defrag even if they are
>> still attached?
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Andrew J. Kelly wrote:
> I don't know for sure if that is true for the Windows defragger but
> certainly not for things like DiskKeeper. I am pretty sure that the windows
> defragger was based on DiskKeepers technology so I suspect it may be true
> for that as well. The makers of DiskKeeper vividly state that their tool
> will defrag sql server files even if the server is live. Not that I am
> recommending that practice but it is certainly possible.
>
Yeah, I know they claim that, but the thought of something manipulating
my database files WHILE THEY'RE BEING USED, just scares me a bit. Seems
very risky to me.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I hear you man.
--
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2EBEB.10703@.realsqlguy.com...
> Andrew J. Kelly wrote:
>> I don't know for sure if that is true for the Windows defragger but
>> certainly not for things like DiskKeeper. I am pretty sure that the
>> windows defragger was based on DiskKeepers technology so I suspect it may
>> be true for that as well. The makers of DiskKeeper vividly state that
>> their tool will defrag sql server files even if the server is live. Not
>> that I am recommending that practice but it is certainly possible.
> Yeah, I know they claim that, but the thought of something manipulating my
> database files WHILE THEY'RE BEING USED, just scares me a bit. Seems very
> risky to me.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

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
>

Defraging database / help

Hi all
I want to defrag the SQL database
yes this is code
DBCC INDEXDEFRAG (Northwind, Orders, CustomersOrders)
but this will defrag a specific Table
but i want defrag entire a specific database
how can i do it
ThanksCreate a view in your database:

create view dbo.vw_DBCC_INDEXDEFRAG as
select cmd = 'dbcc indexdefrag (' + db_name() + ', ' + o.name + ', ' + i.name + ')'
from sysindexes i
inner join sysobjects o on i.id=o.id
where objectproperty(o.id, 'IsMSShipped') = 0
and objectproperty(o.id, 'IsTable') = 1
and indid > 0 and indid < 255 and (i.status & 64)=0

Then, at command prompt type the following:

C:\>BCP <db_name>.dbo.vw_DBCC_INDEXDEFRAG out DBCC_INDEXDEFRAG.SQL -S <your_server> -T -c
C:\>OSQL -i DBCC_INDEXDEFRAG.SQL -o DBCC_INDEXDEFRAG.OUT -S <server_name> -d <your_database> -E -h-1 -n -w 256

...And you're done.

Defragging SQL Database files

I am trying to defrag my Hard-disk drive. Windows 2000 Server does not
defrag my drice compoletely as major portion is occupied by my SQL database
whose size is around 2GB. The Defrag program on completion says that it
cannot defrag the SQL Database files.
Then how do I do it? Is there any way to defrag the SQL Database files? I
need to do it as major portion of my HDD is occupied by database files.
Regards,
Ashwini"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:uplotOW5DHA.2008@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I am trying to defrag my Hard-disk drive. Windows 2000 Server does not
> defrag my drice compoletely as major portion is occupied by my SQL

---

database

quote:

---

> whose size is around 2GB. The Defrag program on completion says that it
> cannot defrag the SQL Database files.
> Then how do I do it? Is there any way to defrag the SQL Database files? I
> need to do it as major portion of my HDD is occupied by database files.

---

In order to reduce space, via enterprise manager you could
run the "Shrink database" function. Also, you might want to
look at the housekeeping maintenance functions.|||you can also defrag the index files (I don't know if this will save any
space however).
If your situation allows you to mark the db's as SIMPLE recovery the shrink
will work to best effects.
hope this helps
dlr
"mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
news:5LWRb.32581$Wa.1031@.news-server.bigpond.net.au...

quote:

---

> "Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
> news:uplotOW5DHA.2008@.TK2MSFTNGP10.phx.gbl...
> database
I[QUOTE]
> In order to reduce space, via enterprise manager you could
> run the "Shrink database" function. Also, you might want to
> look at the housekeeping maintenance functions.
>
>
>

---

Defragging SQL Database

I am trying to defrag my Hard-disk drive. Windows 2000 Server does not
defrag my drice compoletely as major portion is occupied by my SQL database
whose size is around 2GB. The Defrag program on completion says that it
cannot defrag the SQL Database files.
Then how do I do it? Is there any way to defrag the SQL Database files? I
need to do it as major portion of my HDD is occupied by database files.
Regards,
AshwiniIn order to defrag physical SQL Server files (database and log), the
mssqlserver and the sqlserveragent (and any other related) services must be
turned off. This is because files that are being used (such as the database
and log files) cannot be defragged.
regards,
harsh.
"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:OCMJlTW5DHA.3896@.TK2MSFTNGP11.phx.gbl...

quote:

---

> I am trying to defrag my Hard-disk drive. Windows 2000 Server does not
> defrag my drice compoletely as major portion is occupied by my SQL

---

database

quote:

---

> whose size is around 2GB. The Defrag program on completion says that it
> cannot defrag the SQL Database files.
> Then how do I do it? Is there any way to defrag the SQL Database files? I
> need to do it as major portion of my HDD is occupied by database files.
> Regards,
> Ashwini
>

---

|||Hi:
I think you can backup the user database, then delete the user database
from the sql server temp temporarily,after defrag or not, restore the
database just backup to the major portion or other portion
Best Wishes
Wei Ci Zhou|||There's no need to backup/restore in order to do a defrag. If the defrag pro
gram can't handle open
files, then it is only a matter of stopping the SQL Server service (as harsh
pointed out).
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi:
> I think you can backup the user database, then delete the user databas
e
> from the sql server temp temporarily,after defrag or not, restore the
> database just backup to the major portion or other portion
> Best Wishes
> Wei Ci Zhou
>

---

|||I tried stopping all the SQL Server Services but ti still does not work....
any other ideas?
Ash.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...

quote:

---

> There's no need to backup/restore in order to do a defrag. If the defrag

---

program can't handle open

quote:

---

> files, then it is only a matter of stopping the SQL Server service (as

---

harsh pointed out).

quote:

---

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
database[QUOTE]
>

---

|||Then perhaps the files are so big compared to free space on the drive so tha
t defrag of the file
isn't possible... If you have stopped SQL Server and you do have free space
on the drive, then it is
a matter of the defrag program not doing its job and you need to hunt down t
he solution at this end
(possibly a windows issue if you use the built-in defrag program).
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:uocUXPX5DHA.2580@.TK2MSFTNGP11.phx.gbl...

quote:

---

> I tried stopping all the SQL Server Services but ti still does not work...
.
> any other ideas?
> Ash.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> program can't handle open
> harsh pointed out).
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> database
>

---

|||I suggest beacuse Ashwini says :"I need to do it as major portion of my HDD
is occupied by database files." I suggest this because this can move the
space to other portion
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...

quote:

---

> There's no need to backup/restore in order to do a defrag. If the defrag

---

program can't handle open

quote:

---

> files, then it is only a matter of stopping the SQL Server service (as

---

harsh pointed out).

quote:

---

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
database[QUOTE]
>

---

|||Ahh, I see. Good point!
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:OcqjRXX5DHA.632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I suggest beacuse Ashwini says :"I need to do it as major portion of my HD
D
> is occupied by database files." I suggest this because this can move the
> space to other portion
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> program can't handle open
> harsh pointed out).
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> database
>

---

|||Okay agreed for file sizes of 2GB. But in the list of database files not
fragmented I also have files of 500MB - that should get defragmented as I
have 1.5 GB space free.
Ashwini
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uQyx1WX5DHA.2480@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Then perhaps the files are so big compared to free space on the drive so

---

that defrag of the file

quote:

---

> isn't possible... If you have stopped SQL Server and you do have free

---

space on the drive, then it is

quote:

---

> a matter of the defrag program not doing its job and you need to hunt down

---

the solution at this end

quote:

---

> (possibly a windows issue if you use the built-in defrag program).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
> news:uocUXPX5DHA.2580@.TK2MSFTNGP11.phx.gbl...
work....[QUOTE]
in[QUOTE]
defrag[QUOTE]
http://groups.google.com/groups?oi=...ublic.sqlserver[QUOTE]
the[QUOTE]
>

---

|||Possibly. I'm no file system fragmentation expert ;-).
I suggest you post this in a windows group, as such a group should have more
experts regarding file
sizes, free space etc.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:uZoGQoY5DHA.1504@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Okay agreed for file sizes of 2GB. But in the list of database files not
> fragmented I also have files of 500MB - that should get defragmented as I
> have 1.5 GB space free.
> Ashwini
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uQyx1WX5DHA.2480@.TK2MSFTNGP10.phx.gbl...
> that defrag of the file
> space on the drive, then it is
> the solution at this end
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> work....
> in
> defrag
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> the
>

---

Defragging SQL Database

I am trying to defrag my Hard-disk drive. Windows 2000 Server does not
defrag my drice compoletely as major portion is occupied by my SQL database
whose size is around 2GB. The Defrag program on completion says that it
cannot defrag the SQL Database files.
Then how do I do it? Is there any way to defrag the SQL Database files? I
need to do it as major portion of my HDD is occupied by database files.
Regards,
AshwiniIn order to defrag physical SQL Server files (database and log), the
mssqlserver and the sqlserveragent (and any other related) services must be
turned off. This is because files that are being used (such as the database
and log files) cannot be defragged.
regards,
harsh.
"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:OCMJlTW5DHA.3896@.TK2MSFTNGP11.phx.gbl...
> I am trying to defrag my Hard-disk drive. Windows 2000 Server does not
> defrag my drice compoletely as major portion is occupied by my SQL
database
> whose size is around 2GB. The Defrag program on completion says that it
> cannot defrag the SQL Database files.
> Then how do I do it? Is there any way to defrag the SQL Database files? I
> need to do it as major portion of my HDD is occupied by database files.
> Regards,
> Ashwini
>|||Hi:
I think you can backup the user database, then delete the user database
from the sql server temp temporarily,after defrag or not, restore the
database just backup to the major portion or other portion
Best Wishes
Wei Ci Zhou|||There's no need to backup/restore in order to do a defrag. If the defrag program can't handle open
files, then it is only a matter of stopping the SQL Server service (as harsh pointed out).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I think you can backup the user database, then delete the user database
> from the sql server temp temporarily,after defrag or not, restore the
> database just backup to the major portion or other portion
> Best Wishes
> Wei Ci Zhou
>|||I tried stopping all the SQL Server Services but ti still does not work....
any other ideas?
Ash.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> There's no need to backup/restore in order to do a defrag. If the defrag
program can't handle open
> files, then it is only a matter of stopping the SQL Server service (as
harsh pointed out).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > Hi:
> > I think you can backup the user database, then delete the user
database
> > from the sql server temp temporarily,after defrag or not, restore the
> > database just backup to the major portion or other portion
> >
> > Best Wishes
> > Wei Ci Zhou
> >
> >
>|||Then perhaps the files are so big compared to free space on the drive so that defrag of the file
isn't possible... If you have stopped SQL Server and you do have free space on the drive, then it is
a matter of the defrag program not doing its job and you need to hunt down the solution at this end
(possibly a windows issue if you use the built-in defrag program).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:uocUXPX5DHA.2580@.TK2MSFTNGP11.phx.gbl...
> I tried stopping all the SQL Server Services but ti still does not work....
> any other ideas?
> Ash.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> > There's no need to backup/restore in order to do a defrag. If the defrag
> program can't handle open
> > files, then it is only a matter of stopping the SQL Server service (as
> harsh pointed out).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > > Hi:
> > > I think you can backup the user database, then delete the user
> database
> > > from the sql server temp temporarily,after defrag or not, restore the
> > > database just backup to the major portion or other portion
> > >
> > > Best Wishes
> > > Wei Ci Zhou
> > >
> > >
> >
> >
>|||I suggest beacuse Ashwini says :"I need to do it as major portion of my HDD
is occupied by database files." I suggest this because this can move the
space to other portion :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> There's no need to backup/restore in order to do a defrag. If the defrag
program can't handle open
> files, then it is only a matter of stopping the SQL Server service (as
harsh pointed out).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > Hi:
> > I think you can backup the user database, then delete the user
database
> > from the sql server temp temporarily,after defrag or not, restore the
> > database just backup to the major portion or other portion
> >
> > Best Wishes
> > Wei Ci Zhou
> >
> >
>|||Ahh, I see. Good point!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:OcqjRXX5DHA.632@.TK2MSFTNGP12.phx.gbl...
> I suggest beacuse Ashwini says :"I need to do it as major portion of my HDD
> is occupied by database files." I suggest this because this can move the
> space to other portion :)
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> > There's no need to backup/restore in order to do a defrag. If the defrag
> program can't handle open
> > files, then it is only a matter of stopping the SQL Server service (as
> harsh pointed out).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > > Hi:
> > > I think you can backup the user database, then delete the user
> database
> > > from the sql server temp temporarily,after defrag or not, restore the
> > > database just backup to the major portion or other portion
> > >
> > > Best Wishes
> > > Wei Ci Zhou
> > >
> > >
> >
> >
>|||Okay agreed for file sizes of 2GB. But in the list of database files not
fragmented I also have files of 500MB - that should get defragmented as I
have 1.5 GB space free.
Ashwini
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uQyx1WX5DHA.2480@.TK2MSFTNGP10.phx.gbl...
> Then perhaps the files are so big compared to free space on the drive so
that defrag of the file
> isn't possible... If you have stopped SQL Server and you do have free
space on the drive, then it is
> a matter of the defrag program not doing its job and you need to hunt down
the solution at this end
> (possibly a windows issue if you use the built-in defrag program).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
> news:uocUXPX5DHA.2580@.TK2MSFTNGP11.phx.gbl...
> > I tried stopping all the SQL Server Services but ti still does not
work....
> > any other ideas?
> > Ash.
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> > > There's no need to backup/restore in order to do a defrag. If the
defrag
> > program can't handle open
> > > files, then it is only a matter of stopping the SQL Server service (as
> > harsh pointed out).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > > news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > > > Hi:
> > > > I think you can backup the user database, then delete the user
> > database
> > > > from the sql server temp temporarily,after defrag or not, restore
the
> > > > database just backup to the major portion or other portion
> > > >
> > > > Best Wishes
> > > > Wei Ci Zhou
> > > >
> > > >
> > >
> > >
> >
> >
>|||Possibly. I'm no file system fragmentation expert ;-).
I suggest you post this in a windows group, as such a group should have more experts regarding file
sizes, free space etc.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:uZoGQoY5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> Okay agreed for file sizes of 2GB. But in the list of database files not
> fragmented I also have files of 500MB - that should get defragmented as I
> have 1.5 GB space free.
> Ashwini
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uQyx1WX5DHA.2480@.TK2MSFTNGP10.phx.gbl...
> > Then perhaps the files are so big compared to free space on the drive so
> that defrag of the file
> > isn't possible... If you have stopped SQL Server and you do have free
> space on the drive, then it is
> > a matter of the defrag program not doing its job and you need to hunt down
> the solution at this end
> > (possibly a windows issue if you use the built-in defrag program).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
> > news:uocUXPX5DHA.2580@.TK2MSFTNGP11.phx.gbl...
> > > I tried stopping all the SQL Server Services but ti still does not
> work....
> > > any other ideas?
> > > Ash.
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > > message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> > > > There's no need to backup/restore in order to do a defrag. If the
> defrag
> > > program can't handle open
> > > > files, then it is only a matter of stopping the SQL Server service (as
> > > harsh pointed out).
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > > > news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > > > > Hi:
> > > > > I think you can backup the user database, then delete the user
> > > database
> > > > > from the sql server temp temporarily,after defrag or not, restore
> the
> > > > > database just backup to the major portion or other portion
> > > > >
> > > > > Best Wishes
> > > > > Wei Ci Zhou
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

defrag using deskeeper 2007

Is there anything I need to be aware of when I defrag my SQL 2000 server
using deskeeper 2007? Do I need to stop the services or anything else?
Thanks,
IIRC, Diskeeper claims that there server product will defrag sql database
files while sql is still running. I could be misremembering this. I do
have several clients that have done that without any corruptions.
Having said that - stop your sql server. Best not to take the chance, IMHO.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Dooma" <nowhere@.noway.com> wrote in message
news:OW1mw8lnIHA.980@.TK2MSFTNGP06.phx.gbl...
> Is there anything I need to be aware of when I defrag my SQL 2000 server
> using deskeeper 2007? Do I need to stop the services or anything else?
>
> Thanks,
>
>
|||Do you really need to defrag your filesystem?
Linchi
"Dooma" wrote:

> Is there anything I need to be aware of when I defrag my SQL 2000 server
> using deskeeper 2007? Do I need to stop the services or anything else?
>
> Thanks,
>
>
|||Not sure about this poster's need, but I have certainly had client's that
needed to defrag their file system. 300K+ file fragments (which I have seen
at a client) in their database file directory WILL affect performance! :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:6A544A98-8CCE-433F-85FE-3718DCD793AE@.microsoft.com...[vbcol=seagreen]
> Do you really need to defrag your filesystem?
> Linchi
> "Dooma" wrote:

defrag using deskeeper 2007

Is there anything I need to be aware of when I defrag my SQL 2000 server
using deskeeper 2007? Do I need to stop the services or anything else?
Thanks,"Dooma" <nowhere@.noway.com> wrote in message
news:OW1mw8lnIHA.980@.TK2MSFTNGP06.phx.gbl...
> Is there anything I need to be aware of when I defrag my SQL 2000 server
> using deskeeper 2007? Do I need to stop the services or anything else?
>
> Thanks,
>
Hi
If you are using diskeeper as a service then you should be ok, I am not
sure what stand alone application does, but if it is the same windows
defragmenter you will need to make sure the database is not in use i.e SQL
Server is stopped of the database is detached.
John|||IIRC, Diskeeper claims that there server product will defrag sql database
files while sql is still running. I could be misremembering this. I do
have several clients that have done that without any corruptions.
Having said that - stop your sql server. Best not to take the chance, IMHO.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Dooma" <nowhere@.noway.com> wrote in message
news:OW1mw8lnIHA.980@.TK2MSFTNGP06.phx.gbl...
> Is there anything I need to be aware of when I defrag my SQL 2000 server
> using deskeeper 2007? Do I need to stop the services or anything else?
>
> Thanks,
>
>|||Do you really need to defrag your filesystem?
Linchi
"Dooma" wrote:
> Is there anything I need to be aware of when I defrag my SQL 2000 server
> using deskeeper 2007? Do I need to stop the services or anything else?
>
> Thanks,
>
>|||Not sure about this poster's need, but I have certainly had client's that
needed to defrag their file system. 300K+ file fragments (which I have seen
at a client) in their database file directory WILL affect performance! :-)
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:6A544A98-8CCE-433F-85FE-3718DCD793AE@.microsoft.com...
> Do you really need to defrag your filesystem?
> Linchi
> "Dooma" wrote:
>> Is there anything I need to be aware of when I defrag my SQL 2000 server
>> using deskeeper 2007? Do I need to stop the services or anything else?
>>
>> Thanks,
>>
>>|||"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:8c-dnQ1nWOUmsZXVnZ2dnUVZ_oqhnZ2d@.earthlink.com...
> Not sure about this poster's need, but I have certainly had client's that
> needed to defrag their file system. 300K+ file fragments (which I have
> seen at a client) in their database file directory WILL affect
> performance! :-)
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:6A544A98-8CCE-433F-85FE-3718DCD793AE@.microsoft.com...
>> Do you really need to defrag your filesystem?
>> Linchi
>> "Dooma" wrote:
>> Is there anything I need to be aware of when I defrag my SQL 2000 server
>> using deskeeper 2007? Do I need to stop the services or anything else?
>>
>> Thanks,
Hi
There should usually be a need to defrag the data drives of a system
dedicated to SQL Server unless you have autoshrink on (which is not
recommended), but systems (such as SBS, test or development servers) that
have multiple uses or transient databases can quite often benefit from a
hard disc being defragmented, and I have seen improvements of 20% on some
badly affected systems.
John

Defrag Tool?

I need to know if there are any good tools available to defrag/reindex
SQL2000 databases? I have to manage my own server and I am not a SQL guru. I
run Diskkeeper to take care of the physical files, but I need an easy and
effective way to defrag/reindex the SQL databases.
Thanks,
DerekHi Derek,
You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment the
indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
them both. DBRINDEX will take out locks on your tables though, so you can
only run it when your database is not in use or very quiet.
You can run DBCC DBREINDEX on all tables with:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
And you can create a script to defrag all indexes with:
SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
indid) + ')' + CHAR(13) +
'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
--
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> I need to know if there are any good tools available to defrag/reindex
> SQL2000 databases? I have to manage my own server and I am not a SQL guru.
I
> run Diskkeeper to take care of the physical files, but I need an easy and
> effective way to defrag/reindex the SQL databases.
> Thanks,
> Derek
>|||A disk defrag doesn't really work with SQL Server, apart
from getting the files in one continuous chunk of disk
space.
However you can defrag the files themselves with the DBCC
INDEXDEFRAG command.
J
>--Original Message--
>I need to know if there are any good tools available to
defrag/reindex
>SQL2000 databases? I have to manage my own server and I
am not a SQL guru. I
>run Diskkeeper to take care of the physical files, but I
need an easy and
>effective way to defrag/reindex the SQL databases.
>Thanks,
>Derek
>
>.
>|||Thanks. Just to make sure, I would ut the following into Query Analyzer and
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> Hi Derek,
> You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment
the
> indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
> them both. DBRINDEX will take out locks on your tables though, so you can
> only run it when your database is not in use or very quiet.
> You can run DBCC DBREINDEX on all tables with:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> And you can create a script to defrag all indexes with:
> SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME
(id)
> + ''''+ CHAR(13) +
> 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> indid) + ')' + CHAR(13) +
> 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > I need to know if there are any good tools available to defrag/reindex
> > SQL2000 databases? I have to manage my own server and I am not a SQL
guru.
> I
> > run Diskkeeper to take care of the physical files, but I need an easy
and
> > effective way to defrag/reindex the SQL databases.
> >
> > Thanks,
> > Derek
> >
> >
>|||Hi Derek,
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
--
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> Thanks. Just to make sure, I would ut the following into Query Analyzer
and
> run:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> Also, How do I make my databases offline to run?
> Thanks much!
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > Hi Derek,
> >
> > You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment
> the
> > indexes. DBCC DBREINDEX will defragment as well, so you don't have to
run
> > them both. DBRINDEX will take out locks on your tables though, so you
can
> > only run it when your database is not in use or very quiet.
> >
> > You can run DBCC DBREINDEX on all tables with:
> > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> >
> > And you can create a script to defrag all indexes with:
> > SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME
> (id)
> > + ''''+ CHAR(13) +
> > 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> > indid) + ')' + CHAR(13) +
> > 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> > FROM sysindexes
> > WHERE indid BETWEEN 1 AND 254
> > AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> > AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> > ORDER BY OBJECT_NAME(id), indid
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Derek" <derekb@.nospamderekb.com> wrote in message
> > news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > > I need to know if there are any good tools available to defrag/reindex
> > > SQL2000 databases? I have to manage my own server and I am not a SQL
> guru.
> > I
> > > run Diskkeeper to take care of the physical files, but I need an easy
> and
> > > effective way to defrag/reindex the SQL databases.
> > >
> > > Thanks,
> > > Derek
> > >
> > >
> >
> >
>|||Thanks, I should have been more specific. Diskkeeper takes care of the
external files, I was referring to defrag internally. But I think Jacco has
me taken care of. Thanks anyway!
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:889901c3e97b$4907e810$a401280a@.phx.gbl...
> A disk defrag doesn't really work with SQL Server, apart
> from getting the files in one continuous chunk of disk
> space.
> However you can defrag the files themselves with the DBCC
> INDEXDEFRAG command.
> J
>
> >--Original Message--
> >I need to know if there are any good tools available to
> defrag/reindex
> >SQL2000 databases? I have to manage my own server and I
> am not a SQL guru. I
> >run Diskkeeper to take care of the physical files, but I
> need an easy and
> >effective way to defrag/reindex the SQL databases.
> >
> >Thanks,
> >Derek
> >
> >
> >.
> >|||Thanks! I will try this later tonight
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...
> Hi Derek,
> SQL Server databases do not have to be taken offline to be reindexed. The
> reindexing process will block other processes from accessing the tables
and
> indexes that are being reindexed though, which might be inconvenient if
the
> reindexing takes a long time.
> If that is an issue, you can prevent users from connecting to the database
> with (SQL Server 2000 only):
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> and after the reindexing you can give them access again with:
> ALTER DATABASE <database name> SET MULTI_USER
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > Thanks. Just to make sure, I would ut the following into Query Analyzer
> and
> > run:
> >
> > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> >
> > Also, How do I make my databases offline to run?
> >
> > Thanks much!
> >
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi Derek,
> > >
> > > You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to
defragment
> > the
> > > indexes. DBCC DBREINDEX will defragment as well, so you don't have to
> run
> > > them both. DBRINDEX will take out locks on your tables though, so you
> can
> > > only run it when your database is not in use or very quiet.
> > >
> > > You can run DBCC DBREINDEX on all tables with:
> > > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> > >
> > > And you can create a script to defrag all indexes with:
> > > SELECT 'SELECT ''Defragging index ' + name + ' on table ' +
OBJECT_NAME
> > (id)
> > > + ''''+ CHAR(13) +
> > > 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' +
CONVERT(VARCHAR,
> > > indid) + ')' + CHAR(13) +
> > > 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> > > FROM sysindexes
> > > WHERE indid BETWEEN 1 AND 254
> > > AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> > > AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> > > ORDER BY OBJECT_NAME(id), indid
> > >
> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP
> > >
> > >
> > > "Derek" <derekb@.nospamderekb.com> wrote in message
> > > news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > > > I need to know if there are any good tools available to
defrag/reindex
> > > > SQL2000 databases? I have to manage my own server and I am not a SQL
> > guru.
> > > I
> > > > run Diskkeeper to take care of the physical files, but I need an
easy
> > and
> > > > effective way to defrag/reindex the SQL databases.
> > > >
> > > > Thanks,
> > > > Derek
> > > >
> > > >
> > >
> > >
> >
> >
>

Defrag Tool?

A disk defrag doesn't really work with SQL Server, apart
from getting the files in one continuous chunk of disk
space.
However you can defrag the files themselves with the DBCC
INDEXDEFRAG command.
J

quote:

---

>--Original Message--
>I need to know if there are any good tools available to

---

defrag/reindex

quote:

---

>SQL2000 databases? I have to manage my own server and I

---

am not a SQL guru. I

quote:

---

>run Diskkeeper to take care of the physical files, but I

---

need an easy and

quote:

---

>effective way to defrag/reindex the SQL databases.
>Thanks,
>Derek
>
>.
>

---

Thanks, I should have been more specific. Diskkeeper takes care of the
external files, I was referring to defrag internally. But I think Jacco has
me taken care of. Thanks anyway!
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:889901c3e97b$4907e810$a401280a@.phx.gbl...[QUOTE]
> A disk defrag doesn't really work with SQL Server, apart
> from getting the files in one continuous chunk of disk
> space.
> However you can defrag the files themselves with the DBCC
> INDEXDEFRAG command.
> J
>
> defrag/reindex
> am not a SQL guru. I
> need an easy and

Defrag Tool?

I need to know if there are any good tools available to defrag/reindex
SQL2000 databases? I have to manage my own server and I am not a SQL guru. I
run Diskkeeper to take care of the physical files, but I need an easy and
effective way to defrag/reindex the SQL databases.
Thanks,
DerekHi Derek,
You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment the
indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
them both. DBRINDEX will take out locks on your tables though, so you can
only run it when your database is not in use or very quiet.
You can run DBCC DBREINDEX on all tables with:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
And you can create a script to defrag all indexes with:
SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
indid) + ')' + CHAR(13) +
'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I need to know if there are any good tools available to defrag/reindex
> SQL2000 databases? I have to manage my own server and I am not a SQL guru.

---

I

quote:

---

> run Diskkeeper to take care of the physical files, but I need an easy and
> effective way to defrag/reindex the SQL databases.
> Thanks,
> Derek
>

---

|||Thanks. Just to make sure, I would ut the following into Query Analyzer and
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Derek,
> You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment

---

the

quote:

---

> indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
> them both. DBRINDEX will take out locks on your tables though, so you can
> only run it when your database is not in use or very quiet.
> You can run DBCC DBREINDEX on all tables with:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> And you can create a script to defrag all indexes with:
> SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME

---

(id)

quote:

---

> + ''''+ CHAR(13) +
> 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> indid) + ')' + CHAR(13) +
> 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
guru.[QUOTE]
> I
and[QUOTE]
>

---

|||Hi Derek,
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Thanks. Just to make sure, I would ut the following into Query Analyzer

---

and

quote:

---

> run:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> Also, How do I make my databases offline to run?
> Thanks much!
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> the
run[QUOTE]
can[QUOTE]
> (id)
> guru.
> and
>

---

|||Thanks! I will try this later tonight
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...

quote:

---

> Hi Derek,
> SQL Server databases do not have to be taken offline to be reindexed. The
> reindexing process will block other processes from accessing the tables

---

and

quote:

---

> indexes that are being reindexed though, which might be inconvenient if

---

the

quote:

---

> reindexing takes a long time.
> If that is an issue, you can prevent users from connecting to the database
> with (SQL Server 2000 only):
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> and after the reindexing you can give them access again with:
> ALTER DATABASE <database name> SET MULTI_USER
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> and
defragment[QUOTE]
> run
> can
OBJECT_NAME[QUOTE]
CONVERT(VARCHAR,[QUOTE]
defrag/reindex[QUOTE]
easy[QUOTE]
>

---

defrag table with no clustered idx

Is there any easy way to defrag a table with no clustered idx other than bcp out then back in ? (SQL 2000)

Actually I have a 100GB db that I deleted data from other tables, about 10GB worth, but access to the table in question (38GB) seems to have slowed down dramatically, Or does the entire disk need to be defragmented and a bcp out/in would be a waste of time on this table ?

I run an index defrag every night, reindex weekly.

Thanks.Do you run

DBCC INDEXDEFRAG

??

If so what messages do you get?|||Run it every night on all indexes, it runs via an step in an agent job with no reported errors, so I assumed it defragged all indexes successfully

I have sprocs that run the defrag and reindex jobs.

they are the isp_DBCC_DBREINDEX and isp_DBCC_INDEXDEFRAG sprocs that are available pretty much everywhere.|||the Disk Defragmenter shows a lot of defragmented files on the drive that contains the db mdf files, so I was assuming an o/s level defragment must be performed to resolve this issue. Am I correct in assuming so ?|||Totally correct dude. You need to defrag your disk drive FIRST (very important), THEN defrag your databases. If you don't do both of them, AND do them both in this order, you can suffer badly degraded performance, as the indexes will get rebuilt in order, then scattered across the disk by the disk defrag, effectively randomising their physical order on the disk surface, which can massively increase track-to-track seek times, the enemy of every DBA.

You can do all this manually, but it gets to be a real chore with dozens of servers - fine if you're a developer with a single box, but for the rest of us it's a no-no. If you have the cash, I recommend you save yourself a heap of time by using the server version of Diskeeper (http://www.diskeeper.com/) to run the file level disk defrag once a month ( you can reduce the need for this by sizing/growing your databases so that they're not continually growing, as this causes file fragmentation). Then run Visual Defrag (http://www.visualdefrag.com/) against all your indexes once a week to check them and keep them ordered and running efficiently - this tool's cool as it only defrags the indexes that need doing instead of all of them, which can take an age if you have loads of big indexes. Both decent tools with good scheduling automation (did I mention you probably want to do disk and database defrags at night when no one's using the system and no jobs are running), not too expensive, and both companies are solid on their technical support. Obviously you'll need to adjust the frequency that you run jobs on a per server/database basis according to the usage patterns, but these tools will at least analyze your server and tell you if it needs doing without having to plough through loads of info.|||There's no point running a SQL Server defrag on a heap. Logical fragmentation is when the pages are out of logical order. There is no logical order to a heap. There are other sorts of fragmentation (such as page denisity) but heaps don't page split either, although you do get off-page pointers.

There is no good reason I know for not having a clustered index. How come you don't have one?|||"There's no point running a SQL Server defrag on a heap. Logical fragmentation is when the pages are out of logical order"

Just to clarify that, when we're talking about the logical fragmentation, we're refering to the index pages being out of logical order, which in the case of a clustered index also happens to be the data pages, as the clustered index is formed by re-ordering the pages that contain the data (which is why you can only have one per table)

As for running a defrag on a heap (table with no indexes), totally correct - it's won't take very long as there's nothing to defrag ! As pootle flump said, there's rarely a good reason not to have a clustered index - the PK is often a good candidate if it is used in queries and increments sequentially, go for that as you won't suffer page splits as nothing will ever be inserted between records in a page that's already ordered.|||A free alternative for reorganising\ rebuilding indexes is to code it yourself e.g. http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspx
This is how we do it in our shop.

Indexer - we do have some issues with physical fragmentation - is diskkeeper specifically for SQL boxes? For example will it handle defragging the mdf & ldf files automajically (for example with an option to stop & start the service)?|||No, it's not specifically for SQL boxes, but as you're fundamentally just defragging another Windows file, which is what Diskeeper does, it does the job. It doesn't control service start/stop events, however, although SQL Server does lock the database files to other applications, Diskeeper will defrag them in-situ without stopping and re-starting the SQL Server boxes. Personally, I prefer to stop and start the db engine, you can do this with a Windows scheduled task (or an AT task), using Service Control Manager - just type sc /? at a command prompt and take it from there.
The only thing you have to be aware of with Diskeeper is that it'll obviously impact disk I/O big time (this is unavoidable), so avoid busy SQL Server periods when you do it.

The info at sqlteam is useful, but the only problem is it can't work on SQL Server 2000, which is what the majority of boxes out there still are :( That's where Visual Defrag (or Idera's SQL Defrag Manager) comes into its own, as they'll handle both SQL Server versions, as well as SQL Server 2008, I believe.|||Ah - then you just want a version using the old syntax. This is supported, though deprecated, on 2005. The same principle - just different syntax. I have not checked 2008 out yet.
http://weblogs.sqlteam.com/tarad/archive/2005/01/04/3933.aspx

Ta for the info re diskeeper.|||No problem :) Yeah, your problem there is that although you can still do the defrag, there is no old syntax in 2000 for getting the info to check if the indexes need defragmenting in the first place, as there are no DMVs in 2000 - you're back to horrible old console commands, which are a pain in the ass to trawl through if you've got more than a dozen tables. That's the main reason for me using third party tools, as the SQL Server output is slow to go through each time you decide to defrag.

You also need to be aware that the DMVs aren't always current, and if you want the most up to date info rather than a snapshot from the last time SQL Server checked, you need to specify it in the DMV parameters, which will slow down the server - it's effectively scanning the leaf levels of the index structures to update the DMV, just as showcontig used to.|||Yeah, your problem there is that although you can still do the defrag, there is no old syntax in 2000 for getting the info to check if the indexes need defragmenting in the first place, as there are no DMVs in 2000 - you're back to horrible old console commands, which are a pain in the ass to trawl through if you've got more than a dozen tables. That's the main reason for me using third party tools, as the SQL Server output is slow to go through each time you decide to defrag.What do the third party tools use?|||There's no point running a SQL Server defrag on a heap. Logical fragmentation is when the pages are out of logical order. There is no logical order to a heap. There are other sorts of fragmentation (such as page denisity) but heaps don't page split either, although you do get off-page pointers.

There is no good reason I know for not having a clustered index. How come you don't have one?

This is a 38GB table with 4K record size and 9+million rows. I was thinking of adding an identity column as a PK. This "Heap" (perfect term for this table) existed before I started at this godforsaken place.

Performance is much better today for some reason. I need to stage this table, add the ID/PK and see if it does anything to degrade performance. Outside of an ID PK, the natural PK may be a ridiculously sized composite index.

I've bcp'd out/in this table before and performance has improved 2-3 fold, it just a long operation and I really have no window to do so, except on a Sunday, and you know how we all love to work on Sundays.|||Just in case you thought I made it up - a Heap is the proper name for a table with no clustered index. It is an excellent name though :)

4K record sizes huh? You defo need a clustered index. Remember - clustered index is not the same as a PK. Is this a heavy insert and\ or update table? In particular do records tend to get inserted quite small and then have lots of data updated in them to swell them up to 4k? Heavy reads? You know what - fancy posting the DDL?|||Just in case you thought I made it up - a Heap is the proper name for a table with no clustered index. It is an excellent name though :)

4K record sizes huh? You defo need a clustered index. Remember - clustered index is not the same as a PK. Is this a heavy insert and\ or update table? In particular do records tend to get inserted quite small and then have lots of data updated in them to swell them up to 4k? Heavy reads? You know what - fancy posting the DDL?

36K rows a day inserted in the wee hours, then many updates. It is used for DSS, but 2-3 batch loads a day from our securitymaster with virtually no window for maintenance, except our backup processes. I know about the heap definition, just fits this in another way.

defrag system tables indexes

does anyone know how could i defrag system tables indexes.. because dbcc
indexdefrag or rebuild can't be used, thksnikolakg wrote:
> does anyone know how could i defrag system tables indexes.. because dbcc
> indexdefrag or rebuild can't be used, thks
You shouldn't have to mess with the system table indexes. They're small
enough that defragging really isn't going to help them, and they're
static enough that they shouldn't fragment much, if at all, anyway.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||i ran dbcc showcontig and the scan density for system tables was too low
12-15%.
Ο χρ?στη? "Tracy McKibben" _γγραψε:

> nikolakg wrote:
> You shouldn't have to mess with the system table indexes. They're small
> enough that defragging really isn't going to help them, and they're
> static enough that they shouldn't fragment much, if at all, anyway.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> i ran dbcc showcontig and the scan density for system tables was too low
> 12-15%.
>
How many pages are in the indexes you're looking at?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
syscolumns syscolumns 244 12200 12.810
sysdepends ncsysdepends1 56 2027 13.462
syscolumns ncsyscolumns 93 12200 13.483
sysindexes sysindexes 187 1063 13.793
sysdepends sysdepends 57 2027 14.815
syscomments syscomments 926 1505 16.885
sysobjects sysobjects 27 1680 17.391
sysobjects ncsysobjects 14 1680 20.000
sysproperties sysproperties 6 65 20.000
sysobjects ncsysobjects2 5 1680 25.000
this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
(and some more columns)
Ο χρ?στη? "Tracy McKibben" _γγραψε:

> nikolakg wrote:
> How many pages are in the indexes you're looking at?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
> syscolumns syscolumns 244 12200 12.810
> sysdepends ncsysdepends1 56 2027 13.462
> syscolumns ncsyscolumns 93 12200 13.483
> sysindexes sysindexes 187 1063 13.7
93
> sysdepends sysdepends 57 2027 14.815
> syscomments syscomments 926 1505 16.885
> sysobjects sysobjects 27 1680 17.391
> sysobjects ncsysobjects 14 1680 20.000
> sysproperties sysproperties 6 65 20.000
> sysobjects ncsysobjects2 5 1680 25.000
>
> this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEX
ES
> (and some more columns)
>
Less than 1000 pages, don't worry about them.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||thank you!
Ο χρ?στη? "Tracy McKibben" _γγραψε:

> nikolakg wrote:
> Less than 1000 pages, don't worry about them.
> http://www.microsoft.com/technet/pr...realsqlguy.com
>|||We have an issue because text data from sysIndexes is sitting right at the
end of our primary datafile and this stops us truncating the end off the
file.
We have done a load of housekeeping and could reclaim a greate deal of disk
space.
We cannot do a shrink/movc data pages due to log shipping across our WAN.
DBCC extentinfo (Production, sysIndexes, -1)
Paul

defrag system tables indexes

does anyone know how could i defrag system tables indexes.. because dbcc
indexdefrag or rebuild can't be used, thksnikolakg wrote:
> does anyone know how could i defrag system tables indexes.. because dbcc
> indexdefrag or rebuild can't be used, thks
You shouldn't have to mess with the system table indexes. They're small
enough that defragging really isn't going to help them, and they're
static enough that they shouldn't fragment much, if at all, anyway.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||i ran dbcc showcontig and the scan density for system tables was too low
12-15%.
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" έγγÏ?αÏ?ε:
> nikolakg wrote:
> > does anyone know how could i defrag system tables indexes.. because dbcc
> > indexdefrag or rebuild can't be used, thks
> You shouldn't have to mess with the system table indexes. They're small
> enough that defragging really isn't going to help them, and they're
> static enough that they shouldn't fragment much, if at all, anyway.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> i ran dbcc showcontig and the scan density for system tables was too low
> 12-15%.
>
How many pages are in the indexes you're looking at?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
syscolumns syscolumns 244 12200 12.810
sysdepends ncsysdepends1 56 2027 13.462
syscolumns ncsyscolumns 93 12200 13.483
sysindexes sysindexes 187 1063 13.793
sysdepends sysdepends 57 2027 14.815
syscomments syscomments 926 1505 16.885
sysobjects sysobjects 27 1680 17.391
sysobjects ncsysobjects 14 1680 20.000
sysproperties sysproperties 6 65 20.000
sysobjects ncsysobjects2 5 1680 25.000
this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
(and some more columns)
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" έγγÏ?αÏ?ε:
> nikolakg wrote:
> > i ran dbcc showcontig and the scan density for system tables was too low
> > 12-15%.
> >
> How many pages are in the indexes you're looking at?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
> syscolumns syscolumns 244 12200 12.810
> sysdepends ncsysdepends1 56 2027 13.462
> syscolumns ncsyscolumns 93 12200 13.483
> sysindexes sysindexes 187 1063 13.793
> sysdepends sysdepends 57 2027 14.815
> syscomments syscomments 926 1505 16.885
> sysobjects sysobjects 27 1680 17.391
> sysobjects ncsysobjects 14 1680 20.000
> sysproperties sysproperties 6 65 20.000
> sysobjects ncsysobjects2 5 1680 25.000
>
> this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> (and some more columns)
>
Less than 1000 pages, don't worry about them.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||thank you!
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" έγγÏ?αÏ?ε:
> nikolakg wrote:
> > OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
> > syscolumns syscolumns 244 12200 12.810
> > sysdepends ncsysdepends1 56 2027 13.462
> > syscolumns ncsyscolumns 93 12200 13.483
> > sysindexes sysindexes 187 1063 13.793
> > sysdepends sysdepends 57 2027 14.815
> > syscomments syscomments 926 1505 16.885
> > sysobjects sysobjects 27 1680 17.391
> > sysobjects ncsysobjects 14 1680 20.000
> > sysproperties sysproperties 6 65 20.000
> > sysobjects ncsysobjects2 5 1680 25.000
> >
> >
> > this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> > (and some more columns)
> >
> Less than 1000 pages, don't worry about them.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||We have an issue because text data from sysIndexes is sitting right at the
end of our primary datafile and this stops us truncating the end off the
file.
We have done a load of housekeeping and could reclaim a greate deal of disk
space.
We cannot do a shrink/movc data pages due to log shipping across our WAN.
DBCC extentinfo (Production, sysIndexes, -1)
Paul

defrag system objects/tables

Hi,
I used the DBCC SHOWCONTIG on a database and it shows :
DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 2817.5
- Avg. Page Density (full)................: 65.19%
so i tried to defrag this particular table
using DBCC INDEX command
but got this error DBCC INDEXDEFRAG cannot be used on system table indexes
as you might have noticed the logical/exten scan fragmentation is quite high ,
in this case how can i do a defrag across the whole database including those
system tables/objects ? and do i need to do it offline/online ?
apreciate ur advice
tks & rdgs
Message posted via http://www.droptable.com
hi,
maxzsim via droptable.com wrote:
> Hi,
> I used the DBCC SHOWCONTIG on a database and it shows :
> DBCC SHOWCONTIG scanning 'sysobjects' table...
> Table: 'sysobjects' (1); index ID: 1, database ID: 8
> TABLE level scan performed.
> - Pages Scanned........................: 4
> - Extents Scanned.......................: 4
as you already have been reported, you can not "reindex" system tables...
anyway the report you got is not that drammatic...
you have 4 pages in mixed extents as the object does not fill a full extent
as well...
so keep your house keeping tasks on user objects only...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

defrag SQLServer

Hi All,
Is it safe to defrag SQL server? What's the best utility for this if any?
thanks,
joel"Joel Gacosta" <joel@.bluemediacomm.com> wrote in message
news:#J#AnMWpDHA.3612@.TK2MSFTNGP11.phx.gbl...
> Is it safe to defrag SQL server? What's the best utility for this if any?
>
If you are running a file level defragmenter, then you should have no
issues. If the disk is severely fragmented then the first time it's run, it
would not be a bad idea to stop the MSSQLServer service as this would close
the open database files and allow them to be de-fragmented. As far as
products, we use Diskkeeper on all of our servers, no issues with this
product...
Steve|||Hi Joel
Take a look at this whitepaper which contains almost everything you'll ever
need to know about SQL Server fragmentation:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
ol/sql/maintain/optimize/ss2kidbp.asp
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Joel Gacosta" <joel@.bluemediacomm.com> wrote in message
news:#J#AnMWpDHA.3612@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Is it safe to defrag SQL server? What's the best utility for this if any?
> thanks,
> joel
>|||There is nothing called Defragmenting SQL Server.
I think you are asking about DeFragmenting SQL Server
Database objects. If so,then "YES".
As part of database maintenance to improve performance,
you have to regularly DEFRAGMENT the Fragmented objects.
Table fragmentation occurs through the process of data
modifications (INSERT, UPDATE, and DELETE statements) made
against the table. Because these modifications are not
usually distributed equally among the rows of the table,
the fullness of each page can vary over time.
"DBCC SHOWCONTIG" , Displays fragmentation information for
the data and indexes of the specified table.
When an index is heavily fragmented, there are two choices
for reducing fragmentation:
1. Drop and re-create a clustered index.
2. You can defragment the indexes using "DBCC INDEXDEFRAG"
Also,If you want to shrink the databases, you can do using
SQL Server maintenance plans or in the database options.
Hope this helps.
-SQLVarad(MCDBA-1999,MCSE-1999)
>--Original Message--
>Hi All,
>Is it safe to defrag SQL server? What's the best utility
for this if any?
>thanks,
>joel
>.
>|||Some of what SQLVarad said below is incorrect.
> As part of database maintenance to improve performance,
> you have to regularly DEFRAGMENT the Fragmented objects.
You should only defragment indexes if you have empirical evidence that
increasing fragmentation is causing decreasing performance.
> When an index is heavily fragmented, there are two choices
> for reducing fragmentation:
> 1. Drop and re-create a clustered index.
> 2. You can defragment the indexes using "DBCC INDEXDEFRAG"
To reduce fragmentation in an index (clustered or non-clustered) there are 3
options:
1) run DBCC INDEXDEFRAG
2) run DBCC DBREINDEX
3) manually drop and re-create the index
3) is just a manual version of 2). Read the whitepaper Kalen referenced for
a description of these and when to use them.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLVarad" <SQLVarad@.hotmail.com> wrote in message
news:040e01c3a568$665b09d0$a101280a@.phx.gbl...
> There is nothing called Defragmenting SQL Server.
> I think you are asking about DeFragmenting SQL Server
> Database objects. If so,then "YES".
> As part of database maintenance to improve performance,
> you have to regularly DEFRAGMENT the Fragmented objects.
> Table fragmentation occurs through the process of data
> modifications (INSERT, UPDATE, and DELETE statements) made
> against the table. Because these modifications are not
> usually distributed equally among the rows of the table,
> the fullness of each page can vary over time.
> "DBCC SHOWCONTIG" , Displays fragmentation information for
> the data and indexes of the specified table.
> When an index is heavily fragmented, there are two choices
> for reducing fragmentation:
> 1. Drop and re-create a clustered index.
> 2. You can defragment the indexes using "DBCC INDEXDEFRAG"
> Also,If you want to shrink the databases, you can do using
> SQL Server maintenance plans or in the database options.
> Hope this helps.
> -SQLVarad(MCDBA-1999,MCSE-1999)
> >--Original Message--
> >Hi All,
> >
> >Is it safe to defrag SQL server? What's the best utility
> for this if any?
> >
> >thanks,
> >joel
> >
> >.
> >