Post reply

Running out of disk space?!

  • March 15, 2007 at 10:09 pm

    #176524

    Hi there,

    Probably a dumb question but hey I am running Database Engine Tuning Advisor and I keep running out of disk space even before it even starts to offer any recommendations - it only just starts looking at the file. Then fails - saying Error:Failed to open input workload file.

    I have watched it and it is consuming all the space on the C:\. To be honest there isn't a hell of a lot of space there (approx 200MB), but SQL itself is installed on another disk with lots of space.

    Any ideas on how I can make it do - whatever it is doing - on a different drive?!?!

    Thanks in advance for any assistance.

    Troy

  • March 19, 2007 at 1:01 pm

    #695788

    1) Run it on a different box.

    2) Add another disk.

    3) Create more free space on existing drive (delete files, esp IE crap and temp files, reconfigure recycle bin (this is a given - default of 10% is SO wasteful!!), installers, uninstall unused apps, reduce page file size,  etc).

    2) I STRONGLY recommend that you do not run it at all.  I have seen it DESTROY (as in creating HUNDREDS of indexes containing MANY HUNDREDS of fields for example) databases AND server performance in the hands of users not REALLY experienced with it.  And if a user is really experienced, he/she should be able to examine simple profiler runs and easily develop their own indexing strategy. 

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • March 19, 2007 at 2:24 pm

    #695806

    Thanks for the post Kevin. I am fully aware that I can add more disk, remove aps, etc etc to gain disk space - I was more interested to understand why, when SQL is installed on another drive, that the C:\ fills up when the Tuning advisor is run. 

    Thanks also for the headsup on the recommendations - I am more interested to see what findings it comes up with to see the results, rather than apply all the findings - as I have only hear cautionary tales on this too. So fully appreciate what you are saying.

    Thanks

  • August 6, 2008 at 8:52 am

    #853882

    I don't supposed anyone had any more information on this one?

    I'm in a similar situation. I only have just over a gig free on the C: drive and cannot free up any more space.

    My database drive has plenty of space yet the tuning advisor seems to only want to use the C: for whatever it does when consuming the workload.

    The trace file is approximate 2.5gig in size and the database itself is around 5 gig.

    Is there any setting where I can make its working drive another drive?

  • August 6, 2008 at 9:07 am

    #853893

    I don't know of a way to make it use a different drive for that. May not be one.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • August 6, 2008 at 10:51 am

    #853999

    Just curious, where are your SQL Server tools binaries installed? I wonder if it exhibits the same behavior if SQL Server's tools are installed on a different disk.

    If for some reason you can't install the tools on another disk, something you might be able to try is adding another disk as a mount point under C:\ with the same folder structure name for the SQL Server client tools. I havent' tried this personally, but it seems that it should make sense.

    Other than that, either run the workload on another machine with more disk space and/or tune the statements by hand. I particularly like the approach used by Itzik Ben-Gan in his book "Inside Microsoft SQL Server: T-SQL Querying" for identifying the top query patterns and tuning for those patterns. You'll find the details in chapter 3.

    http://www.sql.co.il/books/insidetsql2005/

    If you're in a time crunch, you can download the sample code from the book from his website. Again, focus on chapter 3. Note that you'll have to compile some C# code to do the regex pattern matching for the query signature assignment, enable CLR, and create a function for it. I found it pretty easy. In the sample code he provides, you'll want to focus on the chaper3.sql file starting at the comment...

    ---------------------------------------------------------------------

    -- Analyze Trace Data

    ---------------------------------------------------------------------

    If you've already got a workload file for the database tuning advisor, you've probably got enough to work with using his approach. Honestly I find the approach much better to tune the "maximum impact" queries that make sense for your business environment. You can also still use database tuning advisor to tune the queries, but in this way you can use it to tune just the queries that are causing the greatest amount of "waits" and vastly reduce the workload that it has to process.

    Good luck!

Viewing 6 posts - 1 through 6 (of 6 total)

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