SQL Server DBA Duties

DAILY

Action: Check Network Connectivity
Reason: To check that hardware & server is up.
To check that IP address & name have not been changed.
Gives early warning if server fails.
Checks IP address & name resolution (sometimes a problem with wins, dns, lmhosts).
Method: 1. Ping sql servers every 15 mins with IP Sentry.
2. Use batch file to ping servers.
3. Use a server monitoring tool.

Action: Check SQL services
Reason: To check that SQL server is available.
To check MSSQLserver & SQLexecutive/agent, DTS services are running.
To check that we can connect.
Method: SEM:
Green lights if SEM, or connect to each server by clicking on the ‘+’ for each server & open sql executive.

Action: Check Scheduled Tasks/ Jobs
Reason: Backups, DBCC checks, etc. are run overnight as scheduled tasks.
Method: SEM: Highlight server, servers | scheduled tasks

Action: Check dba_tools..scheduledtasklog
Reason: If get a failed scheduled task.
Some DBA’s create an error log table, which gives more detail on errors during task execution
Method: SEM: Open ISQL/W, Select * from dba_tools..scheduledtasklog.

Action: Check DBCC output
Reason: DBCC commands check DB integrity & consistency.
Finds errors in the structure of the server & databases.
If more than the usual ‘msg’ string errors, check BOL.
Method: Create desktop or folder shortcuts to point to the dbcc output text files.
Check date when checks done.

Action: Check Hard Disk Space
Reason: If system drives run low they crash.
Backup devices expand dynamically & will fail if insufficient space.
Need to know which partition is the system & which partition holds the backup devices.
Method: Windows explorer: Check drive properties
SEM: Check database devices, even if automatic.
ISQL/W: Create stored procedure or batch file, using xp_fixeddrives for each server.

Action: Check Database & Transaction Log Space
Reason: If DB or txn log space runs out, then transactions will fail. Ver 6.5
Method: SEM: Databases x2 click the DB
ISQL/W: sp_spaceused
www.SQLserverPortal.com
3rd party tools for SQL server

Action: Check Event Logs
Reason: OS errors are logged here & some SQL errors are logged here.
OS & SQL usually warns you before a problem becomes critical.
Method: Control panel, admin tools\event viewer\

Action: Check SQL Error Logs
Reason: SQL errors are logged here, SQL usually warns you before a problem becomes critical.
Method: SEM: servers | error log, scroll to bottom (ctrl+end) = most recent.
If get errors, tap in number into BOL.

WEEKLY

Action: Save all server configuration info.
Reason: You need more than just the db backup to rebuild a SQL server.
Method: ISQL/W: Create stored procedures to get config info. etc & output to txt files.

MONTHLY



Action: Do a test restore of a db backup
Reason: Sometimes backups don’t work eg due to errors found by DBCC commands.
Method: Create a test device, restore from tape to a bkup device, restore, then run DBCC checks on it.