Skip to content

How to create a new Error log file in SQL Server? Or how do I recycle my Error log file without SQL Server Service restart?

February 26, 2013

If your log file is getting too bulky and taking lot of time to load and searching errors from it is becoming a problem it’s time to recycle error log file, although a new error log file is created every time SQL Server services are restarted but it does not mean you have to recycle SQL Server services every time you want error log file to recycle. You can use below command to recycle error log file without service restart.


EXEC sp_cycle_errorlog

Now, next step is to check newly created error log file or How to check when was the last time my Error log file was created? You can do it either using command or directly in SSMS or checking physical log file creation date.

Method 1: Using below command you can easily check when was the latest log file is created. Check the date and time of first line, this is the time when log file was created.


EXEC xp_readerrorlog

Screenshot:

Recycle error log1

Method 2: Using SSMS: Open Error log file SQL Server Agent  → Error log → Current → Scroll down to the bottom of file and check for date on the last line. This is date when this error log file was created.

Screenshot:

Recycle error log2

Method 3: Checking Physical file: How to get physical location of Error log file? You can easily get the location of physical location of error log file form Start up parameters (SQL Server Configuration Manager→ Right Click SQL Server Service → Properties → Advanced → Startup Parameters → Look for location of error log file.

Screenshot:

Recycle error log3

Once the location of Error log file is traced it, it’s really easy to know when the latest file was created. Sort files by date modified and check create date of last modified file or sort files by date created.

Important Points:

  • Be very careful while checking Error log file location, make sure you do not change or delete any start up parameters.
  • This task can easily be automated and scheduled using SQL Agent job.
Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: