Tag Archives: development

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.

late to the party

Boy, I am sure behind the times with having a tech type of blog but being late is better than never.

I am going to try to use this platform to discuss some of my tech journeys while I delve into my new job as a DBA developer. I have worked on SQL Server in the past but I will be venturing into Oracle development as well as more in depth SQL Server stuff. It should be fun, so I will attempt to post some thoughts here. We will see how it goes!