1

Im running SQL server 2014 on a VPS with 150Gb ssd. Today i found that my hard disk was full 100% and there's a very large file from SQL Server Error log that was 45.7Gb.

I tried manually delete the file, but Windows says the disk space doesnt change at all. How can I delete this file ? I dont need error log data, it's fine for me to delete it and regain the disk space.

Thank you very much for your support. enter image description here

4
  • 2
    Besides the answer given how to fix the immediate issue - 45.7gb is a RIDICULOUS size. I would be more concerned about the machine throwing that amount of errors and start investigating this one, besides the cycling. Why does this not cycle automatically? Something is VERY vERY wrong with this SQL Server and just fixing the immediate issue is like taking more and more painkillers instead of going to the doctor. Please investogate. – TomTom Aug 23 '20 at 10:12
  • Thank you, I want to check that file but it is too big, I cant open it with all text editors I have (visual studio code, notepad ...). Do you know any text editor can open it ? – Liberty Aug 24 '20 at 9:17
  • How about TYPE from cmd-prompt and just do ctrl-c pretty quickly? Chances are that it is the very same message that occurs over and over again... I don't know if Notepad++ works with chunks of data instead of reading the whole lot in memory - might be worth a try. – Tibor Karaszi Aug 24 '20 at 14:03
  • Thank you so much for your answer, I checked the file, yes it is because failed login attempts. But here I want to delete the file first, these following commands didn't delete it, could u please give me some ideas? – Liberty Aug 26 '20 at 13:27
2

You can limit the number and size of SQL Server error log files to avoid excessive space. This can be done from SSMS Object Explorer by right-clicking on the SQL Server Logs folder and selecting Configure. Alternatively, use a T-SQL script like the below example that limits the number of error logs to 10 files of up to 100MB each for your default instance:

EXEC master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'ErrorLogSizeInKb'
    , REG_DWORD
    , 100;
EXEC master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'NumErrorLogs'
    , REG_DWORD
    , 10;

Examine the log to identify and remediate the root cause for the excessive size. The reason could be repeated login failures due to an attack, misconfigured application, repeated stack dumps, etc.

To view the current log, double-click on it from SSMS Object Explorer under Management-->SQL Server Logs or with the query below. In either case, you may need to stop the query due to the very large log size but the sampled records should be enough to identify the cause.

EXEC xp_readerrorlog 0, 1;
2
  • Thank you so much for your answer, I checked the file, yes it is because failed login attempts. But here I want to delete the file first, these following commands didn't delete it, could u please give me some ideas? – Liberty Aug 26 '20 at 13:27
  • @Liberty, the old files will be deleted automatically once you've cycled through the max number of files (10 in my example). You can also manually delete the large "errorlog.n" files, like "errorlog.4" in your screen image. – Dan Guzman Aug 26 '20 at 14:34
1

You have to rotate error log to release the file lock kept by SQL Server process. Please execute this procedure:

EXEC sp_cycle_errorlog ;
GO

BTW. Did you check content of the error log? There should be a reason why it's so big and you can find the answare only by checking the content of that file.

5
  • Thank you, I want to check that file but it is too big, I cant open it with all text editors I have (visual studio code, notepad ...). Do you know any text editor can open it ? – Liberty Aug 24 '20 at 9:17
  • you should be able to open it in SSMS (it may take some time to load) – Dominique Boucher Aug 24 '20 at 13:44
  • You can use powershell to display last X lines in the file: Get-Content -Tail 1000 PATH_TO_LOG_FILE – Jacek Wróbel Aug 24 '20 at 14:24
  • Thank you so much for your answer, I checked the file, yes it is because failed login attempts. But here I want to delete the file first, these following commands didn't delete it, could u please give me some ideas? – Liberty Aug 26 '20 at 13:27
  • This errorlog file has a suffix .4 - that means that it's not active file for writing the error messages by the server process. You can manually delete it from disk. – Jacek Wróbel Aug 26 '20 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.