Database Size

Horse06

Hi expert, I have a question on the database size. Our database is running in SQL server. Currently the usage of the database is 60%. Even though I searched the forum and read the menu, I am little timid of expanding the database size.
The database files for MDF is 1200 and file growth is 10% and for NDF is 3766 and it has the same file growth as the MDF. I regularly optimize the tables.Could someone advise? Appreciate it!

Savatage

I like to keep the usage to around 70-75% myself.

Horse06

So it should be OK with 60% usage. If we want to reduce the size, how, even though the menu mentions about it, but not detailed? Thank you!

kriki

[Topic moved from 'NAV Tips & Tricks' forum to 'SQL General' forum]

60% is ok. My general rule is to keep it under 80%.

Shrinking is something I only do on development servers. On production ONLY if the data has shrunk a lot and I know that the free space will never be reused.

Do not use filegrowth in %, but use a fixed size (I generally use 50MB). BUT automatic filegrowth should ONLY be used as a failsafe. Not as something to be relied upon. Growing a database file should be done manually at a moment it can be done (and preferably more than just 50MB). After growing a DB, check if the file is still in 1 piece otherwise defrag for max performance.

stryk

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" 8) ).
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