What process is locking this file?

Posted on March 24, 2013 by Brendan in Windows

I recently had a problem with Microsoft Lync locking my Outlook data file despite exiting Lync. It happened constantly on reboot and locked up my email. It would also lock up the Mail control panel window and I couldn’t rename or move it for debugging purposes.

I used a Windows tool to find it which did exactly what it advertised. It was easy and straightforward to use. It was perfect.

I wanted to share it to hopefully save someone else some time.

http://lockhunter.com/

  • It has both 32 and 64bit versions
  • The installer is 1.5MB
  • It’s very light weight and doesn’t do anything more than you need it to do
  • The GUI is simply and straightforward
  • Simply right mouse click on the file, click What is locking this file? and there it is.

It does require administrator privileges to work though. But I wouldn’t hesitate to use this on a server if I needed to.

Check out their website. I recommend it. *Adds to bookmarks*

What is locking my file?

Leave a comment

Restore SQL Azure Backup to MSSQL Server

Posted on March 24, 2013 by Brendan in Server Administration, Windows

How to Restore Microsoft SQL Azure Backup to Microsoft SQL Server

I’ve been exploring Microsoft Azure more and more recently. One of the most common pains I’ve seen though is getting an SQL Azure backup into to a local development environment for troubleshooting.

Of course, SQL Azure database backups are not straightforward .bak files, no no, they’re .bacpac files. What is a bacpac? Good question, I had to check myself:

“The SQL Database Import/Export Service creates a logical backup (BACPAC) file containing the schema definition and table data of a database in Windows Azure SQL Database. You can then recreate the database on another Windows Azure SQL Database server or an on-premise instance of the SQL Server Database Engine.” – http://msdn.microsoft.com/en-us/library/windowsazure/hh335292.aspx

A significant portion of my searching has turned up mounds of results about moving SQL Server Databases to SQL Azure, but not the other way around. There were some subtle hints of it in the Microsoft documentation but it wasn’t obviously.

This blog post is intended to make it obvious. I found two methods of restoring a .bacpac file to Microsoft SQL Server Database Engine.

  1. Via the Microsoft SQL Management Tools
  2. Via command line.

I know Microsoft SQL Server 2012 is out and about, and that’s really nice. But the majority of the business world is still invested in Microsoft SQL Server 2008 R2 or earlier. I also want to be clear that the below instructions were conducted on:

Server Version: Microsoft SQL Server 2008 R2 SP2 Standard
Management Tools Version: Microsoft SQL 2012 Management Tools

I suspect the server version will be portable at a guess to 2008, maybe 2005 – I’ve not tested it.

A common problem with restoring MSSQL Server .bak files is you cannot restore them to earlier versions of MS SQL Server (e.g. 2008 R2 to 2008, or 2008 to 2005). However I’m so far impressed at the ability to be able to restore the .bacpac files to MSSQL Server 2008 R2. Please, test it out on earlier versions and let me know in the comments how you go.

Backup your SQL Azure Database

This process is already very well documented so I’m not going to rewrite it. Check these out:

http://msdn.microsoft.com/en-us/library/windowsazure/jj650016.aspx

http://msdn.microsoft.com/en-us/library/windowsazure/ff951631.aspx

I personally found it easy and straightforward preferable to use the Azure Management Portal.

Export SQL Azure Database

Export SQL Azure Database

I generally Export the .bacpac to blob storage and download it to the dev environment from there. I’m sure it’s not the best method but it’s one.

I use and recommend CloudBerry Explorer Free for Azure. It allows you to connect to Azure Blob storage and transfer files to and from. The product developer CloudBerry Lab has some other good products worth checking out too.

Restoring BACPAC with Microsoft SQL Management Tools

As I mentioned, there’s a small catch – you need the Microsoft SQL Management Tools from SQL 2012. You simple need a release version (not the release candidate) of the Management Tools, even the Express edition works fine.

http://www.microsoft.com/en-au/download/details.aspx?id=29062

ENU\x64\SQLManagementStudio_x64_ENU.exe
ENU\x86\SQLManagementStudio_x86_ENU.exe

Once again, to be clear, the server I’m restoring to is Microsoft SQL Server 2008 R2 SP2 Standard but the Management Tools are 2012. You can have both the 2008 R2 and 2012 Management Tools installed side-by-side.

I’ve not tested this on other SQL Server versions, I’m sure it will work though. Please test and let me know in the comments.

Confirmation of the Management Tools version I’m using:

MSSQL Version

Connect to your MSSQL Server. Ensure you’re using an appropriately permissioned account – I use an account with sysadmin role.

Right mouse click on Databases and click Import Data-tier Application. Yes, I know, it looks unusual. Just roll with it.

Import Data-tier Application

Follow the wizard:

Import Data-tier Application Wizard

