Regain disk space after cleaning up tables and indexes

Question

Regain disk space after cleaning up tables and indexes

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

dant12 SSCrazy Points: 2712 More actions · Answer 1

you should be profiling the queries that are being run against the server together with perfmon, even if you don't think a period should be experiencing "heavy loads" some poor queries may be running inferring in a high amount of disk reads.

even if the queries are indexed, high index fragmentation may be causing large amount of reads

number of spindles and raid configuration may also be playing its part here

for the shrinking problem, try using SHRINKFILE instead, using target size parameter as in

DBCC SHRINKFILE (N'TEST' , 1000)

for a target size of 1000 MB on the file logically named TEST. the GUI will help on this

Regards

--
Thiago Dantas
@DantHimself

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 2

Agree with danT, you should be profiling to find the high read / write queries. Pick a number, say 20,000 reads, and use that as your filter. Find the queries that run the most frequently and you can then start to work through those. There is a free tool called Clear Trace and a video on how to use it HERE.

As to disk queue, you may have a problem there and as Steve stated more spindles or better throughput / caching at the SAN layer may be the answer. The troubleshooting has to be from both perspectives. The counters you really want to look at are Avg. Disk sec/Read and /Write. The goals are < 20ms read and < 10 ms write if I am not mistaken - been a while since I checked MS recommendations. Check out this article as well. http://technet.microsoft.com/en-us/library/cc966534.aspx

Hope this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 3

Thanks for all the good advice. I had been cleaning the db's and rebuilding indexes gradually all week and weekend while the system had less volume. My databases have grown so much in the past few years that I can only rebuild small groups of indexes at a time. As my log drive is only 65gb and I have to trunc it as I go.

All the maintenance seems to have smoothened things out, but I will definitely apply some of the methods that you all have recommended.

I may come back with more questions if I can get the spare time.

pete adams Valued Member Points: 52 More actions · Answer 4

If the data / cleanup involved LOBs then to free the extents that are allocated to the table structure you need to run DBCC cleantable, then dbcc shrinkfile(N'db, 0)