Tag Archives: PRTG

SQL Script to show available space for all Databases

Posted by dave on May 22, 2013 No comments

Quick little script to return the space available on all databases. I linked this into PRTG Network Monitor to make sure that none of them were too low.

--///////////////////////////////////////////////////////////////////////////////////////////
--////////////////////// Filespace Used and Free   //////////////////////////////////////////
IF (OBJECT_ID('tempdb..#tmpDbSpace') IS NOT NULL)
      DROP TABLE #tmpDbSpace

CREATE TABLE #tmpDbSpace (
      [DBname] varchar (126) NULL, 
      [LogicalFileName] varchar (126) NULL,
      [IsLogFile] tinyint NULL,
      [IsPrimaryFile] tinyint NULL,
      [IsReadOnly] tinyint NULL,
      [FileSizeMB] numeric (12, 2) NULL,
      [SpaceUsedMB] numeric (12, 2) NULL,
      [Growth] varchar (50) NULL)

DECLARE @TSQL varchar (max)

SET @TSQL = 'USE [?]; INSERT INTO #tmpDbSpace
                        SELECT
                              [DBName]=DB_NAME(DB_ID()),
                              [LogicalFileName]=[name],
                              [IsLogFile]=FILEPROPERTY([name], ''IsLogFile''),
                              [IsPrimaryFile]=FILEPROPERTY([name], ''IsPrimaryFile''),
                              [IsReadOnly]=FILEPROPERTY([name], ''IsReadOnly''),
                              [FileSizeMB]=([Size]*8)/1024, -- In pages of 8 Kb
                              [SpaceUsedMB]=(FILEPROPERTY([name], ''SpaceUsed'')*8)/1024, -- In pages of 8 Kb
                              [Growth] = CASE status &amp; 0x100000
                              WHEN 0x100000 THEN      CONVERT(VARCHAR(15), growth) + ''%''
                              ELSE CONVERT(VARCHAR(15), CONVERT (BIGINT, growth) * 8) + '' KB'' END
                              FROM sys.sysfiles'

--Print the command to be issued against all databases  
PRINT @TSQL  

--Run the command against each database  
EXEC sp_MSforeachdb @TSQL 

SELECT
[DBname],  
[LogicalFileName],
[FileType]=CASE
WHEN [IsLogFile]=1 THEN 'Log'
WHEN [IsLogFile]=0 AND [IsPrimaryFile]=1 THEN 'Primary'
ELSE 'Data'
END,
[FileSizeMB],
[SpaceUsedMB],
[FreeSpacePercent]=CASE
WHEN [FileSizeMB] = 0 THEN 0
ELSE CAST((([FileSizeMB]-[SpaceUsedMB])/[FileSizeMB])*100 AS numeric (12, 2))
END,
[Growth]
FROM #tmpDbSpace
ORDER BY [DBname], [IsLogFile]
--///////////////////////////////////////////////////////////////////////////////////////////

--///////////////////////////////////////////////////////////////////////////////////////////

--////////////////////// Filespace Used and Free //////////////////////////////////////////

IF (OBJECT_ID('tempdb..#tmpDbSpace') IS NOT NULL)

DROP TABLE #tmpDbSpace

CREATE TABLE #tmpDbSpace (

[DBname] varchar (126) NULL,

[LogicalFileName] varchar (126) NULL,

[IsLogFile] tinyint NULL,

[IsPrimaryFile] tinyint NULL,

[IsReadOnly] tinyint NULL,

[FileSizeMB] numeric (12, 2) NULL,

[SpaceUsedMB] numeric (12, 2) NULL,

[Growth] varchar (50) NULL)

DECLARE @TSQL varchar (max)

SET @TSQL = 'USE [?]; INSERT INTO #tmpDbSpace

SELECT

[DBName]=DB_NAME(DB_ID()),

[LogicalFileName]=[name],

[IsLogFile]=FILEPROPERTY([name], ''IsLogFile''),

[IsPrimaryFile]=FILEPROPERTY([name], ''IsPrimaryFile''),

[IsReadOnly]=FILEPROPERTY([name], ''IsReadOnly''),

[FileSizeMB]=([Size]*8)/1024, -- In pages of 8 Kb

[SpaceUsedMB]=(FILEPROPERTY([name], ''SpaceUsed'')*8)/1024, -- In pages of 8 Kb

[Growth] = CASE status & 0x100000

WHEN 0x100000 THEN CONVERT(VARCHAR(15), growth) + ''%''

ELSE CONVERT(VARCHAR(15), CONVERT (BIGINT, growth) * 8) + '' KB'' END

FROM sys.sysfiles'

--Print the command to be issued against all databases

PRINT @TSQL

--Run the command against each database

EXEC sp_MSforeachdb @TSQL

SELECT

[DBname],

[LogicalFileName],

[FileType]=CASE

WHEN [IsLogFile]=1 THEN 'Log'

WHEN [IsLogFile]=0 AND [IsPrimaryFile]=1 THEN 'Primary'

ELSE 'Data'

END,

[FileSizeMB],

[SpaceUsedMB],

[FreeSpacePercent]=CASE

WHEN [FileSizeMB] = 0 THEN 0

ELSE CAST((([FileSizeMB]-[SpaceUsedMB])/[FileSizeMB])*100 AS numeric (12, 2))

END,

[Growth]

FROM #tmpDbSpace

ORDER BY [DBname], [IsLogFile]

--///////////////////////////////////////////////////////////////////////////////////////////

Powershell Script to check all Automatic Services with PRTG Network Monitor

Posted by dave on May 22, 2013 No comments

I can check specified services with PTRG, but also wanted a a way to make sure all automatic services were running on a remote server. My first attempt at a script for this was: $machinename=$args[0] $sscount = 0 $ss = Get-WmiObject -Class Win32_Service -Filter “StartMode=’Auto’ AND State=’Stopped’” -computer $machinename foreach ($objItem in $ss) { $sscount++ …

dave.harris.net

Sysadmin and part time Gadgeteer

Tag Archives: PRTG

SQL Script to show available space for all Databases

Powershell Script to check all Automatic Services with PRTG Network Monitor

Error Connecting to TMG server with PRTG

Monitoring a scheduled task in PTRG Network Monitor

web.config Connection String Monitoring with PRTG Network Monitor

PowerShell Script to Monitor Server Date and Time using PRTG Network Monitor

Toner OID’s for Konica-Minolta BizHub C451

PRTG Network Monitor 8 Review

Creating Maps with PRTG Network Monitor 7

Categories

Sysadmin and part time Gadgeteer

Tag Archives: PRTG

Tag Cloud

Categories