I have a database on SQL 2000 SP4 that is set via Enterprise Manager to grow
the database and log files automatically. It doesn't happen, however. They
run out of space and don't grow, even though there is plenty of physical disk
available. Any ideas what may be happening?
Thanks,
Roger> They run out of space and don't grow
Can you be more specific?
Are you getting an error message? If so, what is it?
Otherwise, how do you know they need to grow?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Hi Aaron,
The web application that uses the databases starts to throw errors to the
end users, such as "timeout expired" when they try to save data. If I look at
the database in Enterprise Manager, the transaction log or the database will
have less than 1MB free. If I set allocated space to say 1GB larger and
restart the IIS on the web server then the web app starts working normally
again.
In the application log, errors similar to the following were present:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 5/9/2007
Time: 11:08:18 AM
User: N/A
Computer: SQL01
Description:
Error: 1105, Severity: 17, State: 2
Could not allocate space for object 'EventLog' in database 'Harmony_Live'
because the 'PRIMARY' filegroup is full.|||> Could not allocate space for object 'EventLog' in database 'Harmony_Live'
> because the 'PRIMARY' filegroup is full.
Well, that sounds to me like the disk is full. What is your autogrowth
setting for the data and log files? What is the current result of:
USE Harmony_Live;
GO
EXEC sp_helpfile;
EXEC sp_spaceused;
EXEC master..xp_fixeddrives;
GO|||Sorry about the formatting, but if you paste it into a text document it will
open correctly. I ran the query and the results were:
name fileid filename filegroup
size maxsize growth usage
-- -- --
-- -- -- --
Bremwood_Live_Data 1 F:\Harmony_Live\Harmony_Live.mdf PRIMARY
8192000 KB unlimited 10% data only
Bremwood_Live_Log 2 F:\Harmony_Live\Harmony_Live_log.ldf NULL
1024000 KB Unlimited 76800 KB log only
database_name database_size unallocated space
---
Harmony_Live 9000.00 MB 1318.63 MB
reserved data index_size unused
-- -- -- --
6841720 KB 6424632 KB 392088 KB 25000 KB
drive MB free
-- --
C 31155
D 122125
F 12074
(3 row(s) affected)|||Have you considered moving the log file to a different drive? You have
plenty of space on D:\
How about changing the growth on the data file from a % (since 800MB is a
lot to initialize, and it just gets worse every time) to a more reasonable
fixed number, like 10 MB? Unless the operations that are requiring growth
in the first place are going to need motre than that in one shot?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:7293A561-541B-4D96-9849-1DC7CD0AA0E0@.microsoft.com...
> Sorry about the formatting, but if you paste it into a text document it
> will
> open correctly. I ran the query and the results were:
> name fileid filename filegroup
> size maxsize growth usage
> -- -- --
> -- -- -- --
> Bremwood_Live_Data 1 F:\Harmony_Live\Harmony_Live.mdf PRIMARY
> 8192000 KB unlimited 10% data only
> Bremwood_Live_Log 2 F:\Harmony_Live\Harmony_Live_log.ldf NULL
> 1024000 KB Unlimited 76800 KB log only
> database_name database_size unallocated space
> ---
> Harmony_Live 9000.00 MB 1318.63 MB
>
> reserved data index_size unused
> -- -- -- --
> 6841720 KB 6424632 KB 392088 KB 25000 KB
> drive MB free
> -- --
> C 31155
> D 122125
> F 12074
> (3 row(s) affected)
>|||I've tried them at 10MB, then 75MB, then back to 10%. The same thing happened
with either setting. I have them on that F: drive because it's on our SAN,
whereas the D: drive is not. I can increase the size of that F: drive easily
enough, but since we weren't filling it up I didn't think it would fix the
problem.
Right now the db is at 10% growth and the transaction log is at 75MB growth.
I'll change them both to 75MB, but I think it'll continue to not grow.
Thanks for troubleshooting this with me. Do you have any other ideas?
Roger
"Aaron Bertrand [SQL Server MVP]" wrote:
> Have you considered moving the log file to a different drive? You have
> plenty of space on D:\
> How about changing the growth on the data file from a % (since 800MB is a
> lot to initialize, and it just gets worse every time) to a more reasonable
> fixed number, like 10 MB? Unless the operations that are requiring growth
> in the first place are going to need motre than that in one shot?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006|||> Thanks for troubleshooting this with me. Do you have any other ideas?
Not right now, no. Is it possible that permissions on F:\ have changed, or
the user that SQL Server is running as has changed?
A|||Anything's possible. :)
I logged into the SQL box as the user that SQL runs as and created/deleted a
text file in f:\harmony_live just to be sure.
Is there way I can send an alert if the database or transaction logs reach a
certain percentage of allocated space?
Thanks,
Roger
