Breaking A Database Into Smaller Files

Jana Sattainathan shows how to break a SQL Server database into smaller files, as well as giving some reasons why you might want to do that:

You are probably reading this post because you have experienced the pain yourself and I dont want to waste anymore of your time and get right to the steps involved in breaking up a huge database or a datafile

  1. Check the space situation on your host

  2. Get the space usage by files for the big database/datafile in question

  3. Decide on number of files to add/location

  4. Add multiple secondary datafiles

  5. Distribute data from big datafile into the new datafiles using EMPTYFILE option

  6. Shrink the big datafile and set a maximum size

  7. Change the default filegroup

Read on for Jana’s step-by-step approach.

Related Posts

Azure Without ARM

Ed Elliott gives us a few ways of deploying Azure resources without using ARM templates: So, what are our options? Create/Edit/Delete ourselves using Powershell/.Net/Python/Go/Java/Some Other SDK Process something else (YAML?) into JSON Generate the ARM using c#/Powershell/something else 3rd party tools, (Terraform is the big daddy) / others include Sparkle Formation To be honest, I’d […]

Read More

Checking A Drive’s Allocation Unit Size

Ryan Adams shows how to find the allocation unit size for a disk volume: To identify the allocation unit size for a volume, we can use the fsutil.exe utility.  In the output you are looking for “Bytes Per Cluster” which is your allocation unit size. Here is an example to retrieve the information for the […]

Read More

Categories

August 2017
MTWTFSS
« Jul Sep »
 123456
78910111213
14151617181920
21222324252627
28293031