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… Read More »
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. /* –PROCESS FOR MOVING… Read More »
/* 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… Read More »
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… Read More »
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