Daily report for Disk space (Network Steve Forum)

Daily report for Disk space

Hi All,

I want to send a status report for disk space such as :

Server Name

Drive | Total disk space | free space | used in %

I want do this for multiple server in one SSRS report or HTML format
need query or any other way

Edited by ImranKazi 3 hours 21 minutes ago

August 17th, 2015 12:14am

Hi Imran

you will need two thing, the first is a query that will return free disk space, this is a query modified from this blog

SELECT DISTINCT dovs.logical_volume_name AS LogicalName,dovs.volume_mount_point AS Drive,CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB,CONVERT(INT,dovs.total_bytes/1048576.0) [TotalSpaceInMB],(dovs.available_bytes/1048576.0) / (dovs.total_bytes/1048576.0) [%FreeDislSpace]
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

The second thing is to create a SSRS report, you would need to create a connection to each database server and then execute the above query and put the result in a table. you could then set up a schedule to send out the mail daily.

To improve this you might want to build an ETL that stored the daily values into a table, you would then be able to report on disk usage over time.

# there are also several tools that can give you this functionality.

Free Windows Admin Tool Kit Click here and download it now

August 17th, 2015 12:41am

Hi Imran,

To get the disk space...

Code Block

CREATE TABLE #FixedDrives

(DriveLetter VARCHAR(10),

MB_Free DEC(20,2))

INSERT INTO #FixedDrives EXEC Master..XP_FixedDrives

SELECT * FROM #FixedDrives

DROP TABLE #FixedDrives

To get the uptime and when the server was last started...

Code Block SELECT CONVERT(CHAR(25),login_time,100) as 'Started',
CONVERT(CHAR(25), DATEDIFF(DAY, login_time, GETDATE())) as 'Uptime'
FROM master..sysprocesses
WHERE spid = 1

To get lots of other neat SQL stats....

Create this function.

Code Block

USE [mydatabase]

/****** Object: UserDefinedFunction [dbo].[udf_SQL_StartDT] Script Date: 10/05/2007 13:30:55 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[udf_SQL_StartDT] ()

RETURNS datetime -- Date/time the SQL Server Instance started

* Returns the data/time that the SQL Server instance was started.

* Common Usage:

select dbo.udf_SQL_StartDT() as [System Started AT]

* You may use this function in any of your SQL Server databases

* including databases that you sell, so long as they contain

* other unrelated database objects. You may not publish this

* UDF either in print or electronically.

* Published as Vol 1 #11 of The T-SQL UDF of the Week Newsletter

http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm

****************************************************************/

AS BEGIN

DECLARE @WorkingVariable datetime

SELECT @WorkingVariable = login_time

FROM master..sysprocesses

WHERE cmd='LAZY WRITER'

Return @WorkingVariable

END

Then use this code in your report...

Code Block

DECLARE @SecFromStart bigint
SET @SecFromStart = DATEDIFF(s, dbo.udf_SQL_StartDT(), getdate())
SELECT CAST(CAST(@@TOTAL_READ as Numeric (18,2))/@SecFromStart
               as Numeric (18,2)) as [ReadsperSec]
     , CAST(CAST(@@TOTAL_WRITE as Numeric (18,2))/@SecFromStart
               as Numeric (18,2)) as [WritesperSec]
     , CAST(@@IO_BUSY * @@TIMETICKS/10000.0/@SecFromStart
               as Numeric (18,2)) as [Percent I/O Time]
,@@TOTAL_READ [Total Reads]
     , @@TOTAL_WRITE as [Total Writes]
     , CAST(@@IO_BUSY as FLOAT) * @@TIMETICKS / 1000000.0 as [IO Sec]
, CAST(@@CPU_BUSY as FLOAT) * @@TIMETICKS / 1000000.0 as [CPU BUSY]
, CAST(@@IDLE as FLOAT) * @@TIMETICKS / 1000000.0 as [IDLE]
, @@PACKET_ERRORS AS [PACKET ERRORS]
, @@PACK_SENT AS [PACKETS SENT]
, @@PACK_RECEIVED AS [PACKETS RECEIVED]
, @@TOTAL_ERRORS AS [TOTAL ERRORS]

I would be very interested in a graphical representation of CPU usage along with what SQL users were causing peaks in CPU use.

August 17th, 2015 1:26am

Another method:

https://gallery.technet.microsoft.com/Drive-Capacity-InfoDetails-f2b8fb60

Free Windows Admin Tool Kit Click here and download it now

August 17th, 2015 1:45am

Another method:

https://gallery.technet.microsoft.com/Drive-Capacity-InfoDetails-f2b8fb

August 17th, 2015 3:01am

Free Windows Admin Tool Kit Click here and download it now

August 17th, 2015 3:35am

This topic is archived. No further replies will be accepted.