March 1, 2012 at 8:33 pm
I am looking for a solution to what one would think would be a common problem. I can't seem to find an answer specific enough anywhere. My problem is this: I work for a company that is in need for a database space monitoring solution. We have probably 15 servers, each with a couple named instances and several databases underneath them. I need to be able to monitor database size for each database in each instance on each server and send an email (probably a daily space check) for all databases that are, say, over 90% full. I need to monitor the file size for each database (data and log), not C: or D: drive space.
I am having trouble finding a way to check all servers at once. I have found solutions that allow me to check for one instance but I do not want 30 emails a day. I want one consolidated email. I have tried using SQL code but I cannot seem to pass in linked server names very well. And I am new to powershell, but have not seen much out there either.
Any suggestions would be appreciated!
Thanks!
March 2, 2012 at 1:32 am
i'm working on just that
this should help you get off on the right foot
CREATE TABLE DB.DatabaseFileUsage
(
ID BIGINT IDENTITY(1,1),
DatabaseName SYSNAME,
FileID INT,
FileSizeMB DECIMAL(18,2),
SpaceUsedMB DECIMAL(18,2),
FreeSpaceMB DECIMAL(18,2),
LogicalName SYSNAME,
FileLocation SYSNAME,
DateCollected DATE DEFAULT GETDATE(),
CONSTRAINT PK_DB_DatabaseFileUsage PRIMARY KEY CLUSTERED (ID)
) ON DB
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'INSERT INTO [DB].[DatabaseFileUsage] (DatabaseName, FileID, FileSizeMB, SpaceUsedMB, FreeSpaceMB, LogicalName, FileLocation)' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
)
--SELECT @SQL
EXECUTE sp_executesql @SQL
You will then need to do some calculations on the data and build it into sp_send_dbmail
March 2, 2012 at 3:57 am
We use this to shrink large databases accross many environments via LS's. Donnt have time to customise it to your scenario, but with a bit of tweaking it does what you both want/need.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_ShrinkLargeDBs] Script Date: 03/02/2012 10:40:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_ShrinkLargeDBs]
AS
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#servers') IS NOT NULL drop table #servers
CREATE TABLE #servers(
Environment varchar(50),
SQL_Server_Name varchar(100)
)
IF OBJECT_ID('tempdb..#databases') IS NOT NULL drop table #databases
CREATE TABLE #databases(
DatabaseName varchar(150),
LogicalNameNVARCHAR(200),
Environment varchar(50),
EnvNameNVARCHAR(50),
File_Size int,
File_Free decimal(12, 2),
File_Free_Percent decimal(12, 1),
File_Type varchar(10),
Database_ID int
)
DECLARE @server AS VARCHAR(100)
DECLARE @product as varchar(50)
DECLARE @sqlstring AS VARCHAR(1000)
DECLARE @LogicalDBNameAS VARCHAR(100)
DECLARE @DatabaseNameAS VARCHAR(100)
DECLARE @QueryTimeoutAS NVARCHAR(MAX)
DECLARE @QueryResetAS NVARCHAR(MAX)
-- Populate the servers table
INSERT INTO #servers (Environment, SQL_Server_Name)
SELECT product, name FROM sys.servers WHERE is_linked=1 AND name LIKE '%SRV%'
-- Declare cursor
DECLARE Server_cursor CURSOR
FOR SELECT Environment, SQL_Server_Name FROM #servers
OPEN Server_cursor
FETCH NEXT FROM Server_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstring = '[' + @server + '].master.dbo.sp_GetFullFiles'
EXEC (@sqlstring)
SET @sqlstring = 'select database_name, Logical_File_Name as LogicalName, ''' + @product + ''', ''' + @server + ''', file_size_mb, free_space_mb, free_space_percent, file_type, db_id
FROM [' + @server + '].tempdb.dbo.tFiles
WHERE database_name NOT IN (''MASTER'',''TEMPDB'',''MODEL'',''MSDB'')
AND database_name NOT LIKE ''ReportServer%'''
INSERT #databases (DatabaseName, LogicalName, Environment, EnvName, File_Size, File_Free, File_Free_Percent, File_Type, Database_ID)
EXEC (@sqlstring)
FETCH NEXT FROM Server_cursor INTO @product, @server
END
CLOSE Server_cursor
DEALLOCATE Server_cursor
SET @QueryTimeout = 'sp_configure ''remote query timeout (s)'', 0; reconfigure'
SET @QueryReset = 'sp_configure ''remote query timeout (s)'', 600; reconfigure'
-- Uppdate to avoid divide by 0 error
UPDATE #databases SET file_size = 1 WHERE file_size = 0
select a.Environment,
a.DatabaseName as [Database Name],
CASE File_Type
WHEN 'ROWS' THEN 'Data File'
WHEN 'LOG' THEN 'Log File'
END AS File_type,
a.file_size as [Size MB],
a.file_free as [Free MB],
cast((a.file_free / a.file_size) * 100 as decimal(6,2))as [Free %],
'EXEC sp_configure ''remote query timeout (s)'',0; reconfigure; EXEC [' + a.EnvName + '].[master].[dbo].[usp_execute_remote_sql] ''USE ' + a.DatabaseName + ';
DBCC SHRINKFILE ([' + a.LogicalName + '],1)''; EXEC sp_configure ''remote query timeout (s)'', 600; reconfigure;'
from #databases a
where a.DatabaseName not like 'ReportServer$%'
AND a.file_size > 1000
AND a.file_free > 1000
--order by a.Environment, a.DatabaseName
ORDER BY File_type, Environment, [Database Name] desc
And if you want to run as is you'll need this.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_GetFullFiles] Script Date: 03/02/2012 10:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_GetFullFiles]
AS
IF OBJECT_ID('tempdb..tFiles') IS NOT NULL drop table tempdb..tFiles
CREATE TABLE tempdb..tFiles (
[database_name] [sysname] NOT NULL,
[db_id] int NOT NULL,
[file_size_mb] [decimal](12, 2) NULL,
[space_used_mb] [decimal](12, 2) NULL,
[free_space_mb] [decimal](12, 2) NULL,
[free_space_percent] [decimal](12, 1) NULL,
[autogrowth] [varchar](3) NULL,
[growth_amount] [varchar](60) NULL,
[maximum_size] [varchar](60) NULL,
[file_control] [varchar](10) NULL,
[file_density] [varchar](6) NULL,
[file_id] [int] NULL,
[file_type] [nvarchar](60) NULL,
[file_state] [nvarchar](60) NULL,
[logical_file_name] [sysname] NOT NULL,
[physical_file_name] [nvarchar](260) NOT NULL,
) ON [Primary]
EXEC sp_msforeachdb
'USE [?] INSERT tempdb..tFiles
SELECT database_name = ''?''
, b.database_id
, file_size_mb = Convert(Decimal(12,2),Round(a.size/128.000,2))
, space_used_mb = Convert(Decimal(12,2),Round(FileProperty(a.name, ''SpaceUsed'')/128.000,2))
, free_space_mb = Convert(Decimal(12,2),Round((a.size - FileProperty(a.name, ''SpaceUsed''))/128.000,2))
, free_space_percent = Convert(Decimal(12,1),100*(a.size - FileProperty(a.name,''SpaceUsed''))/Convert(Decimal(12,2),a.size))
, autogrowth = Case When a.growth = 0 Then ''Off'' Else ''On'' End
, growth_amount = Case When a.is_percent_growth = 1 Then Convert(varchar(25), a.growth) + ''%'' Else Convert(varchar(25), Convert(Decimal(12,1), a.growth/128.0)) + '' MB'' End
, maximum_size = Case When a.max_size = 0 Then ''No Growth Allowed'' When a.max_size = -1 Or a.max_size = 268435456 Then ''Unlimited'' Else Convert(varchar(60), Convert(Decimal(12,2),Round(a.max_size/128.000,2))) + '' MB'' End
, file_control = Case When a.is_media_read_only = 1 Or a.is_read_only = 1 Then ''Read-Only'' Else ''Read-Write'' End
, file_density = Case When a.is_sparse = 1 Then ''Sparse'' Else ''Dense'' End
, a.file_id
, a.type_desc
, a.state_desc
, a.name
, a.physical_name
FROM [?].sys.database_files a inner join master.sys.master_files b on a.physical_name collate Latin1_General_CI_AS = b.physical_name collate Latin1_General_CI_AS'
And just to keep him happy if he should see it, these cripts are mostly by my colleage Jason Morris.
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 2, 2012 at 8:17 am
Thank you Ten Centuries but I need something a bit more complex then that. It is a good start but I do have something like that already.
Adam, I like your query a lot but I was wondering if I need to run this on each instance. I have the linked servers added on the local server but when I try to run exec [dbo].[sp_ShrinkLargeDBs], I get
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'master.dbo.sp_GetFullFiles'.
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "starling\olympia_sit" does not contain the table ""tempdb"."dbo"."tFiles"". The table either does not exist or the current user does not have permissions on that table.
Question is, how do I need to setup permissions on the other server/s? (I have only one added for now). I will be looking into how to adapt it to looping through each database.
Thanks for your help!!
March 2, 2012 at 8:22 am
I posted two scripts. The second to sp_GetFullFiles. So you need to have deployed that to your target servers (if you intend to run it as is).
As far as using it, the main Shrink SP should be on a 'master' server that had LS connections to every instance you want to connect to.
HTH
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply