Sql-server – SQL Server Agents jobs and turning off the server

sql server

I'm really new to SQL Agent jobs, but I am attempting to build up a maintainance regime for a server that will be turned off and on again at unknown intervals. It may run without being shutdown for a month, or it might only be turned on 9-5… we don't know and the client can't tell us because they don't know.

So what I'm wondering is, what do I need to do to get SQL Server to run monthly and daily jobs either when they are due, or if the due date is missed, get them to be run when the server is next powered on. I could come up with a mish-mash of periodic jobs and 'on-power-up' jobs, but if there is something more elegant that would be wonderful.

Obviously I'll need to ensure the SQL Server Agent is configure to start when the computer is powered up, but what else?

Best Answer

Your options for sql server agent schedule events are:

Whenever SQL Server Agent starts.
Whenever CPU utilization of the computer is at a level you have defined as idle.
One time, at a specific date and time. On a recurring schedule.

If you need something that handles more events then you can always do something like use the Windows scheduler and call sqlcmd that executes sp_start_job or could even execute the BACKUP DATABASE commands directly.

Related Solutions

Sql-server – Why does sp_send_dbmail fail inside SQL Agent jobs

I figured it out this morning. The problem was with the DATEADD function. When it's inside an EXECUTE statement (which is how a SQL Server Agent job would run it), the interval has to be a specific datepart (day), not one of the tokens ("dd").

So, this function:DATEDIFF(dd,DateDisabled,GETDATE())

Needed to be more like this: DATEDIFF(day,DateDisabled,GETDATE())

There were a multiple calls to that function there, but you get the idea.

Here's how I figured it out: I had SQL Server Management Studio script the job as a CREATE script in a new query editor document. Once I found the step I was trying to run, I copied it out. The juicy bits look something like this:

@command = N'[my query]'

I copied that group of lines to a new window and added a DECLARE for the command variable.

Finally, I used EXECUTE (@command) AS USER = '[the user the job runs as]' to see what would happen. Running the query this way, I got much more detailed error messages that way than I did from the log for the job.

I've corrected it now, and the job runs perfectly.

Sql-server – SQL Server 2000 – Restoring .MDF Using sp_attach_single_file_db fails with error

You can give this a try:

Create a new database with the same name and same MDF and LDF files

Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

Start SQL Server

Now your database will be marked suspect

Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

Restart sql server. now the database will be in emergency mode

Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') -- Undocumented step to create a new log file.

(replace the dbname and log file name based on your requirement)

Execute sp_resetstatus

Restart SQL server and see the database is online.

Source

Best Answer

Related Solutions

Sql-server – Why does sp_send_dbmail fail inside SQL Agent jobs

Sql-server – SQL Server 2000 – Restoring .MDF Using sp_attach_single_file_db fails with error

Related Topic