Hello
We have a SQL Server 2000 database with 2 transaction log files.
The 2nd file was created when we were running out of disk space and the person creating it was not familiar with the dbcc shrink command.
I now want to get rid of the 2nd log file. I ran the following steps with no success:
DBCC SHRINKFILE ('Log_file', EMPTYFILE )
--Message: Cannot shrink log file 3 (log_file) because all logical log files are in use.
ALTER DATABASE db1 REMOVE FILE 'Log_file'
--Message: The file 'Log_file' cannot be removed because it is not empty.
There are no users or open transactions in the database. I have also tried sp_detach_db and sp_attach_single_file_db but that does not work either as the database attaches both the transaction logs back.
Please advise.
Thanks
NinaTry running "checkpoint" after the DBCC SHRINKFILE. I think this was a requirement in the SQL 7.0 days, but I am not sure about SQL 2000.|||Hey
thanks for the help but it worked fine after i truncated the log file.
Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts
Thursday, March 22, 2012
Delayed Write Failed
Hello,
We met a problem when we want to backup our database to a shared disk, the
error message is as following,
{Delayed Write Failed} Windows was unable to save all the data for the file
\Device\MysharedDriver. The data has been lost. This error may be caused by
a failure of your computer hardware or network connection. Please try to
save this file elsewhere.
But we succeeded with backup the pubs database to the same folder in the
shared disk.
Please anyone give help.
Thanks
FrankI think you do not have enough space to save Backup file.
first step:You must sure you have enough disk for your
database.
Second Step:Check the right of Sql Server's SA,how much
disk space SA can used on the shaved disk.
Good luck.
Milton Li
>--Original Message--
>Hello,
>We met a problem when we want to backup our database to
a shared disk, the
>error message is as following,
>{Delayed Write Failed} Windows was unable to save all
the data for the file
>\Device\MysharedDriver. The data has been lost. This
error may be caused by
>a failure of your computer hardware or network
connection. Please try to
>save this file elsewhere.
>But we succeeded with backup the pubs database to the
same folder in the
>shared disk.
>Please anyone give help.
>Thanks
>Frank
>
>.
>|||sometimes its also related to disk issues.
check there are no errors on disks by looking into
your system log.
>--Original Message--
>I think you do not have enough space to save Backup file.
>first step:You must sure you have enough disk for your
>database.
>Second Step:Check the right of Sql Server's SA,how much
>disk space SA can used on the shaved disk.
>Good luck.
>Milton Li
>>--Original Message--
>>Hello,
>>We met a problem when we want to backup our database to
>a shared disk, the
>>error message is as following,
>>{Delayed Write Failed} Windows was unable to save all
>the data for the file
>>\Device\MysharedDriver. The data has been lost. This
>error may be caused by
>>a failure of your computer hardware or network
>connection. Please try to
>>save this file elsewhere.
>>But we succeeded with backup the pubs database to the
>same folder in the
>>shared disk.
>>Please anyone give help.
>>Thanks
>>Frank
>>
>>.
>.
>
We met a problem when we want to backup our database to a shared disk, the
error message is as following,
{Delayed Write Failed} Windows was unable to save all the data for the file
\Device\MysharedDriver. The data has been lost. This error may be caused by
a failure of your computer hardware or network connection. Please try to
save this file elsewhere.
But we succeeded with backup the pubs database to the same folder in the
shared disk.
Please anyone give help.
Thanks
FrankI think you do not have enough space to save Backup file.
first step:You must sure you have enough disk for your
database.
Second Step:Check the right of Sql Server's SA,how much
disk space SA can used on the shaved disk.
Good luck.
Milton Li
>--Original Message--
>Hello,
>We met a problem when we want to backup our database to
a shared disk, the
>error message is as following,
>{Delayed Write Failed} Windows was unable to save all
the data for the file
>\Device\MysharedDriver. The data has been lost. This
error may be caused by
>a failure of your computer hardware or network
connection. Please try to
>save this file elsewhere.
>But we succeeded with backup the pubs database to the
same folder in the
>shared disk.
>Please anyone give help.
>Thanks
>Frank
>
>.
>|||sometimes its also related to disk issues.
check there are no errors on disks by looking into
your system log.
>--Original Message--
>I think you do not have enough space to save Backup file.
>first step:You must sure you have enough disk for your
>database.
>Second Step:Check the right of Sql Server's SA,how much
>disk space SA can used on the shaved disk.
>Good luck.
>Milton Li
>>--Original Message--
>>Hello,
>>We met a problem when we want to backup our database to
>a shared disk, the
>>error message is as following,
>>{Delayed Write Failed} Windows was unable to save all
>the data for the file
>>\Device\MysharedDriver. The data has been lost. This
>error may be caused by
>>a failure of your computer hardware or network
>connection. Please try to
>>save this file elsewhere.
>>But we succeeded with backup the pubs database to the
>same folder in the
>>shared disk.
>>Please anyone give help.
>>Thanks
>>Frank
>>
>>.
>.
>
Monday, March 19, 2012
Defragmenting Local Disk
Is there any performance gain from defragmenting the SQL
SERVER data and log file disk subsystem (or OS). I have
run the "Analyze" part of the windows disk defragmenter
and several of the data and log files have several
fragments within the file. Some of them have 20
fragments. Is there another tool that is preferred? Any
advice or past experienced is much appreciated.
Thanks,
Andy20 fragments is not much compared to the fragmentation you probably have int
ernally in the files. But sure,
stop the SQL Server service and do a defrag if you wish - no harm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Andy" <anonymous@.discussions.microsoft.com> wrote in message news:12e6801c41674$4b7a4c20$a
601280a@.phx.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||OS level defrag is a good thing. SQL Server might report 0% fragmentation
for internal and internal fragmentation within it's data structures. But
that's just for SQL.
SQL Server can't know anything about where the bits are stored at the OS.
Consider a situation where SQL reports no fragmentation, and you're doing a
table scan. Imagine that the file with the big table is scattered all over
disk. Yes, there's be a benefit to OS frag in this case.
However, one thing to consider... defragging at the OS level doesn't always
have as big an impact as some people might expect. SQL Server pre-allocates
space when you create a file. If you create a 100M file on a disk with no
fragmentation... and put 1M of data on it. The file still uses 100M at the
OS and it would be contiguous if the file was created when the disk was not
fragmented... if the disk later becomes fragmented... that 100M file will
still be contiguous.
OS level frag tends to help the most with db files that have grown a number
of times since they were created... or if you're creating DB files on a
disks that are already fragmented...
also... don't forget that you'll need to shut down SQL Server before doing
the defrag. Open files aren't defragged...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||Sure, just like any other file access, defragmenting your database files
will improve performance if they're badly fragmented. I'm not sure if 20
fragments is that bad, however. It really depends on the size of the files
(if the files are very small, 20 fragments may be bad; if they're quite
large, it's probably not affecting contiguous data access too much). Either
way, if you don't mind the downtime (you'll have to shut down the server in
order to defrag the files), it certainly won't hurt. As for defrag tools, I
don't like Windows' built in one. I personally have gotten very good
results from O&O Software Defrag.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy
SERVER data and log file disk subsystem (or OS). I have
run the "Analyze" part of the windows disk defragmenter
and several of the data and log files have several
fragments within the file. Some of them have 20
fragments. Is there another tool that is preferred? Any
advice or past experienced is much appreciated.
Thanks,
Andy20 fragments is not much compared to the fragmentation you probably have int
ernally in the files. But sure,
stop the SQL Server service and do a defrag if you wish - no harm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Andy" <anonymous@.discussions.microsoft.com> wrote in message news:12e6801c41674$4b7a4c20$a
601280a@.phx.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||OS level defrag is a good thing. SQL Server might report 0% fragmentation
for internal and internal fragmentation within it's data structures. But
that's just for SQL.
SQL Server can't know anything about where the bits are stored at the OS.
Consider a situation where SQL reports no fragmentation, and you're doing a
table scan. Imagine that the file with the big table is scattered all over
disk. Yes, there's be a benefit to OS frag in this case.
However, one thing to consider... defragging at the OS level doesn't always
have as big an impact as some people might expect. SQL Server pre-allocates
space when you create a file. If you create a 100M file on a disk with no
fragmentation... and put 1M of data on it. The file still uses 100M at the
OS and it would be contiguous if the file was created when the disk was not
fragmented... if the disk later becomes fragmented... that 100M file will
still be contiguous.
OS level frag tends to help the most with db files that have grown a number
of times since they were created... or if you're creating DB files on a
disks that are already fragmented...
also... don't forget that you'll need to shut down SQL Server before doing
the defrag. Open files aren't defragged...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||Sure, just like any other file access, defragmenting your database files
will improve performance if they're badly fragmented. I'm not sure if 20
fragments is that bad, however. It really depends on the size of the files
(if the files are very small, 20 fragments may be bad; if they're quite
large, it's probably not affecting contiguous data access too much). Either
way, if you don't mind the downtime (you'll have to shut down the server in
order to defrag the files), it certainly won't hurt. As for defrag tools, I
don't like Windows' built in one. I personally have gotten very good
results from O&O Software Defrag.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy
Defragment disk
Hi
Is it acceptable practice to run Disk Defragmenter on a Disk when SQLServer
is installed and running?
(using Win2000, SQLServer 2000)
ThanksSure, as long as you exclude the SQL Server database files (perhaps the tool
will not even try on open files). If it is a dedicated SQL Server and you
don't overuse autogrow, then there shouldn't really be any need for this.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:eQ0ykPfoDHA.1632@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is it acceptable practice to run Disk Defragmenter on a Disk when
SQLServer
> is installed and running?
> (using Win2000, SQLServer 2000)
> Thanks
>|||How does one exclude these from the defragmenter?
I guess what I need to know is will anything bad happen if the data & log
files are defragmented? Is it best to switch off SQLServer first?|||> How does one exclude these from the defragmenter?
You have to check that with your defrag tool. If you are using the built-in
defrag tool, I suggest you check this in a Windows group.
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
Probably, until you got info from the defrag tool vendor that the tool does
not touch locked files. SQL Server does not itself have a problem with the
files being defragged, but you don't want the risk of having two tools
working against the same file(s) at the same time.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> How does one exclude these from the defragmenter?
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
>|||There is no requirement that SQL database devices be excluded from being
defragmented. There is also no requirement that SQL services first be
stopped prior to defragmenting. Microsoft's defrag APIs fully support
defragmenting SQL database devices online.
- Greg/Raxco Software
Microsoft MVP - Windows File System
Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
commercial defrag utility, as a systems engineer in the support department.
Want to email me? Delete ntloader.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > How does one exclude these from the defragmenter?
> You have to check that with your defrag tool. If you are using the
built-in
> defrag tool, I suggest you check this in a Windows group.
>
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> Probably, until you got info from the defrag tool vendor that the tool
does
> not touch locked files. SQL Server does not itself have a problem with the
> files being defragged, but you don't want the risk of having two tools
> working against the same file(s) at the same time.
> --
> Tibor Karaszi
>
> "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > How does one exclude these from the defragmenter?
> >
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> >
> >
>|||Thanks for the info, Greg! Good to know.
I was trying to get that kind of info from another defrag manufacturer, but
never got a response to my question. Hence my careful approach. :-)
--
Tibor Karaszi
"Greg Hayes/Raxco Software" <ghayesntloader@.raxco.com> wrote in message
news:%23EXhy9uoDHA.2216@.TK2MSFTNGP12.phx.gbl...
> There is no requirement that SQL database devices be excluded from being
> defragmented. There is also no requirement that SQL services first be
> stopped prior to defragmenting. Microsoft's defrag APIs fully support
> defragmenting SQL database devices online.
> - Greg/Raxco Software
> Microsoft MVP - Windows File System
> Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
> commercial defrag utility, as a systems engineer in the support
department.
> Want to email me? Delete ntloader.
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > > How does one exclude these from the defragmenter?
> >
> > You have to check that with your defrag tool. If you are using the
> built-in
> > defrag tool, I suggest you check this in a Windows group.
> >
> >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> >
> > Probably, until you got info from the defrag tool vendor that the tool
> does
> > not touch locked files. SQL Server does not itself have a problem with
the
> > files being defragged, but you don't want the risk of having two tools
> > working against the same file(s) at the same time.
> > --
> > Tibor Karaszi
> >
> >
> > "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> > news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > > How does one exclude these from the defragmenter?
> > >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> > >
> > >
> >
> >
>|||Thanks everyone...
Griff
Is it acceptable practice to run Disk Defragmenter on a Disk when SQLServer
is installed and running?
(using Win2000, SQLServer 2000)
ThanksSure, as long as you exclude the SQL Server database files (perhaps the tool
will not even try on open files). If it is a dedicated SQL Server and you
don't overuse autogrow, then there shouldn't really be any need for this.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:eQ0ykPfoDHA.1632@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is it acceptable practice to run Disk Defragmenter on a Disk when
SQLServer
> is installed and running?
> (using Win2000, SQLServer 2000)
> Thanks
>|||How does one exclude these from the defragmenter?
I guess what I need to know is will anything bad happen if the data & log
files are defragmented? Is it best to switch off SQLServer first?|||> How does one exclude these from the defragmenter?
You have to check that with your defrag tool. If you are using the built-in
defrag tool, I suggest you check this in a Windows group.
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
Probably, until you got info from the defrag tool vendor that the tool does
not touch locked files. SQL Server does not itself have a problem with the
files being defragged, but you don't want the risk of having two tools
working against the same file(s) at the same time.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> How does one exclude these from the defragmenter?
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
>|||There is no requirement that SQL database devices be excluded from being
defragmented. There is also no requirement that SQL services first be
stopped prior to defragmenting. Microsoft's defrag APIs fully support
defragmenting SQL database devices online.
- Greg/Raxco Software
Microsoft MVP - Windows File System
Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
commercial defrag utility, as a systems engineer in the support department.
Want to email me? Delete ntloader.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > How does one exclude these from the defragmenter?
> You have to check that with your defrag tool. If you are using the
built-in
> defrag tool, I suggest you check this in a Windows group.
>
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> Probably, until you got info from the defrag tool vendor that the tool
does
> not touch locked files. SQL Server does not itself have a problem with the
> files being defragged, but you don't want the risk of having two tools
> working against the same file(s) at the same time.
> --
> Tibor Karaszi
>
> "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > How does one exclude these from the defragmenter?
> >
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> >
> >
>|||Thanks for the info, Greg! Good to know.
I was trying to get that kind of info from another defrag manufacturer, but
never got a response to my question. Hence my careful approach. :-)
--
Tibor Karaszi
"Greg Hayes/Raxco Software" <ghayesntloader@.raxco.com> wrote in message
news:%23EXhy9uoDHA.2216@.TK2MSFTNGP12.phx.gbl...
> There is no requirement that SQL database devices be excluded from being
> defragmented. There is also no requirement that SQL services first be
> stopped prior to defragmenting. Microsoft's defrag APIs fully support
> defragmenting SQL database devices online.
> - Greg/Raxco Software
> Microsoft MVP - Windows File System
> Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
> commercial defrag utility, as a systems engineer in the support
department.
> Want to email me? Delete ntloader.
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > > How does one exclude these from the defragmenter?
> >
> > You have to check that with your defrag tool. If you are using the
> built-in
> > defrag tool, I suggest you check this in a Windows group.
> >
> >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> >
> > Probably, until you got info from the defrag tool vendor that the tool
> does
> > not touch locked files. SQL Server does not itself have a problem with
the
> > files being defragged, but you don't want the risk of having two tools
> > working against the same file(s) at the same time.
> > --
> > Tibor Karaszi
> >
> >
> > "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> > news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > > How does one exclude these from the defragmenter?
> > >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> > >
> > >
> >
> >
>|||Thanks everyone...
Griff
Defragment
If I want defragment the hard disk.
I will stop the service of SQL server.
Is it OK?Yes, that is fine. But please do a backup before just in case.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vincent" <aaa@.aaa.com> wrote in message news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...[col
or=darkred]
> If I want defragment the hard disk.
> I will stop the service of SQL server.
> Is it OK?
>
>[/color]|||I found that the defragment has completed but the database file remain
fragment.
Any missing step?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uKSTMQkGFHA.1948@.TK2MSFTNGP10.phx.gbl...
> Yes, that is fine. But please do a backup before just in case.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Vincent" <aaa@.aaa.com> wrote in message
news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...
>|||Assuming you stopped SQL Server during the defrag: Perhaps there wasn't enou
gh free space on the
disk in order to defrag your potential large database files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vincent" <aaa@.aaa.com> wrote in message news:OjZJfflGFHA.1948@.TK2MSFTNGP10.phx.gbl...[colo
r=darkred]
>I found that the defragment has completed but the database file remain
> fragment.
> Any missing step?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uKSTMQkGFHA.1948@.TK2MSFTNGP10.phx.gbl...
> news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...
>[/color]|||How are you determining the file is fragmented?
Andrew J. Kelly SQL MVP
"Vincent" <aaa@.aaa.com> wrote in message
news:OjZJfflGFHA.1948@.TK2MSFTNGP10.phx.gbl...
>I found that the defragment has completed but the database file remain
> fragment.
> Any missing step?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uKSTMQkGFHA.1948@.TK2MSFTNGP10.phx.gbl...
> news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...
>
I will stop the service of SQL server.
Is it OK?Yes, that is fine. But please do a backup before just in case.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vincent" <aaa@.aaa.com> wrote in message news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...[col
or=darkred]
> If I want defragment the hard disk.
> I will stop the service of SQL server.
> Is it OK?
>
>[/color]|||I found that the defragment has completed but the database file remain
fragment.
Any missing step?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uKSTMQkGFHA.1948@.TK2MSFTNGP10.phx.gbl...
> Yes, that is fine. But please do a backup before just in case.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Vincent" <aaa@.aaa.com> wrote in message
news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...
>|||Assuming you stopped SQL Server during the defrag: Perhaps there wasn't enou
gh free space on the
disk in order to defrag your potential large database files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vincent" <aaa@.aaa.com> wrote in message news:OjZJfflGFHA.1948@.TK2MSFTNGP10.phx.gbl...[colo
r=darkred]
>I found that the defragment has completed but the database file remain
> fragment.
> Any missing step?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uKSTMQkGFHA.1948@.TK2MSFTNGP10.phx.gbl...
> news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...
>[/color]|||How are you determining the file is fragmented?
Andrew J. Kelly SQL MVP
"Vincent" <aaa@.aaa.com> wrote in message
news:OjZJfflGFHA.1948@.TK2MSFTNGP10.phx.gbl...
>I found that the defragment has completed but the database file remain
> fragment.
> Any missing step?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uKSTMQkGFHA.1948@.TK2MSFTNGP10.phx.gbl...
> news:urR%23csjGFHA.584@.TK2MSFTNGP14.phx.gbl...
>
Defragging SQL Server Data Disk?
I've noticed disk performance on our SQL 2000 cluster
slowing down. The 100 GB disk with the database files is
on an array. Using the included 2000 defrag tool I did an
analyze and found the disk is highly fragmented. It's
been running for about 2 years and never been defraged.
Is it necessary or even safe to defrag the disk with the
databases? I would assume you would need to stop the SQL
service to do it. Would it even do any good? I'd almost
think that since there is a small number of files that
after turning it back up it wouldn't take long before
everthing is fragmented again, but maybe not to the extent
that it is now.Sam,
Stop the SQL Server service and defrag the disk. The primary cause of
file fragmentation is growing and shrinking of the SQL Server data
files. If using autogrow, then grow in large chunks, so that this
operation only occurs every few months or so, or even better longer
periods than that. Turn off autoshrink.
After you have defragged the data files, perform a DBCC DBREINDEX to
locically defrag your indexes. If you have a clustered index on all your
tables, this process will be more thorough.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.|||I agree 100% with Mark. Just want to add that you should make sure you have
valid backups before attempting the defrag. And I also want to emphasize to
stop the shrinking and growing.
--
Andrew J. Kelly SQL MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23Tr%23AB%23cEHA.2752@.TK2MSFTNGP12.phx.gbl...
> Sam,
> Stop the SQL Server service and defrag the disk. The primary cause of
> file fragmentation is growing and shrinking of the SQL Server data
> files. If using autogrow, then grow in large chunks, so that this
> operation only occurs every few months or so, or even better longer
> periods than that. Turn off autoshrink.
> After you have defragged the data files, perform a DBCC DBREINDEX to
> locically defrag your indexes. If you have a clustered index on all your
> tables, this process will be more thorough.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Sam Winston wrote:
> > I've noticed disk performance on our SQL 2000 cluster
> > slowing down. The 100 GB disk with the database files is
> > on an array. Using the included 2000 defrag tool I did an
> > analyze and found the disk is highly fragmented. It's
> > been running for about 2 years and never been defraged.
> > Is it necessary or even safe to defrag the disk with the
> > databases? I would assume you would need to stop the SQL
> > service to do it. Would it even do any good? I'd almost
> > think that since there is a small number of files that
> > after turning it back up it wouldn't take long before
> > everthing is fragmented again, but maybe not to the extent
> > that it is now.|||Hi
As an aside.
The fact that Defragger can even look at your array volumes means that your
disk is formatted with block sizes of 4kb or smaller.
Have a serious look at formatting your drives with larger block sizes, and
if possible, the same size as your RAID block sizes.
SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
improve your performance (8 or 64kb)
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sam Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:4f3601c473dc$d7b2b8b0$a501280a@.phx.gbl...
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.|||Mike,
I agree, 64k seems to be better for "standard" disks.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Mike Epprecht (SQL MVP) wrote:
> Hi
> As an aside.
> The fact that Defragger can even look at your array volumes means that your
> disk is formatted with block sizes of 4kb or smaller.
> Have a serious look at formatting your drives with larger block sizes, and
> if possible, the same size as your RAID block sizes.
> SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
> improve your performance (8 or 64kb)
> Regards|||I have been shrinking and growing alot. It hit me that's
what's resulting in the fragmentation. I'll add in some
overhead, try to stick with fixed sizes, and set the
autogrow to a large chunk.
We are adding a second array. 400 GB total that I'm
formatting as 2 200 GB disks. The server has six majort
databases all are over 5 GB and 3 are over 15 GB. The
current disks are 4k clusters. Larger clusters make
sense. For DBs of this size would'nt it make sense to go
ahead and do 64k?|||Sam,
Yep, 64k clusters would be good.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I have been shrinking and growing alot. It hit me that's
> what's resulting in the fragmentation. I'll add in some
> overhead, try to stick with fixed sizes, and set the
> autogrow to a large chunk.
> We are adding a second array. 400 GB total that I'm
> formatting as 2 200 GB disks. The server has six majort
> databases all are over 5 GB and 3 are over 15 GB. The
> current disks are 4k clusters. Larger clusters make
> sense. For DBs of this size would'nt it make sense to go
> ahead and do 64k?
slowing down. The 100 GB disk with the database files is
on an array. Using the included 2000 defrag tool I did an
analyze and found the disk is highly fragmented. It's
been running for about 2 years and never been defraged.
Is it necessary or even safe to defrag the disk with the
databases? I would assume you would need to stop the SQL
service to do it. Would it even do any good? I'd almost
think that since there is a small number of files that
after turning it back up it wouldn't take long before
everthing is fragmented again, but maybe not to the extent
that it is now.Sam,
Stop the SQL Server service and defrag the disk. The primary cause of
file fragmentation is growing and shrinking of the SQL Server data
files. If using autogrow, then grow in large chunks, so that this
operation only occurs every few months or so, or even better longer
periods than that. Turn off autoshrink.
After you have defragged the data files, perform a DBCC DBREINDEX to
locically defrag your indexes. If you have a clustered index on all your
tables, this process will be more thorough.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.|||I agree 100% with Mark. Just want to add that you should make sure you have
valid backups before attempting the defrag. And I also want to emphasize to
stop the shrinking and growing.
--
Andrew J. Kelly SQL MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23Tr%23AB%23cEHA.2752@.TK2MSFTNGP12.phx.gbl...
> Sam,
> Stop the SQL Server service and defrag the disk. The primary cause of
> file fragmentation is growing and shrinking of the SQL Server data
> files. If using autogrow, then grow in large chunks, so that this
> operation only occurs every few months or so, or even better longer
> periods than that. Turn off autoshrink.
> After you have defragged the data files, perform a DBCC DBREINDEX to
> locically defrag your indexes. If you have a clustered index on all your
> tables, this process will be more thorough.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Sam Winston wrote:
> > I've noticed disk performance on our SQL 2000 cluster
> > slowing down. The 100 GB disk with the database files is
> > on an array. Using the included 2000 defrag tool I did an
> > analyze and found the disk is highly fragmented. It's
> > been running for about 2 years and never been defraged.
> > Is it necessary or even safe to defrag the disk with the
> > databases? I would assume you would need to stop the SQL
> > service to do it. Would it even do any good? I'd almost
> > think that since there is a small number of files that
> > after turning it back up it wouldn't take long before
> > everthing is fragmented again, but maybe not to the extent
> > that it is now.|||Hi
As an aside.
The fact that Defragger can even look at your array volumes means that your
disk is formatted with block sizes of 4kb or smaller.
Have a serious look at formatting your drives with larger block sizes, and
if possible, the same size as your RAID block sizes.
SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
improve your performance (8 or 64kb)
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sam Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:4f3601c473dc$d7b2b8b0$a501280a@.phx.gbl...
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.|||Mike,
I agree, 64k seems to be better for "standard" disks.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Mike Epprecht (SQL MVP) wrote:
> Hi
> As an aside.
> The fact that Defragger can even look at your array volumes means that your
> disk is formatted with block sizes of 4kb or smaller.
> Have a serious look at formatting your drives with larger block sizes, and
> if possible, the same size as your RAID block sizes.
> SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
> improve your performance (8 or 64kb)
> Regards|||I have been shrinking and growing alot. It hit me that's
what's resulting in the fragmentation. I'll add in some
overhead, try to stick with fixed sizes, and set the
autogrow to a large chunk.
We are adding a second array. 400 GB total that I'm
formatting as 2 200 GB disks. The server has six majort
databases all are over 5 GB and 3 are over 15 GB. The
current disks are 4k clusters. Larger clusters make
sense. For DBs of this size would'nt it make sense to go
ahead and do 64k?|||Sam,
Yep, 64k clusters would be good.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I have been shrinking and growing alot. It hit me that's
> what's resulting in the fragmentation. I'll add in some
> overhead, try to stick with fixed sizes, and set the
> autogrow to a large chunk.
> We are adding a second array. 400 GB total that I'm
> formatting as 2 200 GB disks. The server has six majort
> databases all are over 5 GB and 3 are over 15 GB. The
> current disks are 4k clusters. Larger clusters make
> sense. For DBs of this size would'nt it make sense to go
> ahead and do 64k?
Sunday, March 11, 2012
Defragging SQL Server Data Disk?
I've noticed disk performance on our SQL 2000 cluster
slowing down. The 100 GB disk with the database files is
on an array. Using the included 2000 defrag tool I did an
analyze and found the disk is highly fragmented. It's
been running for about 2 years and never been defraged.
Is it necessary or even safe to defrag the disk with the
databases? I would assume you would need to stop the SQL
service to do it. Would it even do any good? I'd almost
think that since there is a small number of files that
after turning it back up it wouldn't take long before
everthing is fragmented again, but maybe not to the extent
that it is now.
Sam,
Stop the SQL Server service and defrag the disk. The primary cause of
file fragmentation is growing and shrinking of the SQL Server data
files. If using autogrow, then grow in large chunks, so that this
operation only occurs every few months or so, or even better longer
periods than that. Turn off autoshrink.
After you have defragged the data files, perform a DBCC DBREINDEX to
locically defrag your indexes. If you have a clustered index on all your
tables, this process will be more thorough.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
|||I agree 100% with Mark. Just want to add that you should make sure you have
valid backups before attempting the defrag. And I also want to emphasize to
stop the shrinking and growing.
Andrew J. Kelly SQL MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23Tr%23AB%23cEHA.2752@.TK2MSFTNGP12.phx.gbl.. .[vbcol=seagreen]
> Sam,
> Stop the SQL Server service and defrag the disk. The primary cause of
> file fragmentation is growing and shrinking of the SQL Server data
> files. If using autogrow, then grow in large chunks, so that this
> operation only occurs every few months or so, or even better longer
> periods than that. Turn off autoshrink.
> After you have defragged the data files, perform a DBCC DBREINDEX to
> locically defrag your indexes. If you have a clustered index on all your
> tables, this process will be more thorough.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Sam Winston wrote:
|||Hi,
If you create your db large enough (considering growth of the db.) then disk defrag. won't effect your SQL server performance.
Running defrag tools on SQL partitions doesn't sound good to me ..
"Sam Winston" wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
>
|||Hi
As an aside.
The fact that Defragger can even look at your array volumes means that your
disk is formatted with block sizes of 4kb or smaller.
Have a serious look at formatting your drives with larger block sizes, and
if possible, the same size as your RAID block sizes.
SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
improve your performance (8 or 64kb)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sam Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:4f3601c473dc$d7b2b8b0$a501280a@.phx.gbl...
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
|||Mike,
I agree, 64k seems to be better for "standard" disks.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Mike Epprecht (SQL MVP) wrote:
> Hi
> As an aside.
> The fact that Defragger can even look at your array volumes means that your
> disk is formatted with block sizes of 4kb or smaller.
> Have a serious look at formatting your drives with larger block sizes, and
> if possible, the same size as your RAID block sizes.
> SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
> improve your performance (8 or 64kb)
> Regards
|||I have been shrinking and growing alot. It hit me that's
what's resulting in the fragmentation. I'll add in some
overhead, try to stick with fixed sizes, and set the
autogrow to a large chunk.
We are adding a second array. 400 GB total that I'm
formatting as 2 200 GB disks. The server has six majort
databases all are over 5 GB and 3 are over 15 GB. The
current disks are 4k clusters. Larger clusters make
sense. For DBs of this size would'nt it make sense to go
ahead and do 64k?
|||Sam,
Yep, 64k clusters would be good.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I have been shrinking and growing alot. It hit me that's
> what's resulting in the fragmentation. I'll add in some
> overhead, try to stick with fixed sizes, and set the
> autogrow to a large chunk.
> We are adding a second array. 400 GB total that I'm
> formatting as 2 200 GB disks. The server has six majort
> databases all are over 5 GB and 3 are over 15 GB. The
> current disks are 4k clusters. Larger clusters make
> sense. For DBs of this size would'nt it make sense to go
> ahead and do 64k?
slowing down. The 100 GB disk with the database files is
on an array. Using the included 2000 defrag tool I did an
analyze and found the disk is highly fragmented. It's
been running for about 2 years and never been defraged.
Is it necessary or even safe to defrag the disk with the
databases? I would assume you would need to stop the SQL
service to do it. Would it even do any good? I'd almost
think that since there is a small number of files that
after turning it back up it wouldn't take long before
everthing is fragmented again, but maybe not to the extent
that it is now.
Sam,
Stop the SQL Server service and defrag the disk. The primary cause of
file fragmentation is growing and shrinking of the SQL Server data
files. If using autogrow, then grow in large chunks, so that this
operation only occurs every few months or so, or even better longer
periods than that. Turn off autoshrink.
After you have defragged the data files, perform a DBCC DBREINDEX to
locically defrag your indexes. If you have a clustered index on all your
tables, this process will be more thorough.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
|||I agree 100% with Mark. Just want to add that you should make sure you have
valid backups before attempting the defrag. And I also want to emphasize to
stop the shrinking and growing.
Andrew J. Kelly SQL MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23Tr%23AB%23cEHA.2752@.TK2MSFTNGP12.phx.gbl.. .[vbcol=seagreen]
> Sam,
> Stop the SQL Server service and defrag the disk. The primary cause of
> file fragmentation is growing and shrinking of the SQL Server data
> files. If using autogrow, then grow in large chunks, so that this
> operation only occurs every few months or so, or even better longer
> periods than that. Turn off autoshrink.
> After you have defragged the data files, perform a DBCC DBREINDEX to
> locically defrag your indexes. If you have a clustered index on all your
> tables, this process will be more thorough.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Sam Winston wrote:
|||Hi,
If you create your db large enough (considering growth of the db.) then disk defrag. won't effect your SQL server performance.
Running defrag tools on SQL partitions doesn't sound good to me ..
"Sam Winston" wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
>
|||Hi
As an aside.
The fact that Defragger can even look at your array volumes means that your
disk is formatted with block sizes of 4kb or smaller.
Have a serious look at formatting your drives with larger block sizes, and
if possible, the same size as your RAID block sizes.
SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
improve your performance (8 or 64kb)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sam Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:4f3601c473dc$d7b2b8b0$a501280a@.phx.gbl...
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
|||Mike,
I agree, 64k seems to be better for "standard" disks.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Mike Epprecht (SQL MVP) wrote:
> Hi
> As an aside.
> The fact that Defragger can even look at your array volumes means that your
> disk is formatted with block sizes of 4kb or smaller.
> Have a serious look at formatting your drives with larger block sizes, and
> if possible, the same size as your RAID block sizes.
> SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
> improve your performance (8 or 64kb)
> Regards
|||I have been shrinking and growing alot. It hit me that's
what's resulting in the fragmentation. I'll add in some
overhead, try to stick with fixed sizes, and set the
autogrow to a large chunk.
We are adding a second array. 400 GB total that I'm
formatting as 2 200 GB disks. The server has six majort
databases all are over 5 GB and 3 are over 15 GB. The
current disks are 4k clusters. Larger clusters make
sense. For DBs of this size would'nt it make sense to go
ahead and do 64k?
|||Sam,
Yep, 64k clusters would be good.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I have been shrinking and growing alot. It hit me that's
> what's resulting in the fragmentation. I'll add in some
> overhead, try to stick with fixed sizes, and set the
> autogrow to a large chunk.
> We are adding a second array. 400 GB total that I'm
> formatting as 2 200 GB disks. The server has six majort
> databases all are over 5 GB and 3 are over 15 GB. The
> current disks are 4k clusters. Larger clusters make
> sense. For DBs of this size would'nt it make sense to go
> ahead and do 64k?
Defragging SQL Server Data Disk?
I've noticed disk performance on our SQL 2000 cluster
slowing down. The 100 GB disk with the database files is
on an array. Using the included 2000 defrag tool I did an
analyze and found the disk is highly fragmented. It's
been running for about 2 years and never been defraged.
Is it necessary or even safe to defrag the disk with the
databases? I would assume you would need to stop the SQL
service to do it. Would it even do any good? I'd almost
think that since there is a small number of files that
after turning it back up it wouldn't take long before
everthing is fragmented again, but maybe not to the extent
that it is now.Sam,
Stop the SQL Server service and defrag the disk. The primary cause of
file fragmentation is growing and shrinking of the SQL Server data
files. If using autogrow, then grow in large chunks, so that this
operation only occurs every few months or so, or even better longer
periods than that. Turn off autoshrink.
After you have defragged the data files, perform a DBCC DBREINDEX to
locically defrag your indexes. If you have a clustered index on all your
tables, this process will be more thorough.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.|||I agree 100% with Mark. Just want to add that you should make sure you have
valid backups before attempting the defrag. And I also want to emphasize to
stop the shrinking and growing.
Andrew J. Kelly SQL MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23Tr%23AB%23cEHA.2752@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Sam,
> Stop the SQL Server service and defrag the disk. The primary cause of
> file fragmentation is growing and shrinking of the SQL Server data
> files. If using autogrow, then grow in large chunks, so that this
> operation only occurs every few months or so, or even better longer
> periods than that. Turn off autoshrink.
> After you have defragged the data files, perform a DBCC DBREINDEX to
> locically defrag your indexes. If you have a clustered index on all your
> tables, this process will be more thorough.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Sam Winston wrote:|||Hi,
If you create your db large enough (considering growth of the db.) then disk
defrag. won't effect your SQL server performance.
Running defrag tools on SQL partitions doesn't sound good to me ..
"Sam Winston" wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
>|||Hi
As an aside.
The fact that Defragger can even look at your array volumes means that your
disk is formatted with block sizes of 4kb or smaller.
Have a serious look at formatting your drives with larger block sizes, and
if possible, the same size as your RAID block sizes.
SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
improve your performance (8 or 64kb)
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sam Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:4f3601c473dc$d7b2b8b0$a501280a@.phx.gbl...
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.|||Mike,
I agree, 64k seems to be better for "standard" disks.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Mike Epprecht (SQL MVP) wrote:
> Hi
> As an aside.
> The fact that Defragger can even look at your array volumes means that you
r
> disk is formatted with block sizes of 4kb or smaller.
> Have a serious look at formatting your drives with larger block sizes, and
> if possible, the same size as your RAID block sizes.
> SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size m
ay
> improve your performance (8 or 64kb)
> Regards|||I have been shrinking and growing alot. It hit me that's
what's resulting in the fragmentation. I'll add in some
overhead, try to stick with fixed sizes, and set the
autogrow to a large chunk.
We are adding a second array. 400 GB total that I'm
formatting as 2 200 GB disks. The server has six majort
databases all are over 5 GB and 3 are over 15 GB. The
current disks are 4k clusters. Larger clusters make
sense. For DBs of this size would'nt it make sense to go
ahead and do 64k?|||Sam,
Yep, 64k clusters would be good.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I have been shrinking and growing alot. It hit me that's
> what's resulting in the fragmentation. I'll add in some
> overhead, try to stick with fixed sizes, and set the
> autogrow to a large chunk.
> We are adding a second array. 400 GB total that I'm
> formatting as 2 200 GB disks. The server has six majort
> databases all are over 5 GB and 3 are over 15 GB. The
> current disks are 4k clusters. Larger clusters make
> sense. For DBs of this size would'nt it make sense to go
> ahead and do 64k?
slowing down. The 100 GB disk with the database files is
on an array. Using the included 2000 defrag tool I did an
analyze and found the disk is highly fragmented. It's
been running for about 2 years and never been defraged.
Is it necessary or even safe to defrag the disk with the
databases? I would assume you would need to stop the SQL
service to do it. Would it even do any good? I'd almost
think that since there is a small number of files that
after turning it back up it wouldn't take long before
everthing is fragmented again, but maybe not to the extent
that it is now.Sam,
Stop the SQL Server service and defrag the disk. The primary cause of
file fragmentation is growing and shrinking of the SQL Server data
files. If using autogrow, then grow in large chunks, so that this
operation only occurs every few months or so, or even better longer
periods than that. Turn off autoshrink.
After you have defragged the data files, perform a DBCC DBREINDEX to
locically defrag your indexes. If you have a clustered index on all your
tables, this process will be more thorough.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.|||I agree 100% with Mark. Just want to add that you should make sure you have
valid backups before attempting the defrag. And I also want to emphasize to
stop the shrinking and growing.
Andrew J. Kelly SQL MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23Tr%23AB%23cEHA.2752@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Sam,
> Stop the SQL Server service and defrag the disk. The primary cause of
> file fragmentation is growing and shrinking of the SQL Server data
> files. If using autogrow, then grow in large chunks, so that this
> operation only occurs every few months or so, or even better longer
> periods than that. Turn off autoshrink.
> After you have defragged the data files, perform a DBCC DBREINDEX to
> locically defrag your indexes. If you have a clustered index on all your
> tables, this process will be more thorough.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Sam Winston wrote:|||Hi,
If you create your db large enough (considering growth of the db.) then disk
defrag. won't effect your SQL server performance.
Running defrag tools on SQL partitions doesn't sound good to me ..
"Sam Winston" wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
>|||Hi
As an aside.
The fact that Defragger can even look at your array volumes means that your
disk is formatted with block sizes of 4kb or smaller.
Have a serious look at formatting your drives with larger block sizes, and
if possible, the same size as your RAID block sizes.
SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
improve your performance (8 or 64kb)
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sam Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:4f3601c473dc$d7b2b8b0$a501280a@.phx.gbl...
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.|||Mike,
I agree, 64k seems to be better for "standard" disks.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Mike Epprecht (SQL MVP) wrote:
> Hi
> As an aside.
> The fact that Defragger can even look at your array volumes means that you
r
> disk is formatted with block sizes of 4kb or smaller.
> Have a serious look at formatting your drives with larger block sizes, and
> if possible, the same size as your RAID block sizes.
> SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size m
ay
> improve your performance (8 or 64kb)
> Regards|||I have been shrinking and growing alot. It hit me that's
what's resulting in the fragmentation. I'll add in some
overhead, try to stick with fixed sizes, and set the
autogrow to a large chunk.
We are adding a second array. 400 GB total that I'm
formatting as 2 200 GB disks. The server has six majort
databases all are over 5 GB and 3 are over 15 GB. The
current disks are 4k clusters. Larger clusters make
sense. For DBs of this size would'nt it make sense to go
ahead and do 64k?|||Sam,
Yep, 64k clusters would be good.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I have been shrinking and growing alot. It hit me that's
> what's resulting in the fragmentation. I'll add in some
> overhead, try to stick with fixed sizes, and set the
> autogrow to a large chunk.
> We are adding a second array. 400 GB total that I'm
> formatting as 2 200 GB disks. The server has six majort
> databases all are over 5 GB and 3 are over 15 GB. The
> current disks are 4k clusters. Larger clusters make
> sense. For DBs of this size would'nt it make sense to go
> ahead and do 64k?
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
defrag/reindex
am not a SQL guru. I
need an easy and
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
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:Thanks, I should have been more specific. Diskkeeper takes care of the
>effective way to defrag/reindex the SQL databases.
>Thanks,
>Derek
>
>.
>
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 on SAN disk ?
I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
there lots of fragmentation, BUT, they are all on SAN disk, so my question
is, will there be any value in running the defrag ?
I do plan to run it when SQL is not running, that sounds like a good idea.
Jim,
Interesting question. Who is the SAN vendor? SANs store data differently
to "normal" file systems. Blocks do not get overwritten (usually), but a
new block gets written when data changes. So, I'm not entirely sure what
would happen if you ran a disk defrag tool on a SAN volume.
I would first of all make sure that you don't have any SQL Server
fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
talking to your infrastructure team about this, failing that speak to
the SAN vendor.
I don't think I would want to defrag a SAN, but I'm not 100% sure.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jim Trowbridge wrote:
> I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
> there lots of fragmentation, BUT, they are all on SAN disk, so my question
> is, will there be any value in running the defrag ?
> I do plan to run it when SQL is not running, that sounds like a good idea.
>
|||I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
on our CX series.
Mark Allison wrote:[vbcol=seagreen]
> Jim,
> Interesting question. Who is the SAN vendor? SANs store data
> differently to "normal" file systems. Blocks do not get overwritten
> (usually), but a new block gets written when data changes. So, I'm
> not entirely sure what would happen if you ran a disk defrag tool on
> a SAN volume.
> I would first of all make sure that you don't have any SQL Server
> fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> talking to your infrastructure team about this, failing that speak to
> the SAN vendor.
> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>
> Jim Trowbridge wrote:
|||Tell your engineer he is full of sxxx<g>. While a large amount of cache may
abstract some aspects of data being read and written to disk there is always
the fact fragmentation can lead to pages that are not as full as you would
like. If the page is half empty on disk it will be half empty when read
into the sql server data cache as well. This means you can only have half
the amount of data or indexes in cache at any one time. It also means lots
more I/O's (even if they are logical) and that means lots more cpu ect.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
> on our CX series.
>
> Mark Allison wrote:
a
>
|||I was answering the file-system fragmentation question, not the data and
index fragmentation question. I did not mean to imply one shouldn't handle
the database fragmentation if the data stores are on a SAN. OK, that being
said, I shot an email to our Engineer and asked again about running a
windows defragmentation on our SAN and he said absolutely keep it defragged
with hard disk defragmentation tool, so regardless of _what_ I was talking
about, I was still wrong. :-O
Thanks Andrew. It's probably beer-thirty for me anyway.
Eric
Andrew J. Kelly wrote:[vbcol=seagreen]
> Tell your engineer he is full of sxxx<g>. While a large amount of
> cache may abstract some aspects of data being read and written to
> disk there is always the fact fragmentation can lead to pages that
> are not as full as you would like. If the page is half empty on disk
> it will be half empty when read into the sql server data cache as
> well. This means you can only have half the amount of data or
> indexes in cache at any one time. It also means lots more I/O's
> (even if they are logical) and that means lots more cpu ect.
>
> "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
>
> Andrew J. Kelly wrote:
>
|||"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
>
Not necessarily.
Windows defrag may do nothing on the SAN. Oh, the SAN will report it done,
etc, but it may virtualize away the actions and no real difference will
happen.
Again, it depends a lot on the SAN.
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
there lots of fragmentation, BUT, they are all on SAN disk, so my question
is, will there be any value in running the defrag ?
I do plan to run it when SQL is not running, that sounds like a good idea.
Jim,
Interesting question. Who is the SAN vendor? SANs store data differently
to "normal" file systems. Blocks do not get overwritten (usually), but a
new block gets written when data changes. So, I'm not entirely sure what
would happen if you ran a disk defrag tool on a SAN volume.
I would first of all make sure that you don't have any SQL Server
fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
talking to your infrastructure team about this, failing that speak to
the SAN vendor.
I don't think I would want to defrag a SAN, but I'm not 100% sure.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jim Trowbridge wrote:
> I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
> there lots of fragmentation, BUT, they are all on SAN disk, so my question
> is, will there be any value in running the defrag ?
> I do plan to run it when SQL is not running, that sounds like a good idea.
>
|||I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
on our CX series.
Mark Allison wrote:[vbcol=seagreen]
> Jim,
> Interesting question. Who is the SAN vendor? SANs store data
> differently to "normal" file systems. Blocks do not get overwritten
> (usually), but a new block gets written when data changes. So, I'm
> not entirely sure what would happen if you ran a disk defrag tool on
> a SAN volume.
> I would first of all make sure that you don't have any SQL Server
> fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> talking to your infrastructure team about this, failing that speak to
> the SAN vendor.
> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>
> Jim Trowbridge wrote:
|||Tell your engineer he is full of sxxx<g>. While a large amount of cache may
abstract some aspects of data being read and written to disk there is always
the fact fragmentation can lead to pages that are not as full as you would
like. If the page is half empty on disk it will be half empty when read
into the sql server data cache as well. This means you can only have half
the amount of data or indexes in cache at any one time. It also means lots
more I/O's (even if they are logical) and that means lots more cpu ect.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
> on our CX series.
>
> Mark Allison wrote:
a
>
|||I was answering the file-system fragmentation question, not the data and
index fragmentation question. I did not mean to imply one shouldn't handle
the database fragmentation if the data stores are on a SAN. OK, that being
said, I shot an email to our Engineer and asked again about running a
windows defragmentation on our SAN and he said absolutely keep it defragged
with hard disk defragmentation tool, so regardless of _what_ I was talking
about, I was still wrong. :-O
Thanks Andrew. It's probably beer-thirty for me anyway.
Eric
Andrew J. Kelly wrote:[vbcol=seagreen]
> Tell your engineer he is full of sxxx<g>. While a large amount of
> cache may abstract some aspects of data being read and written to
> disk there is always the fact fragmentation can lead to pages that
> are not as full as you would like. If the page is half empty on disk
> it will be half empty when read into the sql server data cache as
> well. This means you can only have half the amount of data or
> indexes in cache at any one time. It also means lots more I/O's
> (even if they are logical) and that means lots more cpu ect.
>
> "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
>
> Andrew J. Kelly wrote:
>
|||"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
>
Not necessarily.
Windows defrag may do nothing on the SAN. Oh, the SAN will report it done,
etc, but it may virtualize away the actions and no real difference will
happen.
Again, it depends a lot on the SAN.
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
Defrag on SAN disk ?
I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
there lots of fragmentation, BUT, they are all on SAN disk, so my question
is, will there be any value in running the defrag ?
I do plan to run it when SQL is not running, that sounds like a good idea.Jim,
Interesting question. Who is the SAN vendor? SANs store data differently
to "normal" file systems. Blocks do not get overwritten (usually), but a
new block gets written when data changes. So, I'm not entirely sure what
would happen if you ran a disk defrag tool on a SAN volume.
I would first of all make sure that you don't have any SQL Server
fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
talking to your infrastructure team about this, failing that speak to
the SAN vendor.
I don't think I would want to defrag a SAN, but I'm not 100% sure.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jim Trowbridge wrote:
> I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
> there lots of fragmentation, BUT, they are all on SAN disk, so my question
> is, will there be any value in running the defrag ?
> I do plan to run it when SQL is not running, that sounds like a good idea.
>|||I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
on our CX series.
Mark Allison wrote:
> Jim,
> Interesting question. Who is the SAN vendor? SANs store data
> differently to "normal" file systems. Blocks do not get overwritten
> (usually), but a new block gets written when data changes. So, I'm
> not entirely sure what would happen if you ran a disk defrag tool on
> a SAN volume.
> I would first of all make sure that you don't have any SQL Server
> fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> talking to your infrastructure team about this, failing that speak to
> the SAN vendor.
> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>
> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds like a
>> good
>> idea.|||Tell your engineer he is full of sxxx<g>. While a large amount of cache may
abstract some aspects of data being read and written to disk there is always
the fact fragmentation can lead to pages that are not as full as you would
like. If the page is half empty on disk it will be half empty when read
into the sql server data cache as well. This means you can only have half
the amount of data or indexes in cache at any one time. It also means lots
more I/O's (even if they are logical) and that means lots more cpu ect.
--
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
> on our CX series.
>
> Mark Allison wrote:
> > Jim,
> >
> > Interesting question. Who is the SAN vendor? SANs store data
> > differently to "normal" file systems. Blocks do not get overwritten
> > (usually), but a new block gets written when data changes. So, I'm
> > not entirely sure what would happen if you ran a disk defrag tool on
> > a SAN volume.
> > I would first of all make sure that you don't have any SQL Server
> > fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> > talking to your infrastructure team about this, failing that speak to
> > the SAN vendor.
> >
> > I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >
> >
> > Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds like
a
> >> good
> >> idea.
>|||I was answering the file-system fragmentation question, not the data and
index fragmentation question. I did not mean to imply one shouldn't handle
the database fragmentation if the data stores are on a SAN. OK, that being
said, I shot an email to our Engineer and asked again about running a
windows defragmentation on our SAN and he said absolutely keep it defragged
with hard disk defragmentation tool, so regardless of _what_ I was talking
about, I was still wrong. :-O
Thanks Andrew. It's probably beer-thirty for me anyway.
Eric
Andrew J. Kelly wrote:
> Tell your engineer he is full of sxxx<g>. While a large amount of
> cache may abstract some aspects of data being read and written to
> disk there is always the fact fragmentation can lead to pages that
> are not as full as you would like. If the page is half empty on disk
> it will be half empty when read into the sql server data cache as
> well. This means you can only have half the amount of data or
> indexes in cache at any one time. It also means lots more I/O's
> (even if they are logical) and that means lots more cpu ect.
>
> "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
>> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
>> necessary on our CX series.
>>
>> Mark Allison wrote:
>> Jim,
>> Interesting question. Who is the SAN vendor? SANs store data
>> differently to "normal" file systems. Blocks do not get overwritten
>> (usually), but a new block gets written when data changes. So, I'm
>> not entirely sure what would happen if you ran a disk defrag tool on
>> a SAN volume.
>> I would first of all make sure that you don't have any SQL Server
>> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
>> consider talking to your infrastructure team about this, failing
>> that speak to the SAN vendor.
>> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>>
>> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds
>> like a good
>> idea.|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
>
> Andrew J. Kelly wrote:
> > Tell your engineer he is full of sxxx<g>. While a large amount of
> > cache may abstract some aspects of data being read and written to
> > disk there is always the fact fragmentation can lead to pages that
> > are not as full as you would like. If the page is half empty on disk
> > it will be half empty when read into the sql server data cache as
> > well. This means you can only have half the amount of data or
> > indexes in cache at any one time. It also means lots more I/O's
> > (even if they are logical) and that means lots more cpu ect.
> >
> >
> > "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> > news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> >> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
> >> necessary on our CX series.
> >>
> >>
> >> Mark Allison wrote:
> >> Jim,
> >>
> >> Interesting question. Who is the SAN vendor? SANs store data
> >> differently to "normal" file systems. Blocks do not get overwritten
> >> (usually), but a new block gets written when data changes. So, I'm
> >> not entirely sure what would happen if you ran a disk defrag tool on
> >> a SAN volume.
> >> I would first of all make sure that you don't have any SQL Server
> >> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
> >> consider talking to your infrastructure team about this, failing
> >> that speak to the SAN vendor.
> >>
> >> I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >>
> >>
> >> Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds
> >> like a good
> >> idea.
>|||"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
>
Not necessarily.
Windows defrag may do nothing on the SAN. Oh, the SAN will report it done,
etc, but it may virtualize away the actions and no real difference will
happen.
Again, it depends a lot on the SAN.
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
there lots of fragmentation, BUT, they are all on SAN disk, so my question
is, will there be any value in running the defrag ?
I do plan to run it when SQL is not running, that sounds like a good idea.Jim,
Interesting question. Who is the SAN vendor? SANs store data differently
to "normal" file systems. Blocks do not get overwritten (usually), but a
new block gets written when data changes. So, I'm not entirely sure what
would happen if you ran a disk defrag tool on a SAN volume.
I would first of all make sure that you don't have any SQL Server
fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
talking to your infrastructure team about this, failing that speak to
the SAN vendor.
I don't think I would want to defrag a SAN, but I'm not 100% sure.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jim Trowbridge wrote:
> I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
> there lots of fragmentation, BUT, they are all on SAN disk, so my question
> is, will there be any value in running the defrag ?
> I do plan to run it when SQL is not running, that sounds like a good idea.
>|||I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
on our CX series.
Mark Allison wrote:
> Jim,
> Interesting question. Who is the SAN vendor? SANs store data
> differently to "normal" file systems. Blocks do not get overwritten
> (usually), but a new block gets written when data changes. So, I'm
> not entirely sure what would happen if you ran a disk defrag tool on
> a SAN volume.
> I would first of all make sure that you don't have any SQL Server
> fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> talking to your infrastructure team about this, failing that speak to
> the SAN vendor.
> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>
> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds like a
>> good
>> idea.|||Tell your engineer he is full of sxxx<g>. While a large amount of cache may
abstract some aspects of data being read and written to disk there is always
the fact fragmentation can lead to pages that are not as full as you would
like. If the page is half empty on disk it will be half empty when read
into the sql server data cache as well. This means you can only have half
the amount of data or indexes in cache at any one time. It also means lots
more I/O's (even if they are logical) and that means lots more cpu ect.
--
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
> on our CX series.
>
> Mark Allison wrote:
> > Jim,
> >
> > Interesting question. Who is the SAN vendor? SANs store data
> > differently to "normal" file systems. Blocks do not get overwritten
> > (usually), but a new block gets written when data changes. So, I'm
> > not entirely sure what would happen if you ran a disk defrag tool on
> > a SAN volume.
> > I would first of all make sure that you don't have any SQL Server
> > fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> > talking to your infrastructure team about this, failing that speak to
> > the SAN vendor.
> >
> > I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >
> >
> > Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds like
a
> >> good
> >> idea.
>|||I was answering the file-system fragmentation question, not the data and
index fragmentation question. I did not mean to imply one shouldn't handle
the database fragmentation if the data stores are on a SAN. OK, that being
said, I shot an email to our Engineer and asked again about running a
windows defragmentation on our SAN and he said absolutely keep it defragged
with hard disk defragmentation tool, so regardless of _what_ I was talking
about, I was still wrong. :-O
Thanks Andrew. It's probably beer-thirty for me anyway.
Eric
Andrew J. Kelly wrote:
> Tell your engineer he is full of sxxx<g>. While a large amount of
> cache may abstract some aspects of data being read and written to
> disk there is always the fact fragmentation can lead to pages that
> are not as full as you would like. If the page is half empty on disk
> it will be half empty when read into the sql server data cache as
> well. This means you can only have half the amount of data or
> indexes in cache at any one time. It also means lots more I/O's
> (even if they are logical) and that means lots more cpu ect.
>
> "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
>> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
>> necessary on our CX series.
>>
>> Mark Allison wrote:
>> Jim,
>> Interesting question. Who is the SAN vendor? SANs store data
>> differently to "normal" file systems. Blocks do not get overwritten
>> (usually), but a new block gets written when data changes. So, I'm
>> not entirely sure what would happen if you ran a disk defrag tool on
>> a SAN volume.
>> I would first of all make sure that you don't have any SQL Server
>> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
>> consider talking to your infrastructure team about this, failing
>> that speak to the SAN vendor.
>> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>>
>> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds
>> like a good
>> idea.|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
>
> Andrew J. Kelly wrote:
> > Tell your engineer he is full of sxxx<g>. While a large amount of
> > cache may abstract some aspects of data being read and written to
> > disk there is always the fact fragmentation can lead to pages that
> > are not as full as you would like. If the page is half empty on disk
> > it will be half empty when read into the sql server data cache as
> > well. This means you can only have half the amount of data or
> > indexes in cache at any one time. It also means lots more I/O's
> > (even if they are logical) and that means lots more cpu ect.
> >
> >
> > "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> > news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> >> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
> >> necessary on our CX series.
> >>
> >>
> >> Mark Allison wrote:
> >> Jim,
> >>
> >> Interesting question. Who is the SAN vendor? SANs store data
> >> differently to "normal" file systems. Blocks do not get overwritten
> >> (usually), but a new block gets written when data changes. So, I'm
> >> not entirely sure what would happen if you ran a disk defrag tool on
> >> a SAN volume.
> >> I would first of all make sure that you don't have any SQL Server
> >> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
> >> consider talking to your infrastructure team about this, failing
> >> that speak to the SAN vendor.
> >>
> >> I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >>
> >>
> >> Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds
> >> like a good
> >> idea.
>|||"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
>
Not necessarily.
Windows defrag may do nothing on the SAN. Oh, the SAN will report it done,
etc, but it may virtualize away the actions and no real difference will
happen.
Again, it depends a lot on the SAN.
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
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
Defrag data disk
Hi,
The disks on which my databases are stored is heavily
fragmented. This because of wrong database grow settings.
Can I use the Windows system tool: Disk Defragmenter to
defragment my disk or will SQL not like this?
Thanks,
Jeroen.I believe you can, so long as the block size <= 4KB.
You will need to stop sql server if you want the data files defragemented
"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
> Thanks,
> Jeroen.|||"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
>
Also your disks may appear more fragmented than they really are.
If you have a few large files that are in 2 fragments, you disk can report
as being 90% fragmented without this being a big deal.
David
The disks on which my databases are stored is heavily
fragmented. This because of wrong database grow settings.
Can I use the Windows system tool: Disk Defragmenter to
defragment my disk or will SQL not like this?
Thanks,
Jeroen.I believe you can, so long as the block size <= 4KB.
You will need to stop sql server if you want the data files defragemented
"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
> Thanks,
> Jeroen.|||"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
>
Also your disks may appear more fragmented than they really are.
If you have a few large files that are in 2 fragments, you disk can report
as being 90% fragmented without this being a big deal.
David
Subscribe to:
Posts (Atom)