1. cursor shell - An administrator frequently needs to perform an operation against all objects of a certain type in sysobjects. This 'shell' is easily modified to do those tasks.
  2. nested cursor - Not exactly a 'shell', this routine demonstrates a technique that can simplify the development of complex algorithms.
  3. index maintenance - Some thoughts, procedures, and techniques for optimal integrity and performance of a database.
  4. check tempdb size from batch - A full tempdb is a disapointing end to a long running query. Here's a way to check before you waste the CPU cycles.
  5. Check space used (by table) - Use this script to benchmark and track the growth of your dataset.
  6. Monitoring blocks in a database - Make sure that critical user is not held up by batch processes or an inappropriate query.
  7. Using a debug flag - Finding out what went wrong in a batch process is easy with a little planning. My basic technique is to use a debug parameter as the last parm passed to all stored procedures with a default of 'false'. I explicitly set the parm to 'true' to use debug mode.
    Using net send for real time status
    Using xp_sendmail to see what goes on at night.
    Including an attachment in a debug email
    Interactively display data values
  8. Execute a task from a procedure even if the task must be identified dynamically.
  9. Input buffer dump - find out what everybody is doing.
  10. Automated IDENTITY repair - If you use SQL Server Identities then you need to be ready to fix 'em.
  11. Automatically check maintenance output files for errors
    From the Command Prompt
    From a stored procedure
  12. Convert a shared resource name to a logical drive name
  13. Prevent collisions between maintenance operations
  14. Compare sysusages for two databases before a dump/load operation.
  15. Verify that a dump or load completed successfully.
  16. Get all users out of a database
  17. Build and run a dynamic script on a remote system.
  18. Cleanup in msdb - Tasks, backups, restores, and replication create activity in msdb. If you don't clean up msdb your system could fail.
    periodically purge msdb
    clean up as you go
  19. Finding a unique file name from SQL Server to use for dumps. Here the date is used in building the unique name.
  20. Distributed recursion - If this is not the right maching to do some work on, then send the job to the right one.
  21. Guaranteed file system structure - make sure things in the file system are correct before you try to write to a location that doesn't exist.
  22. Control the CPU used for an operation via xp_cmdshell
  23. Using PKZIP in the replication process.
  24. Change directories and return to original folder later.
  25. Alternate processing based on the day of the week.
  26. Assigning command prompt information to a SQL variable
  27. Perform a task on all systems in a distributed environment.