Suspect Database Issue Due to MSDTC Orphan Transaction

SQLServerF1

It is not strange to see different kinds of issues after restart of SQL Server instance or reboot of a server or after failover of cluster SQL Server instances. Below is one of the issue you may encounter due to MSDTC service orphan transaction causing the database recovery to fail after a restart of the server. Below are the errors you may see in the SQL Server errorlog.

Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
SQL Server detected a DTC/KTM in-doubt transaction with UOW {995D82F2-3C9B-4249-C827-B33FC42BAD82}.Please resolve it following the guideline for Troubleshooting DTC Transactions.
Error: 3437, Severity: 21, State: 3.
An error occurred while recovering database ‘TestDB’. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:-1647710395). Fix MS DTC, and run recovery again.
Error: 3414, Severity: 21, State: 2.
An error occurred during recovery, preventing the database ‘TestDB’ (database ID 6) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

– If you run query against sys.sysdatabases, you will see that the status of the database is shows as suspect. Also suspect status is shown for the database under SSMS GUI.
– This issue happens after restart of server and SQL Server is trying to recover a database which has a incomplete transaction related to linked server or a remote query which uses MSDTC.
– Once the server is rebooted, SQL Server started and tried to start and recover all databases as usual and it found a open transaction in TestDB which was a linked server query using MSDTC, but the MSDTC service was not yet started resulting in failure to recover the transaction.
– To resolve the issue, DBA has to start the MSDTC service, if it is not yet started.
– If MSDTC service has started later, then DBA has to recover and bring the database online by running the below query
Alter database DBName SET ONLINE

Hope this was helpful.

This is applicable for below versions of SQL Server

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

Thanks,

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

 

Quick Health Check After Restart of SQL Server Instance

SQLServerF1

As a DBA one of the roles is to verify, check and monitor critical or important SQL Server instances after restart or cluster failover or reboot of the server. Sometimes it is possible that there are some problems after the server is rebooted or SQL instance is restarted or failover is performed, so a quick check should verify to make sure nothing is wrong or broken. Also, sometimes there are unplanned server reboots or cluster failovers or SQL instance restarts, so keeping the items which are to be monitored after the restarts will be good.

Also, in many environments there are automated monitoring tools which detect any problems, but for critical servers, it is always good to manually have a quick health check to make sure there are no issues. This is because sometimes automated monitoring tools may not start properly or does not detect the problems due to bugs or other issues, so checking critical servers manually is always a good choice. Below are some of the important items which you may want to consider checking after the restart of SQL Server instance, either planned or unplanned.

– Verify the Database Status to make sure all the databases have successfully recovered and shows online status.
– On cluster servers, if you have configured Preferred owners for any SQL Server instance, make sure the SQL Server instances are running on node they are expected to run on.
– There may be jobs which might be running during the time of restart, they would have failed in middly, so it is good to check for any failed jobs and restart them if necessary.
– If there is database mirroring setup, monitor the Mirroring Status to make sure it caught up and is not in suspended state.
– Starting SQL Server 2012, if AlwaysON AG is setup, then make sure Availability Group Status is synchronized and there are no failures and databases or AG not in resolving state.
– If replication has been setup, verify and make sure Replication is functioning fine and all jobs are running as expected.
– Check SQL Server errorlog and SQL Agent log log make sure there are no errors.
– Check Event Viewer application and system logs to make sure there are no errors.

There may be other items to check as well depending on the features used or enabled on the SQL Server instance like Audits, CDC, MDW, Policy management, etc, so list down the features implemented and prepare the list specific to the servers.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

Thanks,

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

 

Helpful Basics of SQL Server Logshipping

SQLServerF1

SQL Server Log logshipping permits you to consequently send exchange log reinforcements from an essential database on an essential server case to one or more optional databases on discrete auxiliary server examples. The exchange log reinforcements are connected to each of the auxiliary databases independently. A discretionary third server occasion, known as the screen server, records the history and status of reinforcement and restore operations and, alternatively, raises alarms if these operations neglect to happen as booked.

SQL Server Log transportation permits you to consequently send exchange log reinforcements from an essential database on an essential server case to one or more optional databases on discrete auxiliary server examples. The exchange log reinforcements are connected to each of the auxiliary databases independently. A discretionary third server occasion, known as the screen server, records the history and status of reinforcement and restore operations and, alternatively, raises alarms if these operations neglect to happen as booked.

Log transportation comprises of three operations:
Go down the exchange log at the essential server occurrence.
Duplicate the exchange log document to the optional server occurrence.
Restore the log reinforcement on the optional server occurrence.

