Prevent DISK FULL when database grows
This procedure helps you to know when the database space free is over the @percent filled up and the disk space left is below @M_free_high bigint OR the disk space left is below < @M_free_low
Example: EXEC msdb.dbo.[get_space_free] 2000, 15000, 80
This means 'Give me information of all databases filled at 80% capacity with less than 15 Mbytes (15 Gbytes) of disk space OR just all database drives with less than 2000 Mbytes (2 gbytes)'
Very useful to prevent DISK FULL when database grows.
This is the display:
USE [gestdb]
GO
/****** Object: StoredProcedure [dbo].[get_space_free] Script Date: 30/08/2013 13:26:57 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* This procedure helps you to know when:
The database space free is over the @percent filled up and the disk space left is below @M_free_high bigint OR the disk space left is below < @M_free_low
Example: EXEC msdb.dbo.[get_space_free] 2000, 15000, 80
This means 'Give me information of all databases filled at 80% capacity with less than 15 Mbytes (15 Gbytes) of disk space OR just all database drives with less than 2000 Mbytes (2 gbytes)'
Very useful to prevent DISK FULL when database grows. */
CREATE PROCEDURE [dbo].[get_space_free] @M_free_low bigint, @M_free_high bigint, @percent int
AS
begin
set nocount on
if exists (select 1
from gestdb..sysobjects
where [Id] = object_id('gestdb..dbfileinfo'))
begin
drop table dbfileinfo
end
if exists (select 1
from gestdb..sysobjects
where [Id] = object_id('gestdb..logsizestats'))
begin
drop table logsizestats
end
if exists (select 1
from gestdb..sysobjects
where [Id] = object_id('gestdb..datafilestats'))
begin
drop table datafilestats
end
if exists (select 1
from gestdb..sysobjects
where [Id] = object_id('gestdb..fixeddrives'))
begin
drop table fixeddrives
end
if exists (select 1
from gestdb..sysobjects
where [Id] = object_id('gestdb..usados'))
begin
drop table usados
end
create table fixeddrives
(
DriveLetter varchar(10),
MB_Free dec(20, 2)
)
create table datafilestats
(
DBName varchar(255),
DBId int,
FileId tinyint,
[FileGroup] tinyint,
TotalExtents dec(20, 2),
UsedExtents dec(20, 2),
[Name] varchar(255),
[FileName] varchar(400)
)
create table logsizestats
-- DBCC SQLPERF -- Provides statistics about how the transaction-log space was used in all databases. It can also be used to reset wait and latch statistics.
(
DBName varchar(255) not null primary key clustered,
-- Database Name -- Name of the database for the log statistics displayed.
DBId int,
LogFile real,
-- Log Size (MB) -- Actual amount of space available for the log. This amount is smaller than the amount originally allocated for log space because the SQL Server 2005 Database Engine reserves a small amount of disk space for internal header information.
LogFileUsed real,
-- Log Space Used (%) -- Percentage of the log file currently occupied with transaction log information.
Status bit
) -- Status -- Status of the log file. Always 0.
create table dbfileinfo
(
[ServerName] varchar(255),
[DBName] varchar(255),
[LogicalFileName] varchar(400),
[UsageType] varchar (30),
[Size_MB] dec(20, 2),
[SpaceUsed_MB] dec(20, 2),
[MaxSize_MB] dec(20, 2),
[NextAllocation_MB] dec(20, 2),
[GrowthType] varchar(65),
[FileId] smallint,
[GroupId] smallint,
[PhysicalFileName] varchar(400),
[DateChecked] datetime
)
declare @SQLString varchar(3000)
declare @MinId int
declare @MaxId int
declare @DBName varchar(255)
declare @tblDBName table (
RowId int identity(1, 1),
DBName varchar(255),
DBId int)
insert into @tblDBName
(DBName,
DBId)
select [Name],
DBId
from master..sysdatabases
where ( Status & 512 ) = 0 /*NOT IN (536,528,540,2584,1536,512,4194841)*/
order by [Name]
insert into logsizestats
(DBName,
LogFile,
LogFileUsed,
Status)
exec ('dbcc sqlperf(logspace) with no_infomsgs')
update logsizestats
set DBId = db_id(DBName)
insert into fixeddrives
exec master..xp_fixeddrives
select @MinId = min(RowId),
@MaxId = max(RowId)
from @tblDBName
while ( @MinId <= @MaxId )
begin
select @DBName = [DBName]
from @tblDBName
where RowId = @MinId
select @SQLString = 'SELECT ServerName = @@SERVERNAME,' +
' DBName = ''' +
@DBName +
''',' +
' LogicalFileName = [name],' +
' UsageType = CASE WHEN (64&[status])=64 THEN ''Log'' ELSE ''Data'' END,'
+
' Size_MB = [size]*8/1024.00,' +
' SpaceUsed_MB = NULL,' +
-- 20081125 Arithmetic overflow error converting expression to data type int.
-- ' MaxSize_MB = CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN [size]*8/1024.00 ELSE maxsize*8/1024.00 END,'+
' MaxSize_MB = CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN [size]*8/1024.00 ELSE maxsize/1024.00*8 END,'
+
-- 20081125 end
' NextExtent_MB = CASE WHEN (1048576&[status])=1048576 THEN ([growth]/100.00)*([size]*8/1024.00) WHEN [growth]=0 THEN 0 ELSE [growth]*8/1024.00 END,'
+
' GrowthType = CASE WHEN (1048576&[status])=1048576 THEN ''%'' ELSE ''Pages'' END,'
+ ' FileId = [fileid],' + ' GroupId = [groupid],' +
' PhysicalFileName= [filename],' +
' CurTimeStamp = GETDATE()' +
-- 20081125 begin @DBName embedded spaces
-- 'FROM '+@DBName+'..sysfiles'
'FROM [' + @DBName + ']..sysfiles'
-- 20081125 end
print @SQLString
insert into dbfileinfo
exec (@SQLString)
update dbfileinfo
-- 20081125 begin LogFileUsed is %
-- SET SpaceUsed_MB = (SELECT LogFileUsed FROM LogSizeStats WHERE DBName = @DBName)
set SpaceUsed_MB = Size_MB / 100.0 * (select LogFileUsed
from logsizestats
where DBName = @DBName)
-- 20081125 end
where UsageType = 'Log'
and DBName = @DBName
-- 20081125 begin @DBName embedded spaces
-- SELECT @SQLString = 'USE ' + @DBName + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'
select @SQLString = 'USE [' + @DBName +
'] DBCC SHOWFILESTATS WITH NO_INFOMSGS'
-- 20081125 end
insert datafilestats
(FileId,
[FileGroup],
TotalExtents,
UsedExtents,
[Name],
[FileName])
execute(@SQLString)
update dbfileinfo
set [SpaceUsed_MB] = S.[UsedExtents] * 64 / 1024.00
from dbfileinfo as F
inner join datafilestats as S
on F.[FileId] = S.[FileId]
and F.[GroupId] = S.[FileGroup]
and F.[DBName] = @DBName
truncate table datafilestats
select @MinId = @MinId + 1
end
select @@servername as servidor,
substring(A.PhysicalFileName, 1, 1) as unidad,
sum ([Size_MB]) as SqlTotalDB,
sum([SpaceUsed_MB]) as SqlTotalUsedSpaceDB,
sum (( [Size_MB] ) - ( [SpaceUsed_MB] ))as SQLTotalFreeSpaceDB
into usados
from dbfileinfo as A
left join fixeddrives as B
on substring(A.PhysicalFileName, 1, 1) = B.DriveLetter
group by substring(A.PhysicalFileName, 1, 1)
select servidor,
DriveLetter,
MB_Free as
RealMb_free,
MB_Free + SQLTotalFreeSpaceDB as
MB_FreeNeto,
SqlTotalDB,
abs(( SqlTotalDB - SQLTotalFreeSpaceDB )) as
SQLTotalUsedSpaceDB,
SQLTotalFreeSpaceDB,
((SqlTotalDB - SQLTotalFreeSpaceDB)*100)/(SqlTotalDB + MB_Free) as
Porcentaje_Uso_DB
from fixeddrives as f
inner join usados as z
on z.unidad = f.DriveLetter
where ((((SqlTotalDB - SQLTotalFreeSpaceDB)*100)/(SqlTotalDB + MB_Free) > @percent) and (MB_Free < @M_free_high))
or MB_Free < @M_free_low
order by MB_FreeNeto
if exists (select 1
from gestdb..sysobjects
where [Id] = object_id('gestdb..dbfileinfo'))
begin
drop table dbfileinfo
end
if exists (select 1
from gestdb..sysobjects
where [Id] = object_id('gestdb..logsizestats'))
begin
drop table logsizestats
end
if exists (select 1
from gestdb..sysobjects
where [Id] = object_id('gestdb..datafilestats'))
begin
drop table datafilestats
end
if exists (select 1
from gestdb..sysobjects
where [Id] = object_id('gestdb..fixeddrives'))
begin
drop table fixeddrives
end
if exists (select 1
from gestdb..sysobjects
where [Id] = object_id('gestdb..usados'))
begin
drop table usados
end
-- 20081125 begin SET NOCOUNT OFF
set nocount off
-- 20081125 end
end
GO 
