22
Brent listed some invalid reasons for stopping the service, but there are valid reasons too:
Restart required by a service pack or other update
Certain configuration changes (e.g. service account change, hardware changes, instant file initialization, this list could go on for weeks)
In a cluster, a restart to force a failover or applying a rolling patch
...
19
Because they think there's a memory problem - SQL Server uses all of the memory available to it, up to its max memory setting (and even beyond.) Unknowing folks go into Task Manager, see SQL Server using a lot of memory, and think, "There must be a memory leak - I'll stop and restart SQL Server, and see what happens." Sure enough, that frees up a lot of ...
6
Yes, it's possible.
SQL> alter database close;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database dismount;
Database altered.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
6
I came across similar problem and tried this Stack Overflow answer by Fabrizio Valencia. It worked.
Stop there! Please do not delete anything! First try to do what I did.
OK, none of the listed answers here solved my problem, even, some of
the answers were making me lost all my db data. So I did exactly what
the error message in XAMPP message panel told me ...
6
This is all detailed exhaustively on this page.
Start, Stop, Pause, Resume, Restart SQL Server Services
Being that your question specifically asks "are there any recommended by Microsoft" I'm inclined to think that this is counter-productive to have this discussion here. The article their details the process through
Using either
command line
Powershell,
...
5
The documentation tells you to send a signal to the master postgres process, or to let pg_ctl do this.
In SQL, you can extract the PID of the master process from pg_read_file('postmaster.pid'), but pg_cancel_backend() does not accept this PID.
However, you should be able to execute these commands with COPY (depending on what rights the postgres OS user has)...
4
Without seeing any additional information, here is my best guess why MySQL is trying to shutdown for a long time: I suspect mysqld no longer has a socket file.
About 1.5 years ago, I answered mysql restart issue after move database. I learned that mysqld depends on the presence of a socket file. If there is no socket, mysqld just draws lots of dots on the ...
3
SHUTDOWN
MySQL 5.7 now features the SHUTDOWN command, which requiresthe SHUTDOWN privilege.
SHUTDOWN METHOD #1: Within mysql client
mysql> SHUTDOWN;
SHUTDOWN METHOD #2: From OS using mysql client
MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -P3306 --protocol=tcp"
mysql ${...
3
You can set the service property to restart it (service properties --> recovery --> first / second / third failure and have a delay between each restarts e.g. 1 min). You should also, have the service set to automatic delayed start.
Alternatively, you can use powershell and schedule it in task manager to check if the service is running or not. If not ...
3
It helps to understand what 'mount mode' means:
Before DB is mounted, instance is up (SGA and other memory structures are allocated but DB and its files etc have not been verified yet. In mount mode, the controlfile will be read, it will make sure all logfiles/datafiles etc are available and ready to go. This is essential maintenance. Then, after mounting, ...
3
If you want to shutdown MySQL effectively, you need to run several things
@echo off
rem
rem Flush Everything and its Grandmother
rem
echo "Stopping MySQL Service"
"C:\host\MariaDB\bin\mysql.exe" -u root -prootpass -ANe"SET GLOBAL innodb_fast_shutdown=0; STOP SLAVE; FLUSH BINARY LOGS; FLUSH TABLES"
"C:\host\MariaDB\bin\...
3
It seems like the obvious answer is that you can't stop a shutdown that is happening, but you can do either a shutdown immediate or shutdown abort from a different session if you did a shutdown normal. As long as you have enough online redo log groups and each group has a log that is large enough, then you can recover from either a shutdown immediate or ...
3
Ideally you can shutdown using
/etc/init.d/mysqld stop
I cannot add comments so requesting to post what do you see in error log when you do
mysqladmin shutdown
About kill commands please note that mysqld_safe is wrapper script which will start mysqld upon killing... so you should kill mysqld_safe first followed by mysqld. ( not a suggested way though)....
2
If you would like to manually control this, here are some suggestions
Login to Window Command Line Shell as Administrator and run this
C:\> net stop mysql
When the prompt comes back, MySQL is down.
If you shutdown Windows, the only evidence for MySQL's shutdown would be in the error log.
EXAMPLE
On my laptop
I have MySQL 5.5.37 for Windows
It's been up ...
2
I'm pretty sure the recovery process is done on each database everytime the instance start. (and in other scenario as well, like an AG failover for example)
If it was stopped in a clean way, there will be no open transaction to roll foward or roll back so the recovery will be done faster.
1
Priviet Юрий
You question puzzled me a bit first. The answer to your question was too easy and this is dangerous...
About time - was, is and will be
How can I find out if the database "was" shutdown slowly? This is quite easy: Look into your MariaDB/MySQL Error Log and there you will find a log sequence similar to the following:
2020-03-30T08:03:36....
1
As mentioned by Dan Guzman if you are using the T-SQL SHUTDOWN command to shut down SQL Server, you see see the details with username in two places:
SQL Server Error Log
Date 3/15/2020 7:44:16 PM Log SQL Server (Archive #1 - 3/15/2020
7:44:00 PM)
Source spid67
Message Server shut down by NOWAIT request from login
...
1
I don't know of a way to do this automatically. You could start a smart shutdown, and then loop through live connections terminating them if they are idle. This would be a bit fragile, as if your grim reaper script were to lose its connection it could not re-establish it. A less fragile way would be to edit pg_hba.conf to block all connections except from ...
1
STARTUP TIME
For everyone running MySQL 5.7
SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM performance_schema.global_status
WHERE variable_name='Uptime';
For anyone running MySQL 5.6 and prior, or MySQL 5.7 in MySQL 5.6 compatibility mode
SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM information_schema.global_status
...
1
Just to add one more possible reason: in my case it was the unattended-upgrades (on Ubuntu) who did the restart of mysql.
1
One reason could be is that you bought new hardware and migrated the databases to this new server. You now are shutting down this sql server instance on the old box (together with the box itself) since you want to make sure nobody connects to it anymore
You moved to the cloud, the on prem box is not needed anymore, it is shut down, reformatted and ...
1
A valid reason is when there is other software running on the same server that needs some of the memory SQL server has, but that only runs a few times a month.
For example my wife (an accountant who wishes to know as little (and no less) about SQL server that is needed to do her job) has a SQL server based system used by 3 people including her to process ...
1
this is surely a resources or limits problem.
For the first check if you have RemoveIPC enabled (Man for login.conf
If it does not solve, review the Database Quick Installation Guide in the sections Configuring Kernel Parameters and Resource Limits and Creating Required Operating System Groups and Users
Regards
Giova
1
You may not be aware of this, but the MySQL service is designed to be restarted automatically.
There is a program called mysqld_safe. Its job is to do the following:
STEP 01 : start mysqld
STEP 02 : check return value of mysqld
STEP 03 : If return value is zero, mysqld_safe terminates normally
STEP 04 : mysqld is restarted due to abnormal termination of ...
1
Try stopping the mysql server, instead of killing the process:
sudo /etc/init.d/mysql stop
1
MySQL can run as a service and I hope that windows is stopping the services gracefully or you are likely to have more problems outside of MySQL. If you want to be careful, stop the service by hand. This should allow MySQL to flush all buffers before stopping. I would guess from your comment that you're getting corruption that you are using the MyISAM engine; ...
1
One perfect example why root access even for admins is bad. Maintaining a sudoers file and a centralized database of privileged users and groups(LDAP for example) would help solving problems like this.
One note: the wrongdoer might well be a cronjob. I'd check that.
1
For security concern who executed what queries at what time etc.. you should use "Audit Plugins".
You can refer my existing answer here https://dba.stackexchange.com/a/62477/6037
Ofcourse you can find "mysqladmin" using shell histories or tracing "General/Error log" of MySQL server.
For example if you have General log enabled either in a "Table" or "File"
...
1
Check the secure log in the event it was run via a sudo service call
sudo grep mysql /var/log/secure
Check to see if there's any mysql related stuff going on in cronjobs
sudo grep mysql /var/log/cron
Check shell histories for mysqladmin calls
cd /home; for u in *: do; sudo grep mysql /home/$u/.bash_history; done
Check with people you know that either ...
1
A shutdown is already in progress. You can either
Wait
Login using su - oracle and then sqlplus / as sysdba and issue the command shutdown abort if that's what you really want.
Only top voted, non community-wiki answers of a minimum length are eligible