How can I leave only only 10 days of SQl Server logs in sql server?‌‌

Question

How can I leave only only 10 days of SQl Server logs in sql server?‌‌

almirfiorio

Say Hey Kid

Points: 665
More actions
March 1, 2018 at 1:18 pm

#344208

Dear Fiends
inside "Management> SQL Server Logs" I want to automatically delete old logs
How can I leave only only 10 days of SQl Server logs in sql server?
Thanks
Almir

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

almirfiorio - Thursday, March 1, 2018 1:18 PM
Dear Fiends
inside "Management> SQL Server Logs" I want to automatically delete old logs
How can I leave only only 10 days of SQl Server logs in sql server?
Thanks
Almir

Right click on the SQLServer Logs folder and select configure. You can set the number of logs in that window that comes up.

Sue

Michael L John One Orange Chip Points: 27038 More actions · Answer 2

Do you want 10 DAYS, or 10 log files?
If you are recycling the logs on a nightly basis, then going into settings is the way to do it as Sue_H suggested.
If you are not recycling the logs, then you may need to implement that as well as what Sue said.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

almirfiorio Say Hey Kid Points: 665 More actions · Answer 3

Michael L John - Thursday, March 1, 2018 1:51 PM
Do you want 10 DAYS, or 10 log files?
If you are recycling the logs on a nightly basis, then going into settings is the way to do it as Sue_H suggested.
If you are not recycling the logs, then you may need to implement that as well as what Sue said.

im want only 10 days of log

Sue_H SSC Guru Points: 90860 More actions · Answer 4

almirfiorio - Thursday, March 1, 2018 2:27 PM
Michael L John - Thursday, March 1, 2018 1:51 PM
Do you want 10 DAYS, or 10 log files?
If you are recycling the logs on a nightly basis, then going into settings is the way to do it as Sue_H suggested.
If you are not recycling the logs, then you may need to implement that as well as what Sue said.
im want only 10 days of log

So as Michael and I are both getting at is you can:
Daily you can execute sp_cycle_errorlog. You can create a job to do this at midnight (or whatever time) every day so the log is cycled every day.
And then set max files to 10.

Sue

almirfiorio Say Hey Kid Points: 665 More actions · Answer 5

Sue_H - Thursday, March 1, 2018 2:32 PM
almirfiorio - Thursday, March 1, 2018 2:27 PM
Michael L John - Thursday, March 1, 2018 1:51 PM
Do you want 10 DAYS, or 10 log files?
If you are recycling the logs on a nightly basis, then going into settings is the way to do it as Sue_H suggested.
If you are not recycling the logs, then you may need to implement that as well as what Sue said.
im want only 10 days of log
So as Michael and I are both getting at is you can:
Daily you can execute sp_cycle_errorlog. You can create a job to do this at midnight (or whatever time) every day so the log is cycled every day.
And then set max files to 10.
Sue

Great

you have this jobs?

im want to keep 10 days of all logs (swl server log)

this job keep only error logs ?

Thanks
Almir

Sue_H SSC Guru Points: 90860 More actions · Answer 6

almirfiorio - Thursday, March 1, 2018 3:15 PM
Sue_H - Thursday, March 1, 2018 2:32 PM
almirfiorio - Thursday, March 1, 2018 2:27 PM
Michael L John - Thursday, March 1, 2018 1:51 PM
Do you want 10 DAYS, or 10 log files?
If you are recycling the logs on a nightly basis, then going into settings is the way to do it as Sue_H suggested.
If you are not recycling the logs, then you may need to implement that as well as what Sue said.
im want only 10 days of log
So as Michael and I are both getting at is you can:
Daily you can execute sp_cycle_errorlog. You can create a job to do this at midnight (or whatever time) every day so the log is cycled every day.
And then set max files to 10.
Sue
Great
you have this jobs?
im want to keep 10 days of all logs (swl server log)
this job keep only error logs ?
Thanks
Almir

The job would cycle the error logs which means just create a new one. And then when you right click on the SQL Server Logs folder select Configure and set it to 10, it will automatically delete the logs and will just keep 10 of them. So if you cycle the log every day, that would leave a log for every day and you would have 10 logs. So 10 days.
To create the job, just create a new job and it's just one t-sql step of:
sp_cycle_errorlog

Sue

almirfiorio Say Hey Kid Points: 665 More actions · Answer 7

