April 24, 2018 at 12:43 pm
Had an issue last week where a vendor application generated excessive content overnight that almost used up all our server free space (ldf - grabbed up space over 7 hours period). Vendor is looking into it. We were down to 10 MB. This is SQL Server 2014 EE.
While this looks to be an application issue, what are our options to prevent unexpected updates from taking place in the future? Or restricting their storage grab? We received an email alert at 1 AM. We also have system software to monitor our servers. They would not stop what happened.
We do not expect any updates after the user staff leaves for the day. This situation was a bug that kicked off activity that grabbed storage. What we need is a preventative measure to stop the transactions (or limit them). Simple recovery model is not an option and frankly would not have made a difference. Running transaction log backups 24 X 7 makes no difference (unless we place the files off the server - prefer not to do that - that would hide this problem for a bit).
We have about 43+ GB free space. Users stop their work at around 4-5 PM. No updates are done after that time. We run our last transaction log backup at 7 PM, then perform a full backup at 8 PM. Later in the evening / early morning we run our system tasks.
Comments / URLs are appreciated. Thank you.
April 24, 2018 at 1:02 pm
bobba - Tuesday, April 24, 2018 12:43 PMHad an issue last week where a vendor application generated excessive content overnight that almost used up all our server free space (ldf - grabbed up space over 7 hours period). Vendor is looking into it. We were down to 10 MB. This is SQL Server 2014 EE.While this looks to be an application issue, what are our options to prevent unexpected updates from taking place in the future? Or restricting their storage grab? We received an email alert at 1 AM. We also have system software to monitor our servers. They would not stop what happened.
We do not expect any updates after the user staff leaves for the day. This situation was a bug that kicked off activity that grabbed storage. What we need is a preventative measure to stop the transactions (or limit them). Simple recovery model is not an option and frankly would not have made a difference. Running transaction log backups 24 X 7 makes no difference (unless we place the files off the server - prefer not to do that - that would hide this problem for a bit).
We have about 43+ GB free space. Users stop their work at around 4-5 PM. No updates are done after that time. We run our last transaction log backup at 7 PM, then perform a full backup at 8 PM. Later in the evening / early morning we run our system tasks.
Comments / URLs are appreciated. Thank you.
One option is to not have any files set to unlimited growth so that you control the max file sizes.
Sue
April 25, 2018 at 4:50 am
Sue_H - Tuesday, April 24, 2018 1:02 PMbobba - Tuesday, April 24, 2018 12:43 PMHad an issue last week where a vendor application generated excessive content overnight that almost used up all our server free space (ldf - grabbed up space over 7 hours period). Vendor is looking into it. We were down to 10 MB. This is SQL Server 2014 EE.While this looks to be an application issue, what are our options to prevent unexpected updates from taking place in the future? Or restricting their storage grab? We received an email alert at 1 AM. We also have system software to monitor our servers. They would not stop what happened.
We do not expect any updates after the user staff leaves for the day. This situation was a bug that kicked off activity that grabbed storage. What we need is a preventative measure to stop the transactions (or limit them). Simple recovery model is not an option and frankly would not have made a difference. Running transaction log backups 24 X 7 makes no difference (unless we place the files off the server - prefer not to do that - that would hide this problem for a bit).
We have about 43+ GB free space. Users stop their work at around 4-5 PM. No updates are done after that time. We run our last transaction log backup at 7 PM, then perform a full backup at 8 PM. Later in the evening / early morning we run our system tasks.
Comments / URLs are appreciated. Thank you.
One option is to not have any files set to unlimited growth so that you control the max file sizes.
Sue
Another option is to set an ALERT on the filling percentage of the log file. The sample code below will execute a SQL Agent job when the log of SSISDB is over 70%. You can also configure the alert to send a message.EXEC msdb.dbo.sp_add_alert @name=N'SSISDB Log Threshold',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=180,
@include_event_description_in=0,
@category_name=N'DB Space',
@performance_condition=N'SQLServer:Databases|Percent Log Used|SSISDB|>|70',
@job_name = N'LOG Backup SSISDB' ;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply