Get Total Free Disk Space from SQL Server backend (1 Viewer)

[Rx_]

Sometimes, the person on alert to look at your SQL Server hard disk space needs a vacation. That is when my database grew with some new EDI along with the automated test db.
What happens when a SQL Server runs out of hard disk space?
Lets not go there, ever.

This is some code to prevent that from happening and to provide some nice trending informaiton. Granted, it is only about as exciting as rotating your car tires. But, maybe as useful as that too.

The first post will cover some basics of what to do in SQL Server back end. Afterwards, I plan to add a function on my splash screen that will notify each user at the form startup about low disk space.

On the SQL Server (back end) - first create a tracking table. Only the first four fields are actually used. The rest if for some Triggers that will populate additional SQL Server system information:

[FONT=Calibri]-- your DB name for MyProductionDB[/FONT]
[FONT=Calibri]USE [MyProductionDB][/FONT]
[FONT=Calibri]GO[/FONT]
[FONT=Calibri]/****** Object:  Table [dbo].[Sys_Info]    Script Date: 08/06/2012 15:42:29 ******/[/FONT]
[FONT=Calibri]SET ANSI_NULLS ON[/FONT]
[FONT=Calibri]GO[/FONT]
[FONT=Calibri]SET QUOTED_IDENTIFIER ON[/FONT]
[FONT=Calibri]GO[/FONT]
[FONT=Calibri]CREATE TABLE [dbo].[Sys_Info]([/FONT]
[FONT=Calibri]              [ID_Sys_Info] [int] IDENTITY(1,1) NOT NULL,[/FONT]
[FONT=Calibri]              [FreeDisk] [int] NULL,[/FONT]
[FONT=Calibri]              [Drive] [nvarchar](2) NULL,[/FONT]
[FONT=Calibri]              [Date_Time] [datetime2](0) NULL,[/FONT]
[FONT=Calibri]              [Desc_2] [nvarchar](75) NULL,[/FONT]
[FONT=Calibri]              [Desc_3] [nvarchar](75) NULL,[/FONT]
[FONT=Calibri]              [FlagTF] [bit] NULL,[/FONT]
[FONT=Calibri]              [Notes] [nvarchar](255) NULL,[/FONT]
[FONT=Calibri]              [SSMA_TimeStamp] [timestamp] NOT NULL[/FONT]
[FONT=Calibri]) ON [PRIMARY][/FONT]
[FONT=Calibri]GO[/FONT]
[FONT=Calibri]ALTER TABLE [dbo].[Sys_Info] ADD  CONSTRAINT [DF_Sys_Info_Date_Time]  DEFAULT (getdate()) FOR [Date_Time][/FONT]

ID_Sys_Info - just an autocounter field. Later, the function will simply find the largest number. In my case, it is a virtual server with only a C:\ drive.
Date_Time has a default value


Created a new Job - Daily - Database Master, run as sa with command:

INSERT INTO [MyProductionDB].[dbo].[Sys_Info]
([Drive]
,[FreeDisk]
) 
EXEC master.sys.xp_fixeddrives
GO
-- Thank Rx_    only to remind him to add part 2

Once a day, the total space on C:\ for the SQL Server is logged.

I submitted part 2 as a Reply. It has not shown up yet. Let me know if it needs to be a new post.

 

[Rx_]

This is a first cut for testing. It does work.

Code Modules - create a new module - SQL_FreeDiskSpace
Assume the linked table from the front-end to SQL table is connected.

Option Compare Database
Option Explicit
Function SQLFreeDisk() As Long
          Dim rs As DAO.Recordset
          Dim fld As DAO.Field
          Dim strsql As String
10                  On Error Resume Next
20      SQLFreeDisk = -99         ' default value -99 shows error
30        strsql = "SELECT Last(Sys_Info.ID_Sys_Info) AS LastOfID_Sys_Info, Last(Sys_Info.FreeDisk) AS LastOfFreeDisk, " & _
            "Last(Sys_Info.Drive) AS LastOfDrive, Last(Sys_Info.Date_Time) AS LastOfDate_Time FROM Sys_Info ORDER BY Last(Sys_Info.ID_Sys_Info);"
40        Set rs = DBEngine(0)(0).OpenRecordset(strsql)
50                Debug.Print rs.Fields(1).Value
60              SQLFreeDisk = rs.Fields(1).Value
70        rs.Close
80        Set rs = Nothing
End Function
Function SQLDriveDisk() As String
          Dim rs As DAO.Recordset
          Dim fld As DAO.Field
          Dim strsql As String
10                  On Error Resume Next
20      SQLDriveDisk = "XX"        ' default value XX shows error
30        strsql = "SELECT Last(Sys_Info.ID_Sys_Info) AS LastOfID_Sys_Info, Last(Sys_Info.FreeDisk) AS LastOfFreeDisk, " & _
            "Last(Sys_Info.Drive) AS LastOfDrive, Last(Sys_Info.Date_Time) AS LastOfDate_Time FROM Sys_Info ORDER BY Last(Sys_Info.ID_Sys_Info);"
40        Set rs = DBEngine(0)(0).OpenRecordset(strsql)
50                Debug.Print rs.Fields(2).Value
60              SQLDriveDisk = rs.Fields(2).Value
70        rs.Close
80        Set rs = Nothing
End Function
Function SQLDateDisk() As Date
          Dim rs As DAO.Recordset
          Dim fld As DAO.Field
          Dim strsql As String
10                  On Error Resume Next
20      SQLDateDisk = "5"        ' default value 5 shows error date of  1/4/1900
30        strsql = "SELECT Last(Sys_Info.ID_Sys_Info) AS LastOfID_Sys_Info, Last(Sys_Info.FreeDisk) AS LastOfFreeDisk, " & _
            "Last(Sys_Info.Drive) AS LastOfDrive, Last(Sys_Info.Date_Time) AS LastOfDate_Time FROM Sys_Info ORDER BY Last(Sys_Info.ID_Sys_Info);"
40        Set rs = DBEngine(0)(0).OpenRecordset(strsql)
50                Debug.Print rs.Fields(3).Value
60              SQLDateDisk = rs.Fields(3).Value
70        rs.Close
80        Set rs = Nothing
End Function

Then in the Startup form - create a label named lblSQLServerFreeSpace
Startup Form's

Private Sub Form_Open(Cancel As Integer)
10        On Error Resume Next
20        Me.lblSQLServerFreeSpace.Caption = "SQL Server Free Space: " & SQLFreeDisk() & " Meg Byte on Drive: " & SQLDriveDisk() & " Last Recorded on " & SQLDateDisk()
30        Me.btn_Wells.SetFocus
40        Call LogUsage("Logo Main Form", "Form_Home", "Form Opened")
End Sub

For the lblSQLServerFreeSpace default caption in the Property - "Error obtaining SQL Free Hard Disk Space"

My project will add code to put a RED warning if the hard disk space drops below 1 GB. And, put out a warning if the Date is over 36 hours old.
The SQL Server JOB should record a new record every evening.

Would a DLookup have been faster? Yes, but not by much. Then there is the error trapping to go with it. This is a splash screen that opens up once per session. This table will only grow to 700 records over 2 years.