Database Size

Comments

• Options

  Savatage Member Posts: 7,142

  2011-04-12

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

  Harry Ruiz
  http://www.BiloBeauty.com
  http://www.autismspeaks.org

  0
• Options

  Horse06 Member Posts: 496

  2011-04-12

  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!

  0
• Options

  kriki Member, Moderator Posts: 9,090

  2011-04-13

  [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.

  Regards,Alain Krikilion
  No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
  
  

  0
• Options

  stryk Member Posts: 645

  2011-04-13

  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

  Jörg A. Stryk (MVP - Dynamics NAV)
  NAV/SQL Performance Optimization & Troubleshooting
  STRYK System Improvement
  The Blog - The Book - The Tool

  0