http://Bj83.de

Sql Usefull Queries & Settings

MSSQL Queries for running proccesses and connections:

/*get all connections to a specific db*/
SELECT
   spid, kpid, waittime, lastwaittype, waitresource, dbid, db_name(dbid) as db, hostname, 
   program_name, cmd, net_address, uid, login_time, last_batch, status
FROM
   sys.sysprocesses
WHERE
   dbid > 0 AND db_name(dbid) LIKE '%db name%';

/*get all connections from a specific host/ip*/
SELECT
   spid, kpid, waittime, lastwaittype, waitresource, dbid, db_name(dbid) as db, hostname, 
   program_name, cmd, net_address, uid, login_time, last_batch, status
FROM
   sys.sysprocesses
WHERE
   dbid > 0 and hostname LIKE'%147%';

/*count all connections*/
SELECT COUNT(*) from sys.sysprocesses;

/*show max conection value*/
SELECT @@MAX_CONNECTIONS AS 'Max Connections';

MYSQL Connection Details:

show variables like 'max_connections';  
+-----------------+-------+ 
| Variable_name   | Value | 
+-----------------+-------+ 
| max_connections | 200   | 
+-----------------+-------+

/*actuall values*/
SHOW STATUS;

/*set new max connections on runtime*/
set GLOBAL max_connections=500;

/*for taking even after restart edit /etc/mysql/my.cnf in section [mysqld]*/
set-variable = max connections=500

/*check setting with*/
show variables like 'max_connections';

/*flush old unused connections*/
flush hosts

/*reset counters*/
FLUSH USER_RESOURCES
/*or*/
FLUSH PRIVILEGES
/*on commandline with "mysqladmin reload" */

Leave a Reply

Your email address will not be published. Required fields are marked *

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: