Fix: Unable to shrink file

You've cleared DB connections, CHECKPOINTed, took a backup, but still can't shrink a file.

Grow the file slightly, even just 1 MB.

Then try the shrink again.

Here's a script you can use: http://www.sqlservercentral.com/Forums/Topic647662-5-1.aspx#bm648809

Fix: SQL Server setup fails with error: "MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code" (GUID)

When running SQL Server setup, SQL Server setup fails with error: "MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code" which is followed by a GUID.

To fix this issue, registry entries (possibly from a previous aborted install) must be removed.

To fix, first make sure there is no installation running currently. If you have the above error on screen, close it, and exit SQL Server Setup entirely.

To locate the offending registry entries, search the registry for the first block of the GUID and remove any entries found. Then, search the registry again, except reverse the order of the first GUID block. Remove any entries found. I note that XML Parser v6 is cited when you locate the offending registry keys.

After these keys are removed you will be able to complete the install.

Thank you to Gaurang Patel for his blog post that helped me resolve this issue.

This was seen with SQL Server 2008 R2 Enterprise Edition Setup.

Fix: Cluster Available Storage not detected by SQL Server Setup

If you have shared storage showing available in Failover Cluster Manager but SQL Server Setup throws an error indicating there is no shared storage available, do the following to fix the problem:

Add the shared disks to another resource group (such as MSDTC).
Then perform the Remove From (Resource Group Name) action.

This returns the disk to Available Storage, and SQL Server Setup can now detect the shared volumes.

This was seen with SQL Server 2008 R2.

Fix: "Property IsLocked is not available for Login" error

Issue: SQL Server accounts (Non-AD) appear to no longer have database access. When attempting to open the account in the SSMS GUI, you receive the message:

"Property IsLocked is not available for Login '[Name_Of_Login]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)"

This message appears due to having the "Enforce Password Policy" box checked on a SQL Server (non-AD) account.

Here is T-SQL to fix the problem:


ALTER LOGIN [login]
WITH PASSWORD = 'password' UNLOCK
,CHECK_POLICY = OFF
,CHECK_EXPIRATION = OFF


When I saw this issue recently, more than one SQL Server instance (SQL Server 2008 R2) was affected simultaneously. They were in the same AD domain, but on different hosts. This leads me to believe it is AD related, though I've found no clear acknowledgment from Microsoft on the issue. The issue has been present since SQL Server 2005.

If you want to enforce AD password policies for SQL Server access, I recommend using an AD account for accessing SQL Server. You gain all the benefits of security / account management with AD, not the least of which is Kerberos authentication. Applications which are still passing plain text passwords to SQL Server in a connection string need to be updated to support AD Authentication. I encourage you to raise this concern with your application development team and support them as they update the apps.

Script all SQL Server jobs and more with SSMS: Object Explorer Details

For years I promptly closed Object Explorer Details when SSMS was launched. Seems this default-launch was changed with SQL 2008, probably due to other DBA’s like me who just wanted it out of the way. Real estate across the horizontal query tabs is in high demand as it is, and especially so if you have anything pinned vertically.

Then one fateful day, while on the hook to script out an epic quantity of jobs, I did some searching. Much to my surprise, Object Explorer Details has a purpose!

To open it in SSMS 2008, click on View \ Object Explorer Details (2005 you seemingly can’t escape it). For an example of how to use it, navigate to your jobs list in Object Explorer and see what you see:

Note that while you can’t select multiple jobs in Object Explorer, you can in the Object Explorer Details window. Select all the jobs you want to script, Right-click \ Script Job As, and you’ve saved yourself a bunch of time. Also you didn’t have to buy a third party tool to get it done. Win!

My Bio

I started (officially) in IT in ‘95. My 1^st job was to run a help desk ($6 / hr) for a start-up ISP, who was offering blazing fast 28k/56k dial up internet access. It was difficult walking people through configuring Windows 95 dial up networking, over the phone, so they could connect to the internet, while they weren’t quite sure what the internet was… So that’s one way to learn how to be patient with users!

Site Purpose

DBAs walk a line between Administrator and Developer. As an Administrator, a DBA makes decisions regarding I/O distribution and is often also a SAN Admin, installs software, manages permissions, and often acts as Server Admin as well. As a Developer, the DBA writes complex SQL code, does application design in the form of stored procedures, SSIS, SSRS, SSAS Development, performs unit & QA testing (which is often times the only testing for this sort of development), assesses application usage and performance characteristics, tunes applications, and so on.