November 21, 2017 at 10:10 am
i have a stored proc that when it run will create temp tables and show me file utilization for all my databases first question i want to run this in a job which is no problem that will run every 4 hours. my client wants two things and i am not sure. one is when it runs on monday can we save the data to a permanent table for historical reasons? second there is a Free Space % column is there a way that if any record in that column has a value of 20 or less to notify immediately and only those that meet that
November 21, 2017 at 10:11 am
sorry here is code
CREATE PROCEDURE usp_Sizing @Granularity VARCHAR(10) = NULL, @Database_Name sysname = NULL AS
DECLARE @SQL VARCHAR(5000)
IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME = '##Results')
BEGIN
DROP TABLE ##Results
END
CREATE TABLE ##Results ([Database Name] sysname,
[File Name] sysname,
[Physical Name] NVARCHAR(260),
[File Type] VARCHAR(4),
[Total Size in Mb] INT,
[Available Space in Mb] INT,
[Growth Units] VARCHAR(15),
[Max File Size in Mb] INT)
SELECT @SQL =
'USE [?] INSERT INTO ##Results([Database Name], [File Name], [Physical Name],
[File Type], [Total Size in Mb], [Available Space in Mb],
[Growth Units], [Max File Size in Mb])
SELECT DB_NAME(),
[name] AS [File Name],
physical_name AS [Physical Name],
[File Type] =
CASE type
WHEN 0 THEN ''Data'''
+
'WHEN 1 THEN ''Log'''
+
'END,
[Total Size in Mb] =
CASE ceiling(/128)
WHEN 0 THEN 1
ELSE ceiling(/128)
END,
[Available Space in Mb] =
CASE ceiling(/128)
WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)
ELSE ((/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)
END,
[Growth Units] =
CASE [is_percent_growth]
WHEN 1 THEN CAST(growth AS varchar(20)) + ''%'''
+
'ELSE CAST(growth*8/1024 AS varchar(20)) + ''Mb'''
+
'END,
[Max File Size in Mb] =
CASE [max_size]
WHEN -1 THEN NULL
WHEN 268435456 THEN NULL
ELSE [max_size]
END
FROM sys.database_files
ORDER BY [File Type], [file_id]'
--Print the command to be issued against all databases
PRINT @SQL
--Run the command against each database
EXEC sp_MSforeachdb @SQL
--UPDATE ##Results SET [Free Space %] = [Available Space in Mb]/[Total Size in Mb] * 100
--Return the Results
--If @Database_Name is NULL:
IF @Database_Name IS NULL
BEGIN
IF @Granularity = 'Database'
BEGIN
SELECT
T.[Database Name],
T.[Total Size in Mb] AS [DB Size (Mb)],
T.[Available Space in Mb] AS [DB Free (Mb)],
T.[Consumed Space in Mb] AS [DB Used (Mb)],
D.[Total Size in Mb] AS [Data Size (Mb)],
D.[Available Space in Mb] AS [Data Free (Mb)],
D.[Consumed Space in Mb] AS [Data Used (Mb)],
CEILING(CAST(D.[Available Space in Mb] AS decimal(10,1))/D.[Total Size in Mb]*100) AS [Data Free %],
L.[Total Size in Mb] AS [Log Size (Mb)],
L.[Available Space in Mb] AS [Log Free (Mb)],
L.[Consumed Space in Mb] AS [Log Used (Mb)],
CEILING(CAST(L.[Available Space in Mb] AS decimal(10,1))/L.[Total Size in Mb]*100) AS [Log Free %]
FROM
(
SELECT [Database Name],
SUM([Total Size in Mb]) AS [Total Size in Mb],
SUM([Available Space in Mb]) AS [Available Space in Mb],
SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]
FROM ##Results
GROUP BY [Database Name]
) AS T
INNER JOIN
(
SELECT [Database Name],
SUM([Total Size in Mb]) AS [Total Size in Mb],
SUM([Available Space in Mb]) AS [Available Space in Mb],
SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]
FROM ##Results
WHERE ##Results.[File Type] = 'Data'
GROUP BY [Database Name]
) AS D ON T.[Database Name] = D.[Databasse Name]
INNER JOIN
(
SELECT [[Database Name],
SUM([Total Size in Mb]) AS [Total Size in Mb],
SUM([Available Space in Mb]) AS [Available Space in Mb],
SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]
FROM ##Results
WHERE ##Results.[File Type] = 'Log'
GROUP BY [Database Name]
) AS L ON T.[Database Name] = L.[Database Name]
ORDER BY D.[Database Name]
END
ELSE
BEGIN
SELECT [Database Name],
[File Name],
[Physical Name],
[File Type],
[Total Size in Mb] AS [DB Size (Mb)],
[Available Space in Mb] AS [DB Free (Mb)],
CEILING(CAST([Available Space in Mb] AS decimal(10,1)) / [Total Size in Mb]*100) AS [Free Space %],
[Growth Units],
[Max File Size in Mb] AS [Grow Max Size (Mb)]
FROM ##Results
END
END
--Return the Results
--If @Database_Name is provided
ELSE
BEGIN
IF @Granularity = 'Database'
BEGIN
SELECT
T.[Database Name],
T.[Total Size in Mb] AS [DB Size (Mb)],
T.[Available Space in Mb] AS [DB Free (Mb)],
T.[Consumed Space in Mb] AS [DB Used (Mb)],
D.[Total Size in Mb] AS [Data Size (Mb)],
D.[Available Space in Mb] AS [Data Free (Mb)],
D.[Consumed Space in Mb] AS [Data Used (Mb)],
CEILING(CAST(D.[Available Space in Mb] AS decimal(10,1))/D.[Total Size in Mb]*100) AS [Data Free %],
L.[Total Size in Mb] AS [Log Size (Mb)],
L.[Available Space in Mb] AS [Log Free (Mb)],
L.[Consumed Space in Mb] AS [Log Used (Mb)],
CEILING(CAST(L.[Available Space in Mb] AS decimal(10,1))/L.[Total Size in Mb]*100) AS [Log Free %]
FROM
(
SELECT [Database Name],
SUM([Total Size in Mb]) AS [Total Size in Mb],
SUM([Available Space in Mb]) AS [Available Space in Mb],
SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]
FROM ##Results
WHERE [Database Name] = @Database_Name
GROUP BY [Database Name]
) AS T
INNER JOIN
(
SELECT [Database Name],
SUM([Total Size in Mb]) AS [Total Size in Mb],
SUM([Available Space in Mb]) AS [Available Space in Mb],
SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]
FROM ##Results
WHERE ##Results.[File Type] = 'Data'
AND [Database Name] = @Database_Name
GROUP BY [Database Name]
) AS D ON T.[Database Name] = D.[Database Name]
INNER JOIN
(
SELECT [Database Name],
SUM([Total Size in Mb]) AS [Total Size in Mb],
SUM([Available Space in Mb]) AS [Available Space in Mb],
SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]
FROM ##Results
WHERE ##Results.[File Type] = 'Log'
AND [Database Name] = @Database_Name
GROUP BY [Database Name]
) AS L ON T.[Database Name] = L.[Database Name]
ORDER BY D.[Database Name]
END
ELSE
BEGIN
SELECT [Database Name],
[File Name],
[Physical Name],
[File Type],
[Total Size in Mb] AS [DB Size (Mb)],
[Available Space in Mb] AS [DB Free (Mb)],
CEILING(CAST([Available Space in Mb] AS decimal(10,1))/[Total Size in Mb]*100) AS [Free Space %],
[Growth Units],
[Max File Size in Mb] AS [Grow Max Size (Mb)]
FROM ##Results
WHERE [Database Name] = @Database_Name
END
END
DROP TABLE ##Results
November 22, 2017 at 1:12 am
For the Monday save, yes, all you need to do is look at DATENAME for the weekday and if its Monday, just before you do the drop ##Results, do a insert into SomeTable select * from ##Results
Something like
END
END
IF DATENAME(weekday, getdate()) = 'Monday' AND IF NOT EXISTS (SELECT date FROM permtable WHERE date = CONVERT(DATE,GETDATE()) )
BEGIN
INSERT INTO permtable SELECT * FROM ##Results
END
DROP TABLE ##Results
The above relies on you having a data column in the permtable to say if data for that Monday has been inserted yet or not, otherwise you would get multi results entered for the Monday.
For the alert, use sp_send_dbmail again at the end and populate it from a query where freespace is less than the required percentage.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply