I recently had a transaction log that had consumed over 99% of the available drive space and was at the verge of running out of room. Initially I did a transaction log backup and immediately followed it up with a log file shrink. No luck. The file would not shrink. So I knew that there were pending transactions that would not allow it to shrink.

To double check before wasting precious time, I ran the following query:

select name, log_reuse_wait, log_reuse_wait_desc from sys.databases

The log_reuse_wait was set to 6, log_reuse_wait_desc was REPLICATION.

Ah. One thing people often forget is that until a transaction has recorded in the distribution database it won't be fully committed in the database. So I had some transactions that hadn't been published yet that were hanging up my log file from shrinking. Although I do have replication enabled on this server, all of the publications/subscriptions are snapshots, so I really don't need to have all the transactions pushed to the distribution database. Regardless, there were transactions waiting.

To find out what transactions were pending replication, I ran this query: sp_repltrans. It returned a list of 9 transactions that were pending. Because I only do snapshots I didn't care about these pending transactions and decided to flush them out. To flush unfinished transactions I use the sp_repldone command. If you want to set all transactions to "published" so they will clear from the transaction log, use the following syntax:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Be careful with this if you are running transactional replication because it will allow your data to get out of sync.

After I flushed the pending replication transactions, I ran another log backup, then a file shrink and the file was down to a respectable size.

Labels: log_reuse_wait, replication, shrink, sp_repldone, sql server, transaction log file

posted by solutionsguy at 10:59 AM 0 Comments

This week I spent a considerable amount of time installing and troubleshooting a SSL install on IIS 6. I won't get into downloading and applying the Cert- that is documented everywhere. Here are few things to check for that will make it easier to get it all working.

1. You HAVE to specify an IP Address in the Web Site Identification box. The default is (All unassigned). Make sure to click on the "Advanced" button and add that IP address in the SSL Identities for the website. If you don't specify an IP Address for the SSL identity it will not work.

2. Make sure your Certficate Chain is valid. The easiest way to do this is with a SSL Diagnostics utility. Run the diagnostics and make sure there are NO errors at all. If there are then your certificate will not work. For the chaining, look at the CertVerifyCertificateChainPolicy and make sure it says "succeeded".

3. Once you have the chain and the IP addresses set it is simple to secure different folders or files on your web server. You either go into "Directory Security" or "File Security". In the "Secure communications" section go to "Edit" and check the box "Require secure channel (SSL)".

Labels: Certificate, CertVerifyCertificateChainPolicy, IIS 6, SSL, SSL Diagnostics, Troubleshooting

posted by solutionsguy at 10:32 AM 0 Comments

So being raised 99% on SQL Server and its tools, it has been interesting having to pick up support on Linux and MySQL. Nothing against either of them, but I guess I've either been spoiled or lazy with not having to do much with command prompts if I don't want to. This week was my first time trying to get connected to mysql that had all connections blocked except for localhost. So for all you other SQL Server DBA's that have to connect to MySQL and don't have a clue where to start, here's a cheatsheet!

First step- you have to establish your connection to the mysql server. If you are lucky you can download the MySQL GUI administrative tool and get connected, piece of cake. I won't even get into the details on that. However, a lot of you are going to see all remote connections disabled except the localhost connection. This poses somewhat more of a challenge for connecting. Essentially is what you end up doing is tricking the MySQL gui to thinking that it is connecting to your own MySQL instance, not a remote one.

So to communicate with the MySQL server you have to set up a "tunnel" to the server. This tunnel is how you will control the MySQL Server. To set it up, you will need putty.exe. This allows you to create a SSH (Secure SHell) connection. Inside of that connection you create a tunnel that forwards communication on a given port from your computer to the MySQL server.

Download putty.
Once you have it downloaded, on the main screen you need to expand the SSH menu so you can see the Tunnels option. In the source port(your machine port) put 3306. In the Destination field put 127.0.0.1:3306 and then click Add. This will save the tunnel information in your connection. Then open your SSH connection.



Once your SSH connection with the tunnel to the MySQL ports is set up you can open the MySQL Admin tool. In the server host you put your localhost (127.0.0.1) and the port number 3306. (Unless you are using a different port. As long as this port number matches with the one you put in the "Source Port" field in the SSH Tunnel). It is also noteworthy that you might have a different login you use with SSH and MySQL- Make sure you the username and password is your database login. Here's a screenshot:


Then click connect and you should be in!

Labels: how to connect, mysql, sql server, SSH, tunnel

posted by solutionsguy at 2:36 PM 0 Comments

For the fourth time in the last several days I have had to mount an ISO file to do an install. For those of you that don't know, and ISO is an image file that is typically used to create a CD or DVD. Rather than creating a new CD/DVD, you can actually attach directly to the ISO file as if it was a CD/DVD.

There are several utilities out there that do just this. I've looked at quite a few of them. I prefer to have a lightweight utility that won't install a lot, if any, extras. Especially since I typically am working on servers when I need to mount an ISO. The most lightweight one I have found so far is actually written by Microsoft. I've used it several times in the past and for some reason had forgotten about it until today.

It is called Virtual CD Rom Control Panel. The download is 60KB! Impressive! Although it takes a few basic steps to mount an image file, I love how lightweight and simple it is. The download is found here: Virtual CD Control Panel 2.1. The utility allows you to select the drive letter you would like to use and then mount the ISO image.

Labels: ISO, Virtual CD

posted by solutionsguy at 11:33 PM 0 Comments

Having little experience with javascript, I came across the issue of being able to access the value of a field that was not an asp.net textbox. With javascript being executed client side, the server really has no knowledge of the value. This can cause a problem when trying to integrate javascript with your website. In my case, I had a javascript pop-up calendar that would allow a user to select a date, once again all client side code. In order to get the value of the selected date in the c# code-behind, I had to do the following:

1. Use the below line of code to create a hidden field on the webpage; I called mine hdnElement. Also, create an input box for the Javascript, mine was called txbxOccDate.

input type="hidden" runat="server" id="hdnElement"

Labels: C#, HTML, Javascript, SolutionsGuy

posted by solutionsguy at 8:03 PM 0 Comments