In the context of a database, maintenance describes routine operational tasks around a database system, such as monitoring, tuning and backup procedures.
4
votes
1answer
116 views
Reindexing plan
I have a reindexing plan failing with error message:
Refresh failed for PhysicalPartition '1'
(Details: SQL Server Enterprise edition running in cluster)
Searching through BOl and other online ...
-1
votes
1answer
50 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 ...
1
vote
1answer
601 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
2answers
59 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 ...
4
votes
1answer
81 views
Process attempted to unlock a resource it does not own
SQL Server 2005 SP4 32-Bit
I have a DBCC CHECKDB job running nightly. Last night, soon after the job started, I got the errors below. The database is NOT in suspect mode, and CHECKDB comes back clean ...
0
votes
1answer
76 views
Maintenance in MYSQL when innodb_file_per_table disabled
I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace.
But what if I ...
3
votes
1answer
69 views
Tools and methodologies to keep to DBs aligned
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 ...
3
votes
3answers
52 views
Which system databases should I checkdb and defrag indices?
I have SQL Server Agent (2005) jobs that periodically perform CHECKDB and a defrag (ALTER INDEX REORGANIZE/REBUILD) of any index that is highly fragmented. These are typical maintenance best ...
6
votes
1answer
188 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 ...
4
votes
2answers
106 views
Can't select database for transaction log backup in maintenance plan
I am running into a problem while trying to save a transaction log database backup maintenance task:
Add the 'Back Up Database Task' to the maintenance plan.
Edit the task.
Select backup type: ...
2
votes
1answer
112 views
PostgreSQL 9.2: vacuum returning disk space to operating system
Vacuum usually does not return disk space to operating system, except some special cases.
From the docs:
The standard form of VACUUM removes dead row versions in tables and indexes and marks the ...
1
vote
2answers
77 views
Database design that handles data growth with time
I have a big table that stores video rental data with the following columns:
id, title, language, duration, owner, remarks, closing_date_for_loan
Assuming every day there are thousands of data ...
4
votes
3answers
487 views
Stop SQL Server service(s) before defragmenting drive?
Our production SQL Server 2005 database's data files live on a separate physical drive, which Microsoft Windows 2003's Disk Defragmenter tool reports as 99% fragmented.
We scheduled a task to ...
1
vote
1answer
209 views
Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan
I’m using SQL Server 2008 and I am running several (15) scripts each day to bulk insert to tables. Each script drops the table at the beginning. I create indexes in the scripts. Some of the scripts ...
7
votes
2answers
175 views
Different results rebuilding an index online and offline
I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages.
When I run ...
1
vote
2answers
545 views
Shrink database operation in maintenance plan failed
EDIT: The solution was to remove the DB shrink and modify the rebuild index task. I was rebuilding an index on 3.8 million rows. This was causing SQL blocking. I have removed the rebuilding index task ...
3
votes
1answer
437 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]
...
1
vote
3answers
156 views
Should I manually VACUUM my PostgreSQL database if autovacuum is turned on?
I use software which makes a big PostgreSQL database (there is a table with a million rows in it) and the developers says I should VACUUM and ANALYZE periodically. But the PostgreSQL database default ...
2
votes
1answer
145 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 ...
5
votes
1answer
204 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 ...
1
vote
2answers
317 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 ...
5
votes
2answers
112 views
Thought about this SQL Server backup plan?
I just started a new job, and I'm reviewing the database maintenance plan. I've got quite a bit of experience writing SQL, but not much experience with DB administration. My last job was at a large ...
2
votes
2answers
493 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
0answers
144 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 ...
5
votes
3answers
162 views
Database Indexing - Maintenance Jobs
I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are ...
7
votes
2answers
2k 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 ...
1
vote
1answer
236 views
Use of Shrink after rebuilding Indexes in sql server 2005
I need to create a weekly database plan for my company's databases (5.8Gb), now I want to rebuild the Indexes, and then want to trigger a Shrink operation. Now I know Shrink creates Index ...
1
vote
1answer
120 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 ...
4
votes
1answer
2k 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 ...
0
votes
2answers
207 views
Can you set up a SQL server maintenance plan to restore a database?
I am looking to setup a maintenance plan that makes a backup of a database (Database1) and then restores to another database (Database2) to confirm that it is able to restore successfully.
Can I do ...
6
votes
1answer
230 views
Can I “Execute T-SQL Statement Task” against all user databases?
I'm setting up a maintenance plan on Sql Server.
I want to add an Execute T-SQL Statement Task to the plan, however I do not see any way to set this task to run in every [user|system|all] database as ...
0
votes
0answers
193 views
Unable to view “Maintenance Plan” folder under Maintenance in SQL Server 2008 R2 Standard Edition
SQL Server version is as follows :
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
Copyright (c) Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service ...
2
votes
1answer
117 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 ...
2
votes
1answer
688 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 ...
4
votes
2answers
399 views
Should we need to use Barriers on a production database (MySQL/InnoDB)?
Even though we are using a journaling filesystem (EXT3) with barrier enabled, is this still safer and recommended?
e.g
mount -o barrier=1 /dev/sda /mntpnt
Reference:
...
2
votes
2answers
335 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 ...
3
votes
1answer
359 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 ...
1
vote
4answers
316 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
1answer
162 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 ...
12
votes
4answers
6k 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 ...
2
votes
2answers
119 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 ...
1
vote
1answer
111 views
How to know there is a maintenance job running on a postgresql database
I have a python script on postgresql database which is fetching records from sql server database and inserting it to postgresql DB after some filtering. this jobs is running by cron every two minutes. ...
1
vote
2answers
323 views
Backup database and clean old backup question
Right now we have a maintenance plan which looks like this:
backup all databases
clean old backups (> 24 hours)
This job runs every day.
Every now and then this script crashes during backup step ...
1
vote
1answer
64 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
295 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
...
0
votes
0answers
289 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
69 views
Where and with what should I be looking for a good hardware backup tool?
Until now, I was doing my SQL Backups using USB Hard disk, and copying that to my local computer. After that, I went zipping and burning onto DVDs.
Now I want to push that on more professional and ...
7
votes
2answers
3k 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
3answers
2k views
SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc
I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup ...
3
votes
2answers
753 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.
...
