Having trouble creating procedure from a query

  • 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

  • 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 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply