Why can't I defragment my SQL 2008 .mdf file?

Posted by LesterDove on Server Fault See other posts from Server Fault or by LesterDove
Published on 2012-05-25T03:21:34Z Indexed on 2012/06/04 16:45 UTC
Read the original article Hit count: 234

Filed under:

sql-server

|

defrag

I am defragmenting a badly (95%) fragmented drive upon which large (35 gig) SQL Server 2008 .mdf files live. After defragmenting and viewing the exception report, I see that the production .mdf file that I'm most interested in could not be defragmented. I initially figured it was because MSSQL had an exclusive lock on the file, so I detached it and tried again. No luck - this particular .mdf file could not be defragmented. What am I missing? Most online references suggest that I should be able to file defrag an .mdf

A note: yes, I'm talking about file defragmentation, not index defrag, which is already being done routinely, and which I'll re-run after this.

Thanks! What am I missing?

© Server Fault or respective owner

Related posts about sql-server

SQL SERVER – Beginning SQL Server: One Step at a Time – SQL Server Magazine

as seen on SQL Authority - Search for 'SQL Authority'
I am glad to announce that along with SQLAuthority.com, I will be blogging on the prominent site of SQL Server Magazine. My very first blog post there is already live; read here: Beginning SQL Server: One Step at a Time. My association with SQL Server Magazine has been quite long, I have written nearly… >>> More
How to install SQL Server 2005 Configuration Manager without installing SQL Server Management Studio

as seen on Server Fault - Search for 'Server Fault'
Hi, I need to configure SQL Server aliases on a public-facing production server. To do that, I need to install SQL Server Configuration Manager. I was not able to find a standalone installer for that, so I am having to install SQL Server 2005 Client Components. This approach is not ideal as we don't… >>> More
[MAJ] SQL Server 2005 Express Edition - La version gratuite de SQL Server 2005

as seen on ASP-PHP.net - Search for 'ASP-PHP.net'
Modification technique pour le site de l'article. >>> More
How to create SQL Server Express DB from SQL Server DB

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a SQL Server 2008 DB. I want to extract SOME tables (and associated schema, constraints, indexes, etc) and create a SQL Server Express DB. It isn't a sync of the target, we stomp on it. We ONLY need to do this in the file system (not across the wire). We are not fond of the synchronization… >>> More
sql server 2000 error, error trying to connect to sql server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
i am connecting to sql server 2000 on a remote computer with a dotnet application, but when i try to open the connection it gives the following error: When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections What… >>> More

Related posts about defrag

Defrag starting when not scheduled. What is triggering the defrag

as seen on Server Fault - Search for 'Server Fault'
I have a fileserver that is starting a defrag around 2:00 PM everyday. This is killing performance as it runs for ours becuase this is a file server and has multiple drives. All scheduled tasks regarding defrag have been disabled. I have verified that it is accessing the data drives(using SysInternals… >>> More
what damage can be caused if scandisk is not run before defrag

as seen on Super User - Search for 'Super User'
Hello, I would like to know what the damage to a drive can be if a scandisk is not performed before a disk defrag. I have looked up some sites that say a scandisk should be run to correct any issues that may be apparent on the system, making sure that the drive is free of errors before a disk defrag… >>> More
Dig Deeper in Windows Defrag via Command Prompt

as seen on How to geek - Search for 'How to geek'
Windows users have learned over the years that they need to keep their computers defragmented to keep running at top speed. While Windows Vista and 7 automatically defrag your disks, here’s some ways you can dig deeper into Windows Defragmenter Latest Features How-To Geek ETC The… >>> More
Azure Blob storage defrag

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
The Blob Storage is really handy for storing temporary data structures during a scaled-out distributed processing. Yet, the lifespan of those data structures should not exceed the one of the underlying operation, otherwise clutter and dead data could potentially start filling up your Blob Storage… >>> More
How does ecryptfs impact harddisk performance?

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I have my home directy encrypted with ecryptfs. Does ecryptfs lead to fragmentation? I have the feeling that reading files, displaying folders and login became continuously slower and slower (although it was not noticeably slow at the beginning). The hard disk makes a lot of seek noise even if I… >>> More