You are here

database files

MySQL - Move Data Files To New Location (Windows)

After setting up MySQL, I realized that I put my data files on the C drive.  Since I don't want database growth to end up taking down the entire OS, I had to move them.  My struggles consumed a couple of hours, so I wanted to clear up and document the process.

Default Database Files

MySQL (5.7) stores the default configuration files at:
C:\ProgramData\MySQL\MySQL Server 5.7\

To verify where your files are stored, simply run this MySQL script:
 

select @@datadir;

Moving Database Files to Another Location

Although moving database files around is simple, it is a still big deal.  When you do this, you are moving the entire database contents to another location.  If you have the space, a backup beforehand wouldn’t be a bad idea.  This is the script that I use to make this happen.

Database File Space and Free Space

/*
 
    Author: Ben Farnsworth
    Date:   1/7/14
     
    Get info on Data files
 
*/
IF OBJECT_ID('tempdb..#space') IS NOT NULL DROP TABLE #space
create table #space(
    name varchar(100)
    ,filename varchar(200)
    ,filesizeMB decimal(12,2)
    ,spaceusedMB decimal(12,2)
    ,freespaceMB decimal(12,2)
)
 
exec sp_msforeachdb @command1 = '
use [?]
INSERT INTO #space
select
      name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB

Shrinking Database Files With Excess Free Space

This script is intended for environments where space constraints require the space to be closely maintained. This is not a good script to run as part of a normal production maintenance plan, unless you adjust the variables to high enough levels that it only catches extreme cases. Shrinking a database takes a lot of time and CPU power and won’t help performance.

This script does include an index rebuild at the end as indexes become very fragmented after this process.

This is not thoroughly tested yet.

SQL Server – Get Size of Database Files

This script gets the size of each database file, not including backups. Essentially, you’ll see .MDF, .NDF, and .LDF files.

SELECT DISTINCT bus.database_name
, (buf.file_size / (1024*1024)) AS [File Size MB]
, buf.physical_name
FROM msdb..backupfile buf
INNER JOIN msdb..backupset bus
ON buf.backup_set_id = bus.backup_set_id
ORDER BY bus.database_name
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
1 + 1 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer