Delete Database Transaction Log File

My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
Dan
I believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan
|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan

Delete Database Transaction Log File

My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
DanI believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan

Delete Database Transaction Log File

My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
DanI believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan

Defragmenting for SQL server performance?

Can Anybody guide me whether DEFRAGMENTING the drive improve the SQL Server performance in general.

What should be the location of the DATABASE files and the TRANSACTION log files.

Should they be in a single drive or seperate drives?

Thanks in advance

Jacx

For best I/O performance they should reside on different drives.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Defraging a drive with a database on it

Hi all,
I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
contains the database has become massively fragmented in the last two months
since the system was put in. I'd like to run defrag on the drive and then
set it up as a scheduled task to run weekly to keep the fragmentation down.
I'm new to the SQL world and don't think that running defrag will mess
anything up but am worried about the "what if" factor. I'm learning SQL out
of a couple of books (no training budget). None of my books say anything
about defrag itself.
Can anyone reassure me that running defrag on a drive with a database on it
is safe? Is there anything I should look out for?
Thanks.
Dan
It is safe, as the database is stored on "regular" files (seen from the operating system's
perspective). Just stop the SQL Server service before defragging. And do SQL Server backups first,
just in case. Also, consider why the files are so fragmented. For instance, read this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
> Hi all,
> I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
> contains the database has become massively fragmented in the last two months
> since the system was put in. I'd like to run defrag on the drive and then
> set it up as a scheduled task to run weekly to keep the fragmentation down.
> I'm new to the SQL world and don't think that running defrag will mess
> anything up but am worried about the "what if" factor. I'm learning SQL out
> of a couple of books (no training budget). None of my books say anything
> about defrag itself.
> Can anyone reassure me that running defrag on a drive with a database on it
> is safe? Is there anything I should look out for?
> Thanks.
>
> Dan
>
|||Tibor,
Thanks for the quick reply. I'm feeling more comfortable about it now.
Dan
"Tibor Karaszi" wrote:

> It is safe, as the database is stored on "regular" files (seen from the operating system's
> perspective). Just stop the SQL Server service before defragging. And do SQL Server backups first,
> just in case. Also, consider why the files are so fragmented. For instance, read this:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
> news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
>
>

Defraging a drive with a database on it

Hi all,
I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
contains the database has become massively fragmented in the last two months
since the system was put in. I'd like to run defrag on the drive and then
set it up as a scheduled task to run weekly to keep the fragmentation down.
I'm new to the SQL world and don't think that running defrag will mess
anything up but am worried about the "what if" factor. I'm learning SQL out
of a couple of books (no training budget). None of my books say anything
about defrag itself.
Can anyone reassure me that running defrag on a drive with a database on it
is safe? Is there anything I should look out for?
Thanks.
DanIt is safe, as the database is stored on "regular" files (seen from the oper
ating system's
perspective). Just stop the SQL Server service before defragging. And do SQL
Server backups first,
just in case. Also, consider why the files are so fragmented. For instance,
read this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
> Hi all,
> I'm running MS SQL 2000 on a 2003 Server. I have found that the drive tha
t
> contains the database has become massively fragmented in the last two mont
hs
> since the system was put in. I'd like to run defrag on the drive and then
> set it up as a scheduled task to run weekly to keep the fragmentation down
.
> I'm new to the SQL world and don't think that running defrag will mess
> anything up but am worried about the "what if" factor. I'm learning SQL o
ut
> of a couple of books (no training budget). None of my books say anything
> about defrag itself.
> Can anyone reassure me that running defrag on a drive with a database on i
t
> is safe? Is there anything I should look out for?
> Thanks.
>
> Dan
>|||Tibor,
Thanks for the quick reply. I'm feeling more comfortable about it now.
Dan
"Tibor Karaszi" wrote:

> It is safe, as the database is stored on "regular" files (seen from the op
erating system's
> perspective). Just stop the SQL Server service before defragging. And do S
QL Server backups first,
> just in case. Also, consider why the files are so fragmented. For instance
, read this:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
> news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
>
>

Defraging a drive with a database on it

Hi all,
I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
contains the database has become massively fragmented in the last two months
since the system was put in. I'd like to run defrag on the drive and then
set it up as a scheduled task to run weekly to keep the fragmentation down.
I'm new to the SQL world and don't think that running defrag will mess
anything up but am worried about the "what if" factor. I'm learning SQL out
of a couple of books (no training budget). None of my books say anything
about defrag itself.
Can anyone reassure me that running defrag on a drive with a database on it
is safe? Is there anything I should look out for?
Thanks.
DanIt is safe, as the database is stored on "regular" files (seen from the operating system's
perspective). Just stop the SQL Server service before defragging. And do SQL Server backups first,
just in case. Also, consider why the files are so fragmented. For instance, read this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
> Hi all,
> I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
> contains the database has become massively fragmented in the last two months
> since the system was put in. I'd like to run defrag on the drive and then
> set it up as a scheduled task to run weekly to keep the fragmentation down.
> I'm new to the SQL world and don't think that running defrag will mess
> anything up but am worried about the "what if" factor. I'm learning SQL out
> of a couple of books (no training budget). None of my books say anything
> about defrag itself.
> Can anyone reassure me that running defrag on a drive with a database on it
> is safe? Is there anything I should look out for?
> Thanks.
>
> Dan
>|||Tibor,
Thanks for the quick reply. I'm feeling more comfortable about it now.
Dan
"Tibor Karaszi" wrote:
> It is safe, as the database is stored on "regular" files (seen from the operating system's
> perspective). Just stop the SQL Server service before defragging. And do SQL Server backups first,
> just in case. Also, consider why the files are so fragmented. For instance, read this:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
> news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
> > Hi all,
> >
> > I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
> > contains the database has become massively fragmented in the last two months
> > since the system was put in. I'd like to run defrag on the drive and then
> > set it up as a scheduled task to run weekly to keep the fragmentation down.
> >
> > I'm new to the SQL world and don't think that running defrag will mess
> > anything up but am worried about the "what if" factor. I'm learning SQL out
> > of a couple of books (no training budget). None of my books say anything
> > about defrag itself.
> >
> > Can anyone reassure me that running defrag on a drive with a database on it
> > is safe? Is there anything I should look out for?
> >
> > Thanks.
> >
> >
> > Dan
> >
>
>

Defragging Tools?

Has anyone got any top tips, or a link to a tool that can help me with
defragging the drive my SQL Server 2K files sit on?
Something that can run on a timer, or be called by scheduled tasks would be
cool.
Thanks
PeterHow about Diskeeper from Executive Software
I think you will need to stop the services to run an External(to SQL Server
defrag) as the files may be passed over because they are locked by SQL
Server itself.
Also look at internal fagmentation
http://www.sql-server-performance.com/rd_index_fragmentation.asp
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Peter Marshall" <texmarshall@.hotmail.com> wrote in message
news:fdbfb.295$Uc7.107@.news-binary.blueyonder.co.uk...
> Has anyone got any top tips, or a link to a tool that can help me with
> defragging the drive my SQL Server 2K files sit on?
> Something that can run on a timer, or be called by scheduled tasks would
be
> cool.
> Thanks
> Peter
>|||In addition to Allan's reply:
If this is a dedicated SQL Server then you shouldn't have to defrag (assuming you don't abuse
autogrow and shrink, of course).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Peter Marshall" <texmarshall@.hotmail.com> wrote in message
news:fdbfb.295$Uc7.107@.news-binary.blueyonder.co.uk...
> Has anyone got any top tips, or a link to a tool that can help me with
> defragging the drive my SQL Server 2K files sit on?
> Something that can run on a timer, or be called by scheduled tasks would be
> cool.
> Thanks
> Peter
>

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 local drive in SQL Server 2005

