统计数据库和日志每天的大小和增加量

CREATE TABLE[dbo].[db_size](
Id int identity,
[vtime][datetime]NULL,
[database_size][nvarchar](50)NULL,
[database_sizeZ][nvarchar](50)NULL,
[database_sizeB][nvarchar](50)NULL,
[free_size][nvarchar](50)NULL,
[free_sizeZ][nvarchar](50)NULL,
[free_sizeB][nvarchar](50)NULL
)
GO
CREATE PROCEDURE[dbo].[get_db_size]
AS
BEGIN
SET NOCOUNT ON;
declare
@pages bigint
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@dbsizePre decimal(18,4)
,@logsizePre decimal(18,4)
,@dbsizeB decimal(18,4)
,@logsizeB decimal(18,4)
,@dbsizeZ decimal(18,4)
,@logsizeZ decimal(18,4)
,@dbsizeBF decimal(18,4)
,@logsizeBF decimal(18,4)
select@dbsize=sum(convert(bigint,case when status&64 =0 then size else 0 end))
,@logsize=sum(convert(bigint,case when status&64 <>0 then size else 0 end))
from dbo.sysfiles
select@reservedpages=sum(a.total_pages),
@usedpages=sum(ed_pages),
@pages=sum(
CASE
When it.internal_type IN(202,204)Then 0
When a.type<> 1 Then ed_pages
When p.index_id< 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id= a.container_id
left join sys.internal_tables it on p.object_id=it.object_id
select top 1
@dbsizePre=CONVERT(decimal,database_size),@logsizePre=CONVERT(decimal,fr ee_size)from db_size order by id desc
set@dbsizeB=CONVERT(decimal,ltrim(str((convert(dec (15,2),@dbsize)+ convert(dec (15,2),@logsize))* 8192 / 1048576,15,2)))
set@logsizeB=CONVERT(decimal,ltrim(str((case when@dbsize>=
@reservedpages then (convert(dec (15,2),@dbsize)-convert(dec (15,2),@reservedpages))* 8192 / 1048576 else 0 end),15,2)))
set@dbsizeZ=@dbsizeB-@dbsizePre
set@logsizeZ=@logsizeB-@logsizePre
set@dbsizeBF=(@dbsizeZ/@dbsizePre)*100
set@logsizeBF=(@logsizeZ/@logsizePre)*100
INSERT INTO db_size(
[vtime]
,[database_size]
,[database_sizeZ]
,[database_sizeB]
,[free_size]
,[free_sizeZ]
,[free_sizeB])
select
vtime=getdate(),
@dbsizeB,
@dbsizeZ,
@dbsizeBF,
@logsizeB,
@logsizeZ,
@logsizeBF
END
GO
--放到每天作业里调用即可。

--exec [get_db_size]
--检查大小：
--select * from db_size
select*from db_size
truncate table db_size
update db_size set database_size=530,free_size=7 where id=7
select top1 CONVERT(bigint,database_size),CONVERT(bigint,free_size)from db_size order by id desc。