The log can be dispatched to various optional server cases. In such cases, operations 2 and 3 are copied for every optional server occurrence. A log shipping arrangement does not naturally fall flat over from the essential server to the auxiliary server. In the event that the essential database gets to be inaccessible, any of the auxiliary databases can be brought online physically. You can utilize an optional database for reporting purposes.

Primary server – The case of SQL Server that is your creation server.
essential database – The database on the essential server that you need to move down to another server. All organization of the log shipping setup through SQL Server Management Studio is performed from the essential database.
auxiliary server – The case of SQL Server where you need to keep a warm standby duplicate of your essential database.
auxiliary database – The warm standby duplicate of the essential database. The auxiliary database may be in either the RECOVERING state or the STANDBY state, which leaves the database accessible for constrained read-just get to.
screen server A discretionary example of SQL Server that tracks the majority of the points of interest of log delivery. Furthermore, you can design alarms for your log shipping arrangement.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

Thanks,

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

 

New SQL Server 2014 Hybrid Cloud

Most applications have some key difficulties, for example, high productivity, business esteem, complex equipment arrangements, monstrous crests on interest, and agreeing to industry and corporate regulations. Considering every one of these components and building an undertaking evaluation innovation can be extremely difficult. Microsoft Hybrid Cloud Strategy gives backing to customary, private cloud, open cloud, and cross breed cloud situations to beat these key difficulties.

At the point when your business obliges an adaptable IT foundation that can scale on interest, you can assemble a private cloud in your server farm or an open cloud in Windows Azure worldwide server farms. When you extend your server farm to meet general society cloud, you construct a mixture cloud model.

By utilizing Microsoft advancements, you can run code both on-premises and in the cloud, run in the cloud utilizing on-premises information, or run totally in the cloud utilizing more than one server farm. In this way, you can move your applications to the cloud at your own particular pace while protecting the benefit of existing legacy IT speculations.

There are half breed cloud situations that compass from on-premises SQL Server to Windows Azure open cloud offerings: SQL Server in Windows Azure Virtual Machines and Windows Azure Storage. Particularly which are

Backup and Restore Databases to/from Windows Azure Storage – A standout amongst the most major chairman assignments is going down and restoring databases. With SQL Server and Windows Azure, you can securely reinforcement your databases in the cloud.
Maintain Database Replicas on Windows Azure Virtual Machines – Having a stable catastrophe recuperation answer for your databases is fundamental for your business’ prosperity. Most clients need to design a catastrophe recuperation site and buy extra equipment for database copies. With SQL Server and Windows Azure, you can keep up one or more copies of your databases in the cloud.
Store SQL Server Data Files in Windows Azure Storage – Putting away on-premises SQL Server information documents in Windows Azure Storage gives an adaptable, solid, and boundless off-site stockpiling for your databases. Beginning with SQL Server 2014, you can utilize another highlight, SQL Server Data Files in Windows Azure, to store SQL Server database records in Windows Azure Storage. With this highlight, you can move information and log documents from on-premises database into Windows Azure Storage, while keeping the register hub of SQL Server running on-premises. This highlight empowers you to have boundless capacity limit in Windows Azure Storage.
Migrate existing SQL Server databases to Windows Azure Virtual Machines – The distributed computing conveys some key advantages to endeavors, for example, boundless virtualized assets are accessible for you on a pay-every utilization premise, you can influence openly accessible cloud server farms instead of building out and oversee server farms all alone, and consequently you can lower IT and equipment costs.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

Thanks,

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

 

List of SQL Server 2014 Enterprise Edition Features

There are some new features introduced with SQL Server 2014 and some are being changed to support advanced functions. Below are some of the top SQL Server 2014 features supported by SQL Server 2014 Enterprise Edition, Evaluation Edition, Developer Edition, Business Intelligence Edition.

Server Core support
Log Shipping
Database mirroring
Backup compression
Database snapshot
Alwayson Failover Cluster Instances
AlwaysOn Availability Groups
Connection Director
Online page and file restore
Online indexing
Online schema change

Fast recovery
Mirrored backups
Hot Add Memory and CPU
Database Recovery Advisor
Encrypted Backup
Smart Backup
Multi-instance support
Table and index partitioning
Data compression
Resource Governor
Partition Table Parallelism
Multiple Filestream containers
NUMA Aware Large Page Memory and Buffer Array Allocation
Buffer Pool Extension
IO Resource Governance
In-Memory OLTP
Delayed durability
Basic Auditing
Fine Grained Auditing
Transparent database encryption
Extensible Key Management
User-Defined Roles
Contained Databases
Encryption for Backups
SQL Server change tracking
Merge replication
Transactional replication
Snapshot replication
Heterogeneous subscribers
Oracle publishing
Peer to Peer transactional replication
Policy automation
Performance data collector
Standard performance reports
Plan guides and plan freezing for plan guides
Direct query of indexed views
Automatic indexed view maintenance
Distributed partitioned views
Parallel indexed operations
Automatic use of indexed view by query optimizer
Parallel consistency check
Change Data Capture Service for Oracle by Attunity
Change Data Capture Designer for Oracle by Attunity
High performance Oracle destination
High performance Teradata destination
SAP BW source and destination
Data mining model training destination adapter
Dimension processing destination adapter
Partition processing destination adapter
Change Data Capture components by Attunity
Connector for Open Database Connectivity
Create cubes without a database
Auto-generate staging and data warehouse schema
xVelocity memory optimized columnstore indexes

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

Thanks,

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

 

Changes to Editions and Components of SQL Server 2014

Microsoft SQL Server 2014 software Editions are categorized into different sets based on the features offered mentioned as below.
Principal Edition(Enterprise, Business Intelligence, Standard Edition) – The premium offering, SQL Server 2014 Enterprise version conveys extensive top of the line datacenter abilities with bursting quick execution, boundless virtualization, and end-to-end business insight.
Specialized editions(Web Edition) – SQL Server 2014 Business Intelligence version conveys extensive stage enabling associations to manufacture and convey secure, adaptable and sensible BI arrangements.
Breadth editions(Developer, Express Edition) – SQL Server 2014 Standard version conveys essential information administration and business brainpower database for offices and little associations to run their applications and backings basic advancement devices for on-reason and cloud

On an Internet server, for example, a server that is running Internet Information Services (IIS), you will normally introduce the SQL Server customer instruments. Customer apparatuses incorporate the customer network segments utilized by an application uniting with an example of SQL Server. You can introduce simply the SQL Server customer segments on a PC that is running customer/server applications that associate specifically to a case of SQL Server. A customer segments establishment is likewise a decent choice in the event that you direct an example of SQL Server on a database server, or on the off chance that you plan to create SQL Server applications.

Below are the SQL Server Components which can be installed with the SQL Server media.
SQL Server Database Engine
Analysis Services
Reporting Services
Integration Services
Master Data Services

Along with SQL Server services, there are also additional tools that can be installed to management and development purposes. Below are some of the components that can be installed on SQL Server box or on application servers or remote servers.
SQL Server Management Studio
SQL Server Configuration Manager
SQL Server Profiler
Database Engine Tuning Advisor
Data Quality Client
SQL Server Data Tools
Connectivity Components
SQL Server Books Online

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2014

Thanks,

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

 

Number of CPU Limits for SQL Server 2014

SQLServerF1

Below are the number of CPUs supported by the different editions of SQL Server 2014.

Enterprise Edition: Core-based Licensing1 – Operating system maximum
Developer Edition – Operating system maximum
Evaluation Edition – Operating system maximum
Business Intelligence Edition – Limited to lesser of 4 Sockets or 16 cores
Standard Edition – Limited to lesser of 4 Sockets or 16 cores
Web Edition – Limited to lesser of 4 Sockets or 16 cores
Express Edition – Limited to lesser of 1 Socket or 4 cores
Express with Tools – Limited to lesser of 1 Socket or 4 cores
Express with Advanced Services – Limited to lesser of 1 Socket or 4 cores

Enterprise Edition with Server + Client Access License (CAL) based authorizing (not accessible for new understandings) is constrained to a greatest of 20 cores every SQL Server case. There are no restrictions under the Core-based Server Licensing model.

In a virtualized domain, the process limit utmost is taking into account the quantity of intelligent processors, not centers, on the grounds that the processor structural engineering is not unmistakable to the visitor applications. Case in point, a server with four attachments populated with quad-center processors and the capacity to empower two hyperthreads every center contains 32 legitimate processors with hyperthreading empowered yet just 16 intelligent processors with hyperthreading incapacitated. These sensible processors can be mapped to virtual machines on the server with the virtual machines’ register stack on that legitimate processor mapped into a string of execution on the physical processor in the host server.

You may need to incapacitate hyperthreading when the execution every virtual processor is essential. One can empower or handicap hyperthreading utilizing a BIOS setting for the processor amid the BIOS setup, however it is regularly a server perused operation that will affect all workloads running on the server. This may propose dividing workloads that will run in virtualized situations from those that would advantage from the hyperthreading execution support in a physical working framework environment.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

Thanks,

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

 

