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
>

---