SQL Server 2008 R2 – Disk Size Considerations

disk-spacesql-server-2008-r2

What's the rule of thumb for Disk size considerations for hosting a mirrored database that supports about 100 writes every second during peak. Current database full back up size is approx 150 MB.

Considering (separate) drives for

  • Data
  • Log
  • TempDb

Database size from exec sp_spaceused is (will take care of huge DB size, it's bloated because of mirroring I reckon)

database_name   database_size   unallocated space
-------------------------------------------------    
APP_PROD        88026.31 MB     0.00 MB

reserved    data        index_size  unused
--------------------------------------------    
122168 KB   118992 KB   2088 KB     1088 KB

Many thanks

Best Answer

You will need exactly the same disk space for your mirrored (secondary) database as for the primary database. They are the same and the secondary database will have the same growth rate as your primary database and should be provisioned the same.

SP_SPACEUSED will show you how much space your primary database is now using (you should make it larger and not rely on autogrowth), and you can grab growth information from the default trace to give you some limited information about how often the database has grown, you can then infer the growth rate from the autogrowth parameters. 

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass 
       WHEN 92 THEN 'Data'
       WHEN 93 THEN 'Log'
   END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
   EventClass IN (92,93)
ORDER BY
   StartTime DESC;
Related Question