January 9, 2013 at 12:38 pm
On two occasions we have had to change or autogrowth setting for our database files from 5 or 6 % to 500MB before transactions began processing again.
Specs:
Windows Server 2003 SP2
SQL Server Standard x64 2005
Database sizes 188GB
The other day our application/database began timing out and throwing errors. We looked at the properties of the database and found the data file growth Autogrowth settings set to 6% unrestricted. We changed the rate to 5% and still the database was not responding. We changed the rate to 500MB unrestricted and the database came back on line.
Do you have any ideas why we would have to resort to setting the file size this way.
Please let me know and any help is appreciated
Jeff
January 9, 2013 at 1:40 pm
I wouldn't recommend using percentages for auto growth options on such large DBs. For instance if you have a DB that's 100GB, has autogrowth set to 5%, and the time comes when it needs to grow - that's a 5GB growth that needs to occur for the data file. Depending on your disk subsystem and whether or not you're using Instant File Initialization, it may take quote some time to grow your file (which could temporarily cause your entire DB to be unresponsive - as file growth is a very IO intensive operation)
It sounds like the percentage you had it set to resulted in a large growth rate, which honestly takes time to complete. When you changed it to a mere 500MB, the growth was pretty much instantaneous resulting in your DB coming back online 🙂
IMHO you should size your DBs large enough to AVOID growth (this operation should only occur RARELY). This will reduce the growth and will save you much time and effort 😀
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 9, 2013 at 2:37 pm
Thank you very much and I will look into sizing the database.
Jeff
January 11, 2013 at 11:16 am
how frequently the DB grows with 500MB growth settings
take this into consideration while setting auto growth option.
Regards
Durai Nagarajan
January 14, 2013 at 8:37 am
Very frequently so I have to increase the size but I do not know by how much at this point. I am trying to monitor my database sizes manually by running sp_databases and recording the sizes on different days and can come up with a growth rate.
I am noticing something strange. I have one database that is 48.3 MB and the mdf file is 9 MB and not sure why this would be. Do you have any idea.
Your help is appricated
Jeff
Jeff
January 14, 2013 at 8:41 am
Is the LDF file 39.3MB?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 14, 2013 at 9:05 am
Yes. That must be where the difference is. I guess the backup guys are not doing their job and the transaction log is not committing to the database. Correct?
Thanks for your help
Jeff
Jeff
January 14, 2013 at 9:12 am
Is the DB is simple, full, or bulk-logged mode?
If in bulk or full mode make certain you have transaction log backups running. The "standard" is typically every 15 minutes however, depending on your environment that may be too soon (the general rule of thumb is "how much data are you comfortable with losing?" :-D)
For more information on the Transaction Log, what it is, how it works, etc - Please refer to this excellent article[/url] by the Master, Gail Shaw
BTW everything is written/committed in the transaction log
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 14, 2013 at 9:44 am
Yes it is in Full Recovery Model, so I will be contact the backup group so ask what is happening. They are only using Backup Exec and no native SQL backup of transaction logs. I am planning to implement native backup job to augment the Backup Exec system.
Thanks for the help
Jeff
Jeff
January 14, 2013 at 1:49 pm
Haven't used Backup Exec in a few years but my guess is it doesn't perform transaction log backups at all (I'm sure it physically backs up the LDF file itself, but that's not the same). The DBA (is this you?) needs to create these manually...
If you don't create a maintenance plan to back them up then your log files will continue to grow
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 15, 2013 at 8:15 am
Everything above is good advice. I would add one thing. It might be a good idea to add and event notification so that you are emailed each time the database grows. It will help you catch growth before it gets out of hand.
Here is an example. The script should be run in 3 parts:
-- Using msdb prevents the need for certificate signing the
-- activation procedure to execute sp_send_dbmail across
-- databases
USE [msdb];
GO
-- Drop the notification if it exists
IF EXISTS ( SELECT *
FROM sys.server_event_notifications
WHERE name = N'CaptureAutogrowEvents' )
BEGIN
DROP EVENT NOTIFICATION CaptureAutogrowEvents ON SERVER;
END
-- Drop the route if it exists
IF EXISTS ( SELECT *
FROM sys.routes
WHERE name = N'AutogrowEventRoute' )
BEGIN
DROP ROUTE AutogrowEventRoute;
END
-- Drop the service if it exists
IF EXISTS ( SELECT *
FROM sys.services
WHERE name = N'AutogrowEventService' )
BEGIN
DROP SERVICE AutogrowEventService;
END
-- Drop the queue if it exists
IF EXISTS ( SELECT *
FROM sys.service_queues
WHERE name = N'AutogrowEventQueue' )
BEGIN
DROP QUEUE AutogrowEventQueue;
END
-- Create a service broker queue to hold the events
CREATE QUEUE [AutogrowEventQueue]
WITH STATUS=ON;
GO
-- Create a service broker service receive the events
CREATE SERVICE [AutogrowEventService]
ON QUEUE [AutogrowEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
-- Create a service broker route to the service
CREATE ROUTE [AutogrowEventRoute]
WITH SERVICE_NAME = 'AutogrowEventService',
ADDRESS = 'LOCAL';
GO
-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureAutogrowEvents]
ON SERVER
WITH FAN_IN
FOR DATA_FILE_AUTO_GROW, LOG_FILE_AUTO_GROW
TO SERVICE 'AutogrowEventService', 'current database';
GO
---------------------------------------------
-- Create the Activation Stored Procedure to Process the Queue
IF EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[ProcessAutogrowEvents]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[ProcessAutogrowEvents];
GO
CREATE PROCEDURE [dbo].[ProcessAutogrowEvents]
WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML;
DECLARE @message_sequence_number INT;
DECLARE @dialog UNIQUEIDENTIFIER;
DECLARE @email_message NVARCHAR(MAX);
WHILE ( 1 = 1 )
BEGIN
BEGIN TRANSACTION;
-- Receive the next available message FROM the queue
WAITFOR
(
RECEIVE TOP(1) -- just handle one message at a time
@message_body=CAST(message_body AS XML)
FROM dbo.AutogrowEventQueue
), TIMEOUT 1000; -- if queue empty for 1 sec, give UPDATE AND GO away
-- If we didn't get anything, bail out
IF ( @@ROWCOUNT = 0 )
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
DECLARE @EventType VARCHAR(128);
DECLARE @ServerName VARCHAR(128);
DECLARE @PostTime VARCHAR(128);
DECLARE @DatabaseName VARCHAR(128);
DECLARE @Duration VARCHAR(128);
DECLARE @GrowthPages INT;
SELECT @EventType = @message_body.value('(/EVENT_INSTANCE/EventType)[1]',
'varchar(128)') ,
@Duration = @message_body.value('(/EVENT_INSTANCE/Duration)[1]',
'varchar(128)') ,
@ServerName = @message_body.value('(/EVENT_INSTANCE/ServerName)[1]',
'varchar(128)') ,
@PostTime = CAST(@message_body.value('(/EVENT_INSTANCE/PostTime)[1]',
'datetime') AS VARCHAR) ,
@DatabaseName = @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]',
'varchar(128)') ,
@GrowthPages = @message_body.value('(/EVENT_INSTANCE/IntegerData)[1]',
'int');
-- Generate formatted email message
SELECT @email_message = 'The following autogrow event occurred:'
+ CHAR(10) + CAST('ServerName: ' AS CHAR(25))
+ @ServerName + CHAR(10) + CAST('PostTime: ' AS CHAR(25))
+ @PostTime + CHAR(10)
+ CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName
+ CHAR(10) + CAST('Duration: ' AS CHAR(25)) + @Duration
+ CHAR(10) + CAST('GrowthSize_KB: ' AS CHAR(25))
+ CAST(( @GrowthPages * 8 ) AS VARCHAR(20));
-- Send email using Database Mail
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Notify', -- your defined email profile
@recipients = 'itdba@company.com', -- your email
@subject = 'AutoGrow Event Notification',
@body = @email_message;
-- Commit the transaction. At any point before this, we could roll
-- back. The received message would be back on the queue AND the
-- response wouldn't be sent.
COMMIT TRANSACTION;
END
GO
---------------------------------------------
-- Alter the queue to use the activation procedure
ALTER QUEUE [AutogrowEventQueue]
WITH STATUS=ON,
ACTIVATION
(STATUS=ON,
PROCEDURE_NAME = [ProcessAutogrowEvents],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER);
GO
January 15, 2013 at 10:36 am
Schedule a log backup immediately, else you cant go for point in time restore and may have to loose data between the previous full backup and server crash(incase).
Regards
Durai Nagarajan
January 15, 2013 at 1:40 pm
Thanks. I will look into it
Jeff
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply