SQL Server Performance Forum – Threads Archive

Delete Data reduce .MDF Size

I’m looking for information on reducing the size of the .MDF file. We are not having performance issues with calls to the database but the .MDF file is 67 GIG for 1 years worth of data. What we tried to do is delete 6 months of data, 90% of our data is in 1 table 9 columns mostly float data just 500-600 million records. I successfully deleted the data, updated the stats, rebuilt all the indexes, shrink the database etc. But the .MDF only dropped to 62 GIG. I’ve successfully done this on other large databases but this one is giving me a headache. There is no clustered index on the table but there is a primary key. When I run the show_contig the three indexes look fine but the first line of the results have a scan density of 23, average free bytes 2332, average page density of 71, bestcount 157711 and actual count of 660411, logical frag 99, extent frag 14. The bestcount and scan density look bad to me and I know how to correct this on indexes but what about the table itself. I’m assuming that the showcontig line that is not an index is the table itself. 1) can this be fixed
2) will this reduce the size of the .mdf Patrick Murphy
Customer/Production Support
SmartSignal Corp

I suppose those values are for table itself. Check this lookin if index id is 0.
Mdf is 67 with space to grow or only data? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

http://www.sql-server-performance.com/rd_index_fragmentation.asp
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp Make sure you run DBCC UPDATEUSAGE on that table, it’s possible the values are outdated and this will fix it. Check the memory consumption during the long runnign queries or those queries. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Luis yes the index is 0 so I assume that it is the table itself and not the indexes. All the indexes showed minimal fragmentation and good scan densities, just not the table. Satya I’ve reviewd all these documents and they really have not helped because the indexes are fine. This table gets data pumped in via bulk insert every 6 minutes with 5000+ rows at the same time there is a high amount of reading the data. What I’m starting to wonder is if the fill factor is set too low. By default the fill factor is set to 60%, however I thought with the amount of data we were adding every few minutes and the use of bulk insert that lower is better. The other thing is there is no clustered index on the table, but I was told a primary key was just as good as a clustered index on a table. I’m begining to wonder if yes a primary key is just as good, BUT only for some maintenance things and not others. In this process to delete data I: 1) removed all indexes (except the primary key) 2) deleted the data 3) ran DBCC UpdateUsage (‘SmartSignal_eCM’) 4) ran Exec sp_createstats ‘fullscan' 5) ran Exec sp_updatestats ‘resample' 6) ran EXEC sp_MSforeachtable @command1="print ‘?’ DBCC DBREINDEX (‘?’,”,0)" 7) ran DBCC SHRINKDATABASE (N’SmartSignal_eCM’, 20) <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ran the backup maintenance job Most of the items above we do during our weekly maintenance. The odd thing is I deleted 2/3rds of the data and it only reduced the size 5 GIG out of 67. Given that the data I deleted is 90% of the data in the system I expected it to shrink much further. Patrick Murphy Customer/Production Support SmartSignal Corp

I’m wondering if:
1) adding a clustered index then removing the clustered index would work 2) or instead of deleting that much data in the future rename the table, create a new table and copy the good data from the old table to the new table would be faster and more beneficial. Patrick Murphy
Customer/Production Support
SmartSignal Corp

True create a clustered index and deleting will have the updated stats on all other non clustered indexes. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Shrink database as last step affect all reindex and update statistics you did before.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>