查看数据大小和日志文件大小
利用以下存储过程可以查看数据文件和日志文件的使用情况。每天运行下，进而掌握数据文件和日志文件的变化
1 USE master; 2 GO 3 4 IF OBJECT_ID('dbo.sp_SDS', 'P') IS NOT NULL 5 DROP PROCEDURE dbo.sp_SDS; 6 GO 7 8 CREATE PROCEDURE dbo.sp_SDS 9 @TargetDatabase nvarchar(128) = NULL, -- NULL: all dbs 10 @Level varchar(10) = 'Database', -- or "File" 11 @UpdateUsage bit = 0, -- default no update 12 @Unit char(2) = 'MB' -- Megabytes, Kilobytes or Gigabytes 13 AS 14 15 /************************************************************************************************** 16 ** 17 ** author: Richard Ding 18 ** date: 4/8/2008 19 ** usage: list db size AND path w/o SUMmary 20 ** test code: sp_SDS -- default behavior 21 ** sp_SDS 'maAster' 22 ** sp_SDS NULL, NULL, 0 23 ** sp_SDS NULL, 'file', 1, 'GB' 24 ** sp_SDS 'Test_snapshot', 'Database', 1 25 ** sp_SDS 'Test', 'File', 0, 'kb' 26 ** sp_SDS 'pfaids', 'Database', 0, 'gb' 27 ** sp_SDS 'tempdb', NULL, 1, 'kb' 28 ** 29 **************************************************************************************************/ 30 31 SET NOCOUNT ON; 32 33 IF @TargetDatabase IS NOT NULL AND DB_ID(@TargetDatabase) IS NULL 34 BEGIN 35 RAISERROR(15010, -1, -1, @TargetDatabase); 36 RETURN (-1) 37 END 38 39 IF OBJECT_ID('tempdb.dbo.##Tbl_CombinedInfo', 'U') IS NOT NULL 40 DROP TABLE dbo.##Tbl_CombinedInfo; 41 42 IF OBJECT_ID('tempdb.dbo.##Tbl_DbFileStats', 'U') IS NOT NULL 43 DROP TABLE dbo.##Tbl_DbFileStats; 44 45 IF OBJECT_ID('tempdb.dbo.##Tbl_ValidDbs', 'U') IS NOT NULL 46 DROP TABLE dbo.##Tbl_ValidDbs; 47 48 IF OBJECT_ID('tempdb.dbo.##Tbl_Logs', 'U') IS NOT NULL 49 DROP TABLE dbo.##Tbl_Logs; 50 51 CREATE TABLE dbo.##Tbl_CombinedInfo ( 52 DatabaseName sysname NULL, 53 [type] VARCHAR(10) NULL, 54 LogicalName sysname NULL, 55 T dec(10, 2) NULL, 56 U dec(10, 2) NULL, 57 [U(%)] dec(5, 2) NULL, 58 F dec(10, 2) NULL, 59 [F(%)] dec(5, 2) NULL, 60 PhysicalName sysname NULL ); 61 62 CREATE TABLE dbo.##Tbl_DbFileStats ( 63 Id int identity, 64 DatabaseName sysname NULL, 65 FileId int NULL, 66 FileGroup int NULL, 67 TotalExtents bigint NULL, 68 UsedExtents bigint NULL, 69 Name sysname NULL, 70 FileName varchar(255) NULL ); 71 72 CREATE TABLE dbo.##Tbl_ValidDbs ( 73 Id int identity, 74 Dbname sysname NULL ); 75 76 CREATE TABLE dbo.##Tbl_Logs ( 77 DatabaseName sysname NULL, 78 LogSize dec (10, 2) NULL, 79 LogSpaceUsedPercent dec (5, 2) NULL, 80 Status int NULL ); 81 82 DECLARE @Ver varchar(20), 83 @DatabaseName sysname, 84 @Ident_last int, 85 @String varchar(2000), 86 @BaseString varchar(2000); 87 88 SELECT @DatabaseName = '', 89 @Ident_last = 0, 90 @String = '', 91 @Ver = CASE WHEN @@VERSION LIKE '%9.0%' THEN 'SQL 2005' 92 WHEN @@VERSION LIKE '%8.0%' THEN 'SQL 2000' 93 WHEN @@VERSION LIKE '%10.0%' THEN 'SQL 2008' 94 WHEN @@VERSION LIKE '%10.5%' THEN 'SQL 2008 R2' 95 END; 96 97 SELECT @BaseString = 98 ' SELECT DB_NAME(), ' + 99 CASE WHEN @Ver = 'SQL 2000' THEN 'CASE WHEN status & 0x40 = 0x40 THEN ''Log'' ELSE ''Data'' END' 100 ELSE ' CASE type WHEN 0 THEN ''Data'' WHEN 1 THEN ''Log'' WHEN 4 THEN ''Full-text'' ELSE ''reserved'' END' END + 101 ', name, ' + 102 CASE WHEN @Ver = 'SQL 2000' THEN 'filename' ELSE 'physical_name' END + 103 ', size*8.0/1024.0 FROM ' + 104 CASE WHEN @Ver = 'SQL 2000' THEN 'sysfiles' ELSE 'sys.database_files' END + 105 ' WHERE ' 106 + CASE WHEN @Ver = 'SQL 2000' THEN ' HAS_DBACCESS(DB_NAME()) = 1' ELSE 'state_desc = ''ONLINE''' END + ''; 107 108 SELECT @String = 'INSERT INTO dbo.##Tbl_ValidDbs SELECT name FROM ' + 109 CASE WHEN @Ver = 'SQL 2000' THEN 'master.dbo.sysdatabases' 110 WHEN @Ver IN ('SQL 2005', 'SQL 2008', 'SQL 2008 R2') THEN 'master.sys.databases' 111 END + ' WHERE HAS_DBACCESS(name) = 1 ORDER BY name ASC'; 112 EXEC (@String); 113 114 INSERT INTO dbo.##Tbl_Logs EXEC ('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'); 115 116 -- For data part 117 IF @TargetDatabase IS NOT NULL 118 BEGIN 119 SELECT @DatabaseName = @TargetDatabase; 120 IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName,'Status') = 'ONLINE' 121 AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY' 122 BEGIN 123 SELECT @String = 'USE [' + @DatabaseName + '] DBCC UPDATEUSAGE (0)'; 124 PRINT '*** ' + @String + ' *** '; 125 EXEC (@String); 126 PRINT ''; 127 END 128 129 SELECT @String = 'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString; 130 131 INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName) 132 EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS'); 133 EXEC ('USE [' + @DatabaseName + '] ' + @String); 134 135 UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName; 136 END 137 ELSE 138 BEGIN 139 WHILE 1 = 1 140 BEGIN 141 SELECT TOP 1 @DatabaseName = Dbname FROM dbo.##Tbl_ValidDbs WHERE Dbname > @DatabaseName ORDER BY Dbname ASC; 142 IF @@ROWCOUNT = 0 143 BREAK; 144 IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName, 'Status') = 'ONLINE' 145 AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY' 146 BEGIN 147 SELECT @String = 'DBCC UPDATEUSAGE (''' + @DatabaseName + ''') '; 148 PRINT '*** ' + @String + '*** '; 149 EXEC (@String); 150 PRINT ''; 151 END 152 153 SELECT @Ident_last = ISNULL(MAX(Id), 0) FROM dbo.##Tbl_DbFileStats; 154 155 SELECT @String = 'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString; 156 157 EXEC ('USE [' + @DatabaseName + '] ' + @String); 158 159 INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName) 160 EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS'); 161 162 UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName WHERE Id BETWEEN @Ident_last + 1 AND @@IDENTITY; 163 END 164 END 165 166 -- set used size for data files, do not change total obtained from sys.database_files as it has for log files 167 UPDATE dbo.##Tbl_CombinedInfo 168 SET U = s.UsedExtents*8*8/1024.0 169 FROM dbo.##Tbl_CombinedInfo t JOIN dbo.##Tbl_DbFileStats s 170 ON t.LogicalName = s.Name AND s.DatabaseName = t.DatabaseName; 171 172 -- set used size and % values for log files: 173 UPDATE dbo.##Tbl_CombinedInfo 174 SET [U(%)] = LogSpaceUsedPercent, 175 U = T * LogSpaceUsedPercent/100.0 176 FROM dbo.##Tbl_CombinedInfo t JOIN dbo.##Tbl_Logs l 177 ON l.DatabaseName = t.DatabaseName 178 WHERE t.type = 'Log'; 179 180 UPDATE dbo.##Tbl_CombinedInfo SET F = T - U, [U(%)] = U*100.0/T; 181 182 UPDATE dbo.##Tbl_CombinedInfo SET [F(%)] = F*100.0/T; 183 184 IF UPPER(ISNULL(@Level, 'DATABASE')) = 'FILE' 185 BEGIN 186 IF @Unit = 'KB' 187 UPDATE dbo.##Tbl_CombinedInfo 188 SET T = T * 1024, U = U * 1024, F = F * 1024; 189 190 IF @Unit = 'GB' 191 UPDATE dbo.##Tbl_CombinedInfo 192 SET T = T / 1024, U = U / 1024, F = F / 1024; 193 194 SELECT DatabaseName AS 'Database', 195 type AS 'Type', 196 LogicalName, 197 T AS 'Total', 198 U AS 'Used', 199 [U(%)] AS 'Used (%)', 200 F AS 'Free', 201 [F(%)] AS 'Free (%)', 202 PhysicalName 203 FROM dbo.##Tbl_CombinedInfo 204 WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%') 205 ORDER BY DatabaseName ASC, type ASC; 206 207 SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM', 208 SUM (T) AS 'TOTAL', SUM (U) AS 'USED', SUM (F) AS 'FREE' FROM dbo.##Tbl_CombinedInfo; 209 END 210 211 IF UPPER(ISNULL(@Level, 'DATABASE')) = 'DATABASE' 212 BEGIN 213 DECLARE @Tbl_Final TABLE ( 214 DatabaseName sysname NULL, 215 TOTAL dec (10, 2), 216 [=] char(1), 217 used dec (10, 2), 218 [used (%)] dec (5, 2), 219 [+] char(1), 220 free dec (10, 2), 221 [free (%)] dec (5, 2), 222 [==] char(2), 223 Data dec (10, 2), 224 Data_Used dec (10, 2), 225 [Data_Used (%)] dec (5, 2), 226 Data_Free dec (10, 2), 227 [Data_Free (%)] dec (5, 2), 228 [++] char(2), 229 Log dec (10, 2), 230 Log_Used dec (10, 2), 231 [Log_Used (%)] dec (5, 2), 232 Log_Free dec (10, 2), 233 [Log_Free (%)] dec (5, 2) ); 234 235 INSERT INTO @Tbl_Final 236 SELECT x.DatabaseName, 237 x.Data + y.Log AS 'TOTAL', 238 '=' AS '=', 239 x.Data_Used + y.Log_Used AS 'U', 240 (x.Data_Used + y.Log_Used)*100.0 / (x.Data + y.Log) AS 'U(%)', 241 '+' AS '+', 242 x.Data_Free + y.Log_Free AS 'F', 243 (x.Data_Free + y.Log_Free)*100.0 / (x.Data + y.Log) AS 'F(%)', 244 '==' AS '==', 245 x.Data, 246 x.Data_Used, 247 x.Data_Used*100/x.Data AS 'D_U(%)', 248 x.Data_Free, 249 x.Data_Free*100/x.Data AS 'D_F(%)', 250 '++' AS '++', 251 y.Log, 252 y.Log_Used, 253 y.Log_Used*100/y.Log AS 'L_U(%)', 254 y.Log_Free, 255 y.Log_Free*100/y.Log AS 'L_F(%)' 256 FROM 257 ( SELECT d.DatabaseName, 258 SUM(d.T) AS 'Data', 259 SUM(d.U) AS 'Data_Used', 260 SUM(d.F) AS 'Data_Free' 261 FROM dbo.##Tbl_CombinedInfo d WHERE d.type = 'Data' GROUP BY d.DatabaseName ) AS x 262 JOIN 263 ( SELECT l.DatabaseName, 264 SUM(l.T) AS 'Log', 265 SUM(l.U) AS 'Log_Used', 266 SUM(l.F) AS 'Log_Free' 267 FROM dbo.##Tbl_CombinedInfo l WHERE l.type = 'Log' GROUP BY l.DatabaseName ) AS y 268 ON x.DatabaseName = y.DatabaseName; 269 270 IF @Unit = 'KB' 271 UPDATE @Tbl_Final SET TOTAL = TOTAL * 1024, 272 used = used * 1024, 273 free = free * 1024, 274 Data = Data * 1024, 275 Data_Used = Data_Used * 1024, 276 Data_Free = Data_Free * 1024, 277 Log = Log * 1024, 278 Log_Used = Log_Used * 1024, 279 Log_Free = Log_Free * 1024; 280 281 IF @Unit = 'GB' 282 UPDATE @Tbl_Final SET TOTAL = TOTAL / 1024, 283 used = used / 1024, 284 free = free / 1024, 285 Data = Data / 1024, 286 Data_Used = Data_Used / 1024, 287 Data_Free = Data_Free / 1024, 288 Log = Log / 1024, 289 Log_Used = Log_Used / 1024, 290 Log_Free = Log_Free / 1024; 291 292 DECLARE @GrantTotal dec(11, 2); 293 SELECT @GrantTotal = SUM(TOTAL) FROM @Tbl_Final; 294 295 SELECT 296 CONVERT(dec(10, 2), TOTAL*100.0/@GrantTotal) AS 'WEIGHT (%)', 297 DatabaseName AS 'DATABASE', 298 CONVERT(VARCHAR(12), used) + ' (' + CONVERT(VARCHAR(12), [used (%)]) + ' %)' AS 'USED (%)', 299 [+], 300 CONVERT(VARCHAR(12), free) + ' (' + CONVERT(VARCHAR(12), [free (%)]) + ' %)' AS 'FREE (%)', 301 [=], 302 TOTAL, 303 [=], 304 CONVERT(VARCHAR(12), Data) + ' (' + CONVERT(VARCHAR(12), Data_Used) + ', ' + 305 CONVERT(VARCHAR(12), [Data_Used (%)]) + '%)' AS 'DATA (used, %)', 306 [+], 307 CONVERT(VARCHAR(12), Log) + ' (' + CONVERT(VARCHAR(12), Log_Used) + ', ' + 308 CONVERT(VARCHAR(12), [Log_Used (%)]) + '%)' AS 'LOG (used, %)' 309 FROM @Tbl_Final 310 WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%') 311 ORDER BY DatabaseName ASC; 312 313 IF @TargetDatabase IS NULL 314 SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM', 315 SUM (used) AS 'USED', 316 SUM (free) AS 'FREE', 317 SUM (TOTAL) AS 'TOTAL', 318 SUM (Data) AS 'DATA', 319 SUM (Log) AS 'LOG' 320 FROM @Tbl_Final; 321 END 322 323 RETURN (0) 324 325 GO
执行存储过程exec dbo.sp_SDS
参考文章：http://www.jppinto.com/2011/12/sp_sds-stored-procedure-updated-to-work-with-sql-server-2008-r2/
