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

0
votes
0answers
30 views

Oracle database in maintenance mode without patch

I could not enable maintenance mode in oracle database without patch Also I don't have patch(Since I don't have Support Identifier). @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE SP2-0310: unable to ...
4
votes
2answers
229 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 ...
4
votes
5answers
295 views

Does index rebuild time depend on the fragmentation level?

Is the required time for index rebuild dependent on the level of fragmentation? Does the rebuild of a 80% fragmented index approximately take 2 minutes if the rebuild of the same index fragmented ...
0
votes
1answer
137 views

Is it possible to set a timer for autovacuum in PostgreSQL

I am running PostgreSQL 9.4.1. I am currently cleaning up data, which requires me to run expensive queries. It seems like my tables get "locked" up every day at the same time in the afternoon. My ...
0
votes
0answers
23 views

Creating clustered primary key index with ONLINE option

I have primary key clustered index which is constantly modified (insert and update) and read (select). Now, in order to change one of the types of the columns from which it is composed I need to ...
0
votes
0answers
14 views

Diagnosing why postgresql query is going in a seemingly endless processing loop

I maintain a social forum built with Django and postgresql 9.3.10. There's a maintenance query I run via psql, to clean up my postgresql table. It goes like this: begin; DELETE FROM links_seen WHERE ...
5
votes
1answer
68 views

Online clustered index rebuild using more log space than expected

I have a table TableA ID PK BIGINT TrxID BIGINT CardBalance INT AccountBalance INT SP_SPACEUSED 'TableA' output: name rows reserved data index_size unused TableA 904054184 ...
0
votes
2answers
48 views

Deleting old postgresql log files (Ubuntu)

In a Ubuntu machine hosting a postgresql database, I have 16G space taken up at /var/lib/postgresql/9.3/main/pg_log. Apparently, log files from many months ago are being stored as well. I manually ...
3
votes
2answers
508 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
0answers
46 views

oracle : Effect of inserting new rows when row movement is enabled

I have a table which is partitioned by key. I want to move few records (which I require later) to another partition so that I can drop this partition and get some free space. I am enabling row ...
0
votes
1answer
23 views

Deleting aged rows from postgresql table which don't have related entries in another table

I maintain a website where users post interesting links, and other users leave replies under the said links. I want to delete aged link entries under which no one replied. The postgresql database is ...
1
vote
1answer
25 views

Deleting aged postgresql rows with foreignkey constraints

I have a postgresql table where I want to delete rows beyond a certain age. But the catch is that some foreign key constraints would be violated For instance, if I run DELETE FROM links_reply WHERE ...
3
votes
3answers
6k 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 ...
29
votes
5answers
77k 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 ...
0
votes
2answers
7k 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. ...
1
vote
0answers
48 views

Disable/Remove Table Index for INSERT Performance

I am doing a database maintenance where I have to add new fields to a table with over 10GB of data about (20 million rows). To solve that I renamed tableA to be old_tableA and created a new table ...
17
votes
1answer
335 views

Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command

I am looking for practical guidance for setting the values for the BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE of the BACKUP command. I have done a bit of research (see below), I have done a bit of ...
1
vote
2answers
50 views

System Databases Integrity Check & Full Backup

