how to let the recovery model set forever as simple

Question

how to let the recovery model set forever as simple

almirfiorio

Say Hey Kid

Points: 665
More actions
March 2, 2018 at 9:23 am

#344212

Dear Friends
my sql server is with recovery model set to simple, but every day it is changing to full.
how does it leave it forever as simple or how to create a job that changes it to simple?
Thanks
Almir

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

almirfiorio - Friday, March 2, 2018 9:23 AM
Dear Friends
my sql server is with recovery model set to simple, but every day it is changing to full.
how does it leave it forever as simple or how to create a job that changes it to simple?
Thanks
Almir

Someone or something is changing the recovery model. SQL Server will not do this by itself. There could be a scheduled job running that is modifying the recovery model, someone with sysadmin (server level) or dbo (database level) privileges could be changing the recovery model. Start asking people that you work with. You could also use extended events to see when this change is occurring and by whom.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

you can check the SQL Agent metadata to check if it's a SQL script in a job. Run this in MSDB database:
SELECT j.job_id, j.name, js.step_id, js.step_name, js.command FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id WHERE js.subsystem = 'TSQL' AND js.command LIKE '%SET RECOVERY%' ORDER BY j.name, js.step_id

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

Chris Harshman - Friday, March 2, 2018 9:55 AM
you can check the SQL Agent metadata to check if it's a SQL script in a job. Run this in MSDB database:
SELECT j.job_id, j.name, js.step_id, js.step_name, js.command FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id WHERE js.subsystem = 'TSQL' AND js.command LIKE '%SET RECOVERY%' ORDER BY j.name, js.step_id

im want to set recovery model to simple forever
or create a job to change every day

this code sql only show the schedule backup

Thanks
Almir

sgmunson SSC Guru Points: 110639 More actions · Answer 4

almirfiorio - Friday, March 2, 2018 1:22 PM
Chris Harshman - Friday, March 2, 2018 9:55 AM
you can check the SQL Agent metadata to check if it's a SQL script in a job. Run this in MSDB database:
SELECT j.job_id, j.name, js.step_id, js.step_name, js.command FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id WHERE js.subsystem = 'TSQL' AND js.command LIKE '%SET RECOVERY%' ORDER BY j.name, js.step_id
im want to set recovery model to simple forever
or create a job to change every day
this code sql only show the schedule backup
Thanks
Almir

If you got a result from that query, then there's a job in existence that sets the recovery model. That's what Chris Harshman was trying to convey with his post of that code. He was giving you a way to find out if there was a job in existence that was setting the recovery model. It was not intended to actually set that model permanently. There's no way that I'm aware of to do that. However, now that you know that there IS a job that does so, you'll have to address the fact that said job affects the recovery model.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

almirfiorio - Friday, March 2, 2018 1:22 PM
Chris Harshman - Friday, March 2, 2018 9:55 AM
you can check the SQL Agent metadata to check if it's a SQL script in a job. Run this in MSDB database:
SELECT j.job_id, j.name, js.step_id, js.step_name, js.command FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id WHERE js.subsystem = 'TSQL' AND js.command LIKE '%SET RECOVERY%' ORDER BY j.name, js.step_id
im want to set recovery model to simple forever
or create a job to change every day
this code sql only show the schedule backup
Thanks
Almir

Yes, you want to set the recovery model to simple and leave it that way. SQL Server does NOT randomly change the recovery model on databases. That has to be done by someone or through an automated (scheduled) process. If it is changing you need to determine who or what is changing it. That is what the code Chris posted helps identify and based on your comment it looks like you may have a job that does that may be affecting your setting of the recovery model on your database(s) that you want in simple recovery model.

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

sgmunson - Friday, March 2, 2018 1:30 PM
almirfiorio - Friday, March 2, 2018 1:22 PM
Chris Harshman - Friday, March 2, 2018 9:55 AM
you can check the SQL Agent metadata to check if it's a SQL script in a job. Run this in MSDB database:
SELECT j.job_id, j.name, js.step_id, js.step_name, js.command FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id WHERE js.subsystem = 'TSQL' AND js.command LIKE '%SET RECOVERY%' ORDER BY j.name, js.step_id
im want to set recovery model to simple forever
or create a job to change every day
this code sql only show the schedule backup
Thanks
Almir
If you got a result from that query, then there's a job in existence that sets the recovery model. That's what Chris Harshman was trying to convey with his post of that code. He was giving you a way to find out if there was a job in existence that was setting the recovery model. It was not intended to actually set that model permanently. There's no way that I'm aware of to do that. However, now that you know that there IS a job that does so, you'll have to address the fact that said job affects the recovery model.

yes im got ! see the result

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 7

Sorry I didn't explain well enough what that script was showing. As Steve and Lynn stated, that query shows you if there are any SQL Agent jobs that already are modifying the recovery model. I'd be concerned that it's changing recovery model in your backup job, and that the step that shows up is named "SHRINKDATABASE" I'm thinking that a scheduled backup job shouldn't be doing either of those 2 things.

sgmunson SSC Guru Points: 110639 More actions · Answer 8

almirfiorio - Friday, March 2, 2018 1:51 PM
sgmunson - Friday, March 2, 2018 1:30 PM
almirfiorio - Friday, March 2, 2018 1:22 PM
Chris Harshman - Friday, March 2, 2018 9:55 AM
you can check the SQL Agent metadata to check if it's a SQL script in a job. Run this in MSDB database:
SELECT j.job_id, j.name, js.step_id, js.step_name, js.command FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id WHERE js.subsystem = 'TSQL' AND js.command LIKE '%SET RECOVERY%' ORDER BY j.name, js.step_id
im want to set recovery model to simple forever
or create a job to change every day
this code sql only show the schedule backup
Thanks
Almir
If you got a result from that query, then there's a job in existence that sets the recovery model. That's what Chris Harshman was trying to convey with his post of that code. He was giving you a way to find out if there was a job in existence that was setting the recovery model. It was not intended to actually set that model permanently. There's no way that I'm aware of to do that. However, now that you know that there IS a job that does so, you'll have to address the fact that said job affects the recovery model.
yes im got ! see the result

It appears that the SHRINK DATABASE command is actually commented out, but it was kind of hard to see without a LOT of magnification of your image. However, what I said before still applies, as others have pointed out. The mere existence of that row of output from your query means that somewhere in that job, a command contains the string that when run, would set the recovery model, and thus change it. Look at all the job steps of that job and you'll likely find the culprit.

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