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

3
votes
2answers
5k 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 ...
3
votes
2answers
5k 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
3answers
3k views

SQL Server Index Fragmentation

When should you rebuild an index? vs reorganize? What index fragmentation value is considered healthy?
3
votes
3answers
666 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 ...
3
votes
2answers
3k views

How to check that stored procedure is valid

As we know, it is possible to compile and to have so stored procedures: CREATE PROC dbo.MyProc1 AS SELECT * FROM Not_Exists_Or_Removed_Table or CREATE PROC dbo.MyProc2 AS SELECT Id ...
3
votes
1answer
2k 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 ...
3
votes
1answer
1k views

Need advice for a 'tag' table design

My website has different content types. All content types should be tag-able (except if specified in the model, whatever). I can think of 2 options for my design: 2 tables option : 1 table for the ...
3
votes
2answers
142 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 ...
3
votes
1answer
4k 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), ...
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 ...
3
votes
2answers
3k views

tsql: table data size won't shrink after large row deletion

i have a table with 12 months+ data in it. I backup the table then delete all rows keeping only rows inserted in the last month. But the table size won't shrink, nor the underlying physical file. ...
3
votes
1answer
35 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 ...
3
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
1answer
177 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 ...
3
votes
1answer
980 views

PostgreSQL maintenance

I am new to PostgreSQL and want to know more about maintenance options in pgAdmin III. I've read the documentation and now I know that vacuuming frees some space and I need to run it regularly. ...
3
votes
1answer
136 views

Thoughts about Oracle backup procedure for 11.2 (beginner question)

Two days ago we "found out" that our old, not taken care of, horribly neglected, Oracle DB server was extremely important to our business. The only person who knew anything about this server was ...
3
votes
1answer
1k views

Daily database maintenance using SQL Server 2008 and a stored procedure

I have created a database maintenance plan using wizard though that link for SQL Server 2005. I have created a Maintenance Plan for SQL Server 2008 and it works fine. Now I required to complete the ...
3
votes
2answers
2k 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] ...
3
votes
0answers
35 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 ...
2
votes
3answers
5k 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 ...
2
votes
3answers
2k views

Should I be regularly shrinking my DB or at least my log file?

My question is, should I be running one or both of the shrink command regularly, DBCC SHRINKDATABASE OR DBCC SHRINKFILE ============================= background Sql Server: Database is 200 ...
2
votes
4answers
17k 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 ...
2
votes
1answer
97 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 ...
2
votes
1answer
654 views

Maintenance Plan Takes Too Long - Locks Tables - Indexing To Blame

System: I have a Maintenance Plan that Rebuilds Indexes for Several Tables (10 tables, 50M records total). Issue: During the Index Rebuild (~20 minutes), we fail to insert data into the DB ...
2
votes
1answer
4k views

Location of Maintenance Plan's Back Up Database Tasks information (SQL Server 2005)

I would like to know where in the database or on the file system the information about Back Up Database Task in the Maintenance Plans Tasks. I can find the Job in msdb.dbo.sysjobs I can find the ...
2
votes
1answer
1k views

Disadvantages of reorganizing all tables nightly

I consider scheduling a SQL Server job that reorganizes all tables nightly. We don't have enterprise, so we cannot rebuild online (this means we need to reorganize). We also don't want to invest too ...
2
votes
4answers
868 views

Maintenance Plan for all Online databases doesn't execute for all databases

Using SQL Server 2008 R2, I have a maintenance plan that has a basic Backup All Databases (ignore offline) task, and clean up after 7 days. However, I note that sometimes I lack backups for ...
2
votes
2answers
820 views

maintenance plan executing by agent

I have a built-in, regular maintenance plan scheduled to run weekly. The databases are SharePoint databases and a snap manager (netapp snap manager) backs up these databases. The rebuild ...
2
votes
2answers
11k 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
5k views

SQL Server 2008 - Run the Check Database Integrity Task with the PHYSICAL_ONLY option?

As part of our nightly maintenance, we include the Check Database Integrity Task which is an encapsulation of the DBCC CHECKDB command. It's beginning to take a considerable amount of time due to the ...
2
votes
2answers
762 views

Reorganize Indexes T-SQL

I'm moving a database to SQL Azure and with that I lose the maintenance plans so I am going to use a Worker Role to schedule them. When I open the properties of the Rebuild Index Task and click on ...
2
votes
1answer
772 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 ...
2
votes
1answer
190 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 ...
2
votes
1answer
297 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 ...
2
votes
1answer
677 views

Database design with different user roles connected by different relationships to one organizaiton table

An user is connected to an organization by some role. Lets say there are 2 roles freelancer and employee. One employee can only be a part of one organization and if he is an employee then he can't be ...
2
votes
2answers
360 views

Is it possible to retrieve the specific databases selected in a maintenance plan step?

I would like to use a tsql query to retrieve the databases involved in one of the steps in a database backup plan I've created. From a previous question, I understand that somehow, the data is ...
2
votes
1answer
78 views

Log Custom SQL Results During Maintenance Plan

I would like the last step of my Maintenance Plan to execute a simple custom SQL script (SELECT * FROM <SomeTable> WHERE <Condition>), and save the results to a file (Text, CSV, etc). Is ...
2
votes
1answer
102 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 ...
2
votes
1answer
1k views

How often should I run integrity and consistency checks on SQL Server 2008?

Before updates of our application, I run some checks on the database like CheckAllocations and CheckCatalog, so I know the database is in good shape before the update scripts get executed. What is ...
2
votes
1answer
1k views

Maintenance Plan fails but the query it generates Runs

I have an SQL Server 2005 Enterprise Edition whose Maintenance plan fails constantly with the error: backup MYSERVER (MYSERVER) Backup Database on MYSERVER Databases that have a compatibility level ...
2
votes
0answers
55 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 ...
2
votes
1answer
68 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, ...
2
votes
0answers
161 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 ...
2
votes
1answer
136 views

Tools and methodologies to keep to DBs aligned [closed]

2 DBs having schemas that represent the same semantic objects. The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres. Once in a while ...
2
votes
0answers
831 views

How to do automated archiving for a SQL Server database based on interpretation of database metadata?

I am looking for a solution giving me automated archiving of SQL Server databases based on a smart interpretation of table relationships that is able to archive records from related tables minimizing ...
2
votes
0answers
776 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 ...
1
vote
1answer
1k 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 ...
1
vote
1answer
113 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?
1
vote
2answers
3k 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
1
vote
1answer
185 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, ...