Defragment Database Files

[RFEULINGatRITE]

Hello,

I'm trying to create a batch file to defrag a hard drive that contains SQL server 2000 database files. My question is, the windows defragmenter is giving me issues with defraging the database files - even though sql server is stopped.

What do I need to do to handle this? I also noticed that there are some sql commands to defragment the database - it appears that this is a different kind of defragment - how is this typically done? Why isnt this functionality apart of the database maintenance tools inside sql enterprise manager?

Thanks for any help!

 

[mrdenny]

There are DBCC commands to defrag the indexes within the database.

Once the SQL Server and the agent are stopped the OS should be able to defrag the files. You can try setting the SQL Server to not start automatically then reboot the server and defrag the drive. After it's defragged start the SQL Server and the agent and set the services to auto restart.

One of the reasons that there is no builtin way to defrag the databases is that when a SQL Server is setup correctly there isn't any need to defrag the drive. The database files should be on there own drive array and shouldn't be shrunk. This way the files are always contiguous.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[DrSql]

I ran into similar issues on bigger db's. The way I approched was
1.)Ran DBBC commands multiple times
2.)DBCC shrink Data & Log files
3.)Then ran windows defrag multiple times. (We ended you some third party tools to defrag. Windows defrag is not that great.)
Ps. make sure you have a good back prior ro this process. After that try again.


Dr.Sql
Good Luck.