August 26, 2008 at 1:06 pm
Disks on our DEV server are highly fragmented (lots of Autogrows and Shrinks, I know that)
IBM is supporting our DEV server.
We requested to DEFRAG all hard disks during weekend.
IBM replied that DEFRAG failed on some disks and sent us a log:
plog 4069748
ises/bl H: drive successfully defraged
G: drive 21,953 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD\BAP_DAT
.mdf taking up all the space cannot defrag
F: drive defrage will not work on huge mdf and log
files, taking up to much space
Volume Local Disk (F:):
Volume size = 169 GB
Cluster size = 4 KB
Used space = 145 GB
Free space = 24,436 MB
Percent free space = 14 %
Volume fragmentation
Total fragmentation = 45 %
File fragmentation = 90 %
Free space fragmentation = 0 %
File fragmentation
Total files = 34,945
Average file size = 4,635 KB
Total fragmented files = 151
Total excess fragments = 24,098
Average fragments per file = 1.68
Pagefile fragmentation
Pagefile size = 0 bytes
Total fragments = 0
Directory fragmentation
Total directories = 2,361
Fragmented directories = 1
Excess directory fragments = 1
Master File Table (MFT) fragmentation
Total MFT size = 37,872 KB
MFT record count = 37,854
Percent MFT in use = 99 %
Total MFT fragments = 39
--------------------------------------------------------------------------------
Fragments File Size Files that did not defragment
2,076 32,256 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD_2006\BAP_PROD_2006_Data.MDF
86 248 MB \Microsoft Analysis Services OLD\BACKUP\TEMP\msm88CA.tmp
81 13,174 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD_2006\cis_dim_Data.NDF
120 7,180 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD_2006\DIM_Data.NDF
2,033 1,262 MB \Microsoft Analysis Services OLD\Data\BAP_PRODOLD\REVENUE_1\REVENUE_1.fact.data
114 835 MB \Microsoft Analysis Services OLD\Data\BAP_PRODOLD\REVENUE_1\REVENUE_2.fact.data
164 1,907 MB \Microsoft Analysis Services OLD\BACKUP\TEMP\msm1C09.tmp
116 1,620 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD\agg_customer_product_level.ndf
81 1,104 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD\cis_customer_dim.ndf
112 1,153 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD\contact_dim.ndf
1,399 1,587 MB \Microsoft Analysis Services OLD\BACKUP\bap_prod.CAB
377 3,962 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD\agg_cs.ndf
136 559 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD\CM_REVENUE_FACT.ndf
449 1,403 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD\agg_revenue_balances_monthly.ndf
461 1,281 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD\AGG_REV_BAL_MONTHLY_INDEX.ndf
1,195 1,261 MB \Microsoft Analysis Services OLD\Data\BAP_PROD\REVENUE_1\REVENUE_1.fact.data
112 1,251 MB \Microsoft Analysis Services OLD\Data\BAP_PROD\REVENUE_1\REVENUE_2.fact.data
91 272 MB \Microsoft Analysis Services OLD\Data\BAP_PROD\REVENUE_1\REVENUE_2.7.fact.map
95 265 MB \Microsoft Analysis Services OLD\Data\BAP_PROD\REVENUE_1\REVENUE_2.10.fact.map
753 1,578 MB \Microsoft Analysis Services OLD\BACKUP\TEMP\msm13.tmp
802 952 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD_COMMERCIAL.mdf
2,521 1,587 MB \Microsoft Analysis Services OLD\BACKUP\TEMP\msm21D1.tmp
78 611 MB \Microsoft Analysis Services OLD\BACKUP\TEMP\msm49C2.tmp
307 2,469 MB \bap_interface\temp\b2007.bak
367 15,549 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD\REVENUE_DATA.ndf
408 156 MB \bap_interface\FTP_NEW\ABFACT_SEP07.TXT
3,620 6,750 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD_2007\BAP_PROD_2007.mdf
92 1,585 MB \Microsoft SQL Server\MSSQL.1\MSSQL\Data\BAP_PROD_2007\BAP_PROD_2007_log.ldf
257 41,453 KB \bap_interface\FTP_NEW\REVFACTAIX.TXT
314 157 MB \bap_interface\FTP_NEW\ACCTBALFACT.TXT
My question is is it clear from this log what caused DEFRAG failure on G: and F: drives and what needs to be done to successfully DEFRAG those disks.
August 26, 2008 at 1:58 pm
Was SQL on during the defrag? It needs to be turned off I believe, otherwise the files are locked.
August 26, 2008 at 2:55 pm
You might want to check out the CONTIG utility available in the File and Disk utlilities section here:
http://technet.microsoft.com/en-us/sysinternals/default.aspx
I've had success defragging specific files, even SQL Server database files while the server was still running.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply