Как чистить sql server logs

How to manage SQL Server logs effectively

This article covers an overview of SQL Server logs for monitoring and troubleshooting issues in SQL Server.

Introduction

The logs are the best resources for a database administrator in troubleshooting any issues. These issues can be related to server configuration, startup, recovery, performance, trace flags, deadlocks, IO, or Checkpoint delay. For example, suppose your SQL Server instance restarted due to unknown reasons, and after startup, SQL Services are up; however, your application cannot access the database. Therefore, to investigate issues, you can look at the latest SQL Server logs and monitor the database recovery process and estimated time in completion. The database administrator can also configure SQL Server for additional logging into the error logs. For example, we can enable a trace flag to capture deadlocks information. The DBA should review these logs proactively to look for potential problems. You can identify information such as backup failure, login failure, IO errors by reviewing logs. These error logs are great to look for existing or potential problems in SQL Server instances. SQL Server logs are known as SQL Server Error logs. This error log has informational, warning, and critical error messages. You can view a few of these logs in the Windows event viewer logs as well. However, it is recommended to use SQL Server logs to get detailed information.

SQL Server Logs and its location

Once you connect to a SQL Server instance in SSMS, navigate to Management -> SQL Server Logs. As shown below, it has the current log and six archive logs ( Archive#1 to Archive #6).

Method 1: Using the xp_readerrorlog extended procedure

The current logs are the latest error log file, and you can use them to view recent activity since SQL Server starts or manual log file recycling. SQL Server error log is a text file stored in the log directory of SQL Server instance. You can use the extended procedure xp_readerrorlog to find out the current location of the error log.

How to remove SQL Server Error Logs

I have six error logs plus the current. They are taking up 10G of disk space on my hard drive on my VPS server. Thats about 25% of the server space. The largest file is 6G and growing. How can I remove the error logs as I don’t need them and reduce the size in which the archive files grow to before recycling. Thanks,

10.9k 11 11 gold badges 41 41 silver badges 60 60 bronze badges
asked Nov 16, 2019 at 17:11
MiscellaneousUser MiscellaneousUser
185 1 1 gold badge 1 1 silver badge 7 7 bronze badges

I mean the ones that appear in «Management -> SQL Server Logs» node. The ones that are stored in the C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log folder.

Nov 16, 2019 at 17:45

1 Answer 1

Thats about 25% of the server space.

First and foremost, you really need to increase the size of your drive or move the error logs to a different drive. ~40GB for a C:\ drive might’ve been an okay default years ago, but you can quickly burn through that with misc files from applications — as you’re seeing.

You can easily configure this via SQL Server Configuration Manager:

by modifying the directory for the -e parameter, which controls the error log destination.

How can I remove the archive logs as I don’t need them and reduce the size in which the archive files grow to before recycling.

You can of course just delete the files manually, but it sounds like you really want to reduce the number of logs used and/or reduce their size.

The steps contained in the above link detail how to do this in SQL Server Management Studio:

1. In Object Explorer, expand the instance of SQL Server, expand Management, right-click SQL Server Logs, and then click Configure.
2. In the Configure SQL Server Error Logs dialog box, choose from the following options. a. Log files count Limit the number of the error log files before they are recycled Check to limit the number of error logs created before they are recycled. A new error log is created each time an instance of SQL Server is started. SQL Server retains backups of the previous six logs, unless you check this option, and specify a different maximum number of error log files below. Maximum number of error log files Specify the maximum number of archived error log files created before they are recycled. The default is 6, not including the current one. This value determines the number of previous backup logs that SQL Server retains before recycling them. b. Log file size Maximum size for error log file in KB You can set the size amount of each file in KB. If you leave it at 0 the log size is unlimited.

What is the command to truncate a SQL Server log file?

I need to empty an LDF file before sending to a colleague. How do I force SQL Server to truncate the log?

1 1 1 silver badge
asked Sep 2, 2008 at 19:44
Aidan Ryan Aidan Ryan
11.5k 13 13 gold badges 56 56 silver badges 87 87 bronze badges

7 Answers 7

In management studio:

• Don’t do this on a live environment, but to ensure you shrink your dev db as much as you can:
  • Right-click the database, choose Properties , then Options .
  • Make sure «Recovery model» is set to «Simple», not «Full»
  • Click OK

  Alternatively, the SQL to do it:

   ALTER DATABASE mydatabase SET RECOVERY SIMPLE DBCC SHRINKFILE (mydatabase_Log, 1) 

  21.7k 13 13 gold badges 116 116 silver badges 118 118 bronze badges
  answered Sep 2, 2008 at 19:51
  Blorgbeard Blorgbeard
  102k 49 49 gold badges 231 231 silver badges 272 272 bronze badges

  Your answer has just saved my day! I didn’t know of the «right-click — Tasks -> Shrink» option. Thank you!

  Apr 20, 2011 at 13:51
  What DO you do in a live environment? Backup the logs first?
  Aug 26, 2013 at 16:19

  I’m no DBA, but yes, I believe that backing up the log will truncate it: technet.microsoft.com/en-us/library/ms179478.aspx

  Aug 26, 2013 at 22:53

  @JohnBubriski If you’re using a recovery model other than simple, the logs are the basis for recovering data or rolling back transactions. So, in production, you’ll need to backup these logs first before you can shrink the log files. Otherwise, there’d be no actual recovery possibility. Unfortunately, if you’re in a recovery situation, you’ll have to re-load all the transaction log backups in order to fully recover the DB. Fun times, to be sure! 🙂