• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

TLC and SQL Server Error Log Management

April 6, 2010 by John Sansom 2 Comments

"TLC"

One of the very first tasks I like to do when presented with a new SQL Server Instance is to review what’s been happening of late in the SQL Server Error Log file. Doing so can often provide a quick insight into items that require my attention. It’s not uncommon to encounter backup failure messages, performance warnings or even nasty error messages.

If the SQL Server Instance has not been given a dose of TLC (no not that 90’s R&B group) for some time, you can often encounter a rather large error log file. This can be a pain to work with because it can take an age to open up in management studio and navigation can be cumbersome.

Managing the SQL Server Error Log

So in order to make working with your SQL Server Error log files easier, it’s a good idea to proactively manage their size.

  1. There are two ways that you can go about this. Modify the configuration properties for your SQL Server Error Log files. (In SQL Server Management Studio simply right click the SQL Server Log folder in Object Explorer and choose configure).
  2. Make use of the system stored procedure sp_cycle_errorlog to immediately cycle the error log.

By default SQL Server retains the last six sql server error log files. The number of files for retention can be modified be editing the configuration properties for the SQL Server Error log.

I’m not going to waste time re-inventing the wheel here as there are already some great tutorials available on the net via your trusty friend Google. I particularly like the general overview of SQL Server Error Logs available at SQL Server Pedia.

Cut that large log down to size

The way I like to do things is even before looking to define a suitable log management scheme for a server, as soon as I encounter a large error log file I like to cycle that log right away using sp_cycle_errorlog. This gets things under control immediately. Simply execute:

EXEC master.sys.sp_cycle_errorlog;

Give your SQL Server Logs some TLC today…

Filed Under: Administration, SQLServerCentral Syndication, SQLServerPedia Syndication Tagged With: SQL Server Error Log

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy