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.
nested cursor - Not exactly a
'shell', this routine demonstrates a technique that can simplify the
development of complex algorithms.
index maintenance - Some
thoughts, procedures, and techniques for optimal integrity and performance
of a database.
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.
Check space used (by table) - Use this
script to benchmark and track the growth of your dataset.
Monitoring blocks in a database -
Make sure that critical user is not held up by batch processes or an
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 for real time status
net send Using xp_sendmail to see what goes on at night.
Including an attachment in a debug email
Interactively display data values
Execute a task from a procedure even if the task must be identified dynamically.
Input buffer dump - find out what everybody is doing.
Automated IDENTITY repair - If you use SQL Server Identities then you need to be ready to fix 'em.
Automatically check maintenance output files for errors
From the Command Prompt
From a stored procedure
Convert a shared resource name to a logical drive name
Prevent collisions between maintenance operations
Compare sysusages for two databases before a dump/load operation.
Verify that a dump or load completed successfully.
Get all users out of a database
Build and run a dynamic script on a remote system.
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
Finding a unique file name from SQL
Server to use for dumps. Here the date is used in building the unique
Distributed recursion - If this is not
the right maching to do some work on, then send the job to the right one.
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.
Control the CPU used for an operation
Using PKZIP in the replication process.
Change directories and return to original folder later.
Alternate processing based on the day of the week.
Assigning command prompt information to a SQL variable
Perform a task on all
systems in a