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.
Zahid AhamedDatabase AdministratorAsked:
Who is Participating?
 
Zahid AhamedDatabase AdministratorAuthor Commented:
I have resolved this issue. There are four SQL Node which is running under AWS availability group and using the same host name. I had to login each box and then assign auto growth settings like it was before limited to 50 GB, i extended to 70 GB now. That is why alert was coming from four node, because four node was replicating same database.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please provide the result of the following command:
sp_helpdb 'DatabaseNameHere'

Open in new window

0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
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
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
I did 512 mb. But still I'm getting email alerts
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I did 512 mb.
What that means?
How much free space is in D: ?
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I guess you have some blocked or long running process in progress. What sp_who2 returns?
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
Vitor, nothing is running.
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
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?
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
Alert is coming again. How do i stop alert for now. It is bothering a lot.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You must have some recursive job or process running. Try to find it.
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
No job running at all. I just checked now
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, something must be running, otherwise the database wouldn't need to grow.
If not a local job then a remote job or process.
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
I checked the activity monitor and ran sp_who2. If the database does not grow then the alert will be coming continue. correct?
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What are you using for monitoring SQL Server?
I think you never told what's sending those alerts. Also, what 017 means?
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
I meant email notification alert severity 017
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
SQL Server Alert System: 'Severity 017' occurring every 3 mins
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
DbName      FileName      CurrentSizeMB      FreeSpaceMB
Sitecore7_.Data      50800.000000      15314.562500
Sitecore7.Log      201.000000      187.812500
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
By the way, check the SQL Server logs to see if there's any error messages.
0
 
Gerald ConnollyCommented:
Is disabling an error alert, a sensible thing to do?
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
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 );
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
I resolved this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.