-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathsizing_sp.sql
More file actions
216 lines (200 loc) · 8.63 KB
/
sizing_sp.sql
File metadata and controls
216 lines (200 loc) · 8.63 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
USE [DBATools]
GO
/****** Object: StoredProcedure [dbo].[usp_Sizing] Script Date: 20.09.2022 12:26:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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([size]/128)
WHEN 0 THEN 1
ELSE ceiling([size]/128)
END,
[Available Space in Mb] =
CASE ceiling([size]/128)
WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)
ELSE (([size]/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
@@SERVERNAME as [Server Name],
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.[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'
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
GO