Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [maintenance]

In the context of a database, maintenance describes routine operational tasks around a database system, such as monitoring, tuning and backup procedures.

When used to describe maintenance tasks on a database management system, the word refers to ongoing operational tasks associated with running a database server. This is quite distinct from the term when used in context of software development, which refers to programming work done to make changes or fix issues on a system after its initial build.

273 questions
191
votes
6 answers

Force drop db while others may be connected

I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB. How can I implement it? I'm using dropdb currently, but…
Alex
  • 2,095
  • 2
  • 15
  • 7
51
votes
5 answers

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 comes to figuring out which other tasks I want…
Josh
  • 673
  • 1
  • 6
  • 7
47
votes
2 answers

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 backups and Maintenance Plan files Of the 23…
Onion-Knight
  • 1,079
  • 2
  • 10
  • 15
46
votes
5 answers

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 delete 50 GB of data -- not on one table, but a…
bumble_bee_tuna
  • 937
  • 2
  • 11
  • 19
37
votes
1 answer

Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command

I am looking for practical guidance for setting the values for the BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE of the BACKUP command. I have done a bit of research (see below), I have done a bit of testing, and I am fully aware that any truly…
Solomon Rutzky
  • 65,256
  • 6
  • 135
  • 267
30
votes
1 answer

VACUUM returning disk space to operating system

VACUUM usually does not return disk space to operating system, except in some special cases. From the docs: The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will…
Vadim Samokhin
  • 535
  • 1
  • 6
  • 10
26
votes
4 answers

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 will Expire:" options while creating Maintenance…
Vijred
  • 417
  • 1
  • 4
  • 8
22
votes
5 answers

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 query plans have to be recompiled, but even…
MAK
20
votes
1 answer

Why not rebuild indexes with page count <1000?

I use Ola Hallengrens script for Index maintenance. Before I did that, I used the following query to see which indexes are fragmented most: SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as…
user1261104
  • 395
  • 2
  • 3
  • 7
19
votes
4 answers

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 Express Edition lacks SQL Agent. What approach would…
rem
  • 1,505
  • 3
  • 20
  • 22
19
votes
3 answers

Is there a reason to update statistics manually?

In SQL Server, statistics are updated automatically when Auto Update Statistics in True (which is the default). Is there a reason to update statistics manually and in what circumstances?
jrara
  • 5,153
  • 19
  • 52
  • 65
18
votes
2 answers

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 table resides in its own tablespace which I created…
Alexander Tobias Bockstaller
  • 395
  • 1
  • 3
  • 11
18
votes
4 answers

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 is autovacuum turned on. Should I vacuum/analyze…
kissgyorgy
  • 305
  • 1
  • 3
  • 8
17
votes
1 answer

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?
NotMe
  • 867
  • 3
  • 10
  • 19
17
votes
4 answers

How to changes or update local server connection in Maintenance plan job

Two days back our client changed one of our Dev Server name After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch. We are using sql server 2012 version and server 2008 OS So today morning i renamed my…
sairam
  • 271
  • 2
  • 4
  • 12
1
2 3
…
18 19