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.

279 questions
218
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,375
  • 2
  • 16
  • 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,089
  • 2
  • 10
  • 15
47
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
  • 947
  • 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
  • 67,276
  • 7
  • 144
  • 280
35
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
  • 595
  • 1
  • 6
  • 12
29
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
  • 447
  • 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
21
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
  • 405
  • 2
  • 4
  • 7
21
votes
1 answer

Does cancelling an (AUTO)VACUUM process in PostgreSQL make all the work done useless?

In some occasions, and after making a massive update, insert or delete from a table, I have started a VACUUM FULL ANALYZE to make sure the DB was not getting too bloated. Doing it in a production database has let me discover that this was not a good…
joanolo
  • 12,949
  • 7
  • 35
  • 65
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,525
  • 3
  • 20
  • 22
19
votes
2 answers

Make Postgres database temporarily read-only (for performing volume snapshots)

The PostgreSQL built-in backup mechanism isn't always very suitable. Sometimes, you want to put the application in a quiescent state, because it has external data with which you want to backup at the same time you back up the PG data. But the only…
Otheus
  • 554
  • 1
  • 3
  • 13
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,253
  • 19
  • 53
  • 65
18
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
  • 281
  • 3
  • 4
  • 12
18
votes
2 answers

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 one of my databases, it does what I want it to do…
Josh Waclawski
  • 313
  • 1
  • 2
  • 5
1
2 3
…
18 19