1
2 /*
3 ** SP_SPACEUSAGE_TRANLOG
4 **
5 ** The driver procedure for the getting space usage information for entity
6 ** type - "tranlog" (transaction log). Called by sp_spaceusage.
7 **
8 ** Parameters:
9 ** @actionword - The action to be performed.
10 ** @unit - Unit for displaying page counts, if any.
11 ** @archive_db - Name of the database where the archive table
12 ** resides or will be created.
13 ** @archive_prefix - Prefix for the archive table name, if any.
14 ** @select_list - Comma separated list of columns to project
15 ** the output.
16 ** @where_clause - A WHERE clause, if any, to filter the output.
17 ** @order_by_clause- An ORDER BY clause, to order the output.
18 ** @from_date - Date, if any, from when on to look for data
19 ** in the archive table to generate report.
20 ** @to_date - Date, if any, till when to look for data in
21 ** the archive table to generate report.
22 **
23 ** Returns
24 ** 0 - if all goes well
25 ** 6 - archive table not found
26 ** other - error while execution
27 {
28 */
29 create procedure sp_spaceusage_tranlog
30 (
31 @actionword varchar(20)
32 , @unit varchar(6) = NULL
33 , @archive_db varchar(30) = NULL
34 , @archive_prefix varchar(237) = NULL
35 , @select_list varchar(1536) = NULL
36 , @where_clause varchar(1536) = NULL
37 , @order_by_clause varchar(768) = NULL
38 , @from_date varchar(30) = NULL
39 , @to_date varchar(30) = NULL
40
41 )
42 as
43 begin -- {
44
45 declare
46 @returnStatus int
47 , @archivetabname varchar(320)
48 , @defaultarchivetabname varchar(30)
49
50 -- String constants for the action
51 , @action_display varchar(8)
52 , @action_report varchar(7)
53 , @action_archive varchar(8)
54
55 select
56 @archivetabname = NULL
57 , @defaultarchivetabname = "spaceusage_tranlog"
58
59 , @action_display = "display"
60 , @action_report = "report"
61 , @action_archive = "archive"
62
63 if @actionword in (@action_report, @action_archive)
64 begin -- This is report/archive mode
65
66 -- Verify that the archive database does exist.
67 if not exists (select 1
68 from master.dbo.sysdatabases
69 where name like @archive_db)
70 begin
71 raiserror 18322, @archive_db
72 return (6)
73 end
74
75 select @archivetabname = @archive_db + "." + user_name() + "."
76 + case @archive_prefix
77 when NULL
78 then @defaultarchivetabname
79 else @archive_prefix
80 + @defaultarchivetabname
81 end
82
83 -- Verify the archive table does exist in the database for the
84 -- report action.
85 --
86 if @actionword = @action_report
87 and object_id(@archivetabname) is NULL
88 begin
89 declare @tmpname varchar(290)
90
91 select @tmpname = user_name() + "."
92 + case @archive_prefix
93 when NULL
94 then @defaultarchivetabname
95 else @archive_prefix
96 + @defaultarchivetabname
97 end
98
99 raiserror 17870, @tmpname, @archive_db, NULL
100 return (6)
101 end
102 end
103
104 /*
105 ** Create and initialize the temporary #syslogsinfo table based on
106 ** the action specified. For 'display'/'archive' action, the table is
107 ** inserted with a single row for the current state of the syslgos using
108 ** the lct_admin() built-ins. For 'report' action, the table is
109 ** populated by selecting into it the relavant data from the archive
110 ** table.
111 **
112 ** NOTE: Any change done to this table needs a change in the
113 ** corresponding #syslogsinfo table created at the beginning of the
114 ** file (at line #15). And the inserts that are done in the procedure
115 ** sp_spaceusage_tranlog_init (at lines #116, 209).
116 */
117 create table #syslogsinfo
118 (
119 VersionNum int NOT NULL
120 , ESDNum int NOT NULL
121 , EBFNum int NOT NULL
122 , ArchiveDateTime datetime NOT NULL
123 , ServerName varchar(255) NULL
124 , MaxPageSize int NOT NULL
125 , DBName varchar(30) NOT NULL
126 , OwnerName varchar(30) NOT NULL
127 , TableName varchar(255) NOT NULL
128 , Id int NOT NULL
129 , NumRows int NULL
130 , IsMLD bit NOT NULL
131 , IsLogFull bit NOT NULL
132 , LCTPageCount int NULL
133 , TotalPageCount int NULL
134 , RsvdPageCount int NULL
135 , CLRPageCount int NULL
136 , UsedPageCount int NULL
137 , FreePageCount int NULL
138 , PctFreePages decimal(5, 2) NULL
139 , PctUsedPages decimal(5, 2) NULL
140 , LCTPages float NULL
141 , TotalPages float NULL
142 , RsvdPages float NULL
143 , CLRPages float NULL
144 , UsedPages float NULL
145 , FreePages float NULL
146 )
147
148 if (@@error != 0)
149 return (@@error)
150
151 exec @returnStatus = sp_spaceusage_tranlog_init @actionword
152 , @archivetabname
153 , @from_date
154 , @to_date
155
156 if @returnStatus != 0
157 return (@returnStatus)
158
159 /*
160 ** Depending on the unit specifier, convert the page counts into the
161 ** appropriate data type before display.
162 **
163 ** NOTE: Although, archive mode ideally would only archive the value
164 ** in pages, it may have a WHERE clause on these computed columns for
165 ** which it requires these converted values.
166 */
167 exec @returnStatus = sp_spaceusage_cnvtounit @unit, "#syslogsinfo"
168
169 if @returnStatus != 0
170 return (@returnStatus)
171
172 /*
173 ** Now, display/archive the information collected and computed to the
174 ** user based on the action. For display/report the user expects to see
175 ** the output on screen. And for archive action the user expects the
176 ** output to be appended to the archive table.
177 */
178 if @actionword in (@action_display, @action_report)
179 begin -- {
180
181 exec @returnStatus = sp_spaceusage_tranlog_genoutput
182 @actionword
183 , @unit
184 , @select_list
185 , @where_clause
186 , @order_by_clause
187
188 if @returnStatus != 0
189 return (@returnStatus)
190
191 end -- }
192 else if @actionword = @action_archive
193 begin -- {
194
195 exec @returnStatus = sp_spaceusage_tranlog_archdata
196 @archivetabname
197 , @where_clause
198
199 if @returnStatus != 0
200 return (@returnStatus)
201
202 end -- }
203
204 drop table #syslogsinfo
205
206 end -- } -- }
207
exec sp_procxmode 'sp_spaceusage_tranlog', 'AnyMode'
go
Grant Execute on sp_spaceusage_tranlog to public
go