I have an SQL Server 2008 and for maintenance solutions I use Ola Hallengren scripts. Recently, out of 15 databases I have on this server, I made two changes in three of them (not system databases, ...
0
votes
3answers
97 views

MySQL: can I put the database into a maintenance mode? (i.e. no user connections)

I'm going to be doing some complicated maintenance on our MySQL servers and I'd like to keep users out during the work. I'd prefer not to drop and re-create all of the user accounts, but that's the ...
3
votes
1answer
46 views

DB2: Reorg and RunStats

Is there any frequency at which i should run REORG and RUNSTATS on my master tables? If i have a daily load of around 1,000,000 records into my table would it be suggestible to run a REORG and ...
2
votes
0answers
67 views

Rebuild index online without risk to other requests

We have a MS sql server 2012 enterprise database whose indices fragment very fast. We use a maintenance plan that reorganizes those indices daily, and rebuilds them weekly. The rebuild happens ...
6
votes
1answer
354 views

Role of Database Administrators

I am currently working as an application developer on a project which involves the creation of a new SQL Server database. I am actively working with a group of DBAs who will play some role in the ...
3
votes
0answers
49 views

Influence of index rebuild tasks in tempdb on the transaction log?

I have read pretty much about index maintenance and all the pros/cons of reorg, rebuild, online, offline, TEMPDB and timing and right now I am kind of more confused than ever before... The goal is to ...
8
votes
2answers
13k views

Difference Between Row Level and Page Level Locking and Consequences

When attempting to run my Maintenance Plan, I receive the following error: Executing the query "" failed with the following error: "The index "" (partition 1) on table "" cannot be reorganized ...
4
votes
2answers
2k 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?
3
votes
3answers
708 views

Should I create a maintenance plan for my database or use jobs?

I tried creating a Maintenance Plan on my SQL Server 2008 database and was impressed by some of the things that are available out of the box. However, after it was created, it seemed difficult to ...
27
votes
5answers
31k views

When is it OK to shrink a Database?

I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I ...
13
votes
2answers
6k views

Central stored procedure to execute in calling database context

I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on ...
2
votes
1answer
151 views

Weekly full backups and daily differential backups, saving the full backups from each week?

I am wanting to create a maintenance plan that performs a full backup that is done every Sunday morning. After work hours on Monday through Friday, I want to perform a differential backup based on ...
1
vote
1answer
2k views

Occasional error shrinking transaction log files

I have a transaction backup job which runs every hour. First it runs a backup of the database log to another drive, then it shrinks the log and deletes old .trn files. The middle task shrinks the ...
0
votes
1answer
134 views

Maintenance plan fails on non-existent database

We have a maintenance plan on our production server which does all the usual stuff... checking integrity, rebuilding indexes, backups, etc. Lately, it has been failing every night, complaining about ...
11
votes
1answer
592 views

Guidelines for full-text index maintenance

What guidelines should be considered for maintaining full-text indexes? Should I REBUILD or REORGANIZE the full-text catalog (see BOL)? What is a reasonable maintenance cadence? What heuristics ...
3
votes
2answers
214 views

MySQL indexes maintenance

I made a lot of research about how to maintain indexes in MySQL to prevent fragmentation and to optimize somehow the execution of some queries. I am familiar with that formula that calculates the ...
30
votes
4answers
29k views

When should I rebuild indexes?

When should I rebuild the indexes in my relational database (SQL Server)? Is there a case for rebuilding indexes on a regular basis?
3
votes
2answers
3k views

Executing the query “UPDATE STATISTICS [dbo].[Contact] WITH FULLSCAN …” failed

My maintenance plan fails with the following error on one of my databases does anyone know how I can fix this problem? is is a corrupt table? Executing the query UPDATE STATISTICS [dbo].[Contact] ...
4
votes
1answer
148 views

Preventing full backups for unmodified/slightly modified databases

Is there a way to either: ensure that unmodified databases are skipped during SQL backups, or even better choose between DIFF or FULL backup depending on the percent database changed since last ...
1
vote
0answers
811 views

Update Statistics Maintenance Plan - Ambiguous column name error

Microsoft SQL Server 2008 R2 Web Edition (RTM) - 10.50.1600.1 (Intel X86) Windows 2003 R2 SP2 I am in the process of setting up a maintenance plan to update statistics on a daily basis for all user ...
18
votes
2answers
31k 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 ...
7
votes
1answer
5k 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
127 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?
3
votes
3answers
7k 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
46 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 ...
2
votes
1answer
1k 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
205 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, ...
12
votes
2answers
12k 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 ...
8
votes
2answers
21k 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
268 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 ...
3
votes
5answers
7k 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
19k 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 ...
5
votes
1answer
4k 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 ...