The following issues are related to MS SQL Server.
1. A warning is displayed: "Shrinks tmpdb and db transaction log. Works only on MS SQL Server execution failed at date/time".
CAS displays yellow triangle with the following warning: “Shrinks tmpdb and db transaction log. Works only on MS SQL Server execution failed at 3/29/12 03:00”.
If the timeout for database shrinking is not long enough, you can increase the timeout and observe it for another day:
- Stop the Watchdog and CAS services using Service Manager.
- Modify the <installdir>\CAS\Config\tasks-100-hcbs.xml file.
- Increase the task timeout
<task ID="MsSqlShrinkDB" name="Shrinks tmpdb and db transaction log. Works only on MS SQL Server" periodType="DAY" period="1" timeLine="SERVER" offsetTime="03:00" timeout="05:00">
- Restart CAS and Watchdog.
If the DB user “sa” password which was stored in CAS is not correct:
- Make sure you can log in to the SQL Server (using SQL Management Studio) as a DB administrator, for example, the 'sa' user.
- Log in to CAS and select Tools -> Diagnostics -> Console from the menu, and type the following command in the command text box: SET DB SUPERUSER sa <current_sa_password>
Example: SET DB SUPERUSER sa passw0rd
- Click Execute to execute the command.
2. CAS or ADS database file size grew very large and is taking a lot of disk space.
The database reports that there's a lot of unused space in the database files. How to shrink a CAS or ADS database from the SQL Management Studio?
In order to reclaim the unused space in the CAS or ADS database, you need to shrink it. It is suggested to create a regular backup of the database before shrinking it. It is also recommended to shut down the corresponding report server for the DB (CAS or ADS) before doing the shrink as it may affect the performance and/or outcomes of this task.
To shrink the database:
- In the SQL Server Management Studio, right-click the database in question, then choose: Tasks -> Shrink -> Files.
- In the window, under the Shrink Action section, click Release unused space and click OK.
Sometimes, if the DB file is very large, the above procedure might take a long time or might not even release the intended space. In such case, instead of using the "Release unused space" option under the "Shrink Action" section, use the "reorganize pages before releasing unused space" and set the file size you want to "Shrink file to:".
3. What is Database Spanning and how might it affect CAS?
Database Spanning is a feature of MS SQL which is used to store or split a database across multiple physical or logical volumes (file groups). The CAS itself will not need special configuration to work with spanned databases, but CAS performance can be directly related to database performance. Signs of database performance impacting CAS performance usually presents first in the nightly maintenance tasks, especially the Daily Status Report task sets may begin to take too long and start to timeout. The main thing to be aware of regarding database spanning is Microsoft SQL configuration and implementation that could lead to i/o contention. For best database performance the TempDB, transaction log file storage, and CAS database would each be on separate disks. If the CAS database is spanned to another file group, it is best for the other file group to be on a different physical volume (not just e.g. partition on the same volume). It would be also be best if there were no other applications performing significant work upon that volume (again, to avoid i/o contention). You can check for disk queue growth, i/o performance, and the like by setting up probes from the Performance Manager on the SQL server itself. If a customer is asking about adding additional storage to their database by using database spanning, this is a potential red flag that they are about to grow their database significantly, and Support should inquire as to their plans in this regard.
4. How to see the SQL queries sent to the SQL Server?
The CAS admin, database administrator, or Dynatrace Support or Development would like to see what queries or commands are being sent to the SQL server.
In CAS or ADS, go to Tools -> Diagnostics -> Console.
The items that can be enabled related to the SQL query reporting are a subset of the features in that table.
Specifically, the items of interest are:
SQLDIAG PROFILE -
This builds a performance table, summarized per minute, of the SQL commands being run, the frequency the command was run, and the total time spent on a given command.
Once enabled, additional logging is stored in the SQL_profile_*.log file.
SQLDIAG SERVLET [servletName] -
Logs SQL commands done by the servlet and logs them to a file in the log directory.
** If you wished to see queries run by a servlet (outside the JVM), enable this feature.
This content is logged in the SQL_Trace_*.log file.
SQLDIAG TRACE [timePeriod] -
Starts trace logging of all SQL commands and logs them to a file in the log directory. The timePeriod parameter is in minutes. To have infinite trace, skip this parameter.
** Enable this feature to see all commands being sent to the SQL server, including those for Microsoft-specific maintenance tasks.
This content is logged in the SQL_Trace_*.log file.
SQLDIAG STATUS -
Shows SQL trace status (on or off). "ON" indicates that one of the listed SQLDIAG modes is enabled. Only one SQLDiag mode can be enabled at a given time.
SQLDIAG OFF -
Stops any enabled SQL diagnostic logging.
What to do next
If the need is to see what commands are being sent to start the Defragmentation of Indexes on Changed Tables task, you could enable SQLDIAG Profile or SQLDiag Trace and see in the relevant log file:
"ALTER INDEX Indexname ON TableName REORGANIZE/Rebuild"
Also, prior to preparing an Export Config for further troubleshooting by Dynatrace Support or Development teams, it is often helpful to first enable SQL Trace mode, repeat the circumstances that are related to the issue being investigated, and the resulting logs will be included in the ExportConfig log collection.