DatabaseProcApplicationCreatedLinks
sybsystemprocssp_logdevice  31 Aug 14Defects 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 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    
36    
37    
38        /*
39        **  If we're in a transaction, disallow this since it might make recovery
40        **  impossible.
41        */
42        if @@trancount > 0
43        begin
44            /*
45            ** 17260, "Can't run %1! from within a transaction."
46            */
47            raiserror 17260, "sp_logdevice"
48            return (1)
49        end
50        else
51        begin
52            set chained off
53        end
54    
55        set transaction isolation level 1
56    
57        select @logbit = 4 /* bit 3 is the one to turn on */
58    
59        /*
60        **  Verify the database name and get the @dbid and @dbuid
61        */
62        select @dbid = dbid, @dbuid = suid
63        from master.dbo.sysdatabases
64        where name = @dbname
65    
66        /*
67        **  If @dbname not found, say so and list the databases.
68        */
69        if @dbid is NULL
70        begin
71            /*
72            ** 17421, "No such database -- run sp_helpdb to list databases."
73            */
74            raiserror 17421
75            return (1)
76        end
77    
78        /*
79        **  Only the Database Owner (DBO) or
80        **  Accounts with SA authorization can execute it.
81        **  Call proc_role() with the required SA authorization.
82        */
83        if ((suser_id() != @dbuid) and (proc_role("sa_role") < 1))
84            return (1)
85    
86        /*
87        ** Determine if we are dealing with a temporary database.
88        */
89        select @tempdb_mask = number
90        from master.dbo.spt_values
91        where type = "D3" and name = "TEMPDB STATUS MASK"
92    
93        if (@dbid = 2) or exists (select * from master.dbo.sysdatabases
94                where dbid = @dbid
95                    and (status3 & @tempdb_mask) != 0)
96        begin
97            select @isatempdb = 1
98        end
99        else
100       begin
101           select @isatempdb = 0
102       end
103   
104       /* 
105       ** Check single user bit (4096).
106       ** Database must be in single user mode so that no log allocation
107       ** or deallocation take place while recalculing the free space counter.
108       ** However, this requirement is relaxed for temporary databases.
109       */
110       if @isatempdb = 0
111       begin
112           select @bitdesc = null
113           select @bitdesc = v.name
114           from master.dbo.spt_values v, master.dbo.sysdatabases d
115           where d.dbid = @dbid
116               and v.type = "D"
117               and d.status & v.number = 4096
118   
119           if @bitdesc is null
120           begin
121               /*
122               ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'."
123               */
124               raiserror 17793, @dbname, "sp_logdevice"
125               return (1)
126           end
127       end
128   
129       /*
130       **  See if the device exists.
131       */
132       if not exists (select *
133               from master.dbo.sysdevices
134               where name like @devname)
135       begin
136           /*
137           ** 17471, "No such device exists -- run sp_helpdevice to list the DataServer devices."
138           */
139           raiserror 17471
140   
141   
142   
143           return (1)
144       end
145   
146       /*
147       **  Only the Database Owner (DBO) or
148       **  Accounts with SA role can execute it.
149       **  Call proc_role() with the required SA role.
150       */
151       if ((suser_id() != @dbuid) and (proc_role("sa_role") < 1))
152           return (1)
153   
154       /*
155       **  Now see if the @dbname uses the @devname
156       */
157       if not exists (select *
158               from master.dbo.sysusages u, master.dbo.sysdevices d
159               where d.name = @devname
160                   and u.vdevno = d.vdevno
161                   and u.dbid = @dbid)
162       begin
163           /*
164           ** 17281, "The specified device is not used by the database."
165           */
166           raiserror 17281
167           return (1)
168       end
169   
170       /*
171       **  Check if there are any additional segments on original device to be made
172       **  into log only (don't want to make the only segment not usable for data!)
173       */
174       if (select count(*)
175               from master.dbo.sysusages u, master.dbo.sysdevices d
176               where d.name != @devname
177                   and u.vdevno = d.vdevno
178                   and u.dbid = @dbid
179                   and u.segmap != @logbit
180                   and d.cntrltype = 0) = 0
181       begin
182           /*
183           ** 17710, "This command has been ignored.  The device specified
184           ** is the only non-log device available for the database and
185           ** cannot be made log-only."
186           */
187           raiserror 17710
188           return (1)
189       end
190   
191       /*
192       **  Set the segments on @devname as log-only.
193       */
194   
195       /* Encapsulate sysusages/anchor change in a transaction */
196       begin transaction
197   
198       update master.dbo.sysusages
199       set segmap = @logbit
200       from master.dbo.sysusages u, master.dbo.sysdevices d
201       where d.name = @devname
202           and u.vdevno = d.vdevno
203           and u.dbid = @dbid
204       /*
205       **  Clear the bit from any database segments on other devices that aren't
206       **  already log only.
207       */
208       update master.dbo.sysusages
209       set segmap = segmap & ~ @logbit
210       from master.dbo.sysusages u, master.dbo.sysdevices d
211       where u.dbid = @dbid
212           and u.vdevno = d.vdevno
213           and d.name != @devname
214           and u.segmap != @logbit
215   
216       if @@rowcount = 0
217       begin
218           rollback transaction
219           return (1)
220       end
221   
222       dbcc dbrepair(@dbname, "upd_usg")
223       if (@@error != 0)
224       begin
225           rollback transaction
226           return (1)
227       end
228   
229       commit transaction
230   
231       /*
232       **  Now we need to activate the new segment map.
233       */
234       dbcc dbrepair(@dbname, remap)
235   
236       /*
237       ** We might be extending onto a segment that used to contain user data,
238       ** so we need to look for stranded objects.
239       */
240       dbcc dbrepair(@dbname, findstranded, NULL, @isatempdb)
241   
242       /*
243       ** Recalculate the last-chance threshold
244       */
245       select @lct = lct_admin("lastchance", @dbid)
246   
247       /*
248       ** 17711, "Syslogs moved."
249       */
250       exec sp_getmessage 17711, @msg output
251       print @msg
252   
253       /*
254       ** Show what the last-chance threshold changed to
255       */
256       if (@lct > 0)
257           exec sp_getmessage 17715, @msg output
258       else
259           exec sp_getmessage 17716, @msg output
260       print @msg, @dbname, @lct
261   
262       return (0)
263   


