SQL Server Performance – Separating DevOps Instance Hard Drives

database-designperformanceperformance-tuningsql serversql-server-2012

We currently have SQL Server Dev, QA, and StagingUAT instances sitting on same VM. The production resides in its own server.

The issue is, if one database MDF or TransactionLog on Dev, suddently consumes too much space, it wipes out hard drive space from all other Devops instances. They all share the same drive. This is a single point of failure. I inherited this legacy system.

Currently
The Dev, QA, and StagingUAT Database MDF have the same G:\ drive.

Dev Databases: on G:\Dev

QA Databases on G:\QA

Staging Databases on G:\Staging

Transaction Logs are on:

Dev Database: on H:\Dev

QA Databases on H:\QA

Staging Databases on H:\Staging

What is the best way to prevent issue of one instance suddenly takes too much space?

Method 1:
I recommended each Devops Instance having its own drive.

MDF Database:

Dev Database: on A:\Dev

QA Databases on B:\QA

Staging Databases on D:\Staging (skip C:)

Transaction Log:

Dev Database: on E:\Dev

QA Databases on F:\QA

Staging Databases on G:\Staging

Method 2:

Have each subfolder have a max hard drive space it can consume.
G:\Dev can only take 10 GB max.

G:\QA can only take 10 GB max.

G:\Staging can only take 10 GB max.

We have current rule, databases on the same Devops instance should be on same drive. Currently we can only provision 1 VM for these devops instances.

Just curious best way to resolve this issue. When creating Devops instances,should each instance be separated from other instances?

Best Answer

For each of the databases you can set a max size per file. Post about setting grow and file size

Here is Microsoft’s details on the alter file command

The advantages of doing it this way is that you can set each database to have a different file size and you don’t have to go to the trouble of creating the new drives and moving each database.

You can do this through click ops in the files section of each database setting the max file size for each file.

Alternatively you can script out the statements by doing a select on the sys.files table with some string building. 

declare @usage table(Fileid int, FileGroup int, TotalExtents float, UsedExtents float, Name sysname, FileName nchar(520))
insert  @usage exec('dbcc showfilestats');
select [name], ((totalextents * cast(64 as float))/1024) [total]
,((usedextents * cast(64 as float))/1024) [used]
,((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024) [free]
, ((totalextents * cast(64 as float))/1024) - (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) + ( (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) * .3  ) [reduce]
,(1-(((usedextents * cast(64 as float))/1024) / ((totalextents * cast(64 as float))/1024))) [free %]
, 'alter database ' + DB_NAME() + ' modify file (name=' + rtrim([name]) + ', MAXSIZE = 50MB);' [Set Max File]
,'DBCC shrinkfile ([' + [name] + '],' + 
    cast(
        cast((((usedextents * cast(64 as float))/1024) + ( (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) * .3  ))
        as int)
     as varchar)
+ ');' [shrink]
, len(substring([name], 1, 36))
--, (select gender + ' ' + sport from v_sportseason where id = substring([name], 1, 36))
, [filename]
, 'alter database ' + DB_NAME() + ' modify file (name=' + rtrim([name]) + ', filename=''' + rtrim([filename]) +''');' [Move File]
 from @usage
Related Question