Tag Archives: Network

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]

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

PRTG Network Monitor 8 Review

Posted by dave on October 20, 2010 4 comments

The other day I was checking my emails and amongst the mundane rubbish of people trying to flog me Viagra or robots spamming my blog, I spotted an interesting mail from a person at the PR company for Paessler, the …

Creating Maps with PRTG Network Monitor 7

Posted by dave on October 6, 2010 No comments

I use PRTG Network Monitor to monitor pretty much everything, and it works very well. I get email alerts when anything critical happens and can also monitor the status of everything from both the windows GUI or the web interface. …

Paessler PRTG Traffic Grapher with Auto Tab Change Script

Posted by dave on April 13, 2010 No comments

I wrote this one a little while back and to be honest it looks after itself so well that I rarely have to touch it. Anyway, I wanted an easy way to keep tabs on what’s happening on the network, …

Foscam FI8908W IP Camera with PT (no Z) Working in ZoneMinder

Posted by dave on March 19, 2010 55 comments

Within Zoneminder you can add various cameras, usb cams, network cams etc. If you are using a network camera, you need to write custom code in Perl to be able to control the Pan, Tilt and Zoom (PTZ) from within …

dave.harris.net

Sysadmin and part time Gadgeteer

Tag Archives: Network

SQL Script to show available space for all Databases

PRTG Network Monitor 8 Review

Creating Maps with PRTG Network Monitor 7

Paessler PRTG Traffic Grapher with Auto Tab Change Script

Foscam FI8908W IP Camera with PT (no Z) Working in ZoneMinder

Categories

Sysadmin and part time Gadgeteer

Tag Archives: Network

Tag Cloud

Categories