SQL Archives - MARKSWINKELS.NL

How to check what features are installed on your SQL environment

When you need to know more information about your SQL environment or cluster, you can check every SQL server separate from each other. This is time consuming to do, definitely if you have a big SQL cluster or environment.

You can use also automate this steps, to use this very simple command!

Go to your SQL environment and browse to the following directory (it depends per SQL version)
* SQL Server 2012 (110)
* SQL Server 2014 (120)
* SQL Server 2016 (130)
* SQL Server 2017 (140)
* SQL Server 2019 (150)
’%programfiles%\Microsoft SQL Server\110\Setup Bootstrap\SQL2012” from the command prompt. Run the command:
’setup.exe /ACTION=RunDiscovery’

By default, there will be created a new file called ‘Summary.txt’, with the configuration of your SQL environment, cluster or AOAG if you’re using Always-On Availability Groups.. Not very usefull and readable. Not very readable, so now Powershell comes in!

Start Powershell and run the following command:
Get-Content “<location of your summary.txt file>. Now you get a report!!

How to: View all packages within your ConfigMgr 2012 R2 environment

When you have a Microsoft ConfigMgr 2012 or 2012 R2 environment, you definitely have a lot of packages and applications. This could be: tools, scripts, client packages, boot images, ISO’s, WIM files, different software applications, etc. When you have no idea where all these source files are located, you can execute a query within your SQL environment.

You can generate an overview of all your packages, with the package ID, description, name, source location, version, etc. Very helpfull and it saves you alot of time!!

1.) Open the Microsoft SQL Server Managent Studio
2.) Login with a user that has enough rights to execute queries
3.) Select the ConfigMgr database (in my example CM_PS1)
4.) Select “New Query”
5.) Type “SELECT * from v_Package”
6.) Select “Execute” or press F5 on your keyboard
7.) Now you’ll see a list off all the packages within your ConfigMgr 2012 environment
8.) Navigate to the table “PkgSourcePath”
9.) Here are your source files locates of all the different packages

Why is my RES Workspace Manager database growing that fast….??

Last week I was troubleshooting a RES Workspace Manager 2012 environment by one of our customers. A couple of times in one week, the datadisk of the SQL server was full, so at that time the SQL environment was not running anymore. The database of RES Workspace Manager was growing that fast….but why??

When looking for the actually size of the database, I saw that the size is just above the 20 GB. That’s pretty big if you ask me! The biggest data is the “logging” section. Okay…but which logging?? Sinds a few years, there’s a great tool available called DBLogCleanup. This tool has been written by Patrick van Grinsven and is extremely usefull for troubleshooting this kind of stuff.

http://resguru.com/2014/02/new-utility-db-cleanup-tool/

Just run the tool, make a connection to your SQL environment and select the RES Workspace Manager database. At the log section, select “User Settings Sampling mode data“. As you can see, there were over the 16 million items in the database! The process iexplore.exe with the AppGUID 02222….6862 is growing realy fast!! So there’s a lot of User Sampling data in the database. After deleting the sampling data, the size of the database was 10 GB smaller.

1.) Run the DBLogCleanup Tool
2.) Fill in the SQL server, database, user and password
3.) Select the section you want to view
4.) Browse through the processes and note the AppGUID
5.) Open your RES Workspace Manager Console
6.) Navigate to your applications and find the specific AppGUID
7.) Open the properties of the application
8.) Navigate to User Settings and open the Sampling tab
9.) In the right corner hit “clear all sampled data”
10.) Make sure you turn off the sampling mode for that application

Conclusion: make sure you do not use sampling mode for a very long time! All the sampled data is stored in your database, so the database is growing very fast!!