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

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