Archive for the ‘SQL 2012 R2’ Category

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!!

RESDB_01    RESDB_02    RESDB_03

RESDB_04    RESDB_05    RESDB_06

 

 

 

How to: change database collation in SQL 2012 R2

When you default install an SQL 2012 R2 server, the database collation is “Latin1_General_CI_AS“. But if you want to install System Center Configuration Manager, one of the prerequisites is that the database collation is “SQL_Latin1_General_CP1_CI_AS“. My SQL server has only one instance, so I had to change the database collation. There are no databases configured yet, so no problem for me at all.

See also the following URL:
http://technet.microsoft.com/en-US/library/ms179254(v=sql.105).aspx

1.) Make sure you backup all your data!
2.) Attach the SQL 2012 R2 ISO
3.) Run the command prompt as administrator
4.) Type the following command
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts /SAPWD= StrongPassword
/SQLCOLLATION=CollationName
4.) After the setup has finished, the database collation has changed. You’re now ready to rock!

2014-01-17_09h28_36    2014-01-17_09h29_07    2014-01-17_09h32_09