| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_logdevice | | 31 Aug 14 | Defects 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
| 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) |
