MySQL : Monitor live MySQL queries

You can run the mysql command "show processlist" to see what queries are being processed at any given time, but that probably won't achieve what you're hoping for as this won’t show the whole SQL text.

Here are the steps to dump sql into a file for analysis.

mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+

| Variable_name    | Value                      |

+------------------+----------------------------+

| general_log      | OFF                        |

| general_log_file | /var/run/mysqld/mysqld.log |

+------------------+----------------------------+

Turn on the SQL Logging and go to the general_log_file location.

mysql> SET GLOBAL general_log = 'ON';

Do your queries (on any db). Grep or otherwise examine /var/run/mysqld/mysqld.log

Then don't forget to disable the general_log parameter to OFF.

mysql> SET GLOBAL general_log = 'OFF';

or the performance will plummet and your disk will fill!

MSSQL : Bring database back to normal from Restoring State

If you are restoring a database using multiple backup files, you would use the WITH NORECOVERY option for each restore except the last. If your database is still in the restoring state and you want to recover it without restoring additional backups you can issue a RESTORE DATABASE .. WITH RECOVERY to bring the database online for users to use.

Here is the syntax.

RESTORE DATABASE <DATABASE_NAME>

WITH RECOVERY

SQL Server: Linked Server is not configured for RPC error:

Workaround for calling table-valued function remotely in SQL Server over Linked Server needs some fancy in the coding. Something like this


EXEC SERVER_LINK.DATABASE_NAME..sp_executesql N'SELECT value FROM fn_testexecute(''value1'', value2, value3, value3, value4);'

Now what if the SQL fails due to below error:

Msg 7411, Level 16, State 1, Line 2
Server 'TEST_LINK' is not configured for RPC.

This is a configuration thing with the Linked Server option called "RPC OUT". This is necessary to execute a procedure calls to go out to the linked Server. Where as, "RPC" option allows stored procedure call from the linked Server.

For this case, I had to enable "RPC Out". I ran sp_helpserver and it reported RPC OUT isn’t enabled.

That's it. Problem solved.

MSSQL Server to MSSQL Server Instance connectivity with Linked Server

In order to create LINKED Server the user needs to have SYSADMIN privileges. Here in this demonstration I’m going to show the preferred LINKED SERVER option called “Be made using the login’s current security context”. Condition is, LOGIN needs to stay on both the servers. This is the preferred and recommended method in terms of security as other users can’t use this link except for the Login I’ll be using.


Here are the steps:

1. I’m going to use a user called TEST for this demonstration, and going to provide SYSADMIN privilege to this user. Once the link created and tested, I’ll revoke SYSADMIN privilege from the TEST user.

2. Login as TEST user

3. Open SSMS, Go to Server Objects-->Right Click Linked Servers--> New Linked Server. The below form pops up

4. Give a name for “Linked Server” , choose the provider as shown on form. Product name here I mentioned ‘*’ to specify any SQL Server product. On the Data source I’m mentioning the instance name from where I’d be pulling data. It’s “MSSQLTEST” for my case.

5. On the security tab select “Be made using the login’s current security context”. This would use my used Loging ie, “TEST” for this case.

6. Click OK, now you can browse through the objects using SSMS-->Linked Servers--> TEST_LINK-->Catalogs or you could use below syntax to fetch any table data

select * from [TEST_LINK].[TEST_DB].[dbo].[TEST_TABLE];
  7. Revoke “Sysadmin” privilege from “TEST” user

My “Linked Servers” has been created!

SQL Server - Maintenace plan integrity checks fail with "Alter Failed for server xxx"

My maintenance plan integrity check task was failing with "Alter failed for server XXX". 

After tracing i found the maintenance plan executes sp_configure 'USER OPTIONS',xxxx followed by RECONFIGURE. The reconfigure statement then generates an error causing the plan to fail. This was because the server configuration option "Allow Updates" was set to 1. Changed the setting back to 0 and the reconfigure statement started to work again!

Following command changes "Allow Updates" parameter to "0"

SP_CONFIGURE 'ALLOW UPDATES',0
GO
RECONFIGURE
GO