database files

Moving Database Files to Another Location

ben himself's picture

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.

Tags: 

Database File Space and Free Space

ben himself's picture
/*
 
    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

Tags: 

Shrinking Database Files With Excess Free Space

ben himself's picture

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.

Tags: 

SQL Server – Get Size of Database Files

ben himself's picture

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

Tags: 

Pages

Subscribe to RSS - database files