Ch Ch Ch Changes are afoot

Well it’s been an interesting couple months and there are quite a few changes going on in my life, but before I get into what’s coming up, I feel that I need to give a bit of an overview of my journey thus far.

I never thought about becoming a developer. When I was growing up, I loved school (yeah, I’m weird), I loved math and science (even weirder), I was going to be a doctor, work on genetics, or something similar. That was my plan even when I started college. Then there were some family things that happened and I was steered to law. I changed majors and got a degree in Justice Studies with the plan to go to law school. I eventually went to law school and after 1 semester realized I hated it. There was no way I was going to finish it, so I quit law school.

Problem was, I had no backup plan. I had a no clue what I was going to do. I bounced around a bit, took a class here and there trying to figure it out, but nothing sparked my interest.

I was working a customer service job, and there was an internal job posted for a Web Site Developer that required experience with HTML, CSS, and ColdFusion. I’d never done any of that before, but my feeling was “Heck I’m smart, I can learn it” so I applied it. The manager was nice enough to give me an interview and once he realized I had no experience, he said “I can see you want the job, but no. We need someone with some knowledge.” I completely understood, but I’m stubborn and I decided to prove him wrong. I went out that weekend, bought a few books, and started learning HTML, etc. Via the work grapevine, it got back to the manager of the Web Team that I did this, so he decided that my drive was enough to give me a chance and boom I got the job.

In one day, I went from not knowing what I was going to do, to becoming a developer. Post Web Site Developer, many of my jobs were the same way, I didn’t know the technology required, but I’d learn it. When I left the web job, I needed to know VBA and MS Access, that turned into C#, SQL Server, and Winforms. Each of these I learned on the job or on my own. The only difference was I stopped buying books and started using sites like Stack Overflow to learn.

As I said, I didn’t grow up wanting to be a developer. While I like being a developer, lately I’ve felt burned out with it. I’ve had this nagging feeling that I need to be doing something or working on something that I care about. That I feel passionate about. But I wasn’t sure what that was.

I started using Stack Overflow almost 5 years ago, when I needed to write C#. I had a book but I wasn’t able to solve some of my problems, so I’d either ask a question or search for answers. I loved the site immediately. I loved that I could get a solution to my specific problem. Yes, I had growing pains on the site, like so many others. For example, I wouldn’t give enough details when posting a question, etc. but I learned how the site worked, and I learned a lot from it. I began to feel comfortable answering questions in a few tags. I loved being able to help others and hopefully users would learn something from my answers.

So what does this have to do with changes?

I’ve been both a user and a moderator on Stack Overflow. But now I’m going to be working in a different capacity, I’ve been hired to be a Community Manager by Stack Exchange. I can’t express how excited I am for this. I love the sites, I love the product that they put out there, I love that people get benefit from them. Of course it’s a bit scary because it’s a career change, but the fact that I love what they do is a huge driving force behind my decision. To be able to work on something you care about makes a big difference. I can’t wait to see where these changes take me.

using powershell to fix server space issues

A few weeks ago, we were running into severe disk space and memory issues on our development servers at work. Our set-up is a bit odd, we have 3 servers – one for the transactions, one for the web interface, and the final one for reporting. Using transactional replication we have databases that can exist on all 3 servers. Yes, it’s can be a real nightmare to maintain, but anyone who works with replication already knows this. Each development team gets its own version of production “in a box”. These copies are on VM slices with limited memory and disk size.

Our issues were happening on the web database and reporting servers. We have replication and sql jobs running, development teams testing, and a variety of other things hitting the servers pretty hard and no memory to process it, so we were running out of disk space. There were several days in a row that I noticed we were down to 20MB of space on our web database server. This lack of resources was causing replication to fail throughout our environment, which resulted in delays to our current sprint.

I’m the early bird on my team and every morning I was executing the following code to “clean-up” our environment via shrinking the log files. Eeeek, the dreaded shrinking of log files. I know you don’t want to do this really ever, but it’s a development environment and it’s how we handle our limited memory issues.

My first check to was see the size of the logs by running DBCC SQLPERF (LOGSPACE). This command gives the transaction log space for all of the databases on the server. Once I got the list I was able to target the databases that had excessively large log files in order to shrink them.

Next, I’d get the name of the log file to shrink via:

SELECT name
FROM sys.master_files
WHERE database_id = db_id()
   AND TYPE = 1

Lastly, using the name of the log above I’d run DBCC SHRINKFILE to drop the size of the logs and restore a bit of memory on our box.

Doing this first thing in the morning for multiple databases on multiple servers for multiple days in a row was terrible. So, I decided I needed to automate this. Considering I learned a bit about Powershell from Mike Fal at SQL Saturday Phoenix, I thought “hey, let me take a stab at writing my first Powershell script to do this for me!!”

To be honest, I wasn’t exactly sure where to start so I asked Mike for a bit of help on looping through all databases on a server. He was nice enough to give me a starting script. Using this, I attempted to incorporate my code above into it with the purpose of shrinking all log files on the server. I’m sure there are much better ways to do this but here is my first Powershell script.

Import-Module sqlps -DisableNameChecking
 
$dbs = Invoke-SqlCmd -ServerInstance 'servername' -Query "select name from sys.databases where database_id > 4"
 
foreach($db IN $dbs){
	$Log = Invoke-Sqlcmd -ServerInstance 'servername' -DATABASE $db.name -Query "select name from sys.master_files where database_id = db_id() and type = 1"
	$LogName = $Log.name
	$query = "DBCC SHRINKFile($LogName, 1)"
	Invoke-Sqlcmd -ServerInstance 'servername' -DATABASE $db.name -Query $query
}

This does exactly what I need it to do. I can set it up on our environments to run nightly to shrink the logs so I no longer need to manually “fix” stuff every day. I’ve passed it along to our engineering services group to set this up in all of the development environments when they create them with new production copies.

The script is available on GitHub if anyone is interested in using it, or tweaking it, or criticizing it, etc.

SQL Saturday Adventures

A few weeks bit over a month ago, I took the plunge and attended my very first SQL Saturday, SQL Saturday #370 in Phoenix. I’ve never had a chance to go to any of the local user group events or another SQL Saturday due to scheduling issues, etc. but this year I reshuffled things so I could take part. I’m really glad that I did, I truly learned a lot of stuff and I also realized I know more than I probably give myself credit for. Here’s a few of the highlights from me day:

My first session was Understanding Parameter Sniffing by Benjamin Nevarez. We deal with parameter sniffing a lot at work, so it was something I had experience with. I also gave a Lunch & Learn presentation to co-workers on the topic, which made me curious to see what else I should have covered during my session. To my surprise, I knew most of what Benjamin covered – which was great. His session was listed as “Beginner” but it was nice to know almost everything he discussed.

During lunch, I attended the WIT Panel. For those who don’t know, WIT is for Women in Technology. I’ve rarely worked with female developers, so I went to this session to hear stories from the women in the same field. It was interesting to hear similar stories from the women on the panel, as well as from the audience. I found myself saying yeah, I’ve experienced that repeatedly during the session. Hearing about “Mr. Know-It-All” from multiple panelists made me laugh because we’ve all dealt with that guy. There are so many things I could say about the WIT Panel and being a woman in technology, I’ll have to make that a separate post. My takeaway from this was that my experiences are similar to many other women in tech, which I already knew, but it was nice to hear. I’d recommend this session to anyone attending a future SQL Saturday.

The last session I went to Powershell Tips and Tricks for SQL Server Administration by Mike Fal. I had never used Powershell and had limited exposure to it prior to this session, but I know Mike from DBA.SE and Stack Overflow so I wanted to attend his session and I’m glad that I did. I saw a few things that I would be able to use almost immediately at work with Powershell (I’ll be posting my lame attempts in another post).

I was truly impressed by out SQL Saturday that we had here in Phoenix. I’m glad I forced myself to go and I’ll definitely go to more events in the future.

how to rotate rows into columns in MySQL

I have answered a lot of MySQL pivot questions over on Stack Overflow and a few over on Database Administrators and have learned some things about how to transform data in MySQL.

Unfortunately, MySQL does not have PIVOT function, so in order to rotate data from rows into columns you will have to use a CASE expression along with an aggregate function.

Let’s set up some sample data.

CREATE TABLE products
(
    prod_id INT NOT NULL,
    prod_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (prod_id)
);
 
INSERT INTO products (prod_id, prod_name)
VALUES (1, 'Shoes'), (2, 'Pants'), (3, 'Shirt');
 
CREATE TABLE reps
(
  rep_id INT NOT NULL,
  rep_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (rep_id)
);
 
INSERT INTO reps (rep_id, rep_name)
VALUES (1, 'John'), (2, 'Sally'), (3, 'Joe'), (4, 'Bob');
 
CREATE TABLE sales
(
  prod_id INT NOT NULL,
  rep_id INT NOT NULL,
  sale_date datetime NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (prod_id, rep_id, sale_date),
  FOREIGN KEY (prod_id) REFERENCES products(prod_id),
  FOREIGN KEY (rep_id) REFERENCES reps(rep_id)
);
 
INSERT INTO sales (prod_id, rep_id, sale_date, quantity)
VALUES 
  (1, 1, '2013-05-16', 20),
  (1, 1, '2013-06-19', 2),
  (2, 1, '2013-07-03', 5),
  (3, 1, '2013-08-22', 27),
  (3, 2, '2013-06-27', 500),
  (3, 2, '2013-01-07', 150),
  (1, 2, '2013-05-01', 89),
  (2, 2, '2013-02-14', 23),
  (1, 3, '2013-01-29', 19),
  (3, 3, '2013-03-06', 13),
  (2, 3, '2013-04-18', 1),
  (2, 3, '2013-08-03', 78),
  (2, 3, '2013-07-22', 69);

We can easily query the rep, sales, and product data by joining the tables:

Continue reading

did somebody say pivot?

Paging bluefeet, there is a PIVOT question to be answered.

While that might seem like a joke, it has really happened, especially over on Stack Overflow. If you have seen any of my posts, then the chances are that I was answering a PIVOT question (or something similar). At this time of this post almost 20% of my total answers (over 3k) have been on pivot questions.

You might ask yourself, why pivot? The simple answer is because I love them. I have heard the arguments,1 "don’t do this type of data transformation on a server do it in the application layer", etc. but my feeling is if there is a way to do it then go for it.

While not every database has a PIVOT function, I will answer or attempt to provide a solution on just about any RDBMS.

What’s the point of this post? My goal is to write a series of posts outlining different methods to PIVOT data in a variety of databases. I probably won’t be able to add much more to what is already out there on this topic, but this will be my spin on pivoting because I love it so!!

First up will be MySQL…Stay tuned.