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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zahid AhamedDatabase AdministratorAuthor Commented:
I resolved this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.