SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Quick and dirty script to ascertain datafile headroom

By Haden Kingsland (theflyingdba),

/* Author: Haden Kingsland (FlyingDBA) Date: 19th April 2017 Description: A quick script to ascertain the headroom in SQL Server data files based on current size of data against the max size set for the file. You WILL need to have a max size set for your data file for this to work! This script is offered by the "FlyingDBA" as is, with no warranties or guarantees and should be used with caution in any environment. The FlyingDBA takes no responsibility for environments not under his current control and advises that all scripts are run in non production environments to verify their usefulness prior to moving to production! */ create table #drives ( --servername varchar(100), driveletter varchar(1), MBFree int ) declare @drive varchar(100), @fname varchar(5) set @drive = 'F' set @fname = @drive + ':\%' print @drive print @fname insert into #drives exec xp_fixeddrives -- uncomment for SQL 2012 and above as you can use the new "concat" function --select driveletter, concat(MBFree,' MB') as 'MB Free', concat(MBFree/1024,' GB') as 'GB Free' from #drives -- uncomment for 2008 R2 and below, as you need to use {fn concat] instead! select driveletter, {fn concat (convert(varchar(10),MBFree),' MB')} as 'MB Free', {fn concat (convert(varchar(10),MBFree/1024),' GB')} as 'GB Free' from #drives where driveletter = @drive select af.name, af.filename, convert(bigint,af.size * 8/1024) as 'Current Size (MB)', convert(bigint,af.maxsize * 8 / 1024) as 'Max Size (MB)', convert(bigint,(af.maxsize - af.size) * 8 / 1024) as 'Headroom (MB)', convert(bigint,(af.maxsize - af.size) * 8 / 1024 / 1024) as 'Headroom (GB)' from sys.sysaltfiles af inner join sys.databases d on af.dbid = d.database_id where d.state_desc = 'ONLINE' -- only online databases and af.maxsize not in (-1,268435456) -- only check database files with a max size set! and af.filename like @fname order by 6 desc drop table #drives --exec xp_fixeddrives

Total article views: 284 | Views in the last 30 days: 8
 
Related Articles
FORUM

converting varchar to numeric

converting varchar to numeric

FORUM

convert varchar to numeric

convert varchar to numeric

FORUM

Help with converting varchar to datetime

Convert varchar to datetime

FORUM

Convert varchar to numeric

Please guide in Convert varchar to numeric

FORUM

Converting Getdate() To Varchar

Converting Getdate() to Varchar (16) to include Milliseconds

Tags
file size    
 
Contribute