One option for the user types is that you can have user types automatically copied to tempdb by creating them in the model database (tempdb is made from a copy of model at boot time). If you are using multiple tempdbs and have a template database other than model, also create the user types in the template database. This does cause these user types to also be in any new permanent database you create as well, which you may or may not want.
Yes, "sleep" is one option (perhaps in a loop - try to login, on failure sleep then try again.). Another is to monitor the errorlog for the messages indicating that either tempdb is recovered or all databases are recovered. If you have the configuration option "enable login during recovery" is off, then you want to wait until all database are recovered before trying to login.
Adding the thresholds isn't as time sensitive as adding the user types (users might immediately be logging in and trying to run code that depends on the user types being installed in tempdb), so I'd suggest using the model database approach for the user types rather than a script.
.
Another possible approach would be to create a global login trigger that had some (quick, efficient) mechanism to check if this was the first login since the server booted and ran a stored procedure that did all this work if it was indeed the first login.
There is an open feature request to allow thresholds to be defined based on a percentage of the segment size rather than be a hard-coded number of pages, CR 227134. These could then be created in model and would automatically scale up to the size of tempdb when it is created from model.
-bret
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.