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