Oracle Database Errors or Warnings from Error ORA-48504 to ORA-48800

SQLServerF1

ORA-48504: Relation Parameter Must be Specified
Cause: Relation parameter must be specified during adrexp.
Action: Supply the relation parameter.
ORA-48505: File Parameter Must be Specified
Cause: File parameter must be specified during adrimp.
Action: Supply the file parameter.
ORA-48506: Existing Relation at different version than export [string] [string]
Cause: Attempting to import into an existing relation and the schema version of that relation differs from the schema of the relation that was exported.
Action: Drop the existing relation if you still wish to import the relation.

ORA-48507: Predicate Not Allowed during Import
Cause: The predicate option is not allowed during adrimp.
Action: Remove the predicate arguement.
ORA-48508: Export File Version [string] Can Not be Used by Import [string]
Cause: The version of the export file is not able to be read by this version of adrimp.
Action: Rerun the export using the current version of adrexp.
ORA-48509: Error occurred during operation. See the following errors
Cause: An underlying error has occurred.
Action: Review and correct the underlying error.

ORA-48510: Can not export an in memory relation
Cause: In memory relations can not be exported.
Action: Pick a different relation.
ORA-48600: HM run with name [string] already exists
Cause: The specified run name already existed.
Action: Specify different run name and re-run the check
ORA-48615: Parameter [string] value not specified
Cause: Run was invoked without specifying the parameter and its value
Action: Specify the needed parameter and its value
ORA-48800: “string” for the keyword “string” is not in the right format of timestamp
Cause: The value format is not right
Action: Check the format ADRCI supports

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

What are atOracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database. Each Oracle Dabase 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-48493 to ORA-48503

SQLServerF1

ORA-48493: Sweep command needs parameters
Cause: sweep command needs parameter
Action: Check the syntax of the command
ORA-48494: ADR home is not set, the corresponding operation cannot be done
Cause: The adr home is not set in the current adrci session
Action: Set the adr home using the adrci command “set base” and “set homepath”
ORA-48495: Interrupt requested
Cause: User requested to interrupt the current action
Action: No action is needed

ORA-48496: “string” is a mandatory keyword for the command
Cause: The keyword is not specified for the command
Action: Input the keyword
ORA-48497: “string” is an invalid product type
Cause: The product type is not registered
Action: Check the product type
ORA-48499: The value of the keyword “string” exceeds the maximum length string
Cause: The keyword value is too long
Action: Check the limit and input again

ORA-48500: File Write Error [string] [string]
Cause: Number of bytes written differs from number requested. Possibly due to out of disk space.
Action: Ensure sufficient disk space.
ORA-48501: File Read Error [string] [string]
Cause: Number of bytes read differs from number requested. Possibly due due to corrupted file.
Action: Recreate the file.
ORA-48502: Invalid Command Line – Missing Required Elements
Cause: Missing required command line arguments.
Action: Review the help message and supply the required arguments.
ORA-48503: Invalid Parameter Specified
Cause: Invalid input parameter supplied.
Action: Review the help message and correct the invalid input parameter.

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

What are atOracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database. Each Oracle Dabase 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-48483 to ORA-48492

SQLServerF1

ORA-48483: Spooling failed, it may be because the spool file cannot be created due to a permission issue
Cause: The spooling filename may not be valid or the file cannot be created
Action: Check the permissions of the target directory and verify the filename
ORA-48484: Run script failed, it may be because the script file does not exist
Cause: The script file may not exist
Action: Check if the script file exist
ORA-48485: The file exceeds the maximum length [string]
Cause: The filename is too long
Action: Report to Oracle

ORA-48486: The file [string] exceeds the maximum length [string]
Cause: The filename is too long
Action: Report to Oracle
ORA-48487: The internal predicate string exceeds the maximum length [string]
Cause: The predicate string exceeds the maximum length
Action: Report to Oracle
ORA-48488: The predicate string exceeds the maximum length [string]
Cause: The input predicate string exceeds the maximum length
Action: Report to Oracle

ORA-48489: The input exceeds the maximum length [string]
Cause: The input exceeds the maximum length
Action: Report to Oracle
ORA-48490: The field number exceeds the maximum number [string]
Cause: The input field number exceeds the maximum number
Action: Input less field names
ORA-48491: The program name is too long, exceeds the maximum length [string]
Cause: the program name length exceeds the maximum length setting
Action: Report to Oracle
ORA-48492: The report component name is not defined
Cause: the report component name does not exist
Action: Check the component name to ensure it is registered

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

What are atOracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database. Each Oracle Dabase 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 3 6