Thursday, September 4, 2014

SQL Server Error Log Recycle (ERRORLOG file growing large)


SQL Server Error log is too large and has grown upto 24GB, it takes a lot of time to open and sometimes hangs and SSMS gets into not-responding state.

Go to the location of the error log and see what's the size as shown in the pics below.
If you don't know the location of file, open management studio new query and execute the below query

use master
go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', null, null, N'asc' 
go

From the results set in the text column you can find the location of your error log file.

Recycle log means creating the new error log file to start recording events in the new file so that its easy to search something in a new file rather that has fewer events rather than searching large file takes time.

Open management studio connect to the Instance, open new query.
and run the following statement.

use master
go
exec sp_cycle_errorlog
go


New ERROLOG file will be created by renaming the older file (24Gb) to ERRORLOG.1. You can comfortably delete the file to save space if you don't need it.
and expand the management studio you will see two files.

1) Current being the new file and starts to record new events.

2 Archive being the old recorded events, if you don't need it you can delete the physical file from the location.







1 comment: