Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Size versus Space Available

    We are planning hardware purchases (more is better). One of our databases is 131 gigs in size and has 45 gigs of 'space available'. I'm not a very experienced SQL Server person, but this seems like quite a bit of 'space available'

    1) Is there a way to regulate the amount of 'space available'?
    2) are there any rules of thumb for how 'space available' there should be?

    Appreciate any feedback or help.

    Ray

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by rkobs
    We are planning hardware purchases (more is better). One of our databases is 131 gigs in size and has 45 gigs of 'space available'. I'm not a very experienced SQL Server person, but this seems like quite a bit of 'space available'

    1) Is there a way to regulate the amount of 'space available'?
    2) are there any rules of thumb for how 'space available' there should be?

    Appreciate any feedback or help.

    Ray
    some cautions and then some of my (admittedly personal) thoughts:

    1. Be sure that the information is updated. You may need to run DBCC UPDATEUSAGE in the database to get the most current statistics.

    2. The space available (as viewed in the taskpad view through Enterprise Manager) includes the unused portion of the log file(s). This unused portion of the log file will change depending on how frequently you are performing transaction log backups and on the recovery model for your database.

    That all being said:

    1. I generally let SQL "manage" the growth of my data files when the database data file is less than 10 GB. Be sure to set a "maximum" value for both the log file and the data file, but I think it's safe to let SQL auto grow data files less than 10 GB in size.

    2. Once you have reached the 10 GB threshold, you need to start managing the process yourself and scheduling file growths for off-peak periods. During file growths, SQL can lock tables and prevent updates and/or inserts.

    3. In planning for space utilization, I try to schedule and size the growths so that I only have to do them once every three months and I like to try to keep about 15% of the data file "free" (in other words, size the growth so that at the end of a three month window, there is still 15% space free). That's an arbitrary number and is based more upon my experience with physical hard drives than any real SQL guidelines.

    Bear in mind, these are my practices and not necessarily "best" practices. For sure set maximum values on your files that are set for auto growth.

    regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Jun 2004
    Posts
    57
    Thanks for the help.

    You pretty much nailed in terms of our current process. The file growth is not being managed -- at least not in any planned manner.

    That said, i've seen numbers (MSDN) in the 40% range of 'space available' as the recommended amount (otherwise reindexing jobs will fail). We're on a path to cut the current space available levels from 33% to 20%. I'm concerned that we might have issues with our reindexing jobs if we start down that path...

    Thoughts...

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    When considering free space, consider what 30% means in terms of raw numbers, not just relative. 30% of a 131 GB database is far different from 30% of a 200 MB db. Also, I thought (and I am probably mistaken here), that reindexing consumed space in the transaction log and the tempdb (not so much in the data files).

    I took over management of a 200 GB database that was 98% full and we never had an issue with the reindexing jobs failing.

    I did have issues that the database wasn't being backed up and that all the storage was on-board SCSI with no RAID, but that's a different story for a different day....

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Jun 2004
    Posts
    57

    Shrink DB

    Thanks for the reply. Just a sanity check...

    Did a bit more poking around. For the database i mentioned we have 23 data files. 6 are allocated for the db indexes and the rest for tables.

    I took a look at the 'Shrink Database' wizard. It looks like I can go datafile by datafile or across the entire db and set an amount of 'available space'.

    I assume when you're talking about 15% in terms of 'available space' that you use this wizard (or another similar sp) to manage your available space to that 15% level.

    We're planning on giving it a try for a smaller test db. Assuming it works, we try it on the big db.

    Anything obvious or not so obvious that we're missing?

    Ray

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by rkobs
    Thanks for the reply. Just a sanity check...
    [snip]
    I assume when you're talking about 15% in terms of 'available space' that you use this wizard (or another similar sp) to manage your available space to that 15% level.
    [snip]
    Anything obvious or not so obvious that we're missing?

    Ray
    Nope, I don't use wizards on the big DBS and certainly not the Shrink wizard. The associated commands for shrinking are DBCC SHRINKATABASE and DBCC SHRINKFILE.

    However, my database is constantly growing, so I am not really concerned with shrinking it. Instead I use the ALTER DATABASE command with the MODIFY FILE parameter to increase the zie of the specfied file(s). I calculate how much freespace to add manually (using a calculator and data that I collect plus a factor for "windage"). The command (from BOL) is:

    ALTER DATABASE Test1
    MODIFY FILE
    (NAME = test1dat3,
    SIZE = 20MB)
    GO


    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •