Thursday, August 29, 2013

Cycle Your Error Logs

One thing I like to configure in all of my database instances is the rate at which my error logs are recycled.  If you don't recycle them on a regular basis, they can tend to grow to be very large, often making it difficult to open them.  By default they don't recycle until a restart of the service, respectively.

The first thing I do to address this is to roll them over each night by creating a SQL Server job that runs at midnight.  That keeps them consistent and relatively small.

EXEC sp_cycle_errorlog
EXEC sp_cycle_agent_errorlog  

Next what I will do is increase the Maximum number of error logs files to the number of days I wish to retain.  Most of the time one month is sufficient.  To do this connect to your instance and expand Management.  Right-click on SQL Server Logs, check the box and change the value to 30 or whatever your desired days of retention are.

In the event you are interested in knowing where these logs are stored, they are typically located at C:\Program Files\Microsoft SQL Server\MSSQL.<instance>\MSSQL\LOG.

I hope you find this useful!

Tuesday, August 27, 2013

My DBA Toolbox

Tools and Scripts
Quest/Dell Spotlight - Operational Monitoring, Diagnostics, Administration and Automated Tuning
Quest/Dell Spotlight Freemium - Same as above, minus some functionality - still a great tool. (free)
Quest/Dell Discovery Wizard - Locates SQL Server instances on your network (free)
RedGate SQL Prompt - SSMS add-in to refactor/format tSQL, auto-complete
APEX Refactor - SSMS add-in to refactor/format tSQL (free)
RedGate SQL Compare - Compare and deploy schema changes
SSMSBoost - SSMS add-in with useful features (free)
SSMS Tools Pack - SSMS add-in with useful features, from Mladen Prajdic (free)
Internals Viewer - SSMS add-in to view low-level page details
sp_AskBrent - Great tool for performance diagnostics, from Brent Ozar, Brent Ozar Unlimited (free)
sp_Blitz - Script to check for health and perf problems, from Brent Ozar, Brent Ozar Unlimited (free)
sp_BlitzCache - What's in your plan cache?? from Jeremiah Peschka, Brent Ozar Unlimited (free)
sp_BlitzIndex - Diagnose index disorders, from Kendra Little, Brent Ozar Unlimited (free)
sp_BlitzTrace - Trace a single SPID in close detail, from Kendra Little, Brent Ozar Unlimited (free)
sp_WhoIsActive - sp_who + sp_who2 on steroids, from Adam Machanic (free)
sp_DataProfile - powerful data investigation tool, from Richie Rump (free, new!)
SQLQueryStress - SQL Server query performance testing tool, from Adam Machanic (free)
Set up SQL Server Agent Alerts - script from to set up some basic alerts (free)
Ola Hallengran - backup, DBCC, index/stats maintenance (free)
ScriptSQLConfig - Automate the scripting of SQL Server environment for DR purposes (free)
SQLIO - Tool to determine the I/O capacity of a given hardware configuration (free)
Diskspd - A robust storage testing tool from Microsoft (free, new!)
CrystalDiskMark - disk benchmark software (free)
ClearTrace - Import SQL Server trace files for aggregate analysis (free)
SQL Sentry Plan Explorer - Provides a more in-depth analysis of your execution plans (free)
Diagnostic Information Queries - by Glenn Berry (free) - Tool to parse/summarize your SET STATISTICS IO ON output (free)
Compression Estimator - Estimate compression savings on an entire database (free)
Remote Server Administration Tools Pack - Allows remote administration from a PC (free)
Terminals - Organize your RDP and other connections (free)
Microsoft Kerberos Configuration Manager for SQL Server (free)
Notepad++ - free source code editor