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
Classic Shell - Returns pre-Server 2012 look to Server 2012+, from IvoSoft (free)
Quest Spotlight - Operational Monitoring, Diagnostics, Administration and Automated Tuning
Quest Spotlight Freemium - Same as above, minus some functionality - still a great tool. (free)
Quest Discovery Wizard - Locates SQL Server instances on your network (free)
RedGate SQL Prompt - SSMS add-in to refactor/format tSQL, auto-complete
RedGate SQL Compare - Compare and deploy schema changes
RedGate SQL Search - Locate objects quickly using this SSMS search feature (free)
SSMSBoost - SSMS add-in with useful features (free)
SSMS Tools Pack - SSMS add-in with useful features, from Mladen Prajdic
Internals Viewer - SSMS add-in to view low-level page details
First Responder Kit - Download w/ scripts, worksheets, checklists, etc, from Brent Ozar Unltd (free)
sp_WhoIsActive - sp_who + sp_who2 on steroids, from Adam Machanic (free)
sp_DataProfile - powerful data investigation tool, from Richie Rump (free)
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)
Diskspd - A robust storage testing tool from Microsoft (free)
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)
Code Prettifier - preserve tSQL formatting for blogging purposes, from Simple-Talk (free)
Notepad++ - free source code editor
DBATools - Powerful PowerShell scripts/tools for SQL Server (free)