Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

db autogrowth problems Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 8:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 295, Visits: 311
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
Post #1407285
Posted Tuesday, January 15, 2013 10:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
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
Post #1407372
Posted Tuesday, January 15, 2013 1:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, April 18, 2014 9:56 AM
Points: 85, Visits: 231
Thanks. I will look into it

Jeff
Post #1407477
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse