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


Blogs
Tools and Scripts
Classic Shell - Returns pre-Server 2012 look to Server 2012+, from IvoSoft (free)
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
RedGate SQL Compare - Compare and deploy schema changes
SSMSBoost - SSMS add-in with useful features (free)
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, new!)
SQLQueryStress - SQL Server query performance testing tool, from Adam Machanic (free)
Set up SQL Server Agent Alerts - script from BrentOzar.com 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, 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)
StatisticsParser.com - 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