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 post...so 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.
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.