In the context of a database, maintenance describes routine operational tasks around a database system, such as monitoring, tuning and backup procedures.

learn more… | top users | synonyms

15
votes
2answers
18k views

When To Update Statistics?

I've inherited a Maintenance Plans that does the following: Cleanup old data Checks DB integrity Performs Database and Transaction Log Backups Reorganizes Our indexes Updates Statistics Delete old ...
5
votes
1answer
2k views

VACUUM returning disk space to operating system

VACUUM usually does not return disk space to operating system, except in some special cases. From the docs: The standard form of VACUUM removes dead row versions in tables and indexes and marks ...
1
vote
1answer
45 views

Rebuild vs Reorganize [duplicate]

I currently have a maintenance plan that is reorganizing indexes everyday and I would like to know if you reorganize an index everyday would you ever need to rebuild the index? If so, why?
19
votes
5answers
16k views

When should I rebuild indexes?

When should I rebuild the indexes in my relational database (e.g. SQL Server)? Is there a case for rebuilding indexes on a regular basis?
0
votes
1answer
83 views

SQL Server 2008, problem with backing up transaction log

I have a transaction backup job which runs every hour, first it backups up from the database log to another drive then it shrinks the log and deletes old trn files. The middle task shrinks the log ...
20
votes
4answers
51k views

Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling

I am tasked with devising a maintenance plan for our Sql Server 2005 databases. I know for backups I want to do a daily full database backup and transactional log backups every 15 minutes. My problem ...
2
votes
3answers
802 views

Automatically restore SQL Server database from file from another server

We have a daily task to overwrite a number of development databases using backups of the associated production databases. The backups are produced by maintenance plans on the production server then ...
0
votes
2answers
36 views

Is it a good idea to add a reserved field to a large MySQL table?

As you may know adding a new column to a MySQL table with millions of records is hard and in some cases impossible. Is it a good idea to add a text column to each table (say, meta) to save some ...
0
votes
0answers
22 views

Does automated backup performance suffer if backup completion time exceeds the backup window?

Is there anything "special" about the Amazon RDS Automated Backup Window, or is the backup window merely an arbitrary timespan in which the backup is expected to complete? That is to say, if I have ...
2
votes
1answer
41 views

SQL Maintenance Rebuild Fails

I am pretty new to SQL and I have this maintenance job that keeps failing and I am not sure how to troubleshoot this. I have troubleshot other job failures with google help to success but this one is ...
1
vote
1answer
124 views

Index file growing endlessly - how to fix it?

I need to do some maintenance on a database on our client's site. Both log and index files were huge (Don't know how they took their backups to end this way) anyway I fixed the log file (.ldf) issue, ...
11
votes
2answers
7k views

Why doesn't DELETE + REORG free diskspace (DB2)?

In DB2 I have a table containing large binary data. Now i purged the whole table and ran runstats, reorg, runstats, but the amount of disk space taken does not change. What could be wrong here? The ...
2
votes
2answers
7k views

What is the use of “Backup set will Expire:” option while creating Backup Database task in Maintenance plan

While backing up the database, I would like to use "Backup set will Expire:" option to delete/overwrite old database backups. I am not able to use this option. Any advice on how to use "Backup set ...
0
votes
0answers
239 views

Changing fill factor on msrepl_command db in distribution

I can't think of any reason why you couldn't change the fill factor on the table without any issues, but I've never changed the fill factor on a system table before and would like to know if anyone ...
2
votes
1answer
102 views

Ola Hallengren index maintenance - long periods of time between commands?

I run the Ola Hallengren scripts on all of my servers for index and statistics maintenance. When I look through the command log table, I notice long periods of time between a command ending, and the ...
1
vote
5answers
3k views

How to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job)?

