February 3, 2007 at 8:36 am
Hi
My sql database is showing space available for one of the database is 0.
I have got SQL Server 2005 Ent. Edition(32bit). File growth for .mdf file is set to Autogrowth 10% growth Unrestricted.
I have got lots of physical space on hard drive, why then it is showing 0.00 space available for database. What do i need to do to free up space.
When i ran sp_spaceused it gave me following results
database_name database_size unallocated space
XXXX 1817.31 MB 0.00 MB
reserved data index_size unused
1860760 KB 1763496 KB 13240 KB 84024 KB
I would really appreciate if anyone could tell me what is going on over here and would this slow down the data access?
Regards
February 3, 2007 at 9:04 am
There a many cases where a simple 'sp_spaceused' will not return current space utilization and the information is out of date and this behavior is documented in BOL.
To get absolutely correct information, run
exex sp_spaceused @updateusage ='true'
SQL = Scarcely Qualifies as a Language
February 5, 2007 at 2:12 am
I have checked the logs and i am not getting any errors but the data access has gone very slow.
I will look at DBCC UPDATEUSAGE in BOL for more information.
Meanwhile i was wondering if there is lots of physical disk space available then why is SQL not using it and how can i make it use that space.
Mitesh
February 5, 2007 at 6:07 am
This would force a file change :
ALTER DATABASE [Ideal_Audit] MODIFY FILE (NAME = [Ideal_Log], SIZE = 1300MB)
February 12, 2007 at 3:26 pm
Try Remi's version first but here is the visual way to do it, increasing file size as needed is first thing to do while drinking coffee in the morning so you don't run into such issues because waiting for SQL Server to increase the file size is futile. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms189253.aspx
Kind regards,
Gift Peddie
November 2, 2007 at 8:57 am
We have had issues with databases that are set to autogrowth at 10% unrestricted. You are right, it will return a 0 and say that the database is out of space. This is normal function for this particular procedure. You should attempt to find out an appropriate file size growth, in MB, and get a maximum that you are comfortable with.
Let's say I have a database that is 4GB. I will attempt to find out over the course of a week or a month how much data is added to that database, say 100MB in a month. I will get the Autogrowth to restricted growths of 100MB to a maximum of 6GB. This gives the database over 1 1/2 years of growth space, while I don't have to worry about the system running out of room.
We actually have a job that is scheduled every morning at 6:30am. It checks the database sizes, and if something is within 2 growths of max, it sends us an email, and fails the job. This job is pushed out from our Master Server to all our Target Servers. A Reporting Services report generates at 6:45am, everyday, from the Master Server, that lists all failed multi-server jobs that have failed on Target Servers. This allows my team to view 1 report in the morning, see what servers were affected, then look at the email from the server itself to see which databases need attention. We have this information in several places for documentation and duplication, in case one of our team doesn't address the databases, someone else will.
If you are a solo operation, you may only want to put the job on the server and have it complete successfully, sending you an email when the database is low on available space.
Hope this is helpful!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply