Category Archives: InTheFileSystem

List Space used in the File System

Shrink a Data File

Normally, you would never want to shrink data files. Not even in case of emergency when running out of space. If you were to shrink the data file, the database would simply grow again and you end up with the same problem. If the database grew, it is because the database needed the space. If it is partially empty, it is because some data was removed, however, it is very likely that it would be needed again so it is a bad idea to shrink it again. If there was an error that caused the database to grow, or there was a change in process, or something extremely rare happened, you might need to shrink a database file. Since shrinking a database will hit performance and possible introduce some locks on the tables, if you must do it, it is better to do it little by little, using a script similar to this below. Remember, after the shrink is done, your indexes will be fragmented, so it is recommended to rebuild all your indexes (and hence the DB will grow a little)

In order to use this script, you should first find if there is space available on the data files, and find the logical name of the data file to shrink.

To do that, run this script first http://www.cookingsql.com/2016/01/data-file-size-and-free-space-one-database/ and use its output to run the script below.

 

/*
DESCRIPTION   Shrink a single DATA file, in increments.
It will loop to execute the DBCC SHRINKFILE command to shrink the database file by the desired increment
until it reaches the target free space.


CONFIGURATION    TOP.
Run this script in the database with the file to be shrunk.
1.  Make sure to set the USE statement for the correct database
2. Set @DBFileName  Enter the Logical File name (not the physical name)
3. Set @TargetFreeMB to the desired file free space in MB after shrink.
4. Set @ShrinkIncrementMB to the increment to shrink file by in MB

 

Compatibility list:
MSSQL2005
MSSQL2008/SQL2008R2
MSSQL2012
MSSQL2014 
 
*/
SET NOCOUNT ON


declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

/*------ CONFIGURATION ---------*/

--USE DATABASE 

-- Set Name of Database file to shrink
set @DBFileName = 'AdventureWorks2012_Data2'

-- Set Desired file free space in MB after shrink
-- Remember, it is not the target file size but how much empty space will be left after the shrink operation
-- @SizeMB = @TargetFreeMB + @usedSpace
set @TargetFreeMB = 1300

-- Set Increment to shrink file by in MB
-- Make sure the @TargetFreeMB + @ShrinkIncrement is bigger than @EmptyDB
set @ShrinkIncrementMB = 300

/*------ END CONFIGURATION ---------*/

-- Declare variables
declare @sql varchar(8000)
declare @SizeMB int
declare @EmptyMB int
declare @UsedMB int

-- Check Input Values

IF NOT EXISTS (select name from sysfiles where name = @DBFileName)
begin
print 'The Data File does not exist on the current database. You either did not set
the USE statement or did not specify the Logical File name correctly
Terminating Batch'
GOTO EOL
end

select
@SizeMB = CAST(size/128.0 AS int)
,@UsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed' ) AS int)/128.0
,@EmptyMB = @SizeMB - @UsedMB
 
FROM sys.database_files
where name = @DBFileName



IF @EmptyMB < @TargetFreeMB
BEGIN
print 'Cannot proceed with operation because the target free space: ' +cast(@TargetFreeMB as nvarchar(10)) +'MB (user entry)
is bigger than the current free space: '+cast(@EmptyMB as nvarchar(10))+'MB
Terminating Batch'
GOTO EOL
END

IF @EmptyMB < @TargetFreeMB+@ShrinkIncrementMB
BEGIN
print 'Cannot proceed with operation because the Shrink Increment: ' +cast(@ShrinkIncrementMB as nvarchar(10)) +'MB (user entry)
Plus the Target Free '+cast(@TargetFreeMB as nvarchar(10))+'MB is bigger than the empty Space '+cast(@EmptyMB as nvarchar(10))+'MB
Terminating Batch'
GOTO EOL
END

 
declare @loopExit int
set @loopExit =  0


-- Loop until file at desired size
while  (@SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB) and (@loopExit < 10)
	begin

		set @sql =
		'dbcc shrinkfile ( '+@DBFileName+', '+convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) WITH NO_INFOMSGS'

		print 'Start ' + @sql + ' at '+convert(varchar(30),getdate(),121)
		exec ( @sql )

		print 'Done at '+convert(varchar(30),getdate(),121)

		-- Sets again Original Values
		select
		@SizeMB = CAST(size/128.0 AS int)
		,@UsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed' ) AS int)/128.0
		,@EmptyMB = @SizeMB - @UsedMB
		,@loopExit = @loopExit+1
		FROM sys.database_files
		where name = @DBFileName

	end
------------------------------------

IF (@loopExit = 10)
BEGIN
PRINT 'The SHRINK FILE operation completed 10 loops
This could mean that the file is not shrinking
or you need to increase the "shrink increment value"
Proceed with CAUTION
'
GOTO EOL
END

-- Show Size, Space Used, Unused Space, and Name of all database files


SELECT   
b.name
, b.type_desc
, CAST(b.size/128.0 AS int) 'SizeMB'  
, CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, 'SpaceUsed' ) AS int)/128.0 AS int) 'SpaceUsedMB'  
, ceiling(b.size/128.0 -CAST(FILEPROPERTY(b.name,'SpaceUsed' ) AS int)/128.0) 'FreeSpaceMB'
, CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,'SpaceUsed' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + '%'  'FreeSpace'

,  CASE
	WHEN b.is_percent_growth = 0 THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + ' MB, '  
    ELSE 'By ' + CAST(b.growth AS VARCHAR) + ' percent, '
 END
 + CASE  
    WHEN b.max_size = -1 THEN 'unrestricted growth'
	ELSE 'restricted growth to ' + LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + ' MB'  
    END AS Autogrow
, cast(b.max_size/128.0 as int) 'MaxSize'
, physical_name
 
FROM sys.database_files b



EOL:


 

Location of Data files and Tlog

.


/*

-- SQL Server 2008 and R2 Diagnostic Information Queries
-- Glenn Berry
-- June 2012
-- Last Modified: June 19, 2012
-- http://sqlserverperformance.wordpress.com/
-- http://sqlskills.com/blogs/glenn/
-- Twitter: GlennAlanBerry


DESCRIPTION:  List location of all files for all databases

Look for tempDB and  Tlog sharing the same drives as a source of I/O contention issues


 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:

*/

 

 

SELECT

DB_NAME([database_id])AS [Database Name]

,   [file_id]

, name

, physical_name

, type_desc

, state_desc

,  CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

.

Last Autogrowth Event

.


/*
DESCRIPTION:    Queries the default trace to find out last AUTOGROWTH event

The default trace should be enabled by, well, default

Check if it is enabled:  select status, path from sys.traces where is_default = 1

How far back does it report?

The default trace creates up-to 5 files, 20 mb in size. So depending on how busy the server is, it could be 20 mins or days



Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:
 
*/

 
 
Use master
go
 
declare @curr_tracefilename varchar(500) ;  
declare @base_tracefilename varchar(500) ;  
declare @indx int ;
 
select @curr_tracefilename = path from sys.traces where is_default = 1 ;  
 
set @curr_tracefilename = reverse(@curr_tracefilename);
 
select @indx = patindex('%\%', @curr_tracefilename) ;
 
set @curr_tracefilename = reverse(@curr_tracefilename) ;
 
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;  
 
 
select  
 loginname
 , spid
 --,convert(int, EventClass) as EventClass,  
,DatabaseName
,[Filename]
,Duration
, StartTime
, EndTime
, (IntegerData*8.0/1024) as ChangeInSize
, e.name
, RIGHT(@curr_tracefilename,@indx) as curr_tracefilename
 
from ::fn_trace_gettable(@base_tracefilename, default ) a
INNER JOIN sys.trace_events e    ON eventclass = trace_event_id

where  
            e.name = 'Log File Auto Grow'
        OR  e.name = 'Log File Auto Shrink'
        OR  e.name = 'Data File Auto Grow'
        OR  e.name = 'Data File Auto Shrink'
 
order by StartTime desc ;  
GO

.

SQL Job to delete old files

This scripts builds up on the idea of using ForFiles. See

http://www.cookingsql.com/2014/12/batch-file-to-remove-old-files/

/*
DESCRIPTION:
Creates a JOB to delete files of a certain age on a folder, based on EXTENSION
 
CONFIGURATION
Plenty
SET @days = 30   for the age of the file, in days
SET @NAME = 'IBM_Cleanup'  for the name of the job
SET @ext = 'txt'  for the extension of the files to delete.

The DEFAULT FOLDER is the location for your ERRORLOG folder, but it can be customized

After running the script, you still need to schedule the job

Author: Miguel Quintana

LIMITATION:
It does not do subfolders

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000
*/

 

 
declare @cmd nvarchar (1000)
DECLARE @sqlerrorlog TABLE (logdate DATETIME, processinfo SYSNAME, logtext VARCHAR(MAX))
DECLARE @path VARCHAR(300)
DECLARE @LOG_FOLDER varchar(300)
DECLARE @days varchar(4)
DECLARE @NAME varchar(100)

DECLARE @ext varchar (4)


/*
This section sets the ErrorLog folder as destination to delete file.
If you would like to set your own path, comment out this section.
and use the  SET @LOG_FOLDER  section
 
 
*/
SET @days = 30
SET @NAME = 'IBM_Cleanup'
SET @ext = 'txt'


--/*
INSERT INTO @sqlerrorlog EXEC xp_readerrorlog 0, 1
SELECT @path=logtext FROM @sqlerrorlog WHERE logtext LIKE '%Logging SQL Server messages in file%'

SET @LOG_FOLDER= SUBSTRING(@path, CHARINDEX( '''', @path) + 1,LEN(@path)-CHARINDEX( '''', @path) - CHARINDEX( '\', REVERSE(@path)))
-- */
 
-- only use this line below if you do not want the errorlog as your default folder
-- SET @LOG_FOLDER = 'D:\folder_here'
 
 
/*  DO not change below this line */
 

set @cmd=N'cmd /q /c "For /F "tokens=1 delims=" %v In (''ForFiles /P "'+@LOG_FOLDER+'" /m *.'+@ext+' /d -'+@days+' 2^>^&1'') do if EXIST "'+@LOG_FOLDER+'"\%v echo del "'+@LOG_FOLDER+'"\%v& del "'+@LOG_FOLDER+'"\%v"' 

 
--print @cmd
 
USE [msdb]
 
 
/****** Object:  Job [IBM_CommandLog Cleanup]    Script Date: 04/02/2014 14:16:35 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 04/02/2014 14:16:35 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@NAME,  
        @enabled=1,  
        @notify_level_eventlog=2,  
        @notify_level_email=0,  
        @notify_level_netsend=0,  
        @notify_level_page=0,  
        @delete_level=0,  
        @category_name=N'Database Maintenance',  
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [output_text]    Script Date: 04/02/2014 14:16:35 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'output_text',  
        @step_id=1,  
        @cmdexec_success_code=0,  
        @on_success_action=1,  
        @on_success_step_id=0,  
        @on_fail_action=2,  
        @on_fail_step_id=0,  
        @retry_attempts=0,  
        @retry_interval=0,  
        @os_run_priority=0, @subsystem=N'CmdExec',  
        @command=@cmd,
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
 
GO
 
 

Batch File to remove Old Files

This script uses ForFiles to remove old files based on age. However, you must have forfiles installed. Windows 2003 and above have it by default

 

The Syntax is: 

FORFILES -p "Path to the folder" -s -m *.txt -d   -c "cmd /c del @path"

 

Example:

REM  Delete all BAK files from folder C:\backup  that are 5 days or older

FORFILES -p "C:\backup" -s -m *.bak -d -5 -c "cmd /c del @path"

 

 

Want a job to do this automatically? See

http://www.cookingsql.com/2014/12/sql-job-to-delete-old-files/

VBScript to remove files based on age

Sometimes it's necessary to delete files based on age, and we can use a VB Script to do that:

'On Error Resume Next ' < -- Comment out for troubleshooting.

Const strPath = "C:\SQL_Backups\SOG-NADC-SP-DB1" ' Update this parent folder.
Const iDaysOld = 2   

' PURPOSE:	This script will delete all files recursively starting from
'			strPath older than 2 days and empty subfolders.
'			Very convenient for cleaning temp and log files.
' WARNING:	Please understand the risks of using this script.
' WARNING:	DO NOT MODIFY BELOW UNLESS YOU KNOW WHAT YOU ARE DOING.

Dim objFolder, objSubFolder, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.getFolder(strPath)

Call Search(objFolder)

Sub Search(objFolder)
	For Each objFile In objFolder.files
		If dateDiff("d",objFile.dateLastModified,Now) > iDaysOld Then objFile.Delete
	Next
	For Each objSubFolder In objFolder.SubFolders
		Search(objSubFolder)
	Next

End Sub

'  end of file

Shrink TempDB

These operations remove all kinds of caches, which will impact server performance to some degree until they’ve been rebuilt by the SQL Server.

Don’t do this commands unless absolutely necessary.

Do not ever put these scripts in a scheduled task

 

 

/*
DESCRIPTION    Forces the shrinking of TempDB
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2005 all the way to MSSQL2016
 

Please read Warning
*/


/*
 This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, in order to flush everything to disk.
*/

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

/*
Deletes cached execution plans. This means that ad-hoc queries and stored procedures will have to recompile the next time they run. You may notice a significant performance decrease the first few times the store procedures are run unitl the execution plans are cached again
*/
DBCC FREEPROCCACHE;
GO
DBCC FREESYSTEMCACHE ('ALL');
GO
DBCC FREESESSIONCACHE;
GO

use tempdb
declare @cmd nvarchar(100)
declare @size nvarchar(6) = '2048'   --- new size in MB  
declare cur cursor for
select 'DBCC SHRINKFILE ('''+name+''', '+@size+')'  from sys.sysfiles order by groupid desc

OPEN CUR
FETCH NEXT FROM CUR INTO @cmd
while @@FETCH_STATUS =0
begin
exec sp_executesql @cmd
FETCH NEXT FROM CUR INTO @cmd
end
close cur
deallocate cur
GO

/*
Why using a curson instead of simple
DBCC SHRINKFILE ('tempdev', 2048)

Because we might have more than one data file for the tempDB
*/
 


Space usage using Mount Points

What is this?

For reference, check

http://www.mssqltips.com/sqlservertip/2623/configuring-volume-mountpoints-on-a-sql-server-2008-failover-cluster-running-on-windows-server-2008/

A LUN can be mounted as a folder, however, traditional tools are unable to see the space utilized inside the mounted point.

 

To do that, we need to use WMI queries.

Step 1

Save this VBS file on the file system

 

'Author : Vidhya Sagar
'Modified by: Miguel Quintana
'Date : 01st Feb 2009
'Written for sql-articles.com
'Version 2

On Error Resume Next

Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\.\root\cimv2")

' The WMI class will only exist when there are volumes presentm mounted to a drive or a folder
' therefore, the "on error resume next" becomes mandatory.
	
	Set colDisks = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Volume WHERE DriveLetter IS NULL")

' This section deals with volumes mounted to a NTFS folder.
If colDisks.count > 0 Then
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
          & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks = objWMIService.ExecQuery _
        ("Select * from Win32_LogicalDisk Where DriveType = 3")
    For Each objDisk in colDisks
    Wscript.Echo mid((objDisk.size)/1048576,1,8) & "*" & mid((objDisk.Freespace)/1048576,1,8) & "#" & "Logical Disk" & "|" & objDisk.DeviceID
    Next
    Set objWMIService1 = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks1 = objWMIService1.ExecQuery _
        ("SELECT * FROM Win32_Volume WHERE DriveLetter IS NULL")
 
    For Each objDisk1 in colDisks1
    Wscript.Echo mid((objDisk1.Capacity)/1048576,1,8) & "*" & mid((objDisk1.Freespace)/1048576,1,8) & "#" & "MountedDrive" & "|" & mid(objDisk1.Name,1,100)
    Next
Else

' This section deals with drives that are not mounted(such as C:\) and with
' mounted volumes to a drive, not a folder
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
          & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks = objWMIService.ExecQuery _
        ("Select * from Win32_LogicalDisk Where DriveType = 3")
    For Each objDisk in colDisks
    Wscript.Echo mid((objDisk.size)/1048576,1,8) & "*" & mid((objDisk.Freespace)/1048576,1,8) & "#" & "Logical_Disk" & "|" & objDisk.DeviceID
    Next
End if




Step 2, run the following TSQL script:

 

/*
DESCRIPTION    List space available in each drive. Compatible with mounted folders.
Must use with Win.vbs file
 
CONFIGURATION    See config section to specify the location of the win.vbs file
        SET @VBSPATH='cscript C:\temp\win.vbs'

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000

ISSUES:

 
*/


 
    
SET NOCOUNT ON


-- Check to see if 'Show Advanced Option' is enabled
-- Check to see if 'Ole Automation options' is enabled

 
declare @chkOLE as sql_variant
declare @adv_opt as sql_variant
declare @op_ch1 as int
declare @op_ch2 as int
-- these variables keep track if we changed this options, we'll change them back before finishing
 
set @op_ch1=0
set @op_ch2=0
 
select @adv_opt = value from sys.configurations where name = 'show advanced options'
if @adv_opt = 0
begin
 EXEC sp_configure 'show advanced options',1
 RECONFIGURE WITH OVERRIDE;
 set @op_ch1 = 1
end
 
select @chkOLE = value from sys.configurations where name = 'Ole Automation Procedures'
if @chkOLE = 0
begin
 EXEC sp_configure 'xp_cmdshell', 1
 RECONFIGURE WITH OVERRIDE;
 set @op_ch2 = 1
end
 
 


--  Starting the script


-----******************** CONFIGURATION *****************
DECLARE @VBSPATH VARCHAR(200)
SET @VBSPATH='cscript C:\temp\win.vbs' -- Change the path here

-----******************** ENDS CONFIGURATION *****************
 
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp')
DROP TABLE ##tmp
 
CREATE TABLE ##tmp(diskspace VARCHAR(200))
INSERT ##tmp
EXEC master.dbo.xp_cmdshell @VBSPATH
 
SET ROWCOUNT 3
DELETE ##tmp
 
SET ROWCOUNT 0
 
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp2')
DROP TABLE ##tmp2
 
CREATE TABLE ##tmp2(DriveName VARCHAR(100),DriveDescription VARCHAR(15),TotalDiskSpace_in_MB VARCHAR(10), Freespace_in_MB VARCHAR(10))
INSERT ##tmp2
 
 
SELECT RTRIM(SUBSTRING(diskspace,charindex('|',diskspace)+1,100)),
 SUBSTRING(diskspace,charindex('#',diskspace)+1,charindex('|',diskspace)-charindex('#',diskspace)-1)
, SUBSTRING(diskspace,1,charindex('*',diskspace)-1) ,
SUBSTRING(diskspace,charindex('*',diskspace)+1,(charindex('#',diskspace)-charindex('*',diskspace))-1)
 
FROM ##tmp WHERE diskspace IS NOT NULL
SELECT * FROM ##tmp2
 
--select * from ##tmp
 
---  End of the routine
 
-- Clean up. See if we need to change the settings back.
 
if @op_ch1 = 1
begin
 EXEC sp_configure 'xp_cmdshell', 0
 RECONFIGURE WITH OVERRIDE;
end
 
if @op_ch2 = 1
begin
 EXEC sp_configure 'show advanced options',0
 RECONFIGURE WITH OVERRIDE;
 
end


Data file sizes and free space

This script builds up on http://www.cookingsql.com/2014/12/free-space-on-drives/ and correlate the data with the location of data files and their sized.

 

/*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 
Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers

 
*/
declare @t table 
([database_name] sysname, LogicalDBName sysname, type_desc nvarchar(10), sizeMB bigint, FreeSpaceMB bigint,
freespace nvarchar(4), Autogrow nvarchar(100), MAxSize bigint, physname nvarchar(max))

declare @cmd nvarchar (2000)
set @cmd =
'use [?]
SELECT 
''?'',
b.name,
type_desc,
CAST(b.size/128.0 AS int) ''SizeMB'', 
CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, ''SpaceUsed'' ) AS int)/128.0 AS int) ''FreeSpaceMB'', 
CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,''SpaceUsed'' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + ''%''  ''FreeSpace'',
    CASE 
        WHEN b.is_percent_growth = 0 
            THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + '' MB, '' 
        ELSE
            ''By '' + CAST(b.growth AS VARCHAR) + '' percent, ''
    END + 
    CASE 
        WHEN b.max_size = -1 THEN ''unrestricted growth''
        ELSE ''restricted growth to '' + 
            LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + '' MB'' 
    END AS Autogrow,
cast(b.max_size/128.0 as int) ''MaxSize'',
physical_name

FROM [?].sys.database_files b'
insert into @t
Exec sp_MSForEachDB @cmd

select * from @t

Data Allocation per drive

This script joins two scripts

We first get the free space o drives, and correlate the data (inner join) with the location of data files

 /*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none
 
Compatibility list:
MSSQL2008R2 and higher

Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers
 
*/

 /*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none
 
Compatibility list:
MSSQL2008R2 and higher



Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers
 
*/

/* This section checks if xp_cmdshell is enabled, and keeps track of the settings */ 
declare @chkOLE as sql_variant
declare @adv_opt as sql_variant
declare @op_ch1 as int
declare @op_ch2 as int
-- these variables keep track if we changed this options, we will change them back before finishing
 
set @op_ch1=0
set @op_ch2=0
 
select @adv_opt = value from sys.configurations where name = 'show advanced options'
if @adv_opt = 0
begin
 EXEC sp_configure 'show advanced options',1
 RECONFIGURE WITH OVERRIDE
 set @op_ch1 = 1
end
 
select @chkOLE = value from sys.configurations where name = 'Ole Automation Procedures'
if @chkOLE = 0
begin
 EXEC sp_configure 'Ole Automation Procedures',1
 RECONFIGURE WITH OVERRIDE
 set @op_ch2 = 1
end
 
/*
This section queries space avail on each drive.
*/
-- ==============
 
DECLARE @cmd2 NVARCHAR(2000)
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
 
DECLARE @HOSTNAME varchar(20)
 
-- checks if this is a cluster
select top 1  @HOSTNAME=nodename from ::fn_virtualservernodes()
IF @HOSTNAME is NULL set @hostname = cast(ServerProperty('machinename') as nvarchar(10))
 
declare @temp table 
(servername nvarchar(100), database_name nvarchar(100),driveletter nvarchar(2),totaldriveMB int,
 freedriveMB int,FreeDiskPct nvarchar(3), filename nvarchar(100), filesizeMB nvarchar(10), freeSpaceMB int,
FreeSpacePct varchar(8),AutoGrowth nvarchar(100), MaxsizeMB int, phys nvarchar(100))
 
declare @drives TABLE
(ServerName varchar(15),  drive char(1) PRIMARY KEY,
 FreeSpace int NULL,  TotalSize int NULL)  
 
INSERT @drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
 
DECLARE dcur CURSOR LOCAL FAST_FORWARD
 
FOR SELECT drive from @drives
ORDER by drive
OPEN dcur
 
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
 
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE @drives
SET TotalSize=@TotalSize/@MB, ServerName = @HOSTNAME
WHERE drive=@drive
 
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
 
EXEC @hr=sp_OADestroy @fso
 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
 
 
 /* This section turns off the xp_cmdshell settings if needed  */
 
if @op_ch2 = 1
begin
 EXEC sp_configure 'Ole Automation Procedures', 0
 RECONFIGURE WITH OVERRIDE;
end
 
if @op_ch1 = 1
begin
 EXEC sp_configure 'show advanced options',0
 RECONFIGURE WITH OVERRIDE;
 
end



/* This section queries every database for space stats.*/  

declare @t table 
([database_name] sysname, LogicalDBName sysname, sizeMB bigint, FreeSpaceMB bigint,
freespace nvarchar(4), Autogrow nvarchar(100), MAxSize bigint, physname nvarchar(max))

declare @cmd nvarchar (2000)
set @cmd =
'use [?]
SELECT 
''?'',
b.name,
CAST(b.size/128.0 AS int) ''SizeMB'', 
CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, ''SpaceUsed'' ) AS int)/128.0 AS int) ''FreeSpaceMB'', 
CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,''SpaceUsed'' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + ''%''  ''FreeSpace'',
    CASE 
        WHEN b.is_percent_growth = 0 
            THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + '' MB, '' 
        ELSE
            ''By '' + CAST(b.growth AS VARCHAR) + '' percent, ''
    END + 
    CASE 
        WHEN b.max_size = -1 THEN ''unrestricted growth''
        ELSE ''restricted growth to '' + 
            LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + '' MB'' 
    END AS Autogrow,
cast(b.max_size/128.0 as int) ''MaxSize'',
physical_name

FROM [?].sys.database_files b'
insert into @t
Exec sp_MSForEachDB @cmd

-----------------------------------


select * 
from @t t
inner join @drives d on left(t.physname,1)=d.drive