Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012

    Unanswered: Database consumes available free space?

    New to the forum and I consider myself a competent but still learning SQL admin....and this is my first be kind to me.

    I inherited a SQL server (2005) that sits on a Windows 2003 box (upgrading to 2008 R2 and Win2008, ASAP) and on our SQL Data Drive (260GB - 22GB free) we have 16 databases (that are associated with our primary application). One database in particular is causing me quite a bit of pain - on Tuesdays. This table will consume all available free space (save 700kb) and place it within it's 'reserved' space. It's not being used and I can reclaim it by shrinking. I have done this for 3 consecutive weeks now and I have always thought that shrinking is not really a best practice.

    Based on a job I scheduled to spit out various database stats I discovered the following:

    Somewhere after 11pm on Monday (and on or before 11:30pm) the reserved space goes from 9GB down to 23 then 37MB on this particular database.

    After 1:30am on Tuesday (and on or before 2:00am) the database consumes nearly all available disk space and thus expands its reserved space to 28GB

    I believe it has something to do with the Indexes being rebuilt per the Maintenance Plan the Index Rebuild is supposed to occur every Monday @ 11pm. On 6/18 it ran from 11pm 11:44pm with no problems. Starting on 6/26 (and subsequently 7/2 and 7/9) they have started at 11 and ended anywhere between 2 to 3 hours later. And with the following error:

    Failed-1073548784) Executing the query "ALTER INDEX [PK_activityLog] ON [dbo].[activityLog] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF ) " failed with the following error: "Could not allocate a new page for database 'FA_PROD_SDDS' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    The database 'FA_PROD_SDSS' is the one that I am having issues with.

    I'm not sure where to go from here, with regard to troubleshooting and finding a solution. I'm not sure, after this error, what is causing the database to expand

    I can provide whatever more information is needed. I am very comfortable with querying and moving around this server but I'm still getting to know some of these little nuances that seem to be popping up.

    I appreciate any help at all.


  2. #2
    Join Date
    Apr 2004
    Shrinking can cause fragmentation on your hard drives so yes it is probably not recommended to do often:

    Don't Touch That Shrink Button in SQL Server! | Straight Path Solutions, a SQL Server Consultancy

    The solution would be to reset the percentage of growth on your database (and get a larger hard drive). I do not remember off the top of my head the exact procedure to do this but this might help:

    SQL Server Database Growth and Autogrowth Settings

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 11
    The command ALTER INDEX...REBUILD will make a brand new copy of an index alongside the old one. Once the new one is built, then the old one can be deleted (and the space freed up). I suspect you have a clustered index on a large table, and this is causing you all of the trouble. You may want to withdraw that table from the maintenance plan, and do the index rebuilds manually, until you get enough space to let it happen automatically.

Posting Permissions

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