Post reply

Regain disk space after cleaning up tables and indexes

  • March 11, 2011 at 9:49 am

    #1297749

    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

  • March 11, 2011 at 10:00 am

    #1297762

    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

  • March 15, 2011 at 5:36 pm

    #1299171

    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.

  • March 16, 2011 at 7:57 am

    #1299381

    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)

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

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