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.
261
questions
0
votes
1answer
20 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 ...
0
votes
3answers
49 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 ...
2
votes
1answer
74 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 ...
0
votes
3answers
45 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. ...
0
votes
0answers
27 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;...
0
votes
1answer
29 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 ...
4
votes
2answers
55 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 ...
0
votes
1answer
23 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 ...
1
vote
2answers
143 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 (...
0
votes
1answer
52 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 ...
0
votes
1answer
102 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 ...
1
vote
1answer
45 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 ...
1
vote
0answers
27 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.
...
0
votes
1answer
27 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 ...
-2
votes
3answers
188 views
How to maintain a Database with stored procedure that runs forever?
I have an interesting question and I cannot find a reply.
I would like to create a database of prime numbers. On this page Prime numbers in a given range , I can find various examples of SQL code ...
-3
votes
3answers
154 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 ...
0
votes
1answer
21 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 ...
1
vote
1answer
59 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 ...
1
vote
1answer
207 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
...
0
votes
1answer
699 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 ...
6
votes
2answers
1k 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 ...
1
vote
1answer
687 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 ...
1
vote
1answer
457 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\...
0
votes
2answers
441 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 ...
0
votes
2answers
212 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 ...
1
vote
1answer
358 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?
0
votes
2answers
321 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 ...
0
votes
2answers
103 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 ...
0
votes
2answers
715 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 ...
2
votes
2answers
899 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 ...
1
vote
0answers
256 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 ...
1
vote
3answers
2k 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 ...
1
vote
1answer
1k 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 ...
1
vote
1answer
67 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 ...
2
votes
1answer
39 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 ...
0
votes
3answers
293 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 ...
2
votes
2answers
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 ...
1
vote
1answer
98 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 ...
0
votes
1answer
74 views
SQL Server - Non-reindexed Database [closed]
I have a database that has not been reindexed in the last 5 years.
I want a yes or no answer if databases without index maintenance and "Missing Rows" are related to each other.
1
vote
2answers
254 views
Schedule Maintenance Plan - Request For a Good Sequence
I have installed Ola Hallengren's MaintenanceSolution.sql script.
So what I'll get out-of-the-box are a lot of jobs:
My questions is:
What schedules should I implement based on the jobs that ...
1
vote
1answer
559 views
ola-hallengren index rebuild with multi thread and multiple days
Ola scripts for index maintenance work really well for smaller databases. For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the ...
1
vote
1answer
33 views
Log table that grows like flash [closed]
I have my own app that get low traffic (like 100 ppl per day).
As part of my research I am 'logging' all the queries done to by the server in a table in the database. An example, if user enters home ...
6
votes
2answers
4k views
Rebuild Index not freeing up space
I had a situation yesterday where my indexes were rebuilt and database size doubled in size (50 % of the new size was unused). Sort in tempdb is set to off and I get the impression that this rebuild ...
1
vote
2answers
684 views
T-SQL script to check if index rebuild operations are online are off
I wanted to see if it was possible to determine whether my indexes are rebuilt online or offline with some T-SQL. I'm currently trying to troubleshoot extra disk space used during a rebuild procedure ...
1
vote
0answers
277 views
Postgres VACUUM , REINDEX, ANALYSE
How do I know if I need to run any of VACUUM, REINDEX, ANALYSE? Because I don't want to run this every day even if it is not needed.
For example, is it a good option to run all of above after 100 ...
5
votes
1answer
302 views
Dump the buffer pool to disk so it can be loaded after a restart
Is there a way to preserve the buffer pool cache so that is can be loaded in after a restart? My research suggests that other DB servers can do this but SQL Server is not one of them. I'm looking for ...
-1
votes
1answer
122 views
C drive full sql error log huge [closed]
I read a recommendation for setting up a job to run EXEC sp_cycle_errorlog to recycle the SQL Server error log, so that it stops getting so large.
Do I need to set up this job for all of the ...
0
votes
1answer
825 views
Create a maintenance plan for sql database with C#
We are planning to create c# jobs that perform some database maintenance actions such as backup, shrink files etc...
I have read in many blogs that database shrink is not recommended and thats why I'...
7
votes
3answers
4k views
(Ola Hallengren) Deleting log backups older than last FULL backup
When I deploy Ola Hallengren's backup script into my servers, I always set the @CleanupTime parameter for log backups to zero. This way, every time the log backup job runs, it checks for log backup ...
1
vote
0answers
389 views
HowTo: [Output File Cleanup] workaround for UNC targets
Hallengren job [Output File Cleanup] silently fails when the output file directory is a network file share.
Question: Is there a better, cleaner way to resolve the issue detailed below when the job ...
