Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

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.

Filter by
Sorted by
Tagged with
2 votes
1 answer
79 views

When to rerun CLUSTER?

Currently using Postgres 14. Running CLUSTER sometable USING someindex; is great, but is there a query to determine how "unclustered" the table has become after many updates and deletes? ...
RonJohn's user avatar
  • 647
0 votes
0 answers
27 views

Statistics update - worthwhile on a high activity table or not?

I have a table with 1.5 billion records which is sampled at 1%. Said table receives over 200-300k insert/update/deletes per hour. Within a couple of hours, the statistics are auto updated as it ...
DB-Ay's user avatar
  • 11
0 votes
1 answer
25 views

Clarifications on Maintenance jobs & CDC on upgrade to ENTERPRISE EDITION

Raising these on behalf of our DBA team. We are looking at moving to ENTERPRISE SQL SERVER 2022 soon and would need help clarifying a few things: We are looking at a 3-NODE setup: (1) NODE-1: Primary (...
S T's user avatar
  • 1
3 votes
2 answers
197 views

Is automatically renaming indexes when renaming columns relevant for database health/maintenance?

I am currently building out a few features for a project where customers are able to change up their database schema via an UI and we handle all the nitty-gritty details on running the required ...
Firat Oezcan's user avatar
1 vote
2 answers
54 views

Can I get the created time of a PostgreSQL DB?

I'm using PostgreSQL-13.0. Is there a way I can get the created time or last modified time of a database? Thanks!
Leon's user avatar
  • 347
0 votes
1 answer
120 views

Error message when running a SQL maintenance plan backup

I'm receiving the following error message when my daily maintenance backup plans execute: Error Message 1: [298] SQLServer Error: 407, Internal error. The string routine in file e:\sql11_main_t\sql\...
Mike Gomes's user avatar
0 votes
1 answer
285 views

Analyze only one partition in PostgreSQL

I have a metrics table partitioned by year and month (metrics_y2023m01, metrics_y2023m02, etc). The analyze metrics command updates the statistics of the table and each partition individually, but the ...
Rubén Noriega Mier's user avatar
0 votes
1 answer
61 views

Partitioning using column from different table

I have a table ActionValues with the following columns: AccountID Action ActionValue This table is related with a second table called Actions that has the following columns: AccountID Action ...
Alexander's user avatar
0 votes
0 answers
194 views

How to temporarily switch PostgreSQL to logical replication to allow major version upgrade without downtime?

We have a PostgreSQL cluster running on one master (db1) and two async hot-standby servers (db2 and db3) replicating from it. All the servers are working as a backend for a web service and all the ...
Mikko Rantalainen's user avatar
0 votes
1 answer
146 views

During Maintenance in MSSQL SERVER i am getting the error: the step failed

In MSSQL Server, every Saturday a maintenance plan starts, where it rebuilds some indexes. During the last month, it always fails after some hours with the message: The step failed. Executed as user: ...
Vasileios G's user avatar
2 votes
2 answers
2k views

MySQL db optimization error on innodb tables

I have a database named 'mysql' which is being used on RHEL 8 server running MySQL 8.0.32. When trying to optimize the entire database using the mysqlcheck command 'mysqlcheck -o mysql' the below ...
PirateRagnaros's user avatar
-2 votes
2 answers
138 views

SQL Grease : Strategy to maintain Azure SQL database

I am a developer that dabbles a few things across the entire stack. I have never had the opportunity to work to work on a product that uses a monolith Azure SQL database on a Premium tier scaled up to ...
frictionlesspulley's user avatar
-1 votes
1 answer
165 views

sp_BlitzCache detecting thousands of executions of CREATE PROCEDURE dbo.CommandExecute (from Ola Hallengren's Maintenance Solution) [duplicate]

I recently implemented the Maintenance Solution as a nightly job in my company's database, via Windows Scheduler. It works as intended, except for one issue - the procedures are being created ...
dc-ddfe's user avatar
  • 109
5 votes
1 answer
709 views

Rebuild of DB fails, yet size of the DB has doubled

I attempted to rebuild all the indexes of a DB using the query, USE [DB_Name]; GO DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE TableCursor CURSOR FOR SELECT '['+...
theindianvenom's user avatar
0 votes
2 answers
527 views

Postgres: Dropping Database impacts performance

I worked on a DB-server hosting multiple big DBs including DBs that aren't used anymore. I wanted to drop one of those old DBs and it already took longer than I expected(over 40 seconds) then I ...
MrKnista's user avatar
-1 votes
1 answer
112 views

Any possibility that Ola Hallengren could add a job name prefix option to the script that creates all the maintenance jobs? [closed]

When creating the jobs, they sort together for most of them, but some of the jobs are alphabetically far away from the other Ola jobs on servers with hundreds of jobs. I would find it easier to see ...
EricI's user avatar
  • 99
0 votes
1 answer
128 views

SQL Server - Maintenance - How to obtain duration per database from the commandlog table

Attempting to obtain the duration for individual maintenance operations per database in the commandlog table which is available in Ola Hallengren's maintenance scripts If anyone has a better solution ...
Craig Efrein's user avatar
  • 9,640
0 votes
1 answer
337 views

VACUUM FULL using another disk

Current database takes almost all of SSD free space ~802GB. 800 GB of memory is taken by single table and it was never vacuumed since 2019. Recently i deleted old rows from the table and now i need to ...
Tamer Mukayev's user avatar
2 votes
6 answers
3k views

Is there any purpose of Ola Hallengren's SQL Server Index and Statistics Maintenance scripts on sql server running on SAN storage?

I have configured Ola Hallengren's backup and integrity check scripts. However I want advice whether it is required to setup the SQL Server Index and Statistics Maintenance scripts, because my SQL ...
variable's user avatar
  • 3,634
2 votes
1 answer
372 views

Is there a proper order to run mysqlcheck commands in for periodic maintenance?

Is there a proper order in whcih to run the various mysqlcheck commands? e.g. CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE and also FLUSH TABLES and FLUSH LOGS. I wrote this script that I ...
Jeff's user avatar
  • 143
1 vote
1 answer
2k views

AWS RDS "Maintenance Required"

I saw from the web interface that maintenance on my RDS postgresql instances is required, so I ran aws rds describe-pending-maintenance-actions, and it says that there's a "system-update" ...
RonJohn's user avatar
  • 647
0 votes
2 answers
2k views

How to reduce the size of a SQL Server-Database?

Context We have a Backup DB, where we normally store BackUps of tables from production DB before doing any updates/deletes. If anything goes wrong, we can restore the data from that table created in ...
Bishal's user avatar
  • 1
1 vote
1 answer
3k views

Ola Hallengren maintenance solution

When I am executing the below query: EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @...
Hany Helmy's user avatar
0 votes
1 answer
187 views

Oracle gather stats autotask being interrupted before maintenance window ends

a few months ago I managed to enlarge the maintenance windows for autotask on a pluggable database, this way: BEGIN dbms_scheduler.disable( name => 'WINDOW_NAME'); dbms_scheduler....
trustno1's user avatar
0 votes
1 answer
4k views

How to forcefully drop all active connections off a database?

On one server I have a Postgresql database and there're many clients on other servers accross the internet that connect to it. I want to drop all the active connections on demand, they should be ...
Nilcale's user avatar
0 votes
1 answer
271 views

Turning off Secondary Replica in Always on availability group for maintenance

We are planning to turn off the secondary synchronous replica in our always on availability group for few hours for maintenance . Once we are done with the maintenance then we will need to failover ...
SQL_NoExpert's user avatar
  • 1,061
0 votes
2 answers
1k views

When to analyze columns?

I have a table with millions of records. Before select, I run Analyze table (col1, col2). We want to reduce cost, since a lot of IO operations happen in AWS Aurora. Should I run analyze only when we ...
Venkat's user avatar
  • 1
0 votes
1 answer
729 views

Schedule SQL Jobs from Central Management Server

I've set up a Central Management Server on a SQL Server 2016 instance. I'm able to manually run queries against multiple servers, but I want to be able to run automated maintenance scripts. Is there ...
Britt Wescott's user avatar
0 votes
4 answers
210 views

Index Maintenance on busy OLTP database

I have a database where constant read writes happening all the time. Searches are being done with wild card entries. The server is on SQL 2016 standard edition. There is no budget for enterprise nor ...
SQL_NoExpert's user avatar
  • 1,061
2 votes
1 answer
379 views

Limiting total number of records in a table with a nightly job

We have a table that stores audit records (Log in, log out that kind of thing), we already have a nightly job that archives any data that is older than 3 months. However every week or two we get ...
JenniP's user avatar
  • 121
0 votes
3 answers
1k views

How to perform bulk delete and release free space?

Good Afternoon, We have a database that wasn't been an archive anytime. The database has grown tremendously and we are running short of space. Hence there is a need to do a bulk delete and free space. ...
rushabh138's user avatar
0 votes
1 answer
134 views

Which is the best approach to "suspend" the activity of a whole AOG cluster thinking on start it again later?

My company is going to migrate some services from one on-prem site to another but the database servers will remain in the same location (for now). I've been asked for "suspend" all the ...
Roman Peralta's user avatar
5 votes
2 answers
2k views

Auto vacuuming high-write, high-update, and mostly read table types

What are good auto vacuum settings (recommendations) for tables like: High Write Table Insert load Range between 30-10,000 inserts in a day. The table can idle for weeks without load, but can get ...
DDD's user avatar
  • 51
0 votes
2 answers
466 views

Finding tables & columns at risk of hitting max value of int

I just took over a medium-sized postgres database and the team has been surprised with many tables hitting their auto-incrementing id int max limits which triggered unscheduled downtime to fix. Does ...
leonsas's user avatar
  • 213
1 vote
2 answers
1k views

In Postgres, what should be the autovacuum strategy for big tables (hundreds of millions rows) that get added/removed thousands of rows every day?

I have quite a big table with a lot of daily traffic for reads, inserts and deletions. Currently, it has 392 million of live tuples and 27 million of dead ones. Vacuum settings (...
Julen's user avatar
  • 111
0 votes
1 answer
93 views

SQL Server online maintenance - False publicity?

I'm being challenged since we have customers that are seeing different types of errors while running what is supposed to be online maintenance. errors like timeouts, scan stopped due to data movement ...
JohnG's user avatar
  • 1,081
0 votes
1 answer
519 views

DBCC CHECKDB behavior with MAXDOP settings

I am trying to understand the DBCC CHECKDB usage with MAXDOP settings- If i run DBCC CHECKDB with MAXDOP = 0 on a SQL instance where my MAXDOP settings is 8, what is happening actually? Is all of my ...
Newbie-DBA's user avatar
1 vote
1 answer
115 views

Are there differences between which AlwaysOn AG server (Primary or Secondary Replica) I store my backups and retention maintenance plans on?

I have an AlwaysOn AG system in place between two servers. Server A hosts the primary replica, Server B hosts the secondary replica. Are there any differences if my Full & Transaction Log ...
J.D.'s user avatar
  • 37.8k
1 vote
0 answers
63 views

How to select specific databases in a backup retention policy maintenance plan cleanup task?

I have full backups of three different databases. Right now all of their backups follow the same retention policy, but I need to setup the retention to a different amount of time for each database. ...
J.D.'s user avatar
  • 37.8k
0 votes
1 answer
44 views

Does this plan work as a Failover strategy-SQL Server 2016 Standard Edition

We have two SQL Server 2016 Standard edition (standard edition due to budget issues) configured one as primary sql server and another one as fail-over server. I have implemented Transactional ...
user9516827's user avatar
  • 1,315
-1 votes
3 answers
389 views

How to maintain a query that runs forever?

I would like to run a query that finds prime numbers, and I would like to run it forever. @Solomon Rutzky on this page Prime numbers in a given range , give us a good example of the Sieve of ...
Francesco Mantovani's user avatar
-3 votes
3 answers
179 views

Hide business data from maintenance user

I'm newbie in postgres and have a question: we have some clients that have ultra-super-secure-sensitive data on postgres and they don't want to show it anyone. but sometime, the apps experience some ...
Mikhail Aksenov's user avatar
0 votes
1 answer
66 views

Running CheckDB on a clone

My company are looking to bring in Redgate's SQL Clone to deploy live databases into other environments. I wanted to see if running CheckDB on these clones was a viable option instead of restoring a ...
Krishn's user avatar
  • 13
1 vote
1 answer
89 views

Foreign Keys delete data [closed]

I am failing to understand a few concepts about FKs, i have some really big tables (dozens of million rows) with lots of FKs and i want to delete old data, i can disable FKs -- delete Data -- enable ...
xicotaslb's user avatar
1 vote
1 answer
950 views

log to table vs log to output file

I am using ola-hallengren solutions for database maintenance. A question about the log, I see in the documentation website: LogToTable: Log commands to the table dbo.CommandLog. Value Description ...
Anne's user avatar
  • 101
1 vote
1 answer
2k views

Reset all sequences so that they continue after max(id) + 1?

It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like: django.db.utils.IntegrityError: duplicate key value violates ...
d33tah's user avatar
  • 389
7 votes
2 answers
6k views

IndexOptimize - Configuration

We recently switched to Ola Hallengren's maintenance script and automated the deployment of MaintenanceSolution.sql to our customers's SQL Server instance. We need to set these parameters for the job ...
William's user avatar
  • 73
4 votes
1 answer
8k views

Postgres VACUUM taking forever

I have an specific table that is taking forever o VACUUM. There are no locks in the DB and even letting it overnight the VACUUM didn't finish. According to the statistics (ANALYZE works fine) the ...
Balbinator's user avatar
1 vote
1 answer
2k views

SQL Server Backup failing Error :3041

I am running SQL Server 2008R2 Express edition. For all databases full backup, I am using expressmaint.exe. In this, I am using the command -S server name -D ALL -T DB -R D:\SQL_BACKUP\...
HARI VARMA's user avatar
0 votes
2 answers
1k views

What is the behavior of SQL Server Maintenance Plan - Clean Up task for deleting old backups

This seems pretty obvious, but it's remarkably hard to find an actual document that addresses this. The scenario: Full Backup - Weekly (Set to expire after 7 days) Differential Backup - Daily (Set ...
Appleoddity's user avatar

1
2 3 4 5 6