December 7, 2017 at 10:19 am
I have the following code that I combined from someone else's procedure and other code I found on the internet. I returns every drive letter on a server along with the files on those drives and the free space in each of those database files
Ideally I want to run this on a server or group of servers and then save the data into a text file and then use R or something similar to chart the growth of database files
I've had trouble with the BCP command and whenever I save it as a stored procedure I get errors that one of the temp tables doesn't exist
--drop table ##DB_FILE_INFO
--alter procedure super_drive_info3
--as
if exists ( select * from tempdb.dbo.sysobjects o
where o.xtype in ('U') and o.name in ('##DB_FILE_INFO'))
drop table ##DB_FILE_INFO
if exists ( select * from tempdb.dbo.sysobjects o
where o.xtype in ('U') and o.name in ('##output'))
drop table ##output
create table ##DB_FILE_INFO (
[server_Name] varchar(255) not null,
[InstanceName] varchar (255),
[database_name] varchar(255) not null,
[File_ID] int not null,
[File_Type] int not null,
[Drive] varchar(255) not null,
[Logical_Name] varchar(255) not null,
[Physical_Name] varchar(255) not null,
[FILE_SIZE_MB] int not null,
[SPace_USED_MB] int not null,
[Free_space] int not null,
[Max_SIZE] int not null,
[Percent_Log_growth_enabled] int not null,
[growth_rate] int not null,
[current_date] datetime not null
)
go
declare @sql nvarchar(4000)
set @sql =
'use ['+'?'+']
--if db_name() <> N''?'' goto Error_Exit
insert into ##DB_FILE_INFO
(
[server_Name],
[InstanceName],
[database_name],
[File_ID],
[File_Type],
[Drive],
[Logical_Name],
[Physical_Name],
[FILE_SIZE_MB],
[SPace_USED_MB],
[Free_space],
[Max_SIZE],
[Percent_Log_growth_enabled],
[growth_rate],
[current_date]
)
SELECT
@@servername as [Server_Name],
Convert(varchar(255), SERVERPROPERTY(''instancename'')) as [InstanceName],
db_name() as database_name,
[file_id] AS [File_ID],
[type] AS [File_Type],
substring([physical_name],1,1) AS [Drive],
[name] AS [Logical_Name],
[physical_name] AS [Physical_Name],
CAST( as DECIMAL(38,0))/128. AS [File Size MB],
CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128. AS [Space_Used_MB],
(CAST( AS DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128.) AS [Free Space],
[max_size] AS [Max_Size],
[is_percent_growth] AS [Percent_Growth_Enabled],
[growth] AS [Growth Rate],
getdate() AS [Current_Date]
FROM sys.database_files'
exec sp_msforeachdb @sql
declare @svrName varchar(255)
declare @sql2 varchar(400)
--by default it will take the current server name, we can the set the server name as well
set @svrName = @@SERVERNAME
set @sql2 = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
--creating a temporary table
CREATE TABLE ##output
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert ##output
EXEC xp_cmdshell @sql2;
with Output2
--(drivename, capacity(gb),freespace(gb), always_on_status)
as
(
select @@servername as server_name,
rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(':',line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as float)/1024,2) as 'capacityGB'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as float) /1024 ,2)as 'freespaceGB'
,CASE WHEN (SERVERPROPERTY ('IsHadrEnabled')=1) THEN 'YES' WHEN (SERVERPROPERTY ('IsHadrEnabled')=0) THEN 'NO'ELSE 'NOT AVAILABLE' END AS ALWAYS_ON_STATUS
--into #output2
from ##output
where line like '[A-Z][:]%'
--order by drivename
),
DB_FILE_INFO2 as
(
select server_Name,
InstanceName,
database_name,
File_ID,
File_Type,
Drive,
Logical_Name,
Physical_Name,
FILE_SIZE_MB,
SPace_USED_MB,
Free_space,
Max_SIZE,
Percent_Log_growth_enabled,
growth_rate
--current_date
from ##DB_FILE_INFO
--inner join #output b on a.drive = b.drivename and a.server_Name = b.server_name
)
--sqlcmd -i c:\temp\drive_space.sql -o c:\temp\saved_file_size.txt
select
getdate() as Today_Date,
a.server_Name,
a.InstanceName,
a.database_name,
a.Drive,
a.Logical_Name,
a.Physical_Name,
a.FILE_SIZE_MB,
a.Space_Used_MB,
a.Free_space as Free_Space_in_File,
Percent_Free_space = a.Free_space/a.File_Size_MB,
--Percentage_file_free = (a.Space_Used_MB/a.FILE_SIZE_MB),
b.capacitygb as Total_Drive_capacity,
b.freespacegb as Total_Free_Space,
a.Max_SIZE
from DB_FILE_INFO2 a
inner join output2 b on a.server_Name = b.server_name and a.Drive = b.drivename
order by a.drive
--drop table ##DB_FILE_INFO
--drop table #output
December 7, 2017 at 10:47 am
Take the GO out of the middle of your code. You can't have a stored procedure extend beyond a batch terminator. There's no alternative.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply