I tried to restart MySQL through WHM and now its taking way too long to restart, the progress bar is still showing. What can I do? I can still access the server via SSH but I cannot connect to MySQL. This is urgent as my online store is now down. Will restarting the HTTP Server (Apache) help?

Much like the vein of the question that was posted here previously around "Should developers be able to query production databases?" I wanted to get your thoughts on another particularly annoying topic! Many companies prevent developers from install SQL Server Express and the like on development machines, instead promoting the use of centralised development SQL Servers. Specifically this is done to ensure: - Patch level consistency between development servers and production - Ability to prove and validate any patches on the above - Data security; only data on the development servers gets used for development - Recoverability; data is recoverable and still backed up - Collation differences that can cause problems when moved to production To me all these arguments are particularly invalid, with perhaps the exception of the patching ones; but if a database on a local machine is purely used for development activities, and not testing, then the patching would be proven out when an application progressed through Test / UAT etc to Production. Collation does not appear to be a valid reason, as if this was a concern for the database it should be set when it's created anyway. Only SharePoint and SCCM have issues around this as far as I know ;) Now, assuming it's ONLY for development, and the database will not be "moved" to production and the only movements would be: - Scripts that created the database being generated for deployment to production - Backups from "production" third party systems being restored and truncated where appropriate for validation and development Can anyone see any issues? Am I missing something? I guess one of the biggest concerns would be the ability for local db instances coming out of date, but thats a software management issue, not a DBA one IMO.

My team and I have an issue where we need to be able to identify processes that are blocking other processes and kill them. There are a ton of scripts that are freely available to perform these actions. We have tried various ones and scrutinized the code. (If you are someone that has posted one of these queries, thanks!) Before I go further, let me tell you that this is against a vendor application that we cannot modify. The vendor is also not willing to spend the time to figure out why processes are getting blocked. The only choice we have at this point is to kill the long running processes (as suggested by the vendor support). Before killing these processes, we do review the query running, but 99.9% of the time, it shows up as `FETCH API_CURSOR00000000000A7E1F`, which tells us nothing. This has been a manual process up to this point. Now, we want to automate the killing of these long running blocking processes instead of someone manually killing them. We want to test this script before putting it into production. We would like some help creating a script that would intentionally create blocking processes. We have tried with the TEST environment with this application, but unfortunately, we have had no luck replicating blocking processes. Thanks in advance for your assistance!

I have a process(es) hungry for tempdb, but I am struggling to identify this process. any ways I could achieve this? We have been alerted as Tempdb has now grown into the space you reclaimed on the T:\ drive earlier. Again, there is 10MB remaining on disk. I can see a number of auto growth events across the data files on the REP instance starting at 10:18AM this morning. In total there were 330 auto growth events at 512MB each, totalling 168GB. It is difficult to highlight after the fact what has used this space in Tempdb, are you aware of any processes that may be using Tempdb in this manner today? /------------------------------------------------------------\ Identifying How Often an Auto-growth Event has Occurred When SQL Server performs an auto-grow event, the transaction that triggered the auto-grow event will have to wait until the auto-grow event completes before the transaction can finish. These auto-growth events cause your performance to degrade a little when an auto-grow event is taking place. For this reason it is best if you can size your database appropriately so auto-growth events rarely occur. If you are interested in how often an auto-growth event occurs on your system you can capture those events using a trace. By knowing which databases are performing auto-growth events allows you to adjust those database file growth properties so they will perform auto-growth events less frequently. You can use the profiler “Data File Auto-grow” and/or the “Log File Auto-grow” events to track these database auto-growth events. If you are running SQL Server 2005 or above, both these auto-grow events are already being captured by the default trace. If you haven’t turned off the default trace then you can use the default trace file to find these auto-grow events. If you have turned off the default trace you can either enable it, or setup a new profiler trace to capture the “Data File Auto-grow” and “Log File Auto-grow” events. The defaul

