MS SQL Server/automate stored procedure
QUESTION: I need to automate the task of deleting data if its older than an year. One way I came across is to create a stored procedure & assign as a job in sql server agent. Is there any other way this can be done?
Since I've never used server agent before does it affect the performance of sql server/databases in any way? If you suggest to go ahead with this, can you please also guide on how to schedule the procedure.Currently on the SQL Server Mgmt Studio 2005, it says 'Agent XPs disabled'.
Thanks a lot for your help!
ANSWER: SQL Agent is the best way to automate most tasks in SQL Server, so your approach should work. There's 2 ways to handle this.
1) Put the code to delete in one of the job steps directly
2) Put the code into a stored procedure and call that stored procedure from the job.
The performance of the code will be the same. I think you'll get a slight performance increase with #2, because the procedures execution plan will be cached.. but it will be slight and it might be none (I'd have to research the caching of execution plans for SQL code in job steps).
What would determine the method to use for me is:
* Will I ever want to run the code NOT using the job? e.g., perhaps you want to let some other person/app run the code. In that case, #2 is best because they can run the proc directly (given proper permissions of course).
* How often will the code be maintained? It is usually easier to modify a proc than a job step (not a lot, but still...)
All things being equal, I tend to gravitate towards #2 because it "encapsulates" functionality. The proc does the work. The job handles the scheduling.
Let me know if you have further questions on how to do this.
---------- FOLLOW-UP ----------
QUESTION: Can you please tell how to do this the stored procedure way? Thanks! Your help is truly appreciated!
I assume you already have the T-SQL code to do the actual delete. Do you know how to create a stored procedure? If not, look up CREATE PROCEDURE for details, but basically in an SSMS query window, you enter
CREATE PROCEDURE MyDeletingProc AS
<...put your T-SQL code here...>
You can also create a procedure by right clicking on the Stored Procedures "folder" inside your your database in the Object Explorer pane of SSMS. Right clicking should give you a menu with the option "New Procedure", or something like that.
Next, I assume you know how to create a job? That's a bit trickier to do via T-SQL, so I would do it via SSMS (this is from memory, so some of the exact wording and menu options might be a bit different):
Right click SQL Agent, select New Job
Enter the job name
Click Add (maybe "New"?)
Enter the job step name
Make the type "SQL" (maybe "Transact SQL", or something similar?)
Make the step's database the appropriate one (MyDatabase in the example above); the database where the procedure was created.
In the SQL code are of that job step, enter:
Click Save (or Done?)
You should be back to the job properties. If not, click Save/Done until you are.
Click Schedule (this is at the same place you clicked "Steps" above)
Using the GUI, configure the schedule to run the job whenever you want.
Click Save/Done, continue to do so until the job properties editor is gone (making sure to say "Yes" if you get prompts asking you if you want to save changes).
That's a short version. More details can be found thru Google.
The job will now run at the time(s) you specified in the Schedule portion above. You can also run it any time by right-clicking on it (in SSMS's Object Explorer pane, under SQL Agent) and selecting "Run"
Let me know if you have further questions.