-
Notifications
You must be signed in to change notification settings - Fork 16
/
- INFO - Estate Detailed Information.sql
155 lines (118 loc) · 3.36 KB
/
- INFO - Estate Detailed Information.sql
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
/* Number of Servers */
SELECT COUNT(ServerName) as Servers
,Environment
,Location
FROM dbo.InstanceList il
GROUP BY Location, Environment
/*Number of Servers, Number of Databases, Environment and Location */
SELECT COUNT(DISTINCT il.ServerName) as 'number of servers'
,COUNT(d.Name) as 'number of databases'
,il.Environment
,il.Location
FROM dbo.InstanceList il
JOIN info.Databases d
ON il.InstanceID = d.InstanceID
GROUP BY Location,Environment
/*Size, Number of Servers, Number of Databases, Environment and Location */
SELECT COUNT(DISTINCT il.ServerName) AS 'number of servers'
,COUNT(d.Name) AS 'number of databases'
,CAST((SUM(d.SizeMB) / 1024) AS Decimal(7,2)) AS 'Size Gb'
FROM dbo.InstanceList il
JOIN info.Databases d
ON il.InstanceID = d.InstanceID
SELECT COUNT(DISTINCT il.ServerName) AS 'number of servers'
,COUNT(d.Name) AS 'number of databases'
,CAST((SUM(d.SizeMB) / 1024) AS Decimal(7,2)) AS 'Size Gb'
,il.Environment
,il.Location
FROM dbo.InstanceList il
JOIN info.Databases d
ON il.InstanceID = d.InstanceID
GROUP BY Location,Environment
/*Number of Servers, by Version, Edition, SP */
SELECT
SI.SQLVersion
,COUNT(DISTINCT il.ServerName) AS 'number of servers'
FROM dbo.InstanceList il
JOIN info.SQLInfo SI
ON il.ServerName = SI.ServerName
GROUP BY Environment,SI.SQLVersion
ORDER BY SQLVersion Desc
SELECT
SI.SQLVersion
,SI.Edition
-- ,SI.ServicePack
,COUNT(DISTINCT il.ServerName) AS 'number of servers'
FROM dbo.InstanceList il
JOIN info.SQLInfo SI
ON il.ServerName = SI.ServerName
GROUP BY Environment,SI.SQLVersion,SI.Edition
--,SI.ServicePack
ORDER BY SQLVersion Desc
/* Number of Agent Jobs */
SELECT COUNT(DISTINCT il.ServerName) as 'number of servers'
,SUM(ajs.NumberOfJobs) as 'Total Agent Jobs'
,il.Environment
,il.Location
FROM dbo.InstanceList il
JOIN info.AgentJobServer AJS
ON il.InstanceID = AJS.InstanceID
WHERE DATEDIFF( d, AJS.NumberofJobs, GETDATE() ) >300
GROUP BY Location,Environment
/* Number of databases without a full backup*/
SELECT COUNT(DISTINCT il.ServerName) as 'number of servers'
,COUNT(d.Name) as 'number of databases'
,CAST((SUM(d.SizeMB) / 1024) AS Decimal(7,2)) as 'Size Gb'
,il.Environment
,il.Location
FROM dbo.InstanceList il
JOIN info.Databases d
ON il.InstanceID = d.InstanceID
WHERE d.LastBackupDate = '0001-01-01 00:00:00.0000000'
GROUP BY Location,Environment
/* Number of Full databases wihtout a transaction log backup */
SELECT COUNT(DISTINCT il.ServerName) as 'number of servers'
,COUNT(d.Name) as 'number of databases'
,CAST((SUM(d.SizeMB) / 1024) AS Decimal(7,2)) as 'Size Gb'
,il.Environment
,il.Location
FROM dbo.InstanceList il
JOIN info.Databases d
ON il.InstanceID = d.InstanceID
WHERE d.LastLogBackupDate = '0001-01-01 00:00:00.0000000'
and d.RecoveryModel = 'full'
GROUP BY Location,Environment
/* Databases by Recovery Model */
SELECT
il.Environment
,d.RecoveryModel
,COUNT(d.Name) as 'number of databases'
FROM dbo.InstanceList il
JOIN info.Databases d
ON il.InstanceID = d.InstanceID
GROUP BY Environment,d.RecoveryModel
/* OS Operating System*/
SELECT
SOI.OperatingSystem
,COUNT(DISTINCT il.ServerName) as 'Number of Servers'
FROM dbo.InstanceList il
JOIN info.ServerOSInfo SOI
on IL.ServerName = SOI.ServerName
GROUP BY soi.OperatingSystem
ORDER BY soi.OperatingSystem
/* */
/* */
/* */
/* */
/* */
/* *//* */
/* */
/* */
/* */
/* */
/* */
/* */
/* */
/* */
/* */