Performance Considerations for ASPSTATE Database in SQL Server for DBAs

SQLServerF1

One of the database which may appear on multiple SQL Server instances in multiple environments is the database with name ASPSTATE and most often or not these are very small in size, but are very busy with user activity. Most of the times, DBAs are not really concerned with this ASPSTATE database and not much aware of the purpose of this database and if there are any considerations as a DBA to be aware of this database. However at times the performance and management of this database becomes critical for the application to function smoothly. Below are some of the considerations for ASPSTATE databases for DBAs to be aware of.

The ASPSTATE databases is generally very busy with lot of user activity which involves multiple DML or select operations. DML operations like Insert, Update and Delete are performed very frequently on this database which causes lot of write operations to the transaction log file, so it is better to keep the ASPSTATE database in SIMPLE recovery model which will simplify the management of the transaction log file.

By default all the user data is stored in tempdb, so it is important to configure the ASPSTATE database in such a way that the user data is created and stored in the ASPSTATE database itself. As mentioned before this database can have lot of DML and select operations, it is advisable to make sure that the data and transaction log files are on different RAID disks and good to monitor the disk response times where the data and log files reside. Any disk latency can cause significant slowness.

Generally it is possible to have multiple applications use same ASPSTATE database, but as a best practice it is not a good idea, instead always prefer dedicated database for each application which makes monitoring and management of the ASPSTATE database efficient.

The database tables and indexes may show high fragmentation and blocking due to multiple requests to access the tables. For fragmentation, performing index rebuilds and update stats once a day should be good, however it may not show enough benefits as the data keeps changing very frequently. Another idea is to make sure that auto-update stats is turned off. Generally the blocking is common to see against ASPSTATE database, but is mostly very short time blocks, however if the blocking happens more frequently, then action needs to be taken based on what the head blocker is doing.

Starting with SQL Server 2014, In-Memory OLTP technology can be used to make the database access much faster. There are other optimizations too related to deletes and blocking but that are mode dealt by the developers than DBAs. If requested as DBA you could propose any missing index which may be beneficial, but keep in mind that this need to tested throughly before implementing as it can cause negative impact as well.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Basics of ASPSTATE Database in SQL Server for DBAs

SQLServerF1

It is common for DBAs to notice databases with name ASPSTATE on multiple SQL Server instances and most often or not these are very small in size, but are very busy with user activity. Most of the times, DBAs are not really concerned with this ASPSTATE database and not much aware of the purpose of this database and if there are any considerations as a DBA to be aware of this database. Below are some basics about the purpose of this ASPSTATE database and some points for SQL Server Database Administrators to have some knowledge about this database which may turn helpful in future.

What is ASP.Net Session State?
ASP.NET session state allows to store and retrieve values for a user as the user navigates ASP.NET pages in a Web application. HTTP is a stateless protocol. This means that a Web server treats each HTTP request for a page as an independent request. The server retains no knowledge of variable values that were used during previous requests. ASP.NET session state identifies requests from the same browser during a limited time window as a session, and provides a way to persist variable values for the duration of that session. By default, ASP.NET session state is enabled for all ASP.NET applications.

In order to configure an ASP.NET application for ASP session state management, we need to configure the web.config file of the web application. There are different modes that are available for use to store the session state information as mentioned below.

OFF – In this mode the ASP session state is turned off for the web application.
InProc – In this mode ASP sessions are kept in memory on the web server. This is the default behavior.
StateServer – In this mode the session is stored in a separate process called the ASP.NET state service.
SQL Server – In this mode the session state is stored in SQL Server database generally named as ASPSTATE.
Custom – Developers can write their own custom mechanism or any other way that is readily available for the session storage mechanism.

This ASPState database is created by developers using aspnet_regsql.exe utility that comes with the .NET Framework. There are various parameters available for this utility to specify the instance name and other settings like authentication mode to connect to SQL instance, etc. ASPState database is used to store the stored procedures and functions required for session state management and by default all tables which store session state information is created in tempdb which will be lost after restart of the SQL Server instance. To avoid missing session state information even after restart of the SQL Server instance, there are various parameters passed to aspnet_regsql.exe utility which will create/store the tables which hold session state information in ASPSTATE database itself.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Oracle Database Errors or Warnings from Error ORA-00846 to ORA-00852

SQLServerF1

 

ORA-00846: could not shrink MEMORY_TARGET to specified value
Cause: Attempted to shrink MEMORY_TARGET to the specified value but did not succeed because the PGA and SGA components could not be shrunk.
Action: Do not set MEMORY_TARGET to a value below the current value without first shrinking the individual memory components.
ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set together
Cause: MEMORY_TARGET/MEMORY_MAX_TARGET was set to a non-zero value and LOCK_SGA was also set
Action: Do not set MEMORY_TARGET or MEMORY_MAX_TARGET if LOCK_SGA is set to TRUE.

ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET
Cause: STATISTICS_LEVEL of BASIC was incompatible with SGA_TARGET and MEMORY_TARGET.
Action: Fix the incompatible parameter setting. Set STATISTICS_LEVEL to TYPICAL or ALL to use SGA_TARGET or MEMORY_TARGET.
ORA-00849: SGA_TARGET string cannot be set to more than MEMORY_MAX_TARGET string.
Cause: SGA_TARGET value was more than MEMORY_MAX_TARGET value.
Action: Set SGA_TARGET to be less than MEMORY_MAX_TARGET.

ORA-00850: PGA_AGGREGATE_TARGET string cannot be set to more than MEMORY_MAX_TARGET string.
Cause: PGA_AGGREGATE_TARGET value was more than MEMORY_MAX_TARGET value.
Action: Set PGA_AGGREGATE_TARGET to be less than MEMORY_MAX_TARGET.
ORA-00851: SGA_MAX_SIZE string cannot be set to more than MEMORY_TARGET string.
Cause: SGA_MAX_SIZE value was more than MEMORY_TARGET value.
Action: Set SGA_MAX_SIZE to be less than MEMORY_TARGET.
ORA-00852: Rolling migration monitor process terminated.
Cause: The ASM rolling migration monitor process died.
Action: Warm start the instance.

Above are list of Oracle Database Errors or Warnings from Error ORA-00846 to ORA-00852 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 

Oracle Database Errors or Warnings from Error ORA-00820 to ORA-00830

SQLServerF1

 

ORA-00820: Specified value of sga_max_size is too small, needs to be at least stringM
Cause: The specified value of sga_max_size is too small for the SGA to accommodate all of the necessary SGA components such as the log buffer, buffer pools, shared pool, etc.
Action: Set sga_max_size to the recommended value or reduce the values of any SGA component size parameters you have specified.
ORA-00821: Specified value of sga_target stringM is too small, needs to be at least stringM
Cause: The specified value of sga_target is too small for the SGA to accommodate all of the necessary SGA components such as the log buffer, buffer pools, shared pool, etc.
Action: Set sga_target to the recommended value or reduce the values of any SGA component size parameters you have specified.
ORA-00822: MMAN process terminated with error
Cause: The Memory Management process died.
Action: Warm start instance

ORA-00823: Specified value of sga_target greater than sga_max_size
Cause: The specified value of sga_target is greater than sga_max_size.
Action: Increase sga_max_size to match up with sga_target or decrease sga_target to match up with sga_maxsize.
ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings
Cause: Could not set SGA_TARGET or MEMORY_TARGET due to current parameter settings.
Action: Check the other error messages for additional information.
ORA-00825: cannot set DB_BLOCK_BUFFERS if SGA_TARGET or MEMORY_TARGET is set
Cause: SGA_TARGET or MEMORY_TARGET set with DB_BLOCK_BUFFERS set.
Action: Do not set SGA_TARGET, MEMORY_TARGET or use new cache parameters, and do not use DB_BLOCK_BUFFERS which is an old cache parameter.

ORA-00826: cannot set SGA_TARGET or MEMORY_TARGET for an ASM instance
Cause: SGA_TARGET or MEMORY_TARGET set for an ASM instance.
Action: Do not set SGA_TARGET or MEMORY_TARGET.
ORA-00827: could not shrink sga_target to specified value
Cause: Attempted to shrink the SGA to the specified value but did not succeed because the SGA components could not be shrunk as they were already at their minimum sizes.
Action: Do not set sga_target to a value below the current value without first shrinking the individual SGA components.
ORA-00828: specified value of shared_pool_reserved_size inconsistent with internal settings
Cause: Unable to set shared_pool_reserved_size to specified value if SGA_TARGET set, either because the specified value is too small, or because it is too large for the current internal size of shared pool. More details can be found in the alert log.
Action: If possible, do not set shared_pool_reserved_size without setting shared_pool_size if SGA_TARGET set. Examine the alert log for information about current internal size of shared pool, and valid range of values for shared_pool_reserved_size.
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled
Cause: The user attempted to set statistics_level to BASIC with auto-tune SGA enabled which cannot be done because auto-tune SGA cannot work with statistics_level set to BASIC.
Action: Disable auto-tune SGA and try setting the statistics_level to BASIC again.

Above are list of Oracle Database Errors or Warnings from Error ORA-00820 to ORA-00830 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 

Oracle Database Errors or Warnings from Error ORA-00740 to ORA-00752

SQLServerF1

 

ORA-00740: datafile size of (string) blocks exceeds maximum file size
Cause: The user specified datafile size exceeded maximum file size.
Action: Please check REFERENCE for maximum size. Reduce the size and retry.

ORA-00741: logfile size of (string) blocks exceeds maximum logfile size
Cause: The user specified logfile size exceeded maximum logfile size.
Action: Please check REFERENCE for maximum size. Reduce the size and retry.
ORA-00742: Log read detects lost write in thread number sequence number block number
Cause: Either a write issued by Oracle was lost by the underlying operating system or storage system or an Oracle internal error occurred.
Action: The trace file shows the lost write location. Dump the problematic log file to see whether it is a real lost write. Contact Oracle Support Services.

ORA-00750: database has been previously mounted and dismounted
Cause: The instance has already mounted and dismounted the database, which is only allowed once in its lifetime.
Action: Shut down the database.
ORA-00751: could not obtain resilvering status for file
Cause: An error occurred while obtaining the mirror resilvering status of a file.
Action: Refer to other error messages shown for additional details of the problem.
ORA-00752: recovery detected a lost write of a data block
Cause: A write of a data block to storage was lost during normal database operation on the primary database.
Action: Shutdown the primary database and activate the physical standby database to failover. See the Data Guard Concepts and Administration guide for details.

Above are list of Oracle Database Errors or Warnings from Error ORA-00740 to ORA-00752 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 

Oracle Database Errors or Warnings from Error ORA-00711 to ORA-00725

SQLServerF1

 

ORA-00711: new tablespace name is invalid
Cause: An attempt to rename a tablespace failed because the new name is invalid.
Action: Choose a valid new name and retry the command.
ORA-00712: cannot rename system tablespace
Cause: An attempt to rename the system tablespace failed.
Action: No action required.

ORA-00720: ALTER DATABASE RESET COMPATIBILITY command has been de-supported
Cause: ALTER DATABASE RESET COMPATIBILITY command has been de-supported since Oracle 10i.
Action: No action required.
ORA-00721: changes by release string cannot be used by release string
Cause: An attempt to import a tablespace failed because the tablespace contains changes that require a newer software release or that violate the compatibility parameters.
Action: Use a version of the software that can understand the changes or relax the compatibility requirements in the initialization parameter file.
ORA-00722: Feature “string”
Cause: Reporting name of the feature for details of another error.
Action: See associated error message.

ORA-00723: Initialization parameter COMPATIBLE must be explicitly set
Cause: Oracle detected that the initialization parameter COMPATIBLE was not explicitly specified, and the compatibility of the database is lower than the default value of the COMPATIBLE parameter. In order to use the new compatible setting, the intialization parameter must be explicitly set by the user.
Action: Explicitly set the value of COMPATIBLE parameter either in PFILE or SPFILE, whichever is used.
ORA-00724: ALTER DATABASE CONVERT command has been de-supported
Cause: ALTER DATABASE CONVERT command has been de-supported since Oracle 10i.
Action: No action required.
ORA-00725: Desupported ALTER DATABASE SET STANDBY clause specified: string
Cause: A deprecated ALTER DATABASE SET STANDBY was specified.
Action: No action required.

Above are list of Oracle Database Errors or Warnings from Error ORA-00711 to ORA-00725 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 

Oracle Database Errors or Warnings from Error ORA-00700 to ORA-00710

SQLServerF1

 

ORA-00700: soft internal error, arguments: [string], [string], [string], [string], [string], [string], [string], [string], [string], [string], [string], [string]
Cause: Internal inconsistency that will not crash a process
Action: Report as a bug – the first argument is the internal error.
ORA-00701: object necessary for warmstarting database cannot be altered
Cause: Attempt to alter or drop a database object (table, cluster, or index) which are needed for warmstarting the database.
Action: None

ORA-00702: bootstrap verison ‘string’ inconsistent with version ‘string’
Cause: The reading version of the boostrap is incompatible with the current bootstrap version.
Action: Restore a version of the software that is compatible with this bootstrap version.
ORA-00703: maximum number of row cache instance locks exceeded
Cause: There are not enough row cache enqueues.
Action: Increase the row cache enqueue parameter and warm start the system.

ORA-00704: bootstrap process failure
Cause: Failure in processing bootstrap data – see accompanying error.
Action: Contact your customer support representative.
ORA-00705: inconsistent state during start up; shut down the instance, then restart it
Cause: A previous attempt to start an instance was terminated.
Action: Shut down the instance completely, then restart it.
ORA-00706: error changing format of file ‘string’
Cause: An attempt to change the block0 format of the specified file failed because the file is read-only or offline.
Action: Make the file read-write or bring the file online and set the BLK0_FMTCHG event.
ORA-00710: new tablespace name is the same as the old tablespace name
Cause: An attempt to rename a tablespace failed because the new name is the same as the old name.
Action: No action required.

Above are list of Oracle Database Errors or Warnings from Error ORA-00700 to ORA-00710 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 

Oracle Database Errors or Warnings from Error ORA-00601 to ORA-00610

SQLServerF1

 

ORA-00601: cleanup lock conflict
Cause: PMON process runs into lock conflict trying to recovery processes
Action: This is trapped internally, no action necessary
ORA-00602: internal programming exception
Cause: Internal programming exception
Action: Report as bug

ORA-00603: ORACLE server session terminated by fatal error
Cause: An ORACLE server session was in an unrecoverable state.
Action: Login to ORACLE again so a new server session will be created
ORA-00604: error occurred at recursive SQL level string
Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).
Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.
ORA-00606: Internal error code
Cause: A call to deferred upi functions was made in non deferred mode
Action: Report as a bug.

ORA-00607: Internal error occurred while making a change to a data block
Cause: An internal error or memory exception occurred while Oracle was applying redo to a data block.
Action: call Oracle Support
ORA-00608: testing error [string] [string] [string] [string] [string]
Cause: Internal error reserved for testing.
Action: call Oracle Support
ORA-00609: could not attach to incoming connection
Cause: Oracle process could not answer incoming connection
Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.
ORA-00610: Internal error code
Cause: Oracle process started too late
Action: This error should never be seen by the customer. Contact Oraclce Support

Above are list of Oracle Database Errors or Warnings from Error ORA-00601 to ORA-00610 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 

Oracle Database Errors or Warnings from Error ORA-00566 to ORA-00600

SQLServerF1

 

ORA-00566: cannot request processor group – NUMA not enabled
Cause: Cannot start process in a requested processor group when the NUMA feature is disabled.
Action: Start the process without requesting a NUMA processor group.
ORA-00567: Requested processor group string is too large (maximum string)
Cause: The process could not be started in the requested processor group.
Action: Start the process in another processor group.

ORA-00568: Maximum number of interrupt handlers exceeded
Cause: User specified too many ^c handlers
Action: Remove some old handlers.
ORA-00569: Failed to acquire global enqueue.
Cause: A prior error occurred on one of the instances in the cluster. Typically errors are caused by shared pool resource contention.
Action: Check for and resolve prior errors on all instances in the cluster. If there is shared pool resource contention, increase the SHARED_POOL_SIZE, DML_LOCKS, PROCESSES, TRANSACTIONS, CLUSTER_DATABASE_INSTANCES and PARALLEL_MAX_SERVERS initialization parameters.

ORA-00600: internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string], [string], [string], [string], [string]
Cause: This is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition. The first argument is the internal message number. This argument and the database version number are critical in identifying the root cause and the potential impact to your system.
Action: Visit My Oracle Support to access the ORA-00600 Lookup tool (reference Note 600.1) for more information regarding the specific ORA-00600 error encountered. An Incident has been created for this error in the Automatic Diagnostic Repository (ADR). When logging a service request, use the Incident Packaging Service (IPS) from the Support Workbench or the ADR Command Interpreter (ADRCI) to automatically package the relevant trace information (reference My Oracle Support Note 411.1). The following information should also be gathered to help determine the root cause:
– changes leading up to the error

– events or unusual circumstances leading up to the error

– operations attempted prior to the error

– conditions of the operating system and databases at the time of the error Note: The cause of this message may manifest itself as different errors at different times. Be aware of the history of errors that occurred before this internal error.

Above are list of Oracle Database Errors or Warnings from Error ORA-00566 to ORA-00600 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 
1 2