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.
0
votes
2answers
13 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
25 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
47 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
104 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
44 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
1answer
130 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 ...
1
vote
2answers
217 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
97 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 ...
0
votes
3answers
146 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
719 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 ...
2
votes
1answer
41 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
37 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
135 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
407 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
68 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
73 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
160 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 ...
0
votes
1answer
183 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 ...
4
votes
2answers
1k 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
172 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
168 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
225 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
84 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
603 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'...
6
votes
3answers
3k 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
207 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 ...
0
votes
2answers
603 views
Oracle autotask results of space advisor
When querying the dba_autotask_client view on a Oracle 12c database, I can see that the auto space advisor is enabled. Oracle docs says the findings are now implemented automatically. How can I view ...
4
votes
1answer
327 views
transaction log growing out of control during maintenance
I have been asked to clean up preceding and appending spaces in several columns of a table. the table is an import from flat file and has many mistyped rows.
The table has over 150 million rows.
...
0
votes
1answer
627 views
What can cause DBCC CheckDB to take longer than usual? [closed]
I have a maintenance task, which do a DBCC CHECKDB WITH PHYSICAL_ONLY on the Databases on my server (SQL Server 2016 Standard Edition).
This usually takes about 7h30 for about 120 databases and it is ...
8
votes
1answer
597 views
Does RE-BUILDing a HEAP cause downtime?
This is a bit of an embarrassing question, and I can't believe I've missed this for so many years.
I have a vendor third-party database that has 401 heap tables. I've recently got into using Brent ...
1
vote
1answer
729 views
Time taken by VACUUM FULL to reclaim space
After receiving some insightful guidance in a previous post here, I'm going to run VACUUM FULL on 4 PostgreSQL 9.3.10 tables. Table sizes are:
1) links_publicreply: ~30M rows, 9 columns, 3 indexes (...
4
votes
2answers
515 views
Is there any reason to stop transaction log backups during a maintenance window?
We have recently had an issue where the transaction log grew and maxed out the storage space. This happened during a maintenance window (Ola Hallengren index Optimize scripts are being used.) For ...
2
votes
3answers
853 views
Maintenance plan don't stop & don't backup all databases [closed]
I have a server with 100 databases,
I have a maintenance plan (MP) for backups.
I noticed that the maintenance plan don't backup all databases when I'm looking at the backup folder destination I can ...
1
vote
1answer
2k views
Can not drop a PostgreSQL database using dropdb?
I can not drop a database using
dropdb
I get this error:
ERROR: must be owner of database
but with psql \l I see user admin (the one dropping the db) with
admin=CTc/postgres
besides the ...
10
votes
3answers
1k views
Detaching a database permanently
If a database is detached from an instance permanently, are there any cleanup tasks that should be done?
3
votes
2answers
6k views
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 ...
1
vote
2answers
417 views
AWS RDS Post Optimisation after a huge delete
I'm using AWS RDS MySQL. All the tables are in InnoDB and enable innoDB_file_per_table.
One of my logging tables is growing too fast. I'd like to remove older than 1-week data. Even 1 week of data ...
-4
votes
2answers
62 views
Scared of ShrinkDB so will this alternative be OK?
I've read too many horror stories to even touch any T-SQL command with SHRINK in it, but I need to sort out a number of my SQL Server 2008 databases that have bloat.
My databases consist of a simple ...
2
votes
2answers
44 views
TSQL - SQL Server 2008 Maintenance Plan - anything else?
I have a number of SQL 2008 R2 DBs that haven't had any maintenance plans run on them since birth, as my ISP's server provides a SQL version that doesn't have the Maintenance Plan option.
I've ...
0
votes
1answer
44 views
Advice on deleting all rows and reclaiming space of very large table
I'm not a dba...let's start with that :) I'm an app developer. But our database has an archive table which is 252 million rows and 170GB, and I've been tasked to fix it (actually, there are multiple ...
0
votes
1answer
582 views
Locks caused by async stats update
SQL Server has an option for statistics to autoupdate asynchronously (AUTO_UPDATE_STATISTICS_ASYNC).
But, even if this option is ON, from time to time I see locks like this on pic below: say, spid ...
0
votes
1answer
30 views
ORA--07445 error during online Re-org process in 10.2.0.4 database
I was doing re-org of fragmented tables in an Oracle 10.2.0.4 production database:
sys@CLARITY> ALTER TABLE SOP_QUEUE ENABLE ROW MOVEMENT;
Table altered
sys@CLARITY> ALTER TABLE SOP_QUEUE ...
2
votes
2answers
539 views
Ola backups happening on both Primary and Secondary AG
I configured Ola's scripts on a staging AG configuration to test out the logic. When I checked the backup folder today after a week, I see that the backups are happening both on the primary replica ...
0
votes
1answer
671 views
Maintenance job failed saying update is not necessary [closed]
We have scheduled Rebuild index and update stats job weekly manner. However this job was failed with below error message.
Could you please help me to resolve this issue.
Date 04/26/2017 07:00:00 Log ...
7
votes
4answers
2k views
Why does it take so long to view logs on a server?
I am just trying to look up more verbose information for a maintenance plan task that failed. I open up the log file viewer and check the box to view the logs for my maintenance plan. There are no ...
1
vote
0answers
420 views
Modifying Ola Hallengrens index maintenance script for deadlocks
In a SQL Server 2012 Enterprise database without a true maintenance window, I would like to run Ola Hallengrens script to optimize indices.
Testing has shown that while indices are reorganized and ...
6
votes
2answers
2k views
Postgresql 9.6 best settings for large admin work (min_wal and max_wal)
We have a server that's disconnected from the world. It's a high end system with 48GB memory and 500GB SSD hard disk, 16 core CPUs. We're trying to do a pg_restore of a simple database dump of less ...
2
votes
1answer
2k views
Maintenance Plan Using Powershell
At the moment I have around 125 production instances, each with a script-based maintenance plan running as an agent job.
The tasks run are Index Reorg/Rebuild, Stats updates and Checkdb. Backups are ...
3
votes
2answers
3k views
SQL Server Agent Invokes Failed 'sa' Login
I recently upgraded to SQL Server 2016. I was looking through the SQL Server logs and noticed the following error.
Login failed for user 'sa'. Reason: Password did not match that for the login ...
