To keep tabs on things, the script below will fire an email off to all specified recipients (provided Database Mail is configured of course) that lists all files, status and size. We stick this in a maintenance plan and run it once a night. If any files are growing unexpectedly then they can be re-sized accordingly.
DECLARE @Recipients VARCHAR(MAX) = 'a@a.com;b@b.com'
DECLARE @Subject VARCHAR(MAX) = 'HOSTNAME (ROLE) Database Report'
DECLARE @FooterMessage VARCHAR(MAX) = 'This email was generated by a maintenance plan located on HOSTNAME (INSTANCENAME)'
DECLARE @Commands TABLE (
[ID] INT IDENTITY,
[Command] VARCHAR(MAX)
)
INSERT INTO
@Commands ([Command])
SELECT
'use [' + [name] + ']; select [name], [type_desc], [physical_name], [state_desc], [size] / 128 from sys.database_files;' AS [Command]
FROM
sys.databases
DECLARE @NextCommandID INT
SELECT @NextCommandID = MIN([ID])
FROM @Commands
DECLARE @Results TABLE (
[ID] INT IDENTITY,
[FileName] VARCHAR(MAX),
[FileType] VARCHAR(4),
[FilePath] VARCHAR(MAX),
[Status] VARCHAR(MAX),
[Size (MB)] INT
)
WHILE @NextCommandID IS NOT NULL
BEGIN
DECLARE @NextCommand VARCHAR(MAX)
SELECT @NextCommand = [Command]
FROM @Commands
WHERE [ID] = @NextCommandID
INSERT INTO @Results ([FileName], [FileType], [FilePath], [Status], [Size (MB)])
EXEC(@NextCommand)
DELETE
FROM @Commands
WHERE [ID] = @NextCommandID
SELECT @NextCommandID = MIN([ID])
FROM @Commands
END
DECLARE @HtmlResult VARCHAR(MAX)
SET @HtmlResult = '<table cellpadding="0" cellspacing="0"><tr><th>Filename</th><th>Type</th><th>Path</th><th>Status</th><th>Size (MB)</th></tr>'
DECLARE @NextResultID INT
SELECT @NextResultID = MIN([ID])
FROM @Results
WHILE @NextResultID IS NOT NULL
BEGIN
SET @HtmlResult = @HtmlResult +
(
SELECT
'<tr><td>' + [FileName] +
'</td><td>' + [FileType] +
'</td><td>' + [FilePath] +
'</td><td>' + [Status] +
'</td><td>' + CAST([Size (MB)] AS VARCHAR) + '</td></tr>'
FROM
@Results
WHERE
[ID] = @NextResultID)
DELETE
FROM @Results
WHERE [ID] = @NextResultID
SELECT @NextResultID = MIN([ID])
FROM @Results
END
SET @HtmlResult = '<html><head><style>body { font-family: sans-serif; font-size: 10px; } th { text-align: left; } th, td { border: 1px solid #555 }</style></head><body>' + @HtmlResult + '</table><p>' + @FooterMessage + '</p></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients = @Recipients,
@body = @HtmlResult,
@body_format = 'HTML',
@subject = @Subject;
No comments:
Post a Comment