Classic ASP “Write to file failed” Error

A recent problem I came across in one of my applications is a failure to upload files over HTTP. I’ve been using this set of classic ASP file upload classes written by Lewis Moten from way back in 2002. The problem was that every file I uploaded from my brand new Dell notebook running Vista x64 failed with the error “Write to file failed.” Every browser on this notebook exhibited the same failure. Works fine from an XP box, though. So, I did some tracing and discovered that Vista, as a security precaution, does not send the full path anymore when doing an HTTP upload.

If you experience this, in the file named clsField.asp (actually, I may have renamed this long ago from its original name . . . I don’t remember) in this property: “Public Property Let FilePath(ByRef pstrPath)”, find this:

' Parse File Name
If Not InStrRev(pstrPath, "\") = 0 Then
    FileName = Mid(pstrPath, InStrRev(pstrPath, "\") + 1)
End If

And change it to this:

' Parse File Name
If Not InStrRev(pstrPath, "\") = 0 Then
    FileName = Mid(pstrPath, InStrRev(pstrPath, "\") + 1)
Else
    FileName = pstrPath        ' Vista doesn't sent full paths anymore
End If

That should clear up this error for you.

Does your Firefox load slow? Try this.

Like me, you might really like Firefox. Generally speaking, it does a really good job. However, after turning on your computer (I’m talking Windows only), the first time you run Firefox it seems to take a long time to load up. Subsequent loads are okay, but that first one is the slow one. I recently came across a pretty nifty utility called Process Lasso by Bitsum Technologies. I can’t remember why I downloaded it . . . probably because someone recommended it as a way to speed up Windows Vista a bit. I’ve noticed  a significant improvement in load times for Firefox since I installed it a couple of days ago. That was a nice side effect. So, if your Firefox loads slow, try this utility (free for home use). It may speed up more than just Firefox. It works on pretty much all flavors of Windows, including XP x64 and Vista x64. Enjoy!

Slick way of handling browser-specific CSS issues

Every web developer eventually comes across a major headache in cross-browser functionality. My most recent one was with the alignment of text that follows a radio button. Vertically aligning that text with text that comes before a radio button is a bit of a pain. The easiest way to handle this is to use a table and set the valign attribute to middle, like so:

<table>
    <tr>
        <td valign="middle">Select:</td>
        <td valign="middle">
            <input type="radio" name="fruit" />Apple
            <input type="radio" name="fruit" />Orange
        </tr>
    </tr>
</table>

In this example, depending on your browser, you might see the text “Apple” and “Orange” sit lower than the “Select:” text. In Safari and Opera, this renders just fine. In IE, the text is 2 pixels lower and in Firefox, the text is 1 pixel lower. If we design for IE, then Firefox will be 1 pixel above, and Safari and Opera will be off. This is a case where browser-specific CSS is necessary, but you wouldn’t necessarily want to create a whole new stylesheet for it. Enter CSS Browser Selector by Rafael Lima. This nifty little piece of javascript lets you use browser-specific classes in your existing stylesheet. In this case, the solution was simple. First, we change the HTML a bit to put the text following the radio buttons in a label with a class named radio.

<input type="radio" name="fruit" /><label class="radio">Apple</label>
<input type="radio" name="fruit" /><label class="radio">Orange</label>

Then, we reference the small javascript file in our HTML header:

<script type="text/javascript" src="css_browser_selector.js"></script>

Finally, we add the browser-specific CSS to our stylesheet:

.ie label.radio {
    position: relative;
    font-weight: normal;
    top: -2px;
}

.gecko label.radio {
    position: relative;
    font-weight: normal;
    top: -1px;
}

label.radio {
    font-weight: normal;
}

Yep, it’s that easy. Now, IE and Firefox (gecko) render properly. Safari, Opera and other browsers just fall through to the last label.radio.

E Text Editor

If you haven’t checked out the E text editor for Windows, you might want to. This is one powerful editor. I’ve been using the trial version for a little while and have just scratched the surface of some of the cool features. Just this morning, I watched the screencast on the home page and found out there was a very cool feature that will no doubt save me some time. I can use the CTRL key to select multiple words, and change them all at the same time. Imagine changing a variable name in just a small section of code. Just highlight each one using the CTRL key and start typing. I love it! It also supports snippets, which are nothing new, but are very cool anyway. E taglines itself as “The power of TextMate on Windows.” I’ve never used TextMate, so this is a new introduction for me, and I like it. It’s hard to impress me with a text editor because I am a huge vi fan. I used it for a long time on Unix, and in my current job, I use it all the time on various Linux servers. I carried that use over to Windows with gvim. It’s powerful, to be sure. So, for me to even discuss another text editor is a big deal. The trial version of E is time limited to 30 days and the purchase price is $34.95. When my trial is up, I’ll be purchasing this one for sure.

Multitenant SQL Server Architecture

We have a very large database where I work that is architected as a multitenant database. This is great since all we have to do is add a new customer record, and the rest takes care of itself. Because of the proprietary nature of our platform, I can’t put the table schemas up here for you to look at. However, I am looking for advice or techniques you may have used to solve a particular problem.

When customers import their data, imports go fairly slow. We give customers standard fields to import data into, but then they also have the option of importing custom fields. The table that holds custom fields allows one record per customer/user/field. Each record is tied to the user, and the user data is stored in another table. If a customer has 10 custom fields defined and they upload 50,000 users with all 10 field values, this is a total of 500,000 custom data records. The import process has to first see if the custom data exists. If so, it updates that record. If not, it inserts a new record. As you can imagine with this process, the more custom fields a customer has, the slower the import process becomes. Each custom field has to be tied to the user before it gets updated or inserted. The user may or may not exist, so we have to first insert or update the user, get the user id, and use that for each custom data record. That means no bulk loading because we first need the user id. Think of it as a master-detail relationship. The table that holds users is the master. The table that holds custom data is the detail.

I’m curious to know what you’ve done, or would try to do with a scenario like this to make it super fast.

Poderosa – A Viable PuTTY Replacement

I’m a long-time PuTTY user, but one of the annoyances is that it does not save passwords. I understand the reasoning behind why, but the creators of PuTTY took the stance of never going to add that as a feature. I found a decent replacement that does have password saving capabilities, and that is Poderosa. Poderosa is an open-source, free SSH tool with tabs, split window panes, and yes, password saving capabilities. It’s written in C# and built on the .NET 2.0 platform. A lot of people don’t like that fact, but .NET is so standard on Windows nowadays that it’s really no big deal.

I searched for quite awhile for how to have the passwords saved. Nothing I tried worked, so having the source code was useful. I ran the system through my Visual Studio debugger and found out exactly how the passwords are read in from the XML-based shortcut file. There is a configuration setting inside Poderosa that is turned off by default. Click on the “Tools” menu, then “Detailed preferences editor…”. Find the setting named “org.poderosa.protocols.ReadSerializedPasswords” and change it to “true”. Then, in the shortcut file (these have the extension “.gts”) in the second element, next to the “account” attribute, create a new attribute named “passphrase” and add your password there. Here’s what it might look like.

<?xml version="1.0" encoding="Windows-1252"?>
<poderosa-shortcut version="4.0">
  <Poderosa.Terminal.TerminalSettings encoding="iso-8859-1" caption="Remove Server Name Goes Here" />
  <Poderosa.Protocols.SSHLoginParameter destination="127.0.0.1" account="joe" passphrase="myTotallyUnsecurePassword" />
</poderosa-shortcut>

Obviously, you would have the destination equal the IP address of the remote server.

Now, before you go all nuts about clear text passwords, let me just say that I agree with you 100% that passwords in clear text are a bad, bad, bad idea. Since I have the source code, I will be working on a way to have passwords saved in a different, more secured fashion, but for the moment, this meets my needs. Perhaps it will meet your needs, too.

Autogrow stopped working again

Back in December, we had a client who ran a defrag while SQL Server was running. This caused the main database to run out of filegroups space in the ‘PRIMARY’ filegroup. Well, it happened again this week. This time, though, a defrag was not the culprit. We’re still trying to determine what causes this from time to time. Here’s the error in the event log:

MODIFY FILE encountered operating system error 1450(Insufficient system resources exist to complete the requested service.) while attempting to expand the physical file

The solution? Stop all SQL services. Make a physical copy of the database’s MDF file. Change the name of the original MDF file to something else. Rename the copied MDF file to the same name as the original MDF file. Restart SQL services.

Original research showed that this was probably a highly fragmented data file that caused this error, but I don’t know if that’s the case here. Making a copy definitely wrote the physical bits to another area of the disk. For this to happen twice in a three month period is highly suspicious. I’m thinking there’s a drive issue, but system checks didn’t show anything. The investigation continues . . .

Turn off SQL Server before defragmenting your hard drive

Had a fun time this morning with a client’s machine where they defragmented the hard drive on the server over the weekend. One would expect this to be pretty benign. An interesting side effect occurred that I can’t yet explain. The main database ran out of filegroups space in the ‘PRIMARY’ filegroup. The database was set to autogrow, but it wouldn’t autogrow. A manual grow yielded this message:

 

MODIFY FILE encountered operating system error 1450(Insufficient system resources exist to complete the requested service.) while attempting to expand the physical file

Most references to operating system error 1450 yielded results pertaining to a backup. That wasn’t the case here. After quite a bit of searching for information, I came across a blog by Imran Shaikh that basically said this can result from a highly fragmented data file. That was indeed the case with this data file. We turned off SQL Server, ran a defrag again, and turned SQL Server back on. The data file was once again autogrowing. 

I don’t have a real thorough understanding of what happened. My suspicion is that while the original defrag occurred, it couldn’t defrag the database file because it was in use. That may have boxed in the data file so it couldn’t autogrow any more. It’s a bit strange if that’s the case because I would have expected the file to grow elsewhere on the disk, but it didn’t. So, lesson learned: if you’re going to defragment your server’s hard drive, turn off SQL Server.

Database Madness – Day 3

Friday morning rolled around. I already knew it would be a tough day. I didn’t know it would be quite as bad as it was. Just like Thursday, I didn’t get my second email, so I knew right away something was up. Sure enough, tasks were backed up, and the importer was importing around one record every 6 or 7 seconds. No blocking queries again. And of course, a client sent in a critical ticket, which immediately got escalated to one of the founders, who promptly sent it on to me.

This client’s API call were timing out. A simple call to a small table was taking 17 minutes to run. Other normally fast queries were taking an unusually long time to run. And, as before, SQL Server Profiler wasn’t showing any one specific problem. In this case, it looked like everything was a problem. Queries were not utilizing indexes, and that became highly baffling. I had to address this one client’s issue, though, and found our code was sloppy in that one area. So, with a quick consulting of our development director and CTO, I got permission to rewrite that piece and put out an emergency patch. That worked for that one client for that one query. The system was still slow. More folks got involved from the IT side, and they started looking at network bandwidth. One looked at the database server’s disk I/O and found highly unsual activity along with very long wait states. Aha! Something, finally.

I surmised at this point that one very large table with 4.4 billion records was fragmented. We restored a backup onto a secondary server and ran a check. It was 67% fragmented. Definitely a culprit.

During the restore, I researched more into troubleshooting, specifically what the numbers in SQL Server Profiler truly meant. I’m not a DBA, but I know enough to work on issues like these, though is stresses me out severely. Anyway, I found that while Duration is a good number to look at, it could be faulty because it measures the round-trip from the time a query is invoked until the time it completes, including any waiting in between. Since we now knew we had long waits for disk I/O, I needed to look at something else. The Reads column was that key. We found one query running on our API server over and over doing 2.6 million reads each time. There was the smoking gun. But, what was the query? It was some prepared statement, so we set out to look for it.

We ultimately found the problem in the code. It was a piece of code that had been running for a long time with no troubles. It’s unclear as to why it suddenly caused a problem. We’ll search for that answer in the next week or so. Friday evening, we created another patch and pushed it to production. The number of reads went from 2.6M to anywhere between 10 and 100. That’s a HUGE difference. The database is now nice and happy, and things are performing as they should. We still have the large table with high fragmentation. We need to schedule a maintenance window to reindex it. Our timings show it will take around 4 hours for the reindexing to run. Not too bad.

I learned a few lessons from this ordeal.

1. SQL Server Profiler is your friend. Understanding what the numbers mean and how to use them can quickly show up most problems.
2. One high CPU utilizing query can have a cascading effect on the system. In this case, the query optimization engine decided that many of the indexes in these smaller tables were useless since queries were taking so long, and defaulted back to a clustered index scan on the primary key instead of using the index. Once the problem was resolved, the optimization engine started using the indexes again.
3. In the future, one of my first checks will be disk I/O. We had a lot of disk thrashing and a large disk queue length.

Saturday morning came around, and all was quiet. The database was humming along nicely, and clients’ jobs were running well. Looks like I’ll have a relaxing weekend after all.

Database Madness – Day 2

 

Thursday morning rolled around. In the words of Arthur Dent, fictional character of Hitchhiker’s Guide to the Galaxy: “This must be Thursday. I never could get the hang of Thursdays.”

 

 I take a train into work, so I get up early and arrive by just after 6am. I have two custom jobs that send me an email when they complete, so I’m used to seeing two emails every morning. This particular morning, there was only one. This could be one of two things. Either we didn’t receive the file from the client, or something was awry with the processor. We did indeed receive the file, so that ruled out the first possibility. The processor had picked up the job, so that ruled out the second possibility. Very strange. There was a third possibility that I hadn’t originally considered.

 

I checked the list of tasks the system was processing, expecting to see the normal 4 or 5 at this time of day. There were nearly 150. Yikes!

 

In that list was a very odd thing. One client sent 16 import jobs through our user interface so they were all processing simultaneously. Well, surely that would be the culprit. I stopped the importer, but things didn’t get any better. I ran a few simple queries against the database and they were coming back with good speed, so no problems there. I checked for blocking queries, and there were some, but a refresh showed them going away quickly.

 

As the morning progressed, tensions rose. Folks started ambling into the office and awareness that we have a problem quickly entered their coffee-starved minds. This was going to be a long day. By 10am, the situation was beginning to get dire. Publishing jobs were going so slow, and normally fast queries were taking a very long time to run. There was nothing weird with the database except for a very high CPU utilization that we were accustomed to seeing because of one client who had intense jobs. However, it was higher than normal – 98 to 100%. I ran a DBCC SHOWCONTIG on a suspect table and it had 36% fragmentation. We usually re-index this table evey 5 weeks or so because it has a tendency to get fragmented easily.

 

Executives were there, and I recommended restarting the server since we couldn’t pinpoint anything. Even SQL Server Profiler wasn’t showing us any singular query that was a suspect. All agreed, and so we stop SQL Server, and for good measure, rebooted the box. I also recommended taking the scheduled maintenance window for that evening to reindex this table. All agreed. The database came back up, was slow, but still running. We’d have to limp along until evening.

 

Thursday evening came around and we reindexed that table and one other that had a high fragmentation percentage as well. We brought the system back up and things were running, but we noticed an unusual slowness in several areas. But things were running. We were tired, and we decided to leave it until morning.