Adaptive Server performs a data copy for some alter database operations. Logging of page contents is not generally done for these operations. For this reason, the server flushes pages to disk so that these changes can be recovered after a database crash. This is not necessary for tempdb, however, since the contents of tempdb are always re-created during the recovery process.
User-created temporary databases are also freshly re-created during recovery. Therefore, these databases are treated in a manner consistent with tempdb. The following describes some specific cases where this is true for alter database. In addition, there are other instances of alter database where the behavior for user-created temporary databases is identical to that of tempdb.
When the system tempdb is extended, new database pages are not zeroed out. Only allocation pages are written to disk. User-created temporary databases are consistent with this approach.
The model database cannot be larger than the smallest temporary database because model is copied in to the temporary database when the server is restarted. Attempting to alter the size of model triggers a check to verify that the new size of model is not larger than the smallest temporary database. If a smaller temporary database is found, the alter database command reports an error.