Struggling to execute PowerShell commands in SqlServer Agent job of MS SQL Server 2012 R2 (Windows Server 2008R2, Windows 7 Prof) I need to delete (.bak files older, than, say, 7 days in remote ...
2
votes
4answers
11k views

No Maintenance Plans Folder in SQL Studio 2008 R2 [closed]

A client has SQL Server 2008 R2 installed on a Windows 2003 R2 32-bit server. Under Management in SQL Studio 2008 R2, there is no maintenance plans folder. It is not under Management --> Legacy ...
1
vote
1answer
303 views

Do you have to run nodetool repair on every node?

Do you have to run nodetool repair on every node in a cluster, or do you only need to run it on one node, and from there Cassandra will take care of the rest?
5
votes
1answer
2k views

How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”?

We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day. The problem is, the differential backups don't always seem to ...
2
votes
2answers
7k views

Do I need to RUNSTATS after a REORG in DB2?

The REORG documentation says: BUILD Builds indexes. Updates index statistics. But I have always heard REORG/RUNSTATS as if they should be paired. Can anyone verify that "Updates index ...
2
votes
1answer
46 views

Can autoshrink permanently damage a database? [duplicate]

I've been given the task of finding out why our server is slow. I ran a stored procedure I downloaded called sp_Blitz. It let me know that our database had auto-shrink enabled, and to disable it. So, ...
1
vote
3answers
276 views

Eliminating duplicate records in data cleansing [closed]

I have a database full of records of people with simple information like first name, last name, email, location, ... . I need to eliminate the duplicate records. As I've search the process is called ...
2
votes
1answer
80 views

SQL User with only access to maintain indexes and statistics

I need to have an AD user account with access to all databases on a specific instance of SQL Server 2008 R2, and the only thing that user will be doing is; Reorganize indexes Database integrity ...
0
votes
1answer
78 views

Appropriate use of mysql Optimize Table

I want to come up with some best practice for maintaining our mysql database. versions 5.5/6 and using InnoDB. i came across this artice - ...
2
votes
1answer
1k views

Oracle DB occasionally slows down when “Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN”

After midnight Oracle Database 12c turned to "slow mode". DML statements became about 10 times slower, though "selects" feel good. CPU load as usual, disk I/O is perfect (tested by copying 500m file), ...
0
votes
1answer
34 views

Index Maintenance Plan

At our organisation we are migrating to SQL Server from visual foxpro dbf and hence had no maintenance plans and have no DBA. We have gone through our backup plans and Index maintenance is only index ...
14
votes
5answers
8k views

Does restarting SQL Server speed it up?

I have noticed that some DBAs restart SQL Server very frequently, sometimes even nightly. I believe they do it to free up some memory, or perhaps to speed up queries too. I know that after a restart ...
9
votes
1answer
6k views

PostgreSQL Database Data File Integrity Check

When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption? In the ...
3
votes
1answer
141 views

Jobs for cycling error logs

Couple of weeks ago I asked this question Error logs various questions and got the answer I needed. Based on the answer and some readings I want to create the following jobs: cycle the error log ...
1
vote
1answer
76 views

Database never maintained

Just moved from wrong forum i paste here my question: I just got a new client recently migrated from SQL Server 2000 to SQL Server 2005 due to vendor application limitation to new releases. Vendor ...
12
votes
1answer
498 views

SQL Server 2008 R2 index rebuild fails with severity 17

Occasionally during our index maintenance, the job will fail with a SEV 17 error where enough space can not be allocated for the object it is rebuilding. The database is laid out as such: Data_file1 ...
3
votes
2answers
3k views

Shrink database operation in maintenance plan failed

See updates below I have a nightly DB maintenance plan and it's causing some strange behaviour. About 1 out of 10 runs it causes the DB to not response to queries. It takes about 5 minutes for the ...
1
vote
3answers
3k views

Proper MySQL database maintenance

I hope this isn't too broad of a question. I'm certain it'll be able to help me and any future dba noobs that stumble upon it. I'm a systems admin that got put into the roll of DBA (because I helped ...
3
votes
2answers
417 views

Ola Hallengren with database mirroring

I am about to setup Ola Hallengrens database maintenance plan. We have our database mirrored and I was just wanting to know if I need to run the script on both my Sql Server instances or only the ...
0
votes
1answer
31 views

Running several versions of a MySQL database in parallel?

We have a central read-only MySQL database that contains geo data, etc. It is used by various projects. As part of a refactoring, I am thinking about setting up a system for versioning the database. ...
11
votes
3answers
5k views

What does a best-practice SQL Server maintenance plan look like? [closed]

I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the ...
3
votes
1answer
875 views

How can I search SQL Server for maintenance plans that execute specific tasks?

Sometimes certain SQL Server maintenance plan tasks can be problematic, like Shrink Database, Rebuild Index, Update Statistics, etc. I'd like to search for the use of one or more of these tasks on an ...
2
votes
0answers
120 views

backup history script produces incorrect results

I have a database that is backed up via maintenance plan. The database is scheduled to be backed up at 8:00PM. It usually takes 6 hours to complete. According to this query, SELECT distinct ...
3
votes
2answers
3k views

Shrink the database only up to its initial size which is set after creating database

In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file. So now if create a new ...
0
votes
1answer
60 views

What are the compelling resons to update SQL Server name to a real one after its changing?

Took the job of monitoring and tuning a few dozens SQL Server 2012 Enterprise Ed. SP1 (64 bit) on Windows Server 2008 R2 Standard Ed. running inside virtual machines. All of them on running ...
3
votes
5answers
207 views

Does DBA need (a read) access to virtualization infrastructure? And why?

I am in charge of monitoring, troubleshooting, tuning and optimizing a few dozens production SQL Servers 2008 R2/2012 in my company that are being run inside VMWare virtual machines. I've requested ...
0
votes
0answers
105 views

Can maintenance plans be used in SQL Server multiserver (Master-Target servers) administration?

Since I failed to configure SQL Server 2012 Multiserver (Master and Target Servers) administration, I am eager to understand whether it (setting Master and Target servers) would permit to send from ...
-1
votes
1answer
395 views

How can I detect which indexes are being rebuilt offline and how long?

The rebuild of indexes takes appr. the same 55 min. with both "Do not rebuild indexes and "Rebuild indexes offline" "For index types that do not support index rebuilds" with "Keep index online while ...
1
vote
2answers
825 views

What's the owner of SqlServerAgent job for?

What is the possible use of owner of an SQL Server Agent job, I wonder? Removed, and left it blank and the job still succeeds
0
votes
1answer
220 views

Object name 'Clean Up History' in use when trying to complete the maintenance plan wizard in SQL Server 2008 R2

I am trying to create a maintenance plan on a instance running SQL Server 2008 R2 SP1 (no CU's installed). When completing the wizard I get the following error: The object name cannot be changed ...
4
votes
1answer
803 views

Any way to send notifications to different Emails on success/failure of SqlServerAgent job?

SQL Server 2012 R2 on Windows Server 2008R2 Is there any way to send notifications to different Emails: on success of an Sql Server Agent job to one Email address and on failure - to another ? ...
1
vote
1answer
1k views

How to launch SQL Server Agent job on completion of another one?

An SQL Server Agent job (SSA) have to be executed after a few other SSA jobs. Is there any way to start SSA jobs on completion (or success, or failure) of another one? Currently, I do this on ...
0
votes
1answer
172 views

Does failed task considered to be run to completion in SQL Server maintenance plan terminology?

The replies to my previous question: "How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks?" answered me that I should change ...
0
votes
3answers
289 views

How to make SQL Server Agent job history less verbose?

So, I've created a SQL Server Agent job, according to the answer to my prev question: running nightly (at 2:11) according to the schedule: There are 9 .bak files in 3 subdirectory of ...
0
votes
1answer
5k views

How to copy backup files to remote share in SQL Server Agent job without AD/domain accounts involvement?

MS SQL Server 2012... with nightly databases backups to the same/local as SQL Server machine... I am trying to add another SQL Server Agent Job to copy the .bak files to remote (non-windows, i.e. ...