Are there any considerations that must be taken before defragging the local
drives in SQL Server 2005? For RAID and non-RAID drives?A backup would be nice first but other than that not really.
--
Andrew J. Kelly SQL MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
> Are there any considerations that must be taken before defragging the
> local
> drives in SQL Server 2005? For RAID and non-RAID drives?|||Thanks...
"Andrew J. Kelly" wrote:
> A backup would be nice first but other than that not really.
> --
> Andrew J. Kelly SQL MVP
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
> > Are there any considerations that must be taken before defragging the
> > local
> > drives in SQL Server 2005? For RAID and non-RAID drives?
>
>|||morphius wrote:
> Are there any considerations that must be taken before defragging the local
> drives in SQL Server 2005? For RAID and non-RAID drives?
Be sure to shut down SQL Server, otherwise the data files will be locked
and won't be accessible to defrag. DisKeeper claims to defrag database
files without shutting down SQL, but I wouldn't be comfortable doing that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Defrag local drive in SQL Server 2005

Are there any considerations that must be taken before defragging the local
drives in SQL Server 2005? For RAID and non-RAID drives?
A backup would be nice first but other than that not really.
Andrew J. Kelly SQL MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
> Are there any considerations that must be taken before defragging the
> local
> drives in SQL Server 2005? For RAID and non-RAID drives?
|||Thanks...
"Andrew J. Kelly" wrote:

> A backup would be nice first but other than that not really.
> --
> Andrew J. Kelly SQL MVP
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
>
>
|||morphius wrote:
> Are there any considerations that must be taken before defragging the local
> drives in SQL Server 2005? For RAID and non-RAID drives?
Be sure to shut down SQL Server, otherwise the data files will be locked
and won't be accessible to defrag. DisKeeper claims to defrag database
files without shutting down SQL, but I wouldn't be comfortable doing that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Defrag local drive in SQL Server 2005

Are there any considerations that must be taken before defragging the local
drives in SQL Server 2005? For RAID and non-RAID drives?A backup would be nice first but other than that not really.
Andrew J. Kelly SQL MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
> Are there any considerations that must be taken before defragging the
> local
> drives in SQL Server 2005? For RAID and non-RAID drives?|||Thanks...
"Andrew J. Kelly" wrote:

> A backup would be nice first but other than that not really.
> --
> Andrew J. Kelly SQL MVP
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
>
>|||morphius wrote:
> Are there any considerations that must be taken before defragging the loca
l
> drives in SQL Server 2005? For RAID and non-RAID drives?
Be sure to shut down SQL Server, otherwise the data files will be locked
and won't be accessible to defrag. DisKeeper claims to defrag database
files without shutting down SQL, but I wouldn't be comfortable doing that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DEFRAG disk drive

SQL 2000

We took SQL Server offline last night and defragged the SAN. Should we
reindex or will be be okay ?

Thanks,

Craig(csomberg@.dwr.com) writes:
> SQL 2000
> We took SQL Server offline last night and defragged the SAN. Should we
> reindex or will be be okay ?

An external disk fragmenter makes the file contiguous, as seen from
the file system. It does not work with the inside of the file, because
it does not know the structure of the file. So, yes, you need to run
reindexing to handle internal fragmentation.

Generally, reindexing is much more important to do on a regular basis
than running a disk defragmenter.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||SQL level index defrag and OS level file defrag should be orthogonal to ech
other (both help for sure). There is no clear reason why conducting one
defrag requires the other one. I will be interested in knowing if this is
not the case, r me in that case please.

But it is a good practice to regularly check and defrag the index.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
<csomberg@.dwr.com> wrote in message
news:1115133809.049446.242770@.z14g2000cwz.googlegr oups.com...
> SQL 2000
> We took SQL Server offline last night and defragged the SAN. Should we
> reindex or will be be okay ?
> Thanks,
> Craig