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
4answers
1k 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 ...
8
votes
2answers
4k 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
1answer
49 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), ...
15
votes
3answers
39k 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
1answer
30 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 ...
0
votes
2answers
382 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 ...
1
vote
3answers
7k views

No Maintenance Plans Folder in SQL Studio 2008 R2

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 ...
12
votes
5answers
6k 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
5k 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
95 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
67 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
260 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
2k 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
2k 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
375 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
27 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. ...
10
votes
3answers
4k 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
253 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 ...
0
votes
0answers
48 views

Rebuilding indexes and optimizing databases on a directory full of `*.dbf` and `*.cdx` files?

There is a program at work that runs on a directory full of *.dbf (FoxPro "tables") and *.cdx (FoxPro "indexes") files. The program itself has two menu items that we run every day to maintain the ...
2
votes
0answers
80 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
1k 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
54 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
176 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
66 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
200 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
249 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
173 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
397 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
589 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
142 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
143 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
3k 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. ...
4
votes
3answers
1k 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: ...
1
vote
2answers
165 views

Eliminating duplicate records in data cleansing

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 ...
1
vote
0answers
114 views

How can I generate a usage log? [closed]

I'm trying to automatically generate an events log report in SQL Server that records events including successful login, Create/Delete Account, An Account was Unlocked, Account locked due to maximum ...
0
votes
1answer
91 views

Renaming a production database

We have a production database that we have identified needs some maintenance as a colleague leaves - some column deletion and renaming, and a couple of table renames We're trying to find the best ...
17
votes
4answers
9k 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?
2
votes
1answer
329 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. ...
2
votes
1answer
2k 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 ...
5
votes
2answers
141 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 ...
1
vote
1answer
835 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 ...
3
votes
1answer
131 views

Forwarded Records, effective way to fix them

I'm dealing with an issue in SQL Server databases of an application. This application is used for Healthcare and I only maintain the databases, I do not create the application. Clients are facing the ...
4
votes
1answer
251 views

Reindexing plan [closed]

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 ...
5
votes
1answer
471 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 ...
2
votes
1answer
118 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 ...
3
votes
1answer
2k 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 ...
5
votes
6answers
734 views

Find procedures that haven't been called in <n> days

We are deleting old stored procedures and tables. How can I know what procedures haven't been called recently? dm_exec_procedure_stats and dm_exec_query_stats aren't reliable, since they only return ...
11
votes
2answers
12k 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 ...
2
votes
3answers
569 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
1answer
939 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 ...