|
How to find out the size of data in MS SQL databases |
||||||||
If you are running Microsoft SQL server you may already be familiar with how the database file system can enlarge itself to astronomical proportions. There are 'Auto shrink' and 'Auto grow' options, but these are really not recommended for production environments.
So how do you go about determining how much space is being used by actual data IE records, and how much is being used by log files etc? This article will show you how to run a query that will return the size of all the tables in your database, and highlight a TSQL command that will clean up the file storage of a database.
|
Blog redesign and a version update to BlogCFC 5.9.5 |
||||||||
I've been running an older version of Blog CFC for a while now, so I thought it was well past due an update to the latest version. It seemed like a good time to give the site a lick of paint, so I have de- web 2.0'd it. At some point I got swept up in the curved corners and shiny gradients of Web 2.0ness, and now I just plain don't like it.
If you didn't know already you can get BlogCFC from RIA forge here: http://blogcfc.riaforge.org/. There seem to have been some nice tweaks in the last few versions, and it's nice to see a software application maturing.
In the process of redesigning the site I came across this recent survey of the installed user base for windows fonts. It is always risky for a developer to use a non standard font, so surveys like this are ideal. http://www.codestyle.org/css/font-family/sampler-WindowsResults.shtml
Also I had a little Photoshop help creating the image header. Here is a pretty painless Photoshop tutorial on "Fade Images in Photoshop Using Layer Masks", http://articles.sitepoint.com/article/photoshop-fade-layer-mask
Lastly I've added some Feedburner integration. Feedburner is a great way of consuming your RSS feeds, it adds a whole load of options, and allows you to push feed content out to other community sources, such as Twitter or FaceBook. It also provides stats, a la Google Analytics style, and its free (requires a Google account)! http://feedburner.google.com.
I think I took two things away from this redesign. Firstly, double check your CSS. The only real issues I had during the entire process were CSS related (thanks IE). Secondly index your database properly. I'm not sure if the previous install was indexed at all, or whether the new version just has better indexing in the installation scripts, but the performance difference is notably quicker!
|
Quotes being changed to question marks using cfsavecontent and MySql |
||||||||
One of the tables in an online application stores entire html templates in a blob field, this data is pulled out and used to build emails, replacing specific keywords.
I recently needed to update some of this data, but there is not an administration system for it, so a manual SQL script would have to do.
The problem I encountered was that creating a html template as a variable using cfsavecontent and inserting it into MySql was causing all the ' to appear as ?. I read a blog entry based on Coldfusion MX and setting the character encoding in your coldfusion administrator, for each datasource. Or you could do it in the template, like this:
2<cfcontent type="text/html; charset=iso-8859-1">
This didn't seem to work at all, in fact I found that it would error whilst trying to insert the cfsavecontent variable using cfqueryparam. There seemed to be some sort of encoding issue between the two tags.
2Html template code
3<body> etc </body>
4</cfsavecontent>
5<cfquery datasource="#application.dsn#">
6INSERT INTO table (fields)
7VALUES ('#htmlContent#');
8</cfquery>
This didn't work either with Cfqueryparam, or as a non param input. It turns out that you simply need to escape the characters (double them, or use character representations). IE don't use a single quote, use two . In this way the MySql encoding treats the character as a character, rather than a potential command. I still think that this is something that can be fixed by setting the encoding type, but in this case there was a far simpler solution.
|
Importing large datasets into MySQL - Packet too large errors |
||||||||
I was recently migrating some data from one application to another and came across a stumbling block in MySQL. If you are trying to run a large SQL file backup restoration and the source file is quite large you may encounter the error : 'Packet too large' or 'MySQL server has gone away'. This is cuased by a MySQL server memory error.
If you look in the mySQLserver installation path directory you should see a 'my.ini' file. This controls the default settings for SQL server when you start the service.
Add in a line of code like this:
under the value of [mysqld]. Save the file and then restart mySQL server.
You should now be able to run:
show variables like 'max_allowed_packet';
And have it return your ini value.
As long as the value you have set it to is greater than the file size it should run successfully.
You can also run it as a set session variable command in your SQL environment, like this:
This will set the same value for that instance of the sql server.
Update:
There also seems to be a massive difference in performance in running the imports directly into a GUI environment, or through a shell command. I've had a 600mb file fail several times in the GUI, but running:
It has imported within around 10 to 15 minutes.
