Tag Archives: powershell

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.