locked
How to get a alert when SQL Server Primary file is going to get full ? RRS feed

  • Question

  • Dear Community,

    Today our SQL Server Primary file space got full and our system gone down.

    Error message in logs.

    ODB0000164 - STMT:00 [42000][1105][2] [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not allocate space for object 'TS910.F98761TS910FC' in database 'JDE_TS910' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup.

    To avoid the issue in the future ,

    Option 1 : we can change the file system space to unlimited 

    Option 2 : However we are looking for any other possibility to monitor the Primary file space and send a alert when file system reaches the critical value. Is it possible ?

    Our Database : SQL Server 2014 Enterprise Edition

    Best Regards
    JDEE1Tips
    Systemic Software Solution


    • Edited by JDEE1Tips Sunday, October 9, 2016 5:32 PM
    Sunday, October 9, 2016 5:31 PM

Answers

  • Hi ,

    configure critical  alert for primary group full ,see more ref. in following link

    https://www.mssqltips.com/sqlservertip/2359/find-sql-server-data-and-log-files-that-are-almost-out-of-space/


    Please click Mark As Answer if my post helped.

    • Marked as answer by JDEE1Tips Monday, October 10, 2016 2:49 AM
    Sunday, October 9, 2016 9:11 PM
  • Option 1 : we can change the file system space to unlimited .

    First, identify  with -

    use <yourdb>
    
    go
    
    sp_helpfile




    see how many files exists and on what drives it resides,

    1.some times the auto growth restricted to very small size but drive have lot of room to hold -so you can change the autgrowth to use the space.
    2.If any unexpected data pushed may affect to the database growth (or) what else caused(there might be any other bulk transactions/maintenance) occur may cause this kind of an issue so you need to check what caused & try to tune those ,ofcourse see purging can be used so that internally files will have the space further for other transactions.
    3.if your database files is exists in its own dedicated drive then instead of unlimited(this will  becomes full when the database grows fully & may reappear primary Filegroup full) leave some 10% to  space(this is just to ensure that you will not get drive alert etc..) but this is at most important how you are managing data base in terms of growth like-
    you need to have the report for weekly -how it is growing and if you find abnormal fix it at the right moment by understanding the business logic like purging or expand the drive space further  (or) add the new drive  and add another files in the other drive(s) for the same filegroup & cap the older file(s).
    4.If your database files also shared with other databae files in the same drive(s) then you have to consider that as well before you changing the autogrowth & see if you have more space where another drives exists you can move(requires downtime for moving the file) nor add another database file in to other drive(where space exists).



    Option 2 : However we are looking for any other possibility to monitor the Primary file space and send a alert when file system reaches the critical value. Is it possible ?

    >>as said have a review weekly/monthly capacity of the databases instead waiting for certain threshold reaches -this will helps you take appropriate action before you (or) apps in trouble.

    already few of them shared the links- but keep in mind ensure you set it to 80% (or) 85%(it depends on your wish) when it reaches then send an notification.

    ref-

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6505ed29-b8ea-4c14-821f-a4001776f894/need-email-notification-when-filegroup-reaches-a-given-percent?forum=sqltools



    Regards, S_NO "_"


    • Edited by S_NO Monday, October 10, 2016 1:39 AM +
    • Marked as answer by JDEE1Tips Monday, October 10, 2016 2:49 AM
    Monday, October 10, 2016 1:39 AM

All replies

  • Hello,

    The following lines will create an alert if the instance has Database Mail configured and you have an operator created:

    EXEC sp_add_alert @name = 'Error 1105: Filegroup Full', @message_id=1105, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1

    EXEC sp_add_notification @alert_name = 'Error 1105: Filegroup Full', @operator_name='youroperatorname', @notification_method = 1

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by Teige Gao Monday, October 10, 2016 2:34 AM
    Sunday, October 9, 2016 6:45 PM
  • If you do not have Database mail setup, you could create a stored procedure that does a RAISERROR when the available space is below a certain MB or percentage. Execute the stored procedure from a SQL Agent Job. Of course you have to have some procedures in place for monitoring failed jobs.

    Google "sql monitoring available disc space" or something similar

    This looks handy

    https://www.mssqltips.com/sqlservertip/2444/script-to-get-available-and-free-disk-space-for-sql-server/

    Sunday, October 9, 2016 7:55 PM
  • Hi ,

    configure critical  alert for primary group full ,see more ref. in following link

    https://www.mssqltips.com/sqlservertip/2359/find-sql-server-data-and-log-files-that-are-almost-out-of-space/


    Please click Mark As Answer if my post helped.

    • Marked as answer by JDEE1Tips Monday, October 10, 2016 2:49 AM
    Sunday, October 9, 2016 9:11 PM
  • Option 1 : we can change the file system space to unlimited .

    First, identify  with -

    use <yourdb>
    
    go
    
    sp_helpfile




    see how many files exists and on what drives it resides,

    1.some times the auto growth restricted to very small size but drive have lot of room to hold -so you can change the autgrowth to use the space.
    2.If any unexpected data pushed may affect to the database growth (or) what else caused(there might be any other bulk transactions/maintenance) occur may cause this kind of an issue so you need to check what caused & try to tune those ,ofcourse see purging can be used so that internally files will have the space further for other transactions.
    3.if your database files is exists in its own dedicated drive then instead of unlimited(this will  becomes full when the database grows fully & may reappear primary Filegroup full) leave some 10% to  space(this is just to ensure that you will not get drive alert etc..) but this is at most important how you are managing data base in terms of growth like-
    you need to have the report for weekly -how it is growing and if you find abnormal fix it at the right moment by understanding the business logic like purging or expand the drive space further  (or) add the new drive  and add another files in the other drive(s) for the same filegroup & cap the older file(s).
    4.If your database files also shared with other databae files in the same drive(s) then you have to consider that as well before you changing the autogrowth & see if you have more space where another drives exists you can move(requires downtime for moving the file) nor add another database file in to other drive(where space exists).



    Option 2 : However we are looking for any other possibility to monitor the Primary file space and send a alert when file system reaches the critical value. Is it possible ?

    >>as said have a review weekly/monthly capacity of the databases instead waiting for certain threshold reaches -this will helps you take appropriate action before you (or) apps in trouble.

    already few of them shared the links- but keep in mind ensure you set it to 80% (or) 85%(it depends on your wish) when it reaches then send an notification.

    ref-

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6505ed29-b8ea-4c14-821f-a4001776f894/need-email-notification-when-filegroup-reaches-a-given-percent?forum=sqltools



    Regards, S_NO "_"


    • Edited by S_NO Monday, October 10, 2016 1:39 AM +
    • Marked as answer by JDEE1Tips Monday, October 10, 2016 2:49 AM
    Monday, October 10, 2016 1:39 AM
  • Thanks everyone !
    Monday, October 10, 2016 2:50 AM