I have started to use the "Idera SQL check" tool. It shows floating bubbles for each process active on the Sql Server system: Those bubbles are surrounded by a red border in case the transaction is idle. The legend says this is dangerous: Those are some particular sample information related to spid 200: ``` spid : 200 kpid : 0 blocked : 0 waittime : 0 lastwaittype : MISCELLANEOUS waitresource : dbid : 5 database : DBNAME uid : 1 cpu : 0 physical_io : 0 memusage : 3 login_time : 29.02.2016 09:37:17 last_batch : 29.02.2016 09:37:17 ecid : 0 open_tran : 1 status : SERVERNAME program_name : PROGRAMNAME host_process : 5380 cmd : AWAITING COMMAND nt_damain : nt_username : net_address : 99887766 net_library : TCP/IP loginname : sqlusername stmt_start : 0 stmt_end : 0 input_buffer : ``` It's obvious it is dangerous. But I have trouble finding out the reason. Looking at the `sys.sysprocesses` DMV to find out the queries executed in the context of those spids. The queries executed are running very fast and are no candidates for long running. So I don't understand why they are getting idle within their transactions? How to find out the backgrounds? EDIT: The query behind the scenes getting the processes is the following: ``` exec sp_executesql N' SELECT p.spid, p.kpid, p.blocked, CAST(p.waittime AS BIGINT) as waittime, p.lastwaittype, p.waitresource, p.dbid, db.name as [database], p.uid, CAST(p.cpu / 1000 AS BIGINT) as cpu, CAST(p.physical_io AS BIGINT) as physical_io, p.memusage, p.login_time, p.last_batch , p.ecid , CAST(p.open_tran AS BIGINT) as open_tran, p.status, p.hostname, p.program_name, p.hostprocess, p.cmd, p.nt_domain, p.nt_username, p.net_address, p.net_library, p.loginame, p.stmt_start, p.stmt_end FROM master.dbo.sysprocesses p join m

I have a cube of 55 GB size which takes around 2 Hour to process full. As I have data for last 4 years but our business wants only 2 years of data. For that, I have changed dimension and Fact views to have only last two years of data. Now, my cube size is reduced to 32 GB but processing time increased by 30 min (i.e. 2 hours 30 min). I was expecting it to be less as I have restricted good amount of data going into the cube. Why the processing time has increased when it should actually decrease? How can I reduce processing time now? P.S.: I have already tried cube partition and due to large dimension size it is also increasing processing time. I am using views where I have restricted the data via WHERE clause. My view is basically like this: ``` SELECT V.Col1, V.Col2.... V.Col13 FROM DimeTable V WHERE ``` It is selecting almost all columns from the dimension table so I can't create non clustered index on all these columns as it may slow down insert operation and won't help me much

While testing the Oracle XE connection establishing mechanism I bumped into the following issue. Although connections are closed on each iteration, after 50-100 connections Oracle starts throwing intermittently the following exception: ``` java.sql.SQLException: Listener refused the connection with the following error: ORA-12516, TNS:listener could not find available handler with matching protocol stack at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0] at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:553) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0] at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:254) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0] at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0] at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0] at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0] at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:207) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0] at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:157) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0] at com.vladmihalcea.book.high_performance_java_persistence.jdbc.connection.OracleConnectionCallTest.test(OracleConnectionCallTest.java:57) [test-classes/:na] ``` The test can be found on GitHub: ``` for (int i = 0; i < callCount; i++) { try { long startNanos = System.nanoTime(); try (Connection connection = dataSource.getConnection()) { } timer.update(System.nanoTime() - startNanos, TimeUnit.NANOSECONDS); sleep(waitMillis); } catch (SQLException e) { LOGGER.info("Exception on iteration " + i, e); } } ``` If I try to open/close connections with a 35 ms wait step, everything works fine. If I lower the wait to 10 ms the excep

I have a strange situation. Using `sp_whoisactive` I can see this: Ok, with this query, I can see what is triggering ( does this word exists in english? ) it: ``` SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text FROM sys.dm_exec_cursors (SPID) c --0 for all cursors running CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t ``` the result: it's a simple `select`. Why is this using f`etch_cursor`? Also, I see a lot of "blank" sql_texts too. Does this has something with this "cursor"? `DBCC INPUTBUFFER (spid)` shows me this: there's this question Here ( made by me ) but i don't know if this is the same thing. EDIT1: Using the query provided by kin, I see this: EDIT2: Using Activity Monitor, I can see this: It is the most expensive query ( The first one is intentional, we know about it ). And again, I would like to know, why this `select * from...` is the reason of `FETCH CURSOR`... EDIT3: This "`select * from`..." is running from another server ( via `linked server` ). Well, Now i'm having problems to understand what @kin said. This is the `execution plan` of the query ( running in the same server of the database): this is now, the execution plan, running in the other server, via linked server: Ok, Not a problem too. And NOW! the execution plan , via `**activity monitor**` ( the same `select * from` ):

I am writing a trigger that suppose to fire on inserts for a table and log the exact query used to insert. However, using below ``` SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=CONNECTION_ID(); ``` Always return in original_query variable this above query not the one firing the trigger and count in processlist is 1. I am in MySQL 5.6.18. This is the whole trigger deffinition ``` DROP TRIGGER IF EXISTS affiliate_revenue_i_log$$ CREATE TRIGGER affiliate_revenue_i_log BEFORE INSERT ON cds_affiliate_revenues FOR EACH ROW BEGIN DECLARE original_query VARCHAR(1024); SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=CONNECTION_ID(); INSERT INTO cds_affiliate_revenues_log (action,id,added_date,original_query) VALUES('insert',NEW.id,NOW(),original_query); END$$ ```

I should be ashamed of myself, for asking, I know. Check out this screenshot attached. My server, which is indeed not seeing HUGE amounts of traffic, should not be this overloaded. Mysql is having a party and everyones invited. The thing is I don't like these kinds of wild parties. How do I kill this stuff? How do I regulated it so that it doesn't keep happening? Pretty soon my host is going to call the police and the party will be over for everyone.