Stack Overflow
Stack Overflow

Questions tagged [maintenance-plan]

A Maintenance plan is any variety of scheduled maintenance to an object or item of equipment. In SQL Server a maintenance plan creates a workflow of the tasks required to make sure that the database is optimized, regularly backed up and free of inconsistencies.

105 questions
47
votes
9 answers

Reorganise index vs Rebuild Index in Sql Server Maintenance plan

In the SSW rules to better SQL Server Database there is an example of a full database maintenance plan: SSW. In the example they run both a Reorganize Index and then a Rebuild Index and then Update Statistics. Is there any point to this? I thought…
BTB
  • 2,126
  • 3
  • 21
  • 22
46
votes
11 answers

SQL Maintenance Cleanup Task Working but Not Deleting

I have a Maintenance Plan that is suppose to go through the BACKUP folder and remove all .bak older than 5 days. When I run the job, it gives me a success message but older .bak files are still present. I've tried the step at the following question:…
Alex
  • 1,663
  • 6
  • 23
  • 32
42
votes
3 answers

Maintenance Plan Execute option is greyed out

When I go to SQL Server Management Studio -> Management -> Maintenance Plans -> Right click on the maintenance plan that I want to execute but the option is greyed out. I have checked and SQL Server Integration Services is running. I can execute…
Oxon
  • 4,901
  • 8
  • 40
  • 54
36
votes
5 answers

How often should the indexes be rebuilt in our SQL Server database?

Currently our database has size 10 GB and is growing by around 3 GB per month. Often I hear that one should from time to time rebuild the indexes, to improve the query execution time. So how often should I rebuild the indexes in the given scenario?
HotTester
  • 5,620
  • 15
  • 63
  • 97
18
votes
1 answer

SQL Server: should I use an "Agent Job" or a "Maintenance Plan" to delete old data?

I'm looking for a way to periodically (e.g. weekly) run some SQL statements in a database to delete old data. As far as I can see, there are (at least) two ways to do this: using a "Maintenance Plan" and a "Execute T-SQL Statement Task" using an…
M4N
  • 94,805
  • 45
  • 217
  • 260
14
votes
1 answer

How to create maintenance plan in SQL Server?

When I'm trying to create a new maintenance plan in SQL Server, it is showing this error: TITLE: Microsoft SQL Server Management Studio ------------------------------ 'Agent XPs' component is turned off as part of the security configuration for…
nectar
  • 9,525
  • 36
  • 78
  • 100
13
votes
9 answers

SQL Server xp_delete_file not deleting files

I'm trying to write some SQL that will delete files of type '.7z' that are older than 7 days. Here's what I've got that's not working: DECLARE @DateString CHAR(8) SET @DateString = CONVERT(CHAR(8), DATEADD(d, -7, GETDATE()), 1) EXECUTE…
Chris Burgess
  • 5,787
  • 13
  • 54
  • 69
12
votes
5 answers

Do all SQL server versions rebuild indexes automatically or have a default rebuild criteria?

Do all SQL server versions rebuild indexes automatically or have a default rebuild criteria? I understand statistics are rebuilt automatically but not sure if indexes do as well.
thiswayup
  • 2,069
  • 8
  • 32
  • 52
11
votes
5 answers

How do I monitor and find unused indexes in SQL database

I would like to monitor index usage for an SQL database in order to find unused indexes and then drop them. How can I monitor index usage most efficiently? Which scripts could be useful? I'm aware of this question about identifying unused objects,…
Ole Lynge
  • 4,457
  • 8
  • 43
  • 57
10
votes
1 answer

SQL Maintenance Plan sends mail when no mail configured

I have a Maintenance plan under SQL Server 2008 R2 which runs at midnight. Within this maintenance task, there is no notify operator task. Within the SQL Agent job that executes the maintenance plan, there is no email alert. However, every time this…
mservidio
  • 12,817
  • 9
  • 58
  • 84
9
votes
2 answers

Change SQL Server Maintenance Plan Report Location?

I created some Maintenance Plans in SQL Server 2008 without using the wizard (i.e. right-click on Maintenance Plans and select New Maintenance Plan...). When created that way, there is no option to specify the location for the Maintenance Plan…
RWGodfrey
  • 847
  • 1
  • 9
  • 19
7
votes
2 answers

SQL Server: Programmatically Execute Maintenance Plan

Is there any way to programmatically execute (start) a SQL Server maintenance plan? We have a windows service that runs nightly and updates the DB with lots of data, once that is finished we would like to trigger a maintenance plan in the DB to…
Hungry Beast
  • 3,677
  • 7
  • 49
  • 75
7
votes
5 answers

What is your ratio Bug fixing vs Enhancements?

In the spirit of this question I wanted to have a sense of what is the proportion of time split between fixing bugs and implementing new features. If possible try to give an estimate for the product as a whole as opposed to individual developer…
Newtopian
  • 7,543
  • 4
  • 48
  • 71
7
votes
1 answer

Maintenance Plan for ASP.Net SQL Server SessionState (ASPState) Database

I'd like to clarify what the optimal maintenance plan set up is for the ASPState database that is used by ASP.Net SQL Server session state. The standard user database maintenance plan doesn't seem suitable. I'll never need to do a point-in-time…
Dan Malcolm
  • 4,382
  • 2
  • 33
  • 27
6
votes
1 answer

How to handle maintenance process with React

Here is my app’s current way to handle the maintenance process: I have an environment variable for the app status: REACT_APP_SITE_MODE=“LIVE” | “MAINTENANCE” I'm using react-router for routing the app: switch (process.env.REACT_APP_SITE_MODE) { …
vuongvu
  • 811
  • 6
  • 15
1
2 3 4 5 6 7