Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Tips 21-31
SQL Server 2000 Allows You to Schedule when a Trace Stops
Basic properties of a trace are defined in the General tab of the Trace Properties window. While these are straight-forward, there is a new feature. The General Properties screen contains a new option, Enable Trace Stop Time. This is a scheduling-oriented feature that allows you to specify a date and time at which you want to stop tracing. This is handy if you want to start a trace in the evening before you go home. You can set the stop time so that the trace will run for a few hours but won't affect any nightly processing that might occur later in the evening.
Save Trace Definitions as a Template File
SQL Server 2000's trace templates contain predefined trace settings that address some common auditing needs. These are not actual traces; they simply provide a foundation for you when creating your own traces.
When using these templates, it is important to note that the Trace Name is a relatively unimportant trace property for future traces. When you create a new trace, you can specify a name for the trace; however, this trace name will not be used again. You will not be selecting the trace to run based on the Trace Name that you entered originally. Trace Name is useful only if you are running multiple traces simultaneously and need to distinguish between them more easily. The Trace Name is used as the window title.
If you have a trace definition that you like, you can save it as a template file. If you want to run the trace again in the future, you can create a new trace and select the template file that you saved. This saves you the trouble of having to go through all the properties each time you set up the events, data columns, and filters for your favorite traces.
Create Your Own Custom Shortcuts in Query Analyzer
Query Analyzer provides up to 12 keyboard shortcuts for quickly executing stored procedures in Query Analyzer. SQL Server provides three predefined shortcuts:
Alt+F1—sp_help
Ctrl+1—sp_who
Ctrl+2—sp_lock
You can view or modify these shortcuts, or even add your own by selecting the Customize option from the Tools menu. On the Custom tab, you can enter the name of the stored procedure in the Stored Procedure column next to the desired keyboard shortcut you want to use to execute that stored procedure. You might want to add your frequently executed procedures to the Custom menu to save you the trouble of having to type them every time you want to run them.
Here's a bonus tip: if you have a word, data value, or comma-separated list of values highlighted when you invoke a keyboard shortcut, Query Analyzer passes the highlighted value(s) as parameters to the associated stored procedure.
Use a Batch File to Save Time when Executing Command-Line Utilities
SQL Server command-line utilities provide administrators with a set of powerful tools for tasks such as automating routine maintenance procedures and verifying client connectivity. A batch file, typically a text file with the .bat extension, can be a real timesaver when executing the command-line utilities. You can create the batch file in the same directory as your executable file and specify the appropriate parameters.
One helpful technique: A PAUSE command at the end of the batch file allows you to view the results in the command prompt window before the window disappears.
Connecting with ISQL Utility Using Named Instances
According to the Books Online documentation, the ISQL utility (which is based on Db-Library) does not support named instances. Contrary to the documentation,you can connect with ISQLusing named instance, but you might encounter problems.
If you do experience problems, one workaround is to set up an alias for the named instance using the Client Network utility. After this alias has been created, then the alias name can be used with the \S parameter in ISQL. Another alternative is to use OSQL, which does support named instances and all other SQL Server 2000 features.
Windows 2000 Clients Can Use PathPing to Troubleshoot TCP/IP Connectivity Problems
One way to verify that a client's ODBC connectivity is properly configured is to use the obdcping utility. However, there is another option for Windows 2000 clients. A new utility named PathPing is also available for troubleshooting TCP/IP connectivity problems. Given a target server name, this utility displays the computer name and IP address for each router as it moves toward its destination. If successfully connected to the destination server, the utility echoes reply packets (messages) to the client machine to indicate that TCP/IP connectivity exists.
For Windows 9.x and NT clients, the Ping utility, which has less functionality, is available instead.
Using the rebuildm Utility to Rebuild System Databases Requires Caution
The rebuildm lets you rebuild system databases, including the master, msdb, and model databases that are shipped with SQL Server 2000. The rebuildm utility is typically used if the following situations exist:
A current backup of the master database is unavailable. If the backup were available, the server would need to be started in single user mode first.
Microsoft SQL Server 2000 cannot start because the master database is severely damaged.
However, rebuildm needs to be used with extreme caution because it essentially copies the original versions of these databases over the copies that you have in your database. After rebuildm has been run, all of the information that you had in these databases prior to running rebuildm (including user database information, scheduled tasks, and default database settings) will be gone.
Alternative Methods for Starting and Stopping Services
Usually, SQL Server can be stopped and started easily from the EM console by right-clicking a server, which displays a pop-up window. The available options are to stop the server, which stops the SQL Server service and any dependent services, to pause the server, in which case the server remains running but no new connections are allowed, and to start the server in the case that the server has been stopped. If the server has been paused, then a Continue option appears in the pop-up menu to resume SQL server in a normal connection state.
However, occasionally, Enterprise Manager and the taskbar Service Manager get confused as to what state the server is in and won't allow services to be stopped or started. In this case, go to the Services applet in the Control Panel, or use the net stop/net start commands to stop and restart the appropriate services.
Change Server-Level Settings Cautiously
Changing server-level settings affects all databases on the server and can adversely affect performance. When changing configuration options, consider the effect the change might have and carefully document your changes so they can be undone if required.
Free Text Searches: CONTAINSsbm vs. FREETEXTsbm
While the SQL engine does support basic text searches against specific columns, you may find this method too slow and inflexible. The Microsoft Full Text Search Services provides full text searching capabilities, which is useful for searching large text fields, such as movie reviews, book descriptions, or case notes. You can specify tables or entire databases that you want to index. When you use full text indexing to perform a search for text, your query will use either the CONTAINSsbm or FREETEXTsbm function.
On the flip side, CONTAINS is a better performing function and returns more exact results. FREETEXT returns looser results based on the meaning of the search phrase you enter. It does this by finding alternate word forms in your queries. For example, FREE TEXT (*,'work independently') would match 'work independently",'Independent work preferred', and 'Independence in my work'.
Run SQL Profiler to Trap SQL-EM Commands
SQL Enterprise Manager interacts with SQL Server using standard Transact-SQL commands. For example, when you create a new database through the SQL-EM interface, behind the scenes it generates a CREATE DATABASE SQL command. Whatever you can do through SQL-EM, you can do with the Query Analyzer or even the command line ISQL or OSQL programs.
If you're curious how EM is accomplishing something, you can run SQL Profiler to trap the commands that SQL-EM is sending to the server. You can use this technique to discover some interesting internals information. You can also use this tactic to capture SQL scripts, and then repeat tasks using the script instead of a few dozen interface clicks.