• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

SQL Server Error Log Consuming Lots of Disk Space

June 17, 2012 by Andy Hayes Leave a Comment

I came across a problem this week with one of our SQL Servers whereby one of the drives was very low on space. Whenever I come across a disk space problem, I use my trusty friend Treesize which is a free download and it enables me to quickly find where the space is being consumed.

Having run the tool, I quickly found the culprit. It was the SQL Server error log consuming gigabytes of disk space.

Thankfully this is relatively easy to resolve.

Here are my notes…..

Where is the SQL Server Error Log?

Typically inside the “Log” directory of your SQL Server instance so for example on my laptop it is here.

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Log

Recycle the SQL Server Error Log using sp_cycle_errorlog

You can view the logs and their contents in Management Studio by accessing the “Management” node and by either double clicking or right clicking the logs

sql server error log

So in order to get my disk space back, I ran a stored procedure called sp_cycle_errorlog which will create you a new log and move the old log to a new file called ErrorLog.1 inside your error log directory. sp_cycle_errorlog is installed with SQL Server.

Each time you run this command, a new log is created and the existing logs are cycled until the max number of allowed error logs is reached.  So you get ErrorLog.1, ErrorLog.2 etc in your log directory. When the max logs is reached, the oldest file is removed.

I decided that I did not want to keep the logs as this was a development server and I was aware of what messages were consuming the space. For a production server, you may want to harvest the logs onto another drive before running sp_cycle_errorlog to completely remove the log files.

You can adjust the number of log files to be retained, the minimum is 6 and the maxium is 99 and this can be configured by right clicking the SQL Server Logs node and choosing the configure option.

For more information on sp_cycle_errorlog, you can visit this link

Related Posts:

  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…

Filed Under: All Articles, SQL Server Administration Tagged With: sql server, troubleshooting

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©