Tech Short: TSQL to find Consumed Memory

Poking around in Microsoft SQL Server looking for a way to give me some statistics on memory usage, primarily consumed memory.

My first attempt was using ‘dbcc memorystatus’.  This gives me a good snapshot of the current memory status of the SQL Server. It will take me sometime to fully understand and parse the output.

So I kept looking for more details on how to show the consumed memory. And that is where I found the following query that gives me just what I am looking for.


Declare @MemTable Table
( row_id INT IDENTITY PRIMARY KEY, name VARCHAR(100), value BIGINT );

SELECT top 1 Name,convert(decimal(16,2),value) / 1024/1024 ‘MBUsed’
FROM @MemTable
WHERE RTRIM(LTRIM(name)) = ‘Working Set’;

So if your looking for just the working set here you go

Thank you Jason Brimhall for the tip

O365: Forefront Identity Manager & Office 365 DirSync Failing

I encountered an issue where both Forefront Identity Manager and Office 365 DirSync both failed to start.

My investigation of this after I received an email from @MicrosoftOnline.com which had informed me that Windows Azure Active Directory did not register a synchronization attempt from the Directory Sync tool.


I attempted to do was start the Microsoft Online Services Directory Synchronization Service. This had failed because depends on Forefront Identity Manager Synchronization Service which was also no longer starting.


I attempted to start the Forefront Identity Manager Synchronization Service this failed with the following message:

Verify that the service account has permissions to the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Forefront Identity Manager\2010\Synchronization Service

If the problem persists, run setup and restore the encryption keys from backup.


After my verification I attempted I uninstalled the Office 365 DirSync along with Forefront Identity Manager and SQL which were all installed.  This time around I unable to even install the Office 365 DirSync

All three of my attempts had failed.

So what changed?

I rebooted the system; and after it had resumed the services which worked seemed to no longer function.

Then it *clicked* after much investigation and review.  The question I did not ask.  Could Office 365 exist on the same system that’s also running ADFS.  I soon found out the answer is ” *NO* “.

The Directory Synchronization tool cannot be installed on Active Directory Federation Service.

So I uninstalled the Office 365 DirSync, along with SQL. Followed by the removal of the ADFS Role from the server.

After the restart I installed the Office 365 DirSync again and configured it as I have done before and all is working once again.

And now I and you all know 

I hope this post help you and saves you some time.  I spent a day working on this and waiting for Microsoft to call me.  I seems like I have resolved this issue on my own; once again.


If your using Office 365 DirSync do NOT enable the ADFS Role if you do, you run the sure chance of breaking your working Office 365 DirSync.

Environment: Windows Server 2012 R2 Update 1 (x64)

– Jermal

SQL Report Server ERROR: The number of requests for domain\jermsmit has exceeded the maximum number allowed for a single user

It’s always simple when you see a error message you know.  This time its with SQL Server Report Server.  Error Message: The number of requests for domain\jermsmit  has exceeded the maximum number allowed for a single user

What does that mean?

Well to keep it simple.  The user is running a report which requires far more request that the report server is configured to handle.

So what do you do about this?

One thing would be to isolate the issue and find out why so many requests are being made and perhaps re/wright the report.  Hahaa! Joke!  That doesn’t happen often now does it.

So what will happen is this:

On the report server navigate to the following path:  C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer  *your install path may be different*

Locate the XML configuration file rsreportserver.config

Open the configuration file with notepad.

Press Crtl F and search for MaxActiveReqForOneUser you may get a result such as ‘<Add Key=”MaxActiveReqForOneUser” Value=”20″/>

Change the value to the number of request being made to remove this issue.  Then again if you didn’t correct the above throw some crazy value to it like 256 and call it a day.

What the trade off?

You just use more system resources:  cpu, memory.

More details

Being that these type of request should happen quickly; this may be a sign that something is hogging CPU and SQL time running reports. You should evaluate this rather than just tossing resources at the issue.

Ref:  RSReportServer Configuration File

– Jermal


Remove database from read only mode, error 5120

I posted about setting a Microsoft SQL Database into ‘read only’ mode as part of an upgrade. Today I need to go back and make some changes on the now legacy system.

To handle this request I needed to change back to its normal state, this is when I encountered the error 5120.

In the additional information of this error it tells me that an exception occurred while executing a Transact-SQL statement or batch.

Later indicating that it was unable to open the physical file to the database in question due to an “Operating system error 5”

Have fun searching for that *hint* you will not find any of the top 3 links taking you to any Microsoft Site; that’s for sure.  {{annoyed}}

Other errors indicated where insufficient memory or disk space; thanks again {{annoyed}} for sending me  down the wrong path.  I have plenty of memory, and disk space.

The last one was that I did not have permissions to the files. Seriously, I do have permissions. In most cases someone who’s doing this work does in the first place

So I found out what the issue was in my case. Here is a screenshot:

Yup! The files were set to read-only. Once this attribute was removed I was able to change my database status from read-only to read-write.

I hope this post helps you if in similar situation.

– Jermal

Set SharePoint Content Database in Read-Only Mode

This weekend I am working on a SharePoint 2010 to 2013 upgrade.

One of the steps; the beginning steps I will do is set the databases I am upgrading into read only mode to prevent users or automated process from upgrading the database during the upgrade window.

Now I could have taken the system offline for this; but that would be simple and inconvenient for anyone looking for information.  And in IT its all about the Information now isn’t it. You can find my steps below.

SharePoint Application Server

  • I first logged into my central administration of my SharePoint Server (SharePoint 2010).
  • Under Application Management, Select Manage content databases
  • Chose your web application  and select the database
  • You will notice that there isn’t a location to set this database into read only mode; however you do see that its status indicates its not in read only mode.
  • Keep this page open, as we will reload it later.

SharePoing SQL Database Server

  • Log into your Microsoft SQL Database Server (Or use SSMS).
  • Launch SQL Server Management Studio (SSMS)
  • Connect to the SQL Database Server instance
  • Select the database in question
  • Right click and select Properties
  • Under properties select Options
  • Scroll down to the area that reads “State”
  • You will see Database Read-Only
  • Change the value from False to True, then click OK
  • You will see a notification that it will momentary kick active users.
  • Click OK to continue.

Now you can revisit the SharePoint Application Server and reload the page and you will now notice that the status has changed and is in read only mode.

Congrats, if you followed these steps you did it right.

Uninstall of SQL fails with error about RsFx Driver

I was working on uninstalling installations of SQL 2012 and SQL 2008 R2 to later do a clean install of SQL 2012. All seems to be going relatively smooth until I encountered the following message:

Warning 26003. Microsoft SQL Server 2008 R2 Setup
Support Files cannot be uninstalled because the
following products are installed:
Microsoft SQL Server 2008 R2 RsFx Driver

Understandable; So I will uninstall using Control Panel\Programs\Programs and Features (Add/Remove for the old school). However the item wasn’t listed. After searching on how to do this using the registry I found a simpler way, I am now calling it a clean and #jermsmit supported way to doing this.

Steps to correct this issue:

We need to obtain the product GUID from WMI using WMIC so that we can use the MSIEXEC /X {GUID} command to remove the software.

Open a command prompt as administrator and type: WMIC PRODUCT LIST to get a list of products and the GUID associated with them. I found using the following command works best: WMIC PRODUCT GET Caption, IdentifyingNumber > c:\info.txt 

Once you have the GUID of the software you want to uninstall you simply type: MSIEXEC /X {GUID}

Software is now uninstalled and you can proceed.

*note* the steps provided can be used for any software you are looking to uninstall that is not listed under Add/Remove programs. Just follow the steps as such:

  1. Get the product GUID from WMI (Win32_Product class)
  2. Find the GUID related to the product or products that the SQL error mentions
  3. Run MSIEXEC /X {GUID} for each of the products to uninstall them manually.


I hope you enjoyed this post
Please visit me on Facebook @ http://www.facebook.com/jermsmitcom & via twitter: #jermsmit

restore sql server database from mdf file

  1. First Open SQL Server Management Studio (SSMS). You may need to run as Administrator
  2. Right click the Databases folder and select attach from the menu.
  3. Click Add and select the mdf file. Click Ok, and then Click Ok again. You will get an error at this time because SSMS could not find the corresponding ldf file.
  4. Select the ldf file entry and Click Remove and then Click OK
  5. And that’s all folks


SQL Server Database Error: 661

One of my customers had an issue where they failed to open a connection to a particular database they had been testing. The error read: ‘the database e:somepathsomefolderjermsmit.mdf’ cannot be opened because it is version 611. The server supports version 655 and earlier. A downgrade path is not supported.

This issue is caused because you are not permitted to use a database from a later release on an earlier system. i.e they are not backwards compatible

Problem: Databases created under a newer version of SQL Server are not compatible with previous versions of SQL Server

Solution(s) that I am aware of in this case:

  • You need to either upgrade your SQL Server instance to the same version as the database or one newer
  • Or copy out the data in that database into an earlier version (using the data migration wizard perhaps)

If you need to know the version of your source and destination databases use the following query:


The destination should be the same level or greater in in version.

Best of luck to ya

SQL Server backup in multiple parts

“It’s so logical and so simple. Fat is the backup fuel system. The role it plays in the body is that when there’s no carbohydrate around, fat will become the primary energy fuel. That’s pretty well known.” – Robert Atkins

Recently I have been working with my dB Administrator to get some DR backups to our offsite location. Simple task made difficult with a few challenges: Size of the database, Bandwidth to transfer to remote site, and method of transfer. We got past part of the size issues by compressing the SQL database backups, but we still had issues getting the files to the remote site in time. So we switched from using Microsoft DFSR to using cygwin and Rsync. However there was another issue at play; We could never seem to get the full backup there in time.

Out bottle neck at this point was the available bandwidth needed to transfer and the files. The transfer would break and well it needed to do a checksum on the file to resume. At 200GB into it all this process to a considerable amount of time to only again fail.

That is when I got creative and suggested to use SQL to span/split the backup into multiple parts so that in the event the transfer would break and resume it only had to do some from just a single part of the total sum of data. You know what?  It works. It’s been 2 weeks now and our backups reach the remote site.

And here is what you can do the split up your backups:

And now the backup is split into equal parts of the original backup file size. In my production environment we split our compressed database into 64 parts giving us about 4GB per file.

I hope this helps you,

– Jermal

Identify Microsoft SQL Server version & edition

I wanted to determine which version of Microsoft SQL Server 2008 was running on one of my servers. So I opened up SQL Server Management Studio (SSMS), and ran the following command as a new query window:

SELECT SERVERPROPERTY (‘productversion’),
SERVERPROPERTY (‘productlevel’),

The following results are returned:
-The product version (for example, 10.0.1600.22)
-The product level (for example, RTM)
-The edition (for example, Standard Edition 64-bit)