SQL Server 监视数据文件大小变化

数据库文件大小日志表：

Code

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1

USE [MyManagement] 2

GO 3

SET ANSI_NULLS ON 4

GO 5

SET QUOTED_IDENTIFIER ON 6

GO 7

SET ANSI_PADDING ON 8

GO 9

CREATE TABLE [dbo].[DatabaseFileLog](10

[id] [int] IDENTITY(1,1) NOT NULL,11

[LogTime] [datetime] NOT NULL CONSTRAINT [DF_DatabaseFileLog_LogTime] DEFAULT (getdate()),12

[DatabaseName] [varchar](50) NOT NULL,13

[FileLogicalName] [varchar](50) NOT NULL,14

[FilePath] [varchar](4000) NOT NULL,15

[SizeMB] [decimal](18, 4) NOT NULL,16

[FileGroupID] [int] NOT NULL,17

CONSTRAINT [PK_DatabaseFileLog] PRIMARY KEY CLUSTERED 18

(19

[id] ASC20

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]21

) ON [PRIMARY]22

23

GO24

SET ANSI_PADDING OFF

建立一个存储过程，以便定时调用：

Code

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1

declare @dbName varchar(50) 2

declare @command varchar(1024) 3

declare dbName_cursor CURSOR FOR 4

select [name] 5

from master.dbo.sysdatabases 6

where [name] not in ('master','tempdb','msdb','model') 7

open dbName_cursor 8

FETCH NEXT FROM dbName_cursor INTO @dbName 9

WHILE @@FETCH_STATUS = 0 10

begin11

set @command = '12

insert into MyManagement.dbo.DatabaseFileLog13

select 14

getdate(),15

'''+16

@dbName17

+''',18

name,19

filename, 20

convert(float,size) * (8192.0/1024.0)/1024.0 as ''MB'',21

groupid22

from '+@dbName +'.dbo.sysfiles ';23

exec ( @command );24

FETCH NEXT FROM dbName_cursor INTO @dbName ;25

end26

CLOSE dbName_cursor;27

DEALLOCATE dbName_cursor;

下一步计划是做一个好一点的表现层，以便直观地查看。

如果嫌上面麻烦可以这么用下面的语句：

Code

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1

select 2

'select 3

getdate(), 4

'''+ 5

[name] 6

+''', 7

name, 8

filename, 9

convert(float,size) * (8192.0/1024.0)/1024.0 as ''MB'',10

convert(float,size) * (8192.0/1024.0)/1024.0/1024.0 as ''GB'',11

groupid12

from '+[name] +'.dbo.sysfiles union all '13

from master.dbo.sysdatabases 14

where [name] not in ('master','tempdb','msdb','model')15

union all 16

select 'select null,null,null,null,null,null,null where 1=2'

执行以上的语句，将会再生成一大堆的语句，然后全部复制到新窗口中，再一次过执行。

[@more@]

ITPub博客

SQL Server 监视数据文件大小变化

SQL Server 监视数据文件大小变化

最新文章