Link to home
Start Free TrialLog in
Avatar of Zahid Ahamed
Zahid AhamedFlag for United States of America

asked on

Could not allocate space

Hi,

I am getting continuously the following error even I increased the file group size. Email alert is continuously firing. Can’t stop email alert too

Could not allocate space for object 'dbo.PublishQueue'.'ndxDate' in database 'Sitecore7_master' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup

Please help.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Please provide the result of the following command:
sp_helpdb 'DatabaseNameHere'

Open in new window

Avatar of Zahid Ahamed

ASKER

Vitor,

Here is the  following:--

name      fileid      filename      filegroup      size      maxsize      growth      usage
Sitecore.Master.Data      1      D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Prod_Sitecore_Master7.MDF      PRIMARY      52019200 KB      71680000 KB      524288 KB      data only
Sitecore.Master.Log      2      D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Prod_Sitecore_Master7.ldf      NULL      103424 KB      51200000 KB      102400 KB      log only


As well as

DbName      FileName      CurrentSizeMB      FreeSpaceMB
Sitecore7_master      Sitecore.Master.Data      50800.000000      15314.562500
Sitecore7_master      Sitecore.Master.Log      101.000000      81.210938


Thanks

Zahid
Your database files looks ok to me.
Do you have enough free space in D: drive? You'll need at least 500MB to let the data file grow.
I did 512 mb. But still I'm getting email alerts
I did 512 mb.
What that means?
How much free space is in D: ?
82 GB free out of 199 GB The database size is 50 GB. I enable auto growth 512 MB. The table dbo.PublishQueue'.' index name is ndxDate

which has just only one file group primary. In before autogrowth setting was limited to 50 GB i expanded to 70 GB. But still 017 alert firing continuously.

Thanks
Zahid
I guess you have some blocked or long running process in progress. What sp_who2 returns?
Vitor, nothing is running.
Vitor now  alert seems like stooping. It was coming before every 3 mins. I will give you update. I tired to stop alert though. I disabled 017 alert but didn't work. From database side no blocking, as well as increased the auto growth settings. I was confused after doing this why the alert was generated. Was it probably in the sql server database mail queue?
Alert is coming again. How do i stop alert for now. It is bothering a lot.
You must have some recursive job or process running. Try to find it.
No job running at all. I just checked now
Well, something must be running, otherwise the database wouldn't need to grow.
If not a local job then a remote job or process.
I checked the activity monitor and ran sp_who2. If the database does not grow then the alert will be coming continue. correct?
I already disabled the alert for 017 but i am getting the notification still. Is there any process so that i can stop alerting for temporary
What are you using for monitoring SQL Server?
I think you never told what's sending those alerts. Also, what 017 means?
I meant email notification alert severity 017
SQL Server Alert System: 'Severity 017' occurring every 3 mins
What the following query returns?
USE databaNameHere
GO

SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    size/128.0 AS CurrentSizeMB, 
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

Open in new window

DbName      FileName      CurrentSizeMB      FreeSpaceMB
Sitecore7_.Data      50800.000000      15314.562500
Sitecore7.Log      201.000000      187.812500
By the way, check the SQL Server logs to see if there's any error messages.
Is disabling an error alert, a sensible thing to do?
DbName      FileName      CurrentSizeMB      FreeSpaceMB
Sitecore7_.Data      50800.000000      15314.562500
Sitecore7.Log      201.000000      187.812500


Since I have free space in the primary file group but still getting severity alert 017. My email inbox is flooding.
You need to set the MAX size for the primary file to be much larger. SQL cannot grow the file because the current size + the growth amount would exceed the max size you set.  Might as well adjust the log file as well:

ALTER DATABASE Sitecore7_master MODIFY FILE ( NAME = [Sitecore.Master.Data], MAXSIZE = 32GB );
ALTER DATABASE Sitecore7_master MODIFY FILE ( NAME = [Sitecore.Master.Log], MAXSIZE = 8GB );
ASKER CERTIFIED SOLUTION
Avatar of Zahid Ahamed
Zahid Ahamed
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I resolved this.