April 15, 2014 at 8:01 am
Hi All,
I have a question about the space that is available for two particular databases that I have. Currently, Database A is sitting at about 48 GB in size and has only 638 MB of available space. Database B is sitting at about 206 GB in size and has roughly 30 GB of available space. Now both of these database files live on my D: drive and this has 308 GB of free space.
My question is, why don't both of these databases have more available space to them and since they don't, do I need to change something in the database settings to increase the size? I am asking now as this was never a problem for me before, but I had an SSIS package fail in my nightly scheduler and it was because, I believe, I didn't have enough free space in Database B. I cleaned up some old unused tables to create more available space, reran the package and it loaded successfully. To avoid this from happening in the future, I wanted to make sure there is always plenty of space for loading into Database B.
Please let me know what I can do or if there are any suggestions for best practices.
Thanks
Andrew
April 15, 2014 at 8:33 am
You database has the space that you've either "given" it or is based upon your auto-growth settings. If you ran out of space I would guess that you have disabled the auto-growth option (hence your SSIS package failed). Since growing the database frequently causes IO contention across your disk subsystem, it is advisable to set the DB to have enough free space from the start.
Everyone has different opinions on size requirements. I typically set free space to be 1.5x the current size of the database, and set my auto-growth options to 1,024MB (1 GB) and unrestricted growth - as I have alerts set up to warn me well in advance of my disks running out of space. Since you have about 300GB of free space, you may want to set things differently.
Start with added some "extra" space to both DB's. I wouldn't go all crazy and add 200GB of your remaining space or anything, but would recommend you get an idea of what your DB growth has been for the past several months, then size according to your projected growth over the next few months (and then some). If you add too much free space and end up having to "reclaim" it, that process can be tedious and results in fragmentation in both your tables, and at the OS-level...
Hope this helps!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 15, 2014 at 8:55 am
Thanks for the response. Here is the error message that I got yesterday from the SSIS progress tab.
[HZ_SCORE_FINAL [551]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "There is insufficient system memory in resource pool 'internal' to run this query."
When I checked the properties for the database that I was writing to (Database B) it was showing me something like 0.65 MB of available space. The package was close to complete with loading the data as well. (The table normally has around 35 mil records and I as at about 30 mil at the time of failure.
In the properties of the database, after highlighting the file tab, I have two rows. One for the mdf file and one for the ldf file.
The mdf file is set with the Enable Autogrowth, File growth set at 1MB and Maximum file size as unrestricted file growth.
The ldf file is with the Enable Autogrowth, File growth set at 10% and Maximum file size is restricted at 2,097,152.
When I started on this project, this database had already been created and I have never changed these properties so I don't know if this is the default or if someone set this up.
Not sure if this helps explain what may have happened.
Thanks
Andrew
April 15, 2014 at 9:17 am
You have problems with memory, not disk space. Check min, max server memory.
April 15, 2014 at 9:22 am
"Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "There is insufficient system memory in resource pool 'internal' to run this query."
Do you have the resource governor running on this server?
I am also leaning towards a memory issue as SQL Guy has mentioned...
I would recommend immediately changing your data file 1MB auto-growth to something larger, say at least 128MB. Also read up on Instant File Initialization (requires your SQL service account to perform volume tasks).
I would also recommend getting rid of the percentage growth on your log file, making it a set growth rate.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 15, 2014 at 9:28 am
Your problem is not with the space, however you should have some space in each database for growth.
Depending on dynamics of the database allocate something between 15% to 50% free space in mdf and double it for ldf.
Set the auto grot to something bigger 128Mb, 256Mb (1Mb is too small, 1Gb too big).
The job of a DBA is to keep the "horses in the allocated stable area", read DBA manages the size of database files.
You have to monitor the free space within the databases and on Hard Drive.
Otherwise you will have surprises.
April 15, 2014 at 12:51 pm
Thanks all for the feedback. This is some great info for me to research and look into. Appreciate the help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply