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

1
vote
2answers
1k 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 ...
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
677 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 ...
1
vote
0answers
810 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
82 views

Where and with what should I be looking for a good hardware backup tool? [closed]

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 ...
1
vote
4answers
892 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 ...
7
votes
2answers
2k views

Defragmentation - Rebuilding Indexes SQL Server 2005

I'm researching defragmenting databases and it seems the following SQL statement is what I'm looking for: ALTER INDEX ALL ON mytablename REBUILD WITH(ONLINE = ON) When I pull the info from ...
6
votes
4answers
3k views

How does SQL Server reduce index fragmentation?

If a user never runs REBUILD or REORGANIZE on their database, does SQL Server still somehow defragment the indexes? MSDN suggests that if an index is over 30% fragmented, it is recommended to run ...
10
votes
1answer
8k 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 ...
-1
votes
1answer
202 views

SQL Server reports for management

I have been placed in charged of overseeing our DB and web servers (SQL Server 2005/2008.) Not being a technical person, what reports should I request on a weekly/monthly basis to asses the health of ...
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 ...
11
votes
1answer
5k views

Compact Large Objects in Reorganize Index task

I'm setting up a maintenance plan in SQL Server 2008 R2. On the Reorganize Index Task it has an option for "Compact Large Objects". What does that mean and do I want to turn it on?
5
votes
4answers
5k views

SQL Rebuild Index, Recovery Model, and db log filesize?

I have a database that we currently run transaction log backups throughout the day, every 30 minutes to be exact, and we run a full backup each day at 2am. Every Saturday at 3am, we have a job setup ...
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 ...
15
votes
5answers
13k 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 ...
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 ...
11
votes
5answers
570 views

SQL Server Backups - A couple of questions

We run our weekly backup job at 9pm on Fridays and we are experiencing a couple of issues with regard to disk space (which gets perilously low at times) and performance. We're looking at ...
0
votes
0answers
267 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 ...
12
votes
6answers
5k views

SQL Server Checklists

Following up from my other question, I would like to start thinking about what I should take a look at on daily/weekly/monthly bases in terms of alerts. I am hoping to be able to see problems coming ...
13
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 ...
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?
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 ...
9
votes
3answers
704 views

What are best practices for safely permanently deleting a database?

We have an "organic" environment, meaning people piled code on code for ten years with minimal oversight or documentation. The server I use has several databases which I believe are no longer being ...
5
votes
1answer
2k views

In MS SQL how do I find out what tables have not been queried or updated in a long time?

I'm maintaining a database that is being shared by a few systems. Over the years a few of those systems have been deprecated and lots of the tables are actually orphaned. Is there a query I can run to ...
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 ...
3
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

How to ensure that SQL Jobs run in the correct order within a minimal maintenance window?

From this question I learned about a script which creates jobs for backing up databases, checking database integrity, optimizing indexes, and cleaning files. According to the documentation, this ...
3
votes
3answers
705 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 ...
1
vote
2answers
884 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 ...
7
votes
6answers
2k views

SQL Server 2005 Huge DB issue

My DB files are growing very fast ( designer is not me and just a weekly report makes 7 GB growth ). And finally, I don't have enough space in the disk. And number of un-reported weeks counting up. ...
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 ...
4
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. ...
8
votes
1answer
11k views

Breaking and Restoring a mirror

I have to do some maintenance on a secondary node of a mirror. Since this is the result of the site becoming slow while the mirror re-established after a brief network outage I want to be safe and ...
3
votes
3answers
3k views

SQL Server Index Fragmentation

When should you rebuild an index? vs reorganize? What index fragmentation value is considered healthy?
17
votes
4answers
8k views

Task scheduler for SQL Server Express

I have an ASP.NET MVC app which works with database under SQL Server 2008 R2 Express edition. There is a need to perform a regular task on updating some records in the database. Unfortunately the ...
9
votes
2answers
483 views

SQL 2005: Can we determine how much the rebuild index maintenance job can grow database log files?

On SQL server 2005, if all the databases are in Full mode (with hourly transaction log backups), is it possible to determine if rebuilding all indexes of a database can grow log file of a database? ...
3
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 ...
5
votes
3answers
6k 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 ...