Database can't expand automatically | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Database can’t expand automatically

Hi, One of my databases can’t seem to expand automatically, although i’ve checked the box to allow for auto-grow. It would run out of space, and i’ll need to manually increase the space allocated. After which, it would run fine again. Can anybody help explain why this is happening? Thanks! Raymond
General question we ask everyone. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> I suppose you have the latest service packs on SQL Server don’t you? That would be 3a.<br /><br />Also, just for kicks and grins, run sp_helpfile and make sure the setting is correct when you run this. The service account that SQL is running under is part of the local/administrators group isn’t it?<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Yep, latest service pack 3a applied.
Checked sp_helpfile also, shows unlimited file growth…
and yes, startup account is part of the admin group.
Raymond
Does this only happen on one database or on all of them? If it only happens on only on one I would backup the database, delete it, and restore from the backup file to see if recreating the log file fixes it. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Check tempdb, may be the message is for lack tempdb space available to grow. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
True, what was the error generated?
ENsure there are no issues with physical disk space where data files are located on the server. When was the last transaction log backup performed? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Actually, i’ve been experiencing this problem on this database for quite some time. I’ve just backed up the db and restored it in a new box, hoping the problem would disappear but obviously it’s still persistent… There are no physical disk space issues…and tempdb has enough space to expand…
no errors generated either, either in the event viewer or sql server logs… Raymond
Ray What was the error when you’re at :although i’ve checked the box to allow for auto-grow. It would run out of space, and i’ll need to manually increase the space allocated stage? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
There are no error messages. I’ll only realise the problem when i try to insert some new records, and a message pops up saying ‘cannot insert because table has run out of space’… Raymond
Strange, can you try monitoring the activity using PROFILER during this behaviour.
Try to get database sizes before and after insert activity. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I’ve done this before. Everything works fine, inserts/deletes/updates are normally, until the db needs to expand. It just can’t seem to do it automatically, needs manual intervention… Raymond
Supply the values SP_HELPDB ‘dbname’ & SP_HELPFILE. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
sp_helpdb: LogDatabase 64485.88 MBfinisardba14May 7 2004Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics80
sp_helpfile: LogDatabase_Data 1e:SQLLogDatabaseLogDatabase_Data.mdf PRIMARY23168832 KBUnlimited10%data only LogDatabase_Log 2e:SQLLogDatabaseLogDatabase_Log.ldf NULL1904704 KBUnlimited10%log only LogDataBase_Data_Module 3e:SQLLogDatabaseLogDatabase_Data_Module.mdf Module_Tbl40960000 KBUnlimited10%data only Issit because it’s two *.mdf files?
Raymond
Yes as you’ve 2 filegroups of locating data on primary and data modules. On which filegroup all the tables are located, primary or data?
What is the available space from the database?
Also information about TEMPDB too. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
The tables are spread out among the two filegroups… don’t really get your first question… currently, the file
Logdatabase_Data has 1.4GB free space and
Logdatabase_Data_Module has 5GB free space sp_helpdb:
tempdb 1767.25 MBsa2May 11 2004Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics80 sp_helpfile: tempdev1E:Microsoft SQL ServerMSSQLdata empdb.mdf PRIMARY1739904 KBUnlimited10%data only
templog2E:Microsoft SQL ServerMSSQLdata emplog.ldfNULL69760 KBUnlimited10%log only
Raymond
As you said tables are spread out across 2 filegroups, then no issue. Can you reckon any recent changes on this SQL SErver/database/tables? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
The only change made was that this database was backed up and restored to a newer server… So it doesn’t matter if i have two physical files with that end with *.mdf? Raymond
BOL
The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf. Secondary data files comprise all of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files. The recommended file name extension for secondary data files is .ndf.

I don’t see any issue and if possible change the secondary data file extension to .NDF. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>