Sue_H - Thursday, March 1, 2018 4:14 PM
almirfiorio - Thursday, March 1, 2018 3:15 PM
Sue_H - Thursday, March 1, 2018 2:32 PM
almirfiorio - Thursday, March 1, 2018 2:27 PM
Michael L John - Thursday, March 1, 2018 1:51 PM
Do you want 10 DAYS, or 10 log files?
If you are recycling the logs on a nightly basis, then going into settings is the way to do it as Sue_H suggested.
If you are not recycling the logs, then you may need to implement that as well as what Sue said.
im want only 10 days of log
So as Michael and I are both getting at is you can:
Daily you can execute sp_cycle_errorlog. You can create a job to do this at midnight (or whatever time) every day so the log is cycled every day.
And then set max files to 10.
Sue
Great
you have this jobs?
im want to keep 10 days of all logs (swl server log)
this job keep only error logs ?
Thanks
Almir
The job would cycle the error logs which means just create a new one. And then when you right click on the SQL Server Logs folder select Configure and set it to 10, it will automatically delete the logs and will just keep 10 of them. So if you cycle the log every day, that would leave a log for every day and you would have 10 logs. So 10 days.
To create the job, just create a new job and it's just one t-sql step of:
sp_cycle_errorlog
Sue

Great

but im want only last 10 days , (not 10 files)

im can keep only last 10 days ?

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 8

If you follow the steps to create a scheduled task to cycle the error log every day, and then set SQL Server to retain only ten logs, then that'll give you your ten days of logs.

Unfortunately, if you reboot the server, that'll also run sp_cycle_errorlog, so you might want to set the number of logs to 11. <insert Spinal Tap reference here>

Thomas Rushton
blog: https://thelonedba.wordpress.com

almirfiorio Say Hey Kid Points: 665 More actions · Answer 9

ThomasRushton - Friday, March 2, 2018 6:01 AM
If you follow the steps to create a scheduled task to cycle the error log every day, and then set SQL Server to retain only ten logs, then that'll give you your ten days of logs.
Unfortunately, if you reboot the server, that'll also run sp_cycle_errorlog, so you might want to set the number of logs to 11. <insert Spinal Tap reference here>

When reboot the server the jobs cannot continue ? im need to setg to 10 again? why ?

Thanks
Almir

sgmunson SSC Guru Points: 110639 More actions · Answer 10

almirfiorio - Friday, March 2, 2018 8:53 AM
ThomasRushton - Friday, March 2, 2018 6:01 AM
If you follow the steps to create a scheduled task to cycle the error log every day, and then set SQL Server to retain only ten logs, then that'll give you your ten days of logs.
Unfortunately, if you reboot the server, that'll also run sp_cycle_errorlog, so you might want to set the number of logs to 11. <insert Spinal Tap reference here>
When reboot the server the jobs cannot continue ? im need to setg to 10 again? why ?
Thanks
Almir

His point was simply that every re-boot of the server will cause the sp_cycle_errorlog procedure to be run, in addition to the scheduled job that Sue was recommending you set up to run daily. If a re-boot occurs, think about what would then happen to the log files - If the re-boot doesn't happen to span the time frame that you would normally have that job scheduled for, then you'll end up getting an extra execution of the procedure, and thus holding on to 11 log files instead of 10 can avoid premature data loss simply because the server gets re-booted.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

almirfiorio Say Hey Kid Points: 665 More actions · Answer 11

ThomasRushton - Friday, March 2, 2018 6:01 AM
If you follow the steps to create a scheduled task to cycle the error log every day, and then set SQL Server to retain only ten logs, then that'll give you your ten days of logs.
Unfortunately, if you reboot the server, that'll also run sp_cycle_errorlog, so you might want to set the number of logs to 11. <insert Spinal Tap reference here>

is it possible to limit the size of log in size (mb) ?

MMartin1 One Orange Chip Points: 27764 More actions · Answer 12

https://technet.microsoft.com/en-us/library/ms182512(v=sql.110).aspx

----------------------------------------------------

MMartin1 One Orange Chip Points: 27764 More actions · Answer 13

almirfiorio - Friday, March 2, 2018 9:11 AM
ThomasRushton - Friday, March 2, 2018 6:01 AM
If you follow the steps to create a scheduled task to cycle the error log every day, and then set SQL Server to retain only ten logs, then that'll give you your ten days of logs.
Unfortunately, if you reboot the server, that'll also run sp_cycle_errorlog, so you might want to set the number of logs to 11. <insert Spinal Tap reference here>
is it possible to limit the size of log in size (mb) ?

Just a quick search yielded information about altering the registry in order to limit the size of the error log file. I am not too knowledgeable on all of that but wonder why this would be a worry.

----------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 14

The error log is an ongoing file, one that's not bound by time in any sense. You could reboot or recycle every day, as mentioned, but that only sets a new file, one that isn't again, bound by days. If you had to reboot multiple times, you've have multiple files for a day. If you forget (or your job/task fails), then you'll have multiple files in a day.

If this is an interview question, that's the answer. If this is something you need to do, perhaps you should share the requirement or reasoning why someone wants 10 (or some number) of days of error logs.