exec sp_procxmode 'sp_logdevice', 'AnyMode'
go

Grant Execute on sp_logdevice to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 117
 QCSC 4 Costly 'select count()', use 'exists()' 174
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
91
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
116
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 180
 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 198
 MNER 3 No Error Check should check @@error after update 208
 MNER 3 No Error Check should check return value of exec 250
 MNER 3 No Error Check should check return value of exec 257
 MNER 3 No Error Check should check return value of exec 259
 MUCO 3 Useless Code Useless Brackets 48
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 226
 MUCO 3 Useless Code Useless Brackets 256
 MUCO 3 Useless Code Useless Brackets 262
 QAFM 3 Var Assignment from potentially many rows 89
 QISO 3 Set isolation level 55
 QNAJ 3 Not using ANSI Inner Join 114
 QNAJ 3 Not using ANSI Inner Join 158
 QNAJ 3 Not using ANSI Inner Join 175
 QNAJ 3 Not using ANSI Inner Join 200
 QNAJ 3 Not using ANSI Inner Join 210
 QNUA 3 Should use Alias: Column segmap should use alias u 209
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
161
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
178
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
203
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
211
 MSUB 2 Subquery Marker 93
 MSUB 2 Subquery Marker 132
 MSUB 2 Subquery Marker 157
 MSUB 2 Subquery Marker 174
 MTR1 2 Metrics: Comments Ratio Comments: 51% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 19 = 26dec - 9exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 119 21
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 157
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 174
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 198
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 208

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