Well, the first rule should be:
AUTOGROWTH HAS TO BE AVOIDED!AG should just happen as some kind of
emergency solution, e.g. adding a chunk to the db-files to continue working.
If AG happens too often, the db files
fragment physically, and additionall the Log fragements
logically, means an overhead of
VLogs is created.
IF Autogrowth happens, THEN it should be preferred to add a
fixed size chunk; not progressive. Adding something to a database file (mdf/ndf) should be a matter of split-seconds, as only information in the "
File Allocation Table" is updated; nothing really happens to the physical file. Thus, it should be failsafe to add a decent chunk, e.g. 500MB to 1000MB.
Adding a chunk to the log (ldf) will indeed result in an
initialisation of the added part; hence, depending on the chunk size this couold take a while longer (I'd say 10 to 30 seconds per 1000MB).
Of course, all this depends on the
technical specifications of the disk-subsystem!
So I suggest to create the db files
as large as possible, as sufficient as possible. The log file should be sized to the estimated maximum (which e.g. also depends on REcovery Model and backup frequency).
Thus, if the db-files are sufficiently sized, I prefer to have a job which monitors the
fill-degree of these files (see below, this is part of the "
NAV/SQL Performance Toolbox"
).
Once the amount of free space drops below 20% it forces an error, which will send a
notification to an Operator; thus, a human being could take care about file sizes etc.:
-- replace the term DATABASENAME with the name of the actual database before executing this script
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'STRYK System Improvement' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'STRYK System Improvement'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SSI: File Usage (DATABASENAME)',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Monitors DB file usage and raises error if free space is less than 20 percent',
@category_name=N'STRYK System Improvement',
@owner_login_name=N'sa',
@notify_email_operator_name=N'SSI: PTB Admin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'db',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
SET STATISTICS IO OFF
SET NOCOUNT ON
DECLARE @size_kb int, @used_kb int, @free_perc decimal(18,2), @cmd nvarchar(250)
CREATE TABLE #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
INSERT #tmpspc EXEC (''dbcc showfilestats with no_infomsgs'')
SELECT
@size_kb = SUM(s.size * CONVERT(float,8)),
@used_kb = SUM(CAST(tspc.UsedExtents * convert(float,64) AS float))
FROM sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 AND s.database_id = db_id() AND (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE (CAST(cast(g.name as varbinary(256)) AS sysname)=N''Data Filegroup 1'')
DROP TABLE #tmpspc
declare @max_idx bigint
-- select @max_idx = max(page_count) * 8 / 1024
-- from sys.dm_db_index_physical_stats (db_id(), 0, null, null, ''limited'')
-- select @max_idx as [max_IdxSize (MB)], @max_idx * 1.5 as [min_FreeDbSpace (MB)]
SET @free_perc = ((convert(decimal(18,2), @size_kb) - convert(decimal(18,2), @used_kb)) / convert(decimal(18,2), @size_kb) * 100)
IF @free_perc < 20 BEGIN
SET @cmd = ''Warning: Low free space in database "'' + db_name() + ''", File Size (MB): '' + convert(varchar(15), @size_kb / 1024) +
'', Used Space (MB): '' + convert(varchar(15), @used_kb / 1024) +
'', Free Space (MB): '' + convert(varchar(15), (@size_kb - @used_kb) / 1024) +
'', Free Space (Percent): '' + convert(varchar(15), @free_perc) + char(13) + char(10) +
''Expand the file size to have at least 20 Percent free space.''
RAISERROR(@cmd, 19, 1) WITH LOG
END ELSE
SELECT ''Database "'' + db_name() + ''", File Size (MB): '' + convert(varchar(15), @size_kb / 1024) +
'', Used Space (MB): '' + convert(varchar(15), @used_kb / 1024) +
'', Free Space (MB): '' + convert(varchar(15), (@size_kb - @used_kb) / 1024) +
'', Free Space (Percent): '' + convert(varchar(15), @free_perc)
',
@database_name=N'DATABASENAME',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=6,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110308,
@active_end_date=99991231,
@active_start_time=20000
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
As mentioned before,
20% free space is sufficient best-practices. Actually SQL Server needs this free space to re-sort indexes in case of index maintenance (ALTER INDEX): it writes the index into the free space and writes it back defragmented in optimized order (this is similar like defragmenting a hard-disk-drive).
[This only happens if not using the "Sort in tempdb" option]So basically one could calculate exactly the amount of free space really required:
it's
1.5 times the size of the largest index (the script above contains some commented-out lines which would do this).
For example, if the largest index is 10GB, then you'll need 15GB free space to defragment this index
completely. Of course, this is a worst case scenario, so I reccommend to stick to the 20% best-practice rule.
NEVER USE AUTOSHRINK (in a productive system)! Together with "Auto Growth" this would be nothing but a "disk-benchmark". Also, AutoShrink could re-sort indexes upside-down, thus causing additional fragmentation.
Cheers,
Jörg
Login
Register
RSS Feed
Rules
FAQ
Search the forum
