SQL server - monitor size of a table Options

[RA]

CODE

-----------------------------------------------------------------
-- Name: SQLTableSize.lua
-- Author: Kevin Prestage
-- Required INM version: 3.3
-- Version: 1.0
-- Date: 2007-09-21
-- Description: This script will monitor the size of a table
-- in the specified database.  Stored values include the number
-- of rows in the table, the amount of disk space RESERVED for
-- the table, and the amount of space actually USED by the table.
--
-- Please note that this monitor uses the built in sql stored
-- procedure sp_space_used which relies on indexing statistics
-- for much of it's data.  Indexes are not always up to date,
-- so if precision is needed you should modify the code to
-- gather this data through other means.  (i.e.  select count(*)
-- from table_name)
--

--
-- Arguments:
-- 1) Server Name
-- 2) name of database containing table.
-- 3) name of table to check.
-- 4) unit of measure
--    (default is kb.  You may optionally specify mb or gb)

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

servername = GetArgument(0);
dbname = GetArgument(1);
tablename = GetArgument(2);
measure = GetArgument(3);

if (measure == nil) then
    measure = "kb";
    div = 1;
end

if (measure == "kb") then
    div = 1;
end

if (measure == "mb") then
    div = 1024;
end

if (measure == "gb") then
    div = 1048576;
end

db = TLuaDB();
con = servername .. "@" .. dbname;
print(con);
sql = "exec sp_spaceused '" .. tablename .. "'";
x = db:Connect(con,TLuaDB.CLIENT_SQLSERVER);
result = "";
rows = 0;
reserved = 0;
data = 0;
index = 0;

if (x == true) then
    x = db:Execute(sql);
    if (db:ResultAvilable() == true) then
        db:NextRow();
        rows = db:GetCol(2);
        reserved = db:GetCol(3);
        data = db:GetCol(4);
        index = db:GetCol(5);
        reserved = string.gsub(reserved, " KB", "");
        data = string.gsub(data, " KB", "");
        index = string.gsub(index, " KB", "");
        reserved = reserved / div;
        data = data / div;
        index = index / div;

        result = rows .. " Rows, " .. reserved .. " " .. measure .. " reserved, " .. data .. " " .. measure .. " used by data, " .. index .. " " .. measure .. " used by indexes.";



        StoreStatisticalData(0,rows,0,"Rows")
        StoreStatisticalData(1,reserved,0,measure)
        StoreStatisticalData(2,data,0,measure)
        StoreStatisticalData(3,index,0,measure)
    end
    


else
    result = db:GetErrorDescription();
end

SetExitStatus(result, x);