Having trouble creating procedure from a query

• alen teplitsky

  SSC-Dedicated

  Points: 30014

  More actions

  December 7, 2017 at 10:19 am

  #330412

  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


• sgmunson

  SSC Guru

  Points: 110651

  More actions

  December 7, 2017 at 10:47 am

  #1971222

  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)