DatabaseProcApplicationCreatedLinks
sybsystemprocssp_logdevice  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	5.0	14.2	11/12/91	sproc/src/logdevice */
4     
5     /*
6     ** Messages for "sp_logdevice"          17710
7     **
8     ** 17260, "Can't run %1! from within a transaction."
9     ** 17421, "No such database -- run sp_helpdb to list databases."
10    ** 17471, "No such device exists -- run sp_helpdevice to list the DataServer devices."
11    ** 17281, "The specified device is not used by the database."
12    ** 17710, "This command has been ignored.  The device specified
13    **	   is the only non-log device available for the database and
14    **	   cannot be made log-only."
15    ** 17711, "Syslogs moved."
16    ** 17715, "The last-chance threshold for database %1! is now %2! pages."
17    ** 17716, "Could not update the last-chance threshold for database %1!"
18    ** 17289, "Set your curwrite to the hurdle of current database."
19    */
20    
21    create or replace procedure sp_logdevice
22        @dbname varchar(30), /* database name that has the syslogs */
23        @devname varchar(30) /* device name to put syslogs on */
24    
25    as
26    
27        declare @dbid smallint /* dbid of the database to be changed */
28        declare @dbuid int /* id of the owner of the database */
29        declare @logbit int /* this is the bit to turn on in sysusages */
30        declare @lct int /* last-chance threshold value */
31        declare @msg varchar(1024)
32        declare @bitdesc varchar(30) /* bit description for the db */
33        declare @tempdb_mask int /* All database status bit for a tempdb */
34        declare @isatempdb int /* Is this a temp. db ? */
35        declare @nullarg char(1)
36        declare @dummy int
37        declare @status int
38        declare @gp_enabled int
39    
40    
41    
42    
43        /*
44        **  If we're in a transaction, disallow this since it might make recovery
45        **  impossible.
46        */
47        if @@trancount > 0
48        begin
49            /*
50            ** 17260, "Can't run %1! from within a transaction."
51            */
52            raiserror 17260, "sp_logdevice"
53            return (1)
54        end
55        else
56        begin
57            set chained off
58        end
59    
60        set transaction isolation level 1
61    
62        select @logbit = 4 /* bit 3 is the one to turn on */
63    
64        /*
65        **  Verify the database name and get the @dbid and @dbuid
66        */
67        select @dbid = dbid, @dbuid = suid
68        from master.dbo.sysdatabases
69        where name = @dbname
70    
71        /*
72        **  If @dbname not found, say so and list the databases.
73        */
74        if @dbid is NULL
75        begin
76            /*
77            ** 17421, "No such database -- run sp_helpdb to list databases."
78            */
79            raiserror 17421
80            return (1)
81        end
82    
83    
84        /* 
85        ** If granular permissions is not enabled then the user must be dbo or
86        ** sa_role is required.
87        ** If granular permissions is enabled then the permission 'own database' is
88        ** required.  proc_role and proc_auditperm will also do auditing
89        ** if required. Both will also print error message if required.
90        */
91    
92        select @nullarg = NULL
93        execute @status = sp_aux_checkroleperm "sa_role", "own database",
94            @dbname, @gp_enabled output
95    
96        /* For Auditing */
97        if (@gp_enabled = 0)
98        begin
99            if ((suser_id() != @dbuid) and (proc_role("sa_role") < 1))
100               return (1)
101       end
102       else
103       begin
104           select @dummy = proc_auditperm("own database", @status, @dbname)
105       end
106   
107       if (@status != 0)
108           return (1)
109       /*
110       ** Determine if we are dealing with a temporary database.
111       */
112       select @tempdb_mask = number
113       from master.dbo.spt_values
114       where type = "D3" and name = "TEMPDB STATUS MASK"
115   
116       if (@dbid = 2) or exists (select * from master.dbo.sysdatabases
117               where dbid = @dbid
118                   and (status3 & @tempdb_mask) != 0)
119       begin
120           select @isatempdb = 1
121       end
122       else
123       begin
124           select @isatempdb = 0
125       end
126   
127       /* 
128       ** Check single user bit (4096).
129       ** Database must be in single user mode so that no log allocation
130       ** or deallocation take place while recalculing the free space counter.
131       ** However, this requirement is relaxed for temporary databases.
132       */
133       if @isatempdb = 0
134       begin
135           select @bitdesc = null
136           select @bitdesc = v.name
137           from master.dbo.spt_values v, master.dbo.sysdatabases d
138           where d.dbid = @dbid
139               and v.type = "D"
140               and d.status & v.number = 4096
141   
142           if @bitdesc is null
143           begin
144               /*
145               ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'."
146               */
147               raiserror 17793, @dbname, "sp_logdevice"
148               return (1)
149           end
150       end
151   
152       /*
153       **  See if the device exists.
154       */
155       if not exists (select *
156               from master.dbo.sysdevices
157               where name like @devname)
158       begin
159           /*
160           ** 17471, "No such device exists -- run sp_helpdevice to list the DataServer devices."
161           */
162           raiserror 17471
163   
164   
165   
166           return (1)
167       end
168   
169       /*
170       **  Now see if the @dbname uses the @devname
171       */
172       if not exists (select *
173               from master.dbo.sysusages u, master.dbo.sysdevices d
174               where d.name = @devname
175                   and u.vdevno = d.vdevno
176                   and u.dbid = @dbid)
177       begin
178           /*
179           ** 17281, "The specified device is not used by the database."
180           */
181           raiserror 17281
182           return (1)
183       end
184   
185       /*
186       **  Check if there are any additional segments on original device to be made
187       **  into log only (don't want to make the only segment not usable for data!)
188       */
189       if (select count(*)
190               from master.dbo.sysusages u, master.dbo.sysdevices d
191               where d.name != @devname
192                   and u.vdevno = d.vdevno
193                   and u.dbid = @dbid
194                   and u.segmap != @logbit
195                   and d.cntrltype = 0) = 0
196       begin
197           /*
198           ** 17710, "This command has been ignored.  The device specified
199           ** is the only non-log device available for the database and
200           ** cannot be made log-only."
201           */
202           raiserror 17710
203           return (1)
204       end
205   
206       /*
207       **  Set the segments on @devname as log-only.
208       */
209   
210       /* Encapsulate sysusages/anchor change in a transaction */
211       begin transaction
212   
213       update master.dbo.sysusages
214       set segmap = @logbit
215       from master.dbo.sysusages u, master.dbo.sysdevices d
216       where d.name = @devname
217           and u.vdevno = d.vdevno
218           and u.dbid = @dbid
219       /*
220       **  Clear the bit from any database segments on other devices that aren't
221       **  already log only.
222       */
223       update master.dbo.sysusages
224       set segmap = segmap & ~ @logbit
225       from master.dbo.sysusages u, master.dbo.sysdevices d
226       where u.dbid = @dbid
227           and u.vdevno = d.vdevno
228           and d.name != @devname
229           and u.segmap != @logbit
230   
231       if @@rowcount = 0
232       begin
233           rollback transaction
234           return (1)
235       end
236   
237       dbcc dbrepair(@dbname, "upd_usg")
238       if (@@error != 0)
239       begin
240           rollback transaction
241           return (1)
242       end
243   
244       commit transaction
245   
246       /*
247       **  Now we need to activate the new segment map.
248       */
249       dbcc dbrepair(@dbname, remap)
250   
251       /*
252       ** We might be extending onto a segment that used to contain user data,
253       ** so we need to look for stranded objects.
254       */
255       dbcc dbrepair(@dbname, findstranded, NULL, @isatempdb)
256   
257       /*
258       ** Recalculate the last-chance threshold
259       */
260       select @lct = lct_admin("lastchance", @dbid)
261   
262       /*
263       ** 17711, "Syslogs moved."
264       */
265       exec sp_getmessage 17711, @msg output
266       print @msg
267   
268       /*
269       ** Show what the last-chance threshold changed to
270       */
271       if (@lct > 0)
272           exec sp_getmessage 17715, @msg output
273       else
274           exec sp_getmessage 17716, @msg output
275       print @msg, @dbname, @lct
276   
277       return (0)
278   


exec sp_procxmode 'sp_logdevice', 'AnyMode'
go

Grant Execute on sp_logdevice to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 140
 MTYP 4 Assignment type mismatch @bitdesc: varchar(30) = varchar(255) 136
 QCSC 4 Costly 'select count()', use 'exists()' 189
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
114
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
139
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 195
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysdevices  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_logdevice  
 MNER 3 No Error Check should check @@error after update 213
 MNER 3 No Error Check should check @@error after update 223
 MNER 3 No Error Check should check return value of exec 265
 MNER 3 No Error Check should check return value of exec 272
 MNER 3 No Error Check should check return value of exec 274
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 238
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 271
 MUCO 3 Useless Code Useless Brackets 277
 QAFM 3 Var Assignment from potentially many rows 112
 QISO 3 Set isolation level 60
 QNAJ 3 Not using ANSI Inner Join 137
 QNAJ 3 Not using ANSI Inner Join 173
 QNAJ 3 Not using ANSI Inner Join 190
 QNAJ 3 Not using ANSI Inner Join 215
 QNAJ 3 Not using ANSI Inner Join 225
 QNUA 3 Should use Alias: Column segmap should use alias u 224
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
176
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
193
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
218
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
226
 VNRD 3 Variable is not read @nullarg 92
 VNRD 3 Variable is not read @dummy 104
 MSUB 2 Subquery Marker 116
 MSUB 2 Subquery Marker 155
 MSUB 2 Subquery Marker 172
 MSUB 2 Subquery Marker 189
 MTR1 2 Metrics: Comments Ratio Comments: 47% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 19 = 27dec - 10exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 127 21
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 172
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 189
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 213
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 223

DEPENDENCIES
PROCS AND TABLES USED
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table master..sysdevices (1)  
read_writes table master..sysusages (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
reads table master..sysdatabases (1)