InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 19:59:56 GMT20http://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxThanks. I will look into itTue, 15 Jan 2013 13:40:07 GMTjayoub1http://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxSchedule 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).Tue, 15 Jan 2013 10:36:48 GMTdurai nagarajanhttp://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxEverything 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-- databasesUSE [msdb];GO-- Drop the notification if it existsIF EXISTS ( SELECT * FROM sys.server_event_notifications WHERE name = N'CaptureAutogrowEvents' ) BEGIN DROP EVENT NOTIFICATION CaptureAutogrowEvents ON SERVER; END-- Drop the route if it existsIF EXISTS ( SELECT * FROM sys.routes WHERE name = N'AutogrowEventRoute' ) BEGIN DROP ROUTE AutogrowEventRoute; END-- Drop the service if it existsIF EXISTS ( SELECT * FROM sys.services WHERE name = N'AutogrowEventService' ) BEGIN DROP SERVICE AutogrowEventService; END-- Drop the queue if it existsIF EXISTS ( SELECT * FROM sys.service_queues WHERE name = N'AutogrowEventQueue' ) BEGIN DROP QUEUE AutogrowEventQueue; END-- Create a service broker queue to hold the eventsCREATE QUEUE [AutogrowEventQueue]WITH STATUS=ON;GO-- Create a service broker service receive the eventsCREATE SERVICE [AutogrowEventService]ON QUEUE [AutogrowEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);GO-- Create a service broker route to the serviceCREATE ROUTE [AutogrowEventRoute]WITH SERVICE_NAME = 'AutogrowEventService',ADDRESS = 'LOCAL';GO-- Create the event notification to capture the eventsCREATE EVENT NOTIFICATION [CaptureAutogrowEvents]ON SERVERWITH FAN_INFOR DATA_FILE_AUTO_GROW, LOG_FILE_AUTO_GROWTO SERVICE 'AutogrowEventService', 'current database';GO----------------------------------------------- Create the Activation Stored Procedure to Process the QueueIF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ProcessAutogrowEvents]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) DROP PROCEDURE [dbo].[ProcessAutogrowEvents];GOCREATE PROCEDURE [dbo].[ProcessAutogrowEvents] WITH EXECUTE AS OWNERAS 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 queueWAITFOR ( 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; ENDGO----------------------------------------------- Alter the queue to use the activation procedureALTER QUEUE [AutogrowEventQueue] WITH STATUS=ON, ACTIVATION (STATUS=ON, PROCEDURE_NAME = [ProcessAutogrowEvents], MAX_QUEUE_READERS = 1, EXECUTE AS OWNER);GOTue, 15 Jan 2013 08:15:28 GMTNoetic DBAhttp://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxHaven'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 growMon, 14 Jan 2013 13:49:18 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxYes 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 helpJeffMon, 14 Jan 2013 09:44:31 GMTjayoub1http://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxIs 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 [url=http://www.sqlservercentral.com/articles/Administration/64582/]excellent article[/url] by the Master, Gail ShawBTW everything is written/committed in the transaction logMon, 14 Jan 2013 09:12:15 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxYes. 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 helpJeffMon, 14 Jan 2013 09:05:38 GMTjayoub1http://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxIs the LDF file 39.3MB?Mon, 14 Jan 2013 08:41:03 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxVery 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 JeffMon, 14 Jan 2013 08:37:07 GMTjayoub1http://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxhow frequently the DB grows with 500MB growth settingstake this into consideration while setting auto growth option.Fri, 11 Jan 2013 11:16:26 GMTdurai nagarajanhttp://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxThank you very much and I will look into sizing the database. Wed, 09 Jan 2013 14:37:44 GMTjayoub1http://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxI 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 :-DWed, 09 Jan 2013 13:40:55 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1404951-146-1.aspxOn 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 SP2SQL Server Standard x64 2005Database sizes 188GBThe 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 appreciatedWed, 09 Jan 2013 12:38:57 GMTjayoub1