Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We do have some maintenance plan for our sql server 2008 r2 express. Every month we do defragment of the database if any table has page count more tahn 50 for any table and average fragmentation more than 20.

If the database log size>2 MB, then the recovery mode is made as simple, and it is shrinked, and the recovery mode is set back to FULL. If the Page_count>50 and avg_fragmentation_in_percent > 30 then the index is REBUILD.

And if Page_count>50 and avg_fragmentation_in_percent > 5 and <30 then the index is REORGANIZE.

This is what we are doing till now.But we found that autogrowth events are resource incentive and it should not happen repeatedly. Now for all database autogrowth is set to MB for mdf file and 10% for ldf file which is default value while creating new database.We are planning to increase the autogrowth values for the database depending on how much database is getting bigger every day.But i want to know how much autogrowth events is ideal for the database.Should i set autogroth so that it happens only once a day,week or month etc.So please help me to set the autogrowth value for my database. Also there is another problem.If i do monthly defragmentation of database then it will be shrinked. So after this for all database for which i did shrink autogrowth occurs once when new data is written to it.So there will be so many autogrowth events. So whether it will be a problem? Please tell me a solution.

share|improve this question
add comment

2 Answers

if any table has page count more [than] 50 [pages]

That is still extremely small. I typically don't take fragmentation into account for any index with less than 1000 pages.

and avg_fragmentation_in_percent > 30 then the index is REBUILD

That's a good threshold for rebuilds, but I'd also consider reorganizing indexes with fragmentation in the range of 5% to 30%.

If the database log size>2 MB, then the recovery mode is made as simple, and it is shrinked, and the recovery mode is set back to FULL

Why are you doing this? Why is it a problem if a log file is greater than 2 MB? That's extremely small. Not to mention, when you switch to SIMPLE, shrink, and then back to FULL recovery you are breaking the log chain. You could get yourself into serious trouble for point in time recovery. Size your log files appropriately, and allow for autogrowth only in the case of emergency. Shrinking a database file should only be done in extremely corner cases, and by no means part of a routine maintenance schedule.

But we found that autogrowth events are resource incentive and it should not happen repeatedly

Correct. That is where sizing comes into play. Monitor and get alerted when a certain percentage of space is being used. That way you can plan your manual file growth operations for a maintenance window to minimize end-user impact.

and 10% for ldf file which is default value while creating new database

Even though a percentage file growth is default, I recommend against it. You don't want to have a variable amount of file growth. If you must fall back on autogrowth, then you want to know exactly how much the file will grow by. Not to mention, with growth of a log file you will have a variable amount of virtual log files (VLF) with the additional space. You want to smartly plan this, and this can be done through a fixed amount of file growth.

Should i set autogroth so that it happens only once a day,week or month etc

Set autogrowth for a fixed amount of space (not percentage), and only you can determine what is a good amount. Too small, you will have frequent autogrowth with minimal end user impact, but happening often. Autogrowth set too high, then you'll have less frequent growths but when it does happen the impact will be for a longer duration.

My recommendation? Monitor the space used in your database files. When it hits a certain percentage (say, 80% space used) you should be alerted. And then schedule a manual growth during a maintenance window.

share|improve this answer
3  
Also if you are going to have autogrowth turned on you should look into Instant File Initialization. It only works for data files but it will make those growth event's far less intensive. –  Kenneth Fisher Jul 19 '13 at 16:43
2  
@KennethFisher I 1000% agree with you. IFI is a quick and easy huge gain with data files. –  Thomas Stringer Jul 19 '13 at 16:44
    
@ThomasStringer can you please tell how often autogrowth should occur? Just to set the autogrowth for my databases i wanted to know it.(I know there is no particular rules that autogrowth can happen only limited time). –  IT researcher Jul 22 '13 at 9:46
    
@ThomasStringer After shrinking the database i do rebuild or reorganize the index.Also i take full backup of the database too so that logchain won't break. –  IT researcher Jul 22 '13 at 10:08
add comment

Thomas Stringer's points are all you need for your answer. But I would put some more insight on how you determine your autogrowth settings ?

But i want to know how much autogrowth events is ideal for the database.Should i set autogroth so that it happens only once a day,week or month etc.So please help me to set the autogrowth value for my database. Also there is another problem.If i do monthly defragmentation of database then it will be shrinked. So after this for all database for which i did shrink autogrowth occurs once when new data is written to it.So there will be so many autogrowth events. So whether it will be a problem?

There is no mathematical formula to calculate your autogrowth settings, especially when you do not do a baseline of you databases.

Now, as @ThomasStringer pointed out that you should not allow your database autogrowth to be in %, rather set it to MB, you can find out Autogrowth events happening on your server instance using the default Trace.

--below code will help you in finding autogrowth events on your server instance.
IF OBJECT_ID('tempdb..#autogrowthTotal') IS NOT NULL
    DROP TABLE #autogrowthTotal;

IF OBJECT_ID('tempdb..#autogrowthTotal_Final') IS NOT NULL
    DROP TABLE #autogrowthTotal_Final;

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1
    AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.', @filename);
SET @ec = CHARINDEX('_', @filename) + 1;
SET @efn = REVERSE(SUBSTRING(@filename, 1, @bc));
SET @bfn = REVERSE(SUBSTRING(@filename, @ec, LEN(@filename)));
-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT ftg.StartTime
    ,te.NAME AS EventName
    ,DB_NAME(ftg.databaseid) AS DatabaseName
    ,ftg.[FileName] AS LogicalFileName
    ,(ftg.IntegerData * 8) / 1024.0 AS GrowthMB
    ,(ftg.duration / 1000) AS DurMS
    ,mf.physical_name AS PhysicalFileName
INTO #autogrowthTotal
FROM::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
INNER JOIN sys.master_files mf ON (mf.database_id = ftg.databaseid)
    AND (mf.NAME = ftg.[FileName])
WHERE (
        ftg.EventClass = 92 -- Data File Auto-grow
        OR ftg.EventClass = 93
        ) -- Log File Auto-grow
ORDER BY ftg.StartTime

SELECT count(1) AS NoOfTimesEventFired
    ,CONVERT(VARCHAR(10), StartTime, 120) AS StartTime
    ,EventName
    ,DatabaseName
    ,[LogicalFileName]
    ,PhysicalFileName
    ,SUM(GrowthMB) AS TotalGrowthMB
    ,SUM(DurMS) AS TotalDurationMS
INTO #autogrowthTotal_Final
FROM #autogrowthTotal
GROUP BY CONVERT(VARCHAR(10), StartTime, 120)
    ,EventName
    ,DatabaseName
    ,[LogicalFileName]
    ,PhysicalFileName
--having count(1) > 5 or SUM(DurMS)/1000 > 60 -- change this for finetuning....
ORDER BY CONVERT(VARCHAR(10), StartTime, 120)

SELECT *
FROM #autogrowthTotal_Final

Below will be the output

enter image description here

Note: I have highlighted in the image what each column means and what you should look for.

Basically, you have to monitor your autogrowth events for a duration of time e.g. during high activity or for you entire business cycle and then averaging it will give you a some what exact value that you can choose for autogrowth settings.

Now, for Log file, you also have to consider factors like Index maintenance, CHECKDB running, etc. So size the log file to support the volume of data changes occurring in the database and take log backups frequently so as to allow rapid reuse of space within the log file.

Also, worth to mention that you should enable Instant File Initialization as well. Works only for Data files !

Refer to Importance of data file size management esp the Data file growth and Data file shrinking by Paul Randal.

Note: Do not shrink your database, unless you do a massive purge of your data and you are sure that the database is not going to grow that big again. It causes fragmentation and databases are meant to grow !

share|improve this answer
    
I find the default trace disabled most of the time. I find it easier to get log growth with DMV in SQL 2005+ or sysperfinfo in SQL 2000: sqlblog.com/blogs/jonathan_kehayias/archive/2009/06/06/… –  Shawn Melton Jul 19 '13 at 23:46
    
@ShawnMelton you have to consider benefits of default trace as well. DMV's are great in 2005 and up, but they get reset everytime sql server restarts or even when database if offlined. Default trace is enabled by default and its a very light weight trace with max of 5 files and they roll-over as time passes. Refer to simple-talk.com/sql/performance/…. Note: I am not saying that you should not use DMV's, but the same info can be obtained with less calculation and coding, why not leverage it ! –  Kin Jul 20 '13 at 1:40
    
Also, it wont give you the start and end time of the events happened. –  Kin Jul 20 '13 at 1:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.