| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2006-12-16 : 06:33:52
|
| Hi thereI just want to feedback/comment about implementing the defrag at OS level. We've done the index defrag and stuff and now trying to move on to OS level.Need to know pro and cons or any info along with thirdparty tool such as DiskKeeper or any others?I am appreciated your comment.Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-16 : 06:39:49
|
| The files should not grow much, and will be static, so defragging them once-in-a-while is sufficient.I use CONTIG from Sysinternals (which allows single-file defragging)The database needs to be offline at the time (or SQL Service stopped), and as such we do this whenever we have scheduled downtime on a server.Kristen |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-12-16 : 06:43:57
|
| Hi KristenWhy need to be offline? Is it because Sysinternals CONTIG won't access the open file?Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-16 : 07:54:38
|
| Well, now you say that, I've assumed that. Maybe it isn't the case - or maybe ALL defragging systems require the files to be closed. Dunno the answer to that, but I expect someone will.I have always taken the view that SQL Server will have parts of the file in memory - and maybe including the physical location on the disk that the disk block came from, and therefore defragging the file might cause mayhem, so I have only ever done it with the file "closed".Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-12-16 : 09:06:19
|
sql server on my local dev machine runs every time i run defrag.so that's probably not a problem for a defrag program. sql esrver probably keeps the files locked anyway so the defrag can't rearange them.i can't say that for sure of course but i'm assuming it since my db defragmentation hasn't increased or decreased after disk defragmentation.Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2006-12-16 : 10:21:44
|
| Just another questions to add here.Do we still need to defrag if the data and log files are on SAN box?thanksSachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-12-16 : 12:14:07
|
| you can't defrag a file while it is open. You can run the report feature in contig to see how many fragments a given file has while the file is open though.also, you will only really need to defrag a datafile if you have done many shrink operations on it in the past. |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-12-16 : 13:20:04
|
| Ah that's another interesting question from sachinsamuel: "Do we still need to defrag if the data and log files are on SAN box?"I guess it's still ... but anyone? |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-12-18 : 03:59:14
|
| According to MS [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/url] (see section on Physical Disk fragmentation) you don't need to correct disk fragmentation on a SANsteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-18 : 04:15:47
|
| "also, you will only really need to defrag a datafile if you have done many shrink operations on it in the past."... or if it has grown bit-by-bit.A straight restore from backup should create a contiguous file, or a file in as few fragments as possibleEither way we use CONTIG once-in-a-while and, given that our files are growing steadily, with time, it usually finds something to do!Kristen |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2006-12-18 : 08:58:00
|
If you use diskeeeper, it defrags the files while they are in use . . . .I have done it and was very surprised that I didn't kill the server. Like Kristen, I've set the TLogs at an appropriate size so that they don't grow. Then take the DB offline and Defrag the disks. for database defragging again don't let the DB's autogrow (especially using the Defaults of 10%!). Monitor the amount of Space and Grow the DB's manually as they approach the limit (I use within 20% of the available space as my threshold, but then I know, now, the growth patterns for the various systems).File defragging is NOT required on SAN based storage (doesn't stop you doing it though  ) although it could be a requirement depending on how many disks you have in the diskgroup, and how many files are in the filegroup-- RegardsTony The DBA |
|
|
|
|
File Defrag at OS Level