What are some of the tools that you use to troubleshoot a SQL Server issue?

Answer 1

* SQL Server Error Log
  * SQL Server Agent Error Log
  * Windows Application Event Log
  * SQL Server Integration Services Logs
  * Custom Application\Process Logs

Answer 2

When you need to troubleshoot a problem, prior to SQL Server 2005 you have to depend upon PROFILER or query system tables constantly or some other means to trace the system usage.

SQL Server 2005 onwards DBAs are equipped with Dynamic Management Views (and Data Mining Extenstions DMX for Analysis Services) for core database engine to obtain the snapshot of information on resource usage such as disk, cpu, memory and many more. The 2 types of DMVs are server-scoped & database scoped whics requires obvious elevated privileges to execute on that SQL instance.