In the context of a database, maintenance describes routine operational tasks around a database system, such as monitoring, tuning and backup procedures.
15
votes
4answers
7k 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 ...
12
votes
4answers
4k 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?
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 ...
11
votes
4answers
2k 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 ...
10
votes
2answers
3k views
What does a best-practice SQL Server maintenance plan look like?
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 ...
9
votes
5answers
409 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 ...
9
votes
3answers
20k 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 ...
8
votes
2answers
420 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? ...
8
votes
6answers
927 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 ...
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
...
7
votes
2answers
3k 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
3answers
233 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 ...
7
votes
1answer
4k 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 ...
7
votes
2answers
178 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 ...
6
votes
6answers
1k 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.
...
6
votes
2answers
831 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
1answer
190 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 ...
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 ...
5
votes
4answers
784 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 ...
5
votes
4answers
2k 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 ...
5
votes
2answers
113 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 ...
5
votes
3answers
165 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 ...
5
votes
1answer
213 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 ...
5
votes
1answer
134 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 ...
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 ...
4
votes
3answers
530 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 ...
4
votes
2answers
113 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: ...
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 ...
4
votes
2answers
409 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:
...
4
votes
1answer
85 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 ...
3
votes
3answers
54 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 ...
3
votes
1answer
647 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 ...
3
votes
2answers
2k 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
2answers
768 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
726 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?
3
votes
1answer
361 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
1answer
440 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
1answer
84 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 ...
2
votes
2answers
74 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
296 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
4answers
167 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
3answers
684 views
SQL Server Index Fragmentation
When should you rebuild an index? vs reorganize?
What index fragmentation value is considered healthy?
2
votes
1answer
1k 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
115 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 ...
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 ...
2
votes
1answer
163 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 ...
2
votes
1answer
713 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
337 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
2answers
523 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
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 ...
