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
5 answers
601 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 ...
user avatar
  • 2,130
0 votes
0 answers
16 views

Maintenance Postgres cloudsql + cloudsqladmin role

A scheduled maintenance was carried out which lasted just a few minutes, the weird thing is after that, the cloudsqladmin user increased his transactions/sec considerably, it's been like this for a ...
user avatar
2 votes
1 answer
44 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 ...
user avatar
  • 143
0 votes
1 answer
270 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" ...
user avatar
  • 455
0 votes
2 answers
77 views

How to reduce database size in SQL server?

I have a scenario that there is a Backup DB where we normally store BackUps of tables from production DB before doing any updates/deletes so that if anything goes wrong, we can restore the data from ...
user avatar
  • 1
0 votes
1 answer
450 views

Ola Hallengren maintenance solution

When I am executing the below query: EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @...
user avatar
  • 41
1 vote
1 answer
47 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....
user avatar
0 votes
1 answer
122 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 ...
user avatar
0 votes
0 answers
26 views

Are these files safe to delete?

Over the weekend my postgres 12 database ran out of space while I was trying to "vaccum full" a large table. Someone, not a postgresql expert, tried to "help out" by deleting some ...
user avatar
0 votes
1 answer
58 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 ...
user avatar
  • 1,007
0 votes
2 answers
24 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 ...
user avatar
  • 1
0 votes
0 answers
445 views

SQL Analyze - dbms_stats consuming much CPU on Oracle Server

In one bigger DB with many user schemas and big tables I have the devs starting regulary the analyze select dbms_stats queries, that consume a lot of cpu when started for many users at once. These ...
user avatar
0 votes
1 answer
166 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 ...
user avatar
0 votes
3 answers
72 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 ...
user avatar
  • 1,007
2 votes
1 answer
88 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 ...
user avatar
  • 121
0 votes
3 answers
68 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. ...
user avatar
0 votes
0 answers
254 views

I need a script to cycle all tables in postgres to analyze if needed

I need a script to cycle all tables in postgres to analyze if needed. Something like: create or replace PROCEDURE analyze_all_tables () language plpgsql as $$ declare q TEXT; r RECORD;...
user avatar
0 votes
1 answer
48 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 ...
user avatar
4 votes
2 answers
357 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 ...
user avatar
  • 41
0 votes
1 answer
31 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 ...
user avatar
  • 173
1 vote
2 answers
452 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 (...
user avatar
  • 111
0 votes
1 answer
67 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 ...
user avatar
  • 1,051
0 votes
1 answer
207 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 ...
user avatar
1 vote
1 answer
56 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 ...
user avatar
  • 24.1k
1 vote
0 answers
31 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. ...
user avatar
  • 24.1k
0 votes
1 answer
35 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 ...
user avatar
  • 1,285
-1 votes
3 answers
209 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 ...
user avatar
-3 votes
3 answers
161 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 ...
user avatar
0 votes
1 answer
30 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 ...
user avatar
  • 13
1 vote
1 answer
62 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 ...
user avatar
1 vote
1 answer
498 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 ...
user avatar
  • 91
0 votes
1 answer
1k 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 ...
user avatar
  • 319
6 votes
2 answers
3k 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 ...
user avatar
  • 63
1 vote
1 answer
3k 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 ...
user avatar
1 vote
1 answer
720 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\...
user avatar
0 votes
2 answers
678 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 ...
user avatar
0 votes
2 answers
337 views

Monitoring Maintenance Tasks in AlwaysOn Clusters

I have an environment with multiple AGs on a single server and they encounter a split situation in which some end up on one node and some end up on the other node (in a failover event, perhaps). When ...
user avatar
1 vote
1 answer
849 views

Azure SQL Database and Database Maintenance

If SQL Agent isn't available for Azure SQL Database then is it possible to maintain (indexes, statistics etc) the databases with Ola Hallengren's maintenance scripts, for example?
user avatar
  • 259
0 votes
2 answers
450 views

Database Maintenance Job and Backup Scheduling

I would request to have expert advise on scheduling maintenance and backup jobs. Below was the scenario prior to my change: Full backup of database scheduled to run at 12:30AM everyday. Differential ...
user avatar
0 votes
2 answers
156 views

How full is my innodb shared table space?

How can I find out how much data is in the innodb shared table space. I have a massive shared idb file but I suspect that it contains only about 350 Gigs of data. I need to know how full it is so I ...
user avatar
0 votes
2 answers
1k views

sp_MSforeachdb Continue on failure when running DBCC CHECKDB

I have the following maintenance script DECLARE @sql NVARCHAR(30) = 'DBCC CHECKDB(?)' EXEC sp_MSforeachdb @sql This checks every database for corruption. I have run this on my test server where I ...
user avatar
  • 1,450
2 votes
2 answers
1k views

Trouble with Ola Hallengren's @DatabasesInParallel parameter

I am testing out this new parameter @DatabasesInParallel and it seems to only back up 1 databases at a time but it doesn't specificy this as a limitation in the documentation so I am wondering if I ...
user avatar
  • 87
1 vote
0 answers
314 views

OLA Output File Cleanup - Better Options

Does anyone have a way to change the Ola Hallengren "Output File Cleanup" job that doesn't require CmdExec? My company is trying to eliminate CmdExec where ever possible. I was thinking a PowerShell ...
user avatar
1 vote
3 answers
3k views

VACUUM did not reduce reported size of database?

I have a database with an extremely large table in postgresql. I'm aware that the only way to shrink the on disk size is VACUUM FULL, but I cannot do that, as I don't have anywhere near enough free ...
user avatar
  • 1,216
1 vote
1 answer
2k views

Cannot create maintenance plan, I get the message "The task with the name '' and the creation name is not registered for use on this computer"

I just installed SQL server 2016 (Standard edition) on a new server, I'm trying to add a Maintenance plan (I've tried back up plan, rebuild indexes and many more) I'm getting a weird message when I'm ...
user avatar
  • 211
1 vote
1 answer
73 views

Suggestions for large DB maintenance

i have one large database (more than 1,500 tables and total db size of 56GB) and i would like to ask for tips and guidance regarding the maintenance of such large database! Will the size be a problem ...
user avatar
2 votes
1 answer
52 views

SQL Rebuild indexes data size percent a little smaller [closed]

I see a small change int the percent of data when rebuilding. I feel that this might be a usual issue but as I am new to SQL I get little worried when I see that the percent of data goes down after ...
user avatar
0 votes
3 answers
378 views

Zero Maintenance self-hosted off-line databases?

I'm looking for a "Zero Maintenance" database engine that can be installed on Windows. We have a lot of customers that want our software installed on their local computers. Although we have a cloud ...
user avatar
  • 133
2 votes
2 answers
1k views

Backup Performance best practices

I'm in the process of re-writing our maintenance plans and at the moment the focus is backups. Whilst preparing this plan, I'd like to try and ensure that procedures are in place to ensure that backup ...
user avatar
  • 333
1 vote
1 answer
119 views

Defragmenting indexes SQL Server bases on avg_page_space_used_in_percent

I am wondering if I should take into account information from sys.dm_db_index_physical_stats column avg_page_space_used_in_percent when deciding on defragmenting/not defragmenting an index. How ...
user avatar
  • 2,193

1
2 3 4 5 6