数据库文件大小日志表:
Code
1USE [MyManagement]
2GO
3SET ANSI_NULLS ON
4GO
5SET QUOTED_IDENTIFIER ON
6GO
7SET ANSI_PADDING ON
8GO
9CREATE 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] ASC
20)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
23GO
24SET ANSI_PADDING OFF
建立一个存储过程,以便定时调用:
Code
1declare @dbName varchar(50)
2declare @command varchar(1024)
3declare dbName_cursor CURSOR FOR
4 select [name]
5 from master.dbo.sysdatabases
6 where [name] not in ('master','tempdb','msdb','model')
7open dbName_cursor
8FETCH NEXT FROM dbName_cursor INTO @dbName
9WHILE @@FETCH_STATUS = 0
10begin
11 set @command = '
12 insert into MyManagement.dbo.DatabaseFileLog
13 select
14 getdate(),
15 '''+
16 @dbName
17 +''',
18 name,
19 filename,
20 convert(float,size) * (8192.0/1024.0)/1024.0 as ''MB'',
21 groupid
22 from '+@dbName +'.dbo.sysfiles ';
23 exec ( @command );
24 FETCH NEXT FROM dbName_cursor INTO @dbName ;
25end
26CLOSE dbName_cursor;
27DEALLOCATE dbName_cursor;
下一步计划是做一个好一点的表现层,以便直观地查看。
如果嫌上面麻烦可以这么用下面的语句:
Code
1select
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 groupid
12 from '+[name] +'.dbo.sysfiles union all '
13from master.dbo.sysdatabases
14where [name] not in ('master','tempdb','msdb','model')
15union all
16select 'select null,null,null,null,null,null,null where 1=2'
执行以上的语句,将会再生成一大堆的语句,然后全部复制到新窗口中,再一次过执行。