Howto: Shrink MS SQL Database Transaction Log

As some hosting provider do limited the size of MS SQL database to be hosted, you might found out your database is not really used up the allocated size.

For many cases, it is because your database transaction log used up those spaces and you might not need to have this transaction log for any purpose. To get this resolve, you may shrink the database transaction log.

1. Open the MS SQL Management Studio and connect to the console.
2. Run the following command from SQL Query.

USE <DatabaseName>
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

** Replace the DatabaseName and TransactionLogName with your database information.

In most of the cases, you may schedule this task to be run automatically if needed.

How to know if you have too many WordPress plugins

Many bloggers who using WordPress were facing a major problem, website slow. Sometimes, this could be a problem from the WordPress application itself instead of the hosting server.

WordPress is designed to be able to use custom plugins or themes. If you simply install the untrusted plugins or themes, it will indirectly causing your website slow.

So you shall not install the unused plugins. If you did it, uninstall it instead of deactivated it as deactivated will still causing the SQL query to query it from the wp_options tables from your database.

Watch your website load time. If your site couldn’t loaded within 5 seconds and where this might caused by the low quality plugins installed.

Check the plugins compatible from the plugins page from URL below,
http://www.mickgenie.com/wordpress-3-2-are-you-sure/

Check the query time,  add the following code to your footer.php and check your site query number and time.

<?php echo $wpdb->num_queries; ?>
<?php _e('queries'); ?>. <?php timer_stop(1); ?> <?php _e('seconds'); ?>

Then try to disable the plugin 1 by 1 or even remove them and test the query time. Otherwise, optimize the database to cleat the overhead data.

Changing MSSQL Ownership of a Table

Sometimes, you are moving your SQL Server database from one server to another server and you found out the connection string is correct but the database could not be connected.

You may check if your schema set correct from your MS SQL server. To done it, you may refer to the step as below.

1. Open the SQL Query Windows.
2. Use the command as below.
==================
USE mydatabase;
GO
ALTER SCHEMA dbo TRANSFER [schema].mytable;
==================
# replace schema with your old schema, mytable with your table name.
3. Execute it.

You will now found the database is connected.

MySQL Stored Procedures Permission problem

With MySQL 5.0.x and if you are using Stored procedures, you might found out the normal user are not able to use it to call the Stored procedures.

Although this is fixed when you are using MySQL 5.1.x, but your server might still stick to the MySQL 5.0.x. To overcome this problem, you may follow the step as below to get it fixed.

Log into the MySQL server to run the SQL Query.
1. Type in, GRANT SELECT ON mysql.proc TO [email protected]’10.10.10.10’; where username is the MySQL username and 10.10.10.10 is your IP or MySQL hostname.
2. Second line, you may flush the privileges, type in,  FLUSH PRIVILEGES;

You have done.

MySQL: Load Data Infile or Outfile

For many cases, you might want to load your data out from the database to a text file or dump the database through the SQL query with a .sql file without a control panel such as phpmyadmin, etc. You may actually done it through the MySQL console query engine.

In this article, Mick Genie will guide you on Load Data Infile and Outfile from MySQL databases query.

Basically, you will need to understand what is infile and what is outfile, to differentiate it, you might refer to the detail as below.
infile: when you using infile, that meant you are inserting data to the database and it is the same when you are using < symbol from the MySQL console.
outfile: If you understand infile, then you shall understand outfile is use to grep the data from the database and store to the text.

Below is the sample script with infile could be used from the PHP script.

$tableName  = ‘mickgenie’;
$query = “LOAD DATA INFILE ‘mickgenie.sql’ INTO TABLE $tableName”;
$result = mysql_query($query);

Below will be the outfile sample from the MySQL query.

SELECT * INTO OUTFILE ‘data.txt’ FIELDS TERMINATED BY ‘,’ FROM table2;

Prevent SQL injection by using IIS URL Rewrite

Nowadays, a bad or unoptimizes SQL query could easily compromised or get attacked. However you may try to reduce or prevent (Don’t say avoid, it is not possible, keke) the SQL injection through your expression rules.

If you are using IIS as your web engine, you may use some expression to reduce the SQL injection.

[dD][\%]*[eE][\%]*[cC][\%]*[lL][\%]*[aA][\%]*[rR][\%]*[eE][\s\S]*[@][a-zA-Z0-9_]+[\s\S]*[nN]*[\%]*[vV][\%]*[aA][\%]*[rR][\%]*[cC][\%]*[hH][\%]*[aA][\%]*[rR][\s\S]*[eE][\%]*[xX][\%]*[eE][\%]*[cC][\s\S]*

Continue reading Prevent SQL injection by using IIS URL Rewrite