Category Archives: SQL

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:

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 $ -Query "select name from sys.master_files where database_id = db_id() and type = 1"
	$LogName = $
	$query = "DBCC SHRINKFile($LogName, 1)"
	Invoke-Sqlcmd -ServerInstance 'servername' -DATABASE $ -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.

    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');
  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');
  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)
  (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.