Coming from Oracle background:

How do you carry thiese admin tasks in SQL 2000 Environment :

Check instance availability
Check listener availability
Check alert log files for error messages
Clean up old log files before log destination gets filled
Analyze tables and indexes for better performance
Check tablespace usage
Find out invalid objects
Monitor users and transactions


Thanks for help.

"Frank" <soal6570@yahoo.com> wrote in message
news:42601b2.0404230147.39de3b50@posting.google.co m...
> Coming from Oracle background:
>
> How do you carry thiese admin tasks in SQL 2000 Environment :
>
> Check instance availability

The easiest way is to try connecting, and if you can connect then the
instance is available. You could use osql.exe, or the SQLDMO or ADO COM
objects from your own script. Or if you already have a tool to monitor
Windows services, then you can use that to check the MSSQLServer and
SQLServerAgent services.

> Check listener availability

I'm not sure what a listener is, but there is no separate component in MSSQL
to handle connections - you connect directly to an instance.

> Check alert log files for error messages

The MSSQL error log is a text file, so any tool for parsing log files will
work. In addition, MSSQL writes to the Windows Application event log, so if
you already have a tool for reading those logs, then you could use that. Or
configure MSSQL alerts for the specific errors that you're interested in.

> Clean up old log files before log destination gets filled

This depends on your backup strategy. If you want to be able to do
point-in.time recovery, then you need log backups - backing up the log
trunctates it (ie frees up space in the log file). If you don't need that,
then you can use the simple recovery model, where the log is automatically
truncated. See "Selecting a Recovery Model" and "Truncating the Transaction
Log" in Books Online.

> Analyze tables and indexes for better performance

The Index Tuning Wizard might be the best place to start - you trace some
typical activity in the database (using Profiler), then the wizard analyzes
it and suggests changes to the current indexes.

> Check tablespace usage

Check out sp_spaceused, sp_helpfilegroup, sp_helpfile, and DBCC SQLPERF in
Books Online. You can also use the SQLDMO COM interface to get information
on files and filegroups.

> Find out invalid objects

I'm not sure what you mean by an "invalid" object - you might want to
explain some more.

> Monitor users and transactions

Profiler is probably the best general tool - you can view all commands sent
to the server, long.running queries, execution plans etc. In Query Analyzer,
you can use sp_who2 to see the current connections, sp_lock to view the
current locks, and fn_get_sql() to view the commands being executed by each
user.

Simon