Select the path to your downloaded BACPAC file. If you’ve not downloaded it from Azure, see the section above on tools for downloading out of blob storage.

Alternatively, you can import from Windows Azure right from the wizard however it is incredibly slow depending on your location.

Import Data-tier Application

Give the database you’re restoring a name and define a location for the data and log files. The default location was wrong for me, I had to define the paths below where I normally store my respective files.

Import Data-tier Application Wizard

Review the summary and ensure all your details are correct.

Import Data-tier Application Wizard Summary

Sit back and let it import. The BACPAC I used in this example was a about 25mb and it took a few minutes.

Import Data-tier Application In Progress

All done! The Import Data-tier Application wizard is now complete. Click close and verify your restore worked.

Import Data-tier Application Completed

And there it is! Now you can start your troubleshooting.

MSSQL Server Database List

Restoring BACPAC via Command Line

Thank you to the author (Flea) of the post on Stackoverflow who recommended this process.

You’ll need at least the SQL Server Data Tools – December 2012 update. You can download the “installer” which downloads all the other files for you. I recommend though you download the ISO image, select a Visual Studio and you’ll find the links at the bottom:

http://msdn.microsoft.com/en-us/data/hh297027

Note: Don’t let the words Visual Studio throw you off. As a test, I spun up a fresh Windows 8 VM and installed only the above “Microsoft SQL Server Data Tools 2010 update” and found I could restore the database perfectly. I went with this version in my testing.

Also remember in Windows 8, you can mount ISOs simply by double clicking on them.

Once you’ve installed the update:

Open command prompt and type the following:

cd C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin

SqlPackage.exe /a:Import /sf:D:\path\to\file.bacpac /tdn:Destination-DB-Name /TargetServerName:(local)

This will restore the BACPAC (D:\path\to\file.bacpac) to database Destination-DB-Name on server (local).

This may not suit your needs so check out the parameter options for SqlPackage.exe.

I hope this helps people out.

10 Comments

Automatically Clean Up Temporary ASP.NET Files

Posted on February 23, 2013 by Brendan in Server Administration, Windows

The Problem

One of the test environments I help maintain is subject to dynamic and regular changes in .NET applications. The development team are constantly releasing new builds that are slightly different.

You may not be aware that .NET applications go through a compilation process when they first start up. I’ve also been told on application pool recycle however I haven’t confirmed this. However, after the application has been removed or updated, the compilation temporary files remain. On a test environment similar to my above scenario, a total of 50GB of disk space can be easily wasted, doing nothing. So if you’re in a similar scenario, you may need to routinely clean up these files.

I know of four locations where these files can build up:

  • C:\Windows\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET Files
  • C:\Windows\Microsoft.NET\Framework64\v1.1.4322\Temporary ASP.NET Files
  • C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files
  • C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files
  • C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files
  • C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files

If you application pools run in 64bit mode, you’ll find the “Framework64” locations more applicable. If your application pools use 32bit mode, you’ll need to consider the “Framework” locations.

Below is an example of a production environment using nearly 13GB:

Powershell Script to Automatically Delete Temp Files

As system administrators, we need to automate and script our workload so we can focus on the important stuff! So we devised a simple implementation of Powershell script which takes care of this clean up problem in one line:

Get-ChildItem “C:\Windows\Microsoft.NET\Framework*\v*\Temporary ASP.NET Files” -Recurse | Remove-Item -Recurse

If you’re reading this and thinking, EWWWW Powershell. Don’t mock it until you try this single line command.

I saved the above command into a file names CleanUpASPNETTempFiles.ps1 and saved it to D:\Tools\. Name and save yours as you see fit.

To take a load off, you can schedule this script with a scheduled task. You can click on these screenshots if you need more detail:

(I opted to run on the first Saturday of the month. Use your own preferences.)

(My powershell.exe was at %SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe)

(Note you need to tick the option box to get to the next screenshot or load the properties on the newly created task.)

Execution Policy Errors

If you’re being told to sign your newly created script, you can either do that for high security environments or you can turn off the signing requirement. It’s great to have this option build into the language but in the typical environment I’ve found it a burden.

In a Powershell command window, type the following to check your current ExecutionPolicy:

Get-ExecutionPolicy

You can change it to Unrestricted by typing:

Set-ExecutionPolicy Unrestricted

A Few Notes

A reminder that files in these locations are normal. Don’t go over the top trying to clean them up.

It should be noted that while the files are in use by the web server, you will not be able to delete them – this is fine; the goal is to clean up unused files.

You should also be aware the next time the application fires up, on app pool start, the application will re-compile again. This may lead to a longer than average initial page load. If this is of concern, consider only removing files that are older than 30 days.

Of course, this script is provided as is. You should test it thoroughly in a test environment before use in production (though I certainly do).

16 Comments