Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Suggested answer

SQL server 2008 R2-Error log File-Limit the size

Posted on by 487

Hi

 How do I limit the size of Error log for SQL Server 2008 R2

C:\Program Files\Common Files\MIcrosoft Shared\Web server\Extensions\14\Data\MSSQL 10.sharepoint\mssql\log(error ;log)

I researched and found some command like "sp_errorlog"(but it will do some recycle of error log not sure what that means).

I went to SQL server Mgt studio --Configure.There u can give the number for error log but dont know how to limit the size of the error log

Can someone please help.

Categories:
  • Suggested answer
    Andre Ghazaleh Profile Picture
    Andre Ghazaleh 870 on at
    RE: SQL server 2008 R2-Error log File-Limit the size

    Hi NDingankar22,


    For pre SQL server 2012 you can't set max log file size, but you can reinitialize error logs with sp_cycle_errorlog stored procedure, set an sql agent job to execute this stored procedure, and set it to run in the frequency you want, so this applies to your case (SQL 2008 R2)

    sp_cycle_errorlog Closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that the new log has been created.

    For SQL server 2012 and later, you can set the max log file size:

    USE [master]; 
    GO 
    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'ErrorLogSizeInKb', REG_DWORD, 5120; 
    GOCode

    In both cases increasing the number of logs you keep will help you limit the error log size.

    Source : support.microsoft.com/.../how-to-manage-the-sql-server-error-log

    Best regards

  • NDingankar22 Profile Picture
    NDingankar22 487 on at
    RE: SQL server 2008 R2-Error log File-Limit the size

    Restarting the SQLserver will create a new Error log. But the old one is in GB and eating space. Can someone tell me how can i limit the size of error log. The error log of Gb was unable to open at my end

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,459 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,783 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans