.mdf fragmentation due to autogrowth

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 08 Jul 2008 12:38 GMT

SQL Server 2005:

Our database is set to autogrow which has resulted in fragmentation of the
.mdf file across the disk. If we restore the database from a .bak file to
the identical location over the existing database, would this maintain the
old fragmentation, or result in far less fragmentation assuming the phyiscal
disk has the capacity to provide reduced fragmentation?

Thanks in advance,
Mark

Reply to this Message

Tibor Karaszi - 08 Jul 2008 12:48 GMT

You can either stop your SQL Server and do a traditional disk defrag. I assume you are tight on disk
and have discarded that option, though...

Yes, RESTORE DATABASE can be used to make SQL Server allocate new fresh storage from windows. The
key here is that you need to use the REPLACE option of the restore command. Or drop the old database
and do the restore. What you want to avoid is for SQL Server to just read in the pages into the
existing database files. You want SQL Server to ask Windows for new storage for these files.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> SQL Server 2005:
>
[quoted text clipped - 5 lines]
> Thanks in advance,
> Mark

Reply to this Message

Linchi Shea - 08 Jul 2008 13:04 GMT

Best practices aside, a question I'd ask is, does the filesystem
fragmentation really cause any significant performance degradation? If it
doesn't, you could postpone the defrag and piggyback on some other planned
maintenance event, and therefore minimize any availability impact (if it is a
concern).

Linchi

> SQL Server 2005:
>
[quoted text clipped - 6 lines]
> Thanks in advance,
> Mark

Reply to this Message