DBCC TRACEON / DBCC TRACEOFF/ DBCC TRACESTATUS

DBCC TRACEON / DBCC TRACEOFF/ DBCC TRACESTATUS

DBCC TRACEON - Enable trace flags.
DBCC TRACEOFF - Disable trace flags.
DBCC TRACESTATUS - Display the status of trace flags.

-->  Turn flag on for only the session level 

DBCC TRACEON (1222)

DBCC TRACEON (1205,1222)
GO

--> Turn flag on globally at server for all the sessions

DBCC TRACEON (1222, -1)
GO

-- >Turn flag off

DBCC TRACEOFF (1222);
GO

--> Show flag status

DBCC TRACESTATUS (1205,1222)

DBCC TRACESTATUS (-1)

Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.

Error Description

Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.

Root Cause

SQL Account only locked out, if it is enfoced to use password policies.



Resolution

You can unlock it by exeuction below command.

ALTER LOGIN sa WITH PASSWORD = 'password' UNLOCK

OR

You can do it GUI as well by unchecked locked checkbox

google ads

Index Rebuild vs Index Reorganize. Diffrence beweent Index Rebuild and Index Reorganize

Index Rebuild	Index Reorganize
It is offline operation	It is online operation
Option is available in all SQL Server 2005 edition	Option is available in SQL Server 2005 Enterprise and Developer edition only
Index rebuilds works by re-creating the index internally again and when that has been achieved, it drops the existing index.	Index reorganize is the process of physically re-organizing the leaf nodes of the index
Index rebuild need more log space so it is advisable to change the database recovery model Simple or Bulk-logged	Reorganize swaps one page with another and thus does not require free space for this operation like rebuild does. Infect, reorganize can free up some pages as it does the reorganize in two phases – compaction and defrag. A reorganize can remove almost all of the logical fragmentation but it cannot necessarily fix extent fragmentation in which the previous and the next extents are physically contiguous.
During the index rebuild process, It will also re-compute index statistics	Reorganize on the other hand does not update the statistics
Can be done on any data type column because it is offline operation.	Another point to note is that an index (clustered or non-clustered) cannot be built online if it contains LOB data (text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data type columns).
If you want to rebuild an index with multiple partitions in an online environment, you need to rebuild the entire index which means rebuilding all the partitions.	Regarding partitions of an index, if an index has multiple partitions, then you cannot rebuild a single partition online. You can reorganize a single index partition online
USE AdventureWorks; GO ALTER INDEX ALL ON Production.Product REBUILD GO	USE AdventureWorks; GO ALTER INDEX ALL ON Production.Product REORGANIZE
Index should be rebuild when index fragmentation is great than 40%.	Index should be reorganized when index fragmentation is between 10% to 40%
ONLINE option will not keep index available during the rebuilding.	ONLINE option will keep index available during the rebuilding.

How to check SQL Services status from management studio?

You can use the below commands to check the sql services status from manangement studio.

xp_servicecontrol querystate, MSSQLAgent
xp_servicecontrol querystate, MSSQLServer
xp_servicecontrol querystate, msdtc

Join me on SQLPass.Org

Dear Readers,

You can join me on SQLPass



http://www.sqlpass.org/Community/PASSPort.aspx?ProfileID=42686

 

Service Principle Name

SPN (Service Principle Name)

What is SPN? A service principal name (SPN) is the name by which a client uniquely identifies an instance of a service. The Kerberos authentication service can use an SPN to authenticate a service. When a client wants to connect to a service, it locates an instance of the service, composes an SPN for that instance, connects to the service, and presents the SPN for the service to authenticate.

How the SPN works or what is the internal process of SQL Server to register SPN?When SQL Server service is started, it first checks if SPN exists and if no SPN found calls API to create new SPN. At the shutdown service sends request to delete the SPN. 

What if SQL Server is running under local administrator account? Can it register SPN?No, it cannot. The account must have domain administrator rights to register the SPN 

Can we register SPN for other services as well? Yes, you can register the SPN for the other services as well. (i.e Analysis Service) 

How can I see the SPN for my different SQL Boxes? First you need to download the SPN tool from below URL http://www.microsoft.com/downloads/details.aspx?FamilyId=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en

 How to use this utility, what are the commands available for it?Please find the SPN Syntax as below.

Syntax setspn [switches data] computername 

 Note: Computer Name can be the name or domain\name

 Switches:
   -R = reset HOST ServicePrincipalName
    Usage:   setspn -R computername
   -A = add arbitrary SPN
    Usage:   setspn -A SPN computername
   -D = delete arbitrary SPN
    Usage:   setspn -D SPN computername
   -L = list registered SPNs
    Usage:   setspn [-L] computername

Examples:
setspn -R daserver1
   It will register SPN “HOST/daserver1 and “HOST/{DNS of daserver1}”
setspn -A http/daserver daserver1
   It will register SPN “http/daserver” for computer “daserver1”
setspn -D http/daserver daserver1
   It will delete SPN “http/daserver” for computer “daserver1”

How should I use this utility SQL Server Services?You can use below commands to register different services 

To Register SQL Server Database Service

SetSPN –A MSSQLSvc/serverHostName.Fully_Qualified_domainName:[TCP Port Number] [Account Name]

Note: You can use the same command Named Instance as well

To Register Analysis Service

Setspn.exe -A MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName OLAP_Service_Startup_Account

How to check whether SQL Server Services are registered or not?

You can use below command.

SetSPN –L [AccountName]

What happens if SQL Server Service fails to create SPN during startup?

If the SQL Server Service is failed to create and register SPN, it will log the error in the error log during startup. Moreover you will get the below type of issues from client side

You will get the connectivity issues from client side as below

Error: 18456, Severity: 14, State: 11.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

How to check after SPN registration SQL Server Kerberos authentication?

Check the auth_scheme column by executing below query

select auth_scheme from sys.dm_exec_connections where session_id=@@spid;

What are the best practices for SPN in cluster environment? In cluster server configure both nodes to use same network DC

Script to Verify the litespeed backup file

Use the below script to verify the litespeed backup file

exec master.dbo.xp_restore_verifyonly
@filename = N'\\backups\full\mydbbackup.full.BAK',
@filenumber = 1,
@logging = 0


Script to list out important properties of database

Use below script to list out the important properties of the database

 


select
sysDB.database_id,
sysDB.Name as 'Database Name',
syslogin.Name as 'DB Owner',
sysDB.state_desc,
sysDB.recovery_model_desc,
sysDB.collation_name,
sysDB.user_access_desc,
sysDB.compatibility_level,
sysDB.is_read_only,
sysDB.is_auto_close_on,
sysDB.is_auto_shrink_on,
sysDB.is_auto_create_stats_on,
sysDB.is_auto_update_stats_on,
sysDB.is_fulltext_enabled,
sysDB.is_trustworthy_on
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid


Script to change the database compatibility level

Use below script to change the database compatibility level

ALTER DATABASE DatabaseName
SET SINGLE_USER
GO
EXEC sp_dbcmptlevel DatabaseName, 90;
GO
ALTER DATABASE DatabaseName
SET MULTI_USER
GO


SQL Server Version	Compatibility Level
SQL Server 6.5	65
SQL Server 7.0	70
SQL Server 2000	80
SQL Server 2005	90
SQL Server 2008	100

Login failed for user XYZ The user is not associated with a trusted SQL Server connection.

Problem:Login failed for user XYZ The user is not associated with a trusted SQL Server connection.

Resolution: Login is failed due the the authentication mode is Windows Authentication and it is not allowing SQL Server authentication for SQL Login.

Change the Authentication Mode of the SQL server from Windows Authentication Mode (Windows Authentication) to Mixed Mode (Windows Authentication and SQL Server Authentication)

Right Click on Server and Select the Server Properties from pop up.

Unable to install Windows Installer MSP file

Problem: Unable to install Windows Installer MSP file 

Description: I got the above error while installing Service Pack3 on SQL Server 2005 box. 

Product                                    : Database Services (INST2)
Product Version (Previous)     : 3257
Product Version (Final)           :
Status                                      : Failure
Log File                                    : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log
Error Number                                     : 11032
Error Description                                : Unable to install Windows Installer MSP file

 Resolution:  

Step 1:  Check the setup log file from C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log 

Step 2: Search for “return value 3” and scan next +/- 10 lines for error details 

Step 3: During scan I found the below error details “Fail create file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData  folder” I have created the required folder at desired location and has resolve the issue. 

There are other causes as well for the failure to resolved it you can follow the below steps

-          Copy the service pack from network location to installation location or on SQL Server box

-          Check for the registry parameters 

Check below KB documents to resolve error

http://support.microsoft.com/kb/926622.

 

Msg 7391, Level 16, State 2, Line 23 - Error while running Linked Server

Error Description 

OLE DB provider "SQLNCLI" for linked server "MYView" returned message "The transaction manager has disabled its support for remote/network transactions.".

 Msg 7391, Level 16, State 2, Line 23

 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "MYView" was unable to begin a distributed transaction.

 Resolution:

1. Check whether DTC is blocked by firewall, if it is blocked by firewall release it.

2. Check or configure DTC to allow network connection as below.

 

Go to Control Panel ->Administrative Tools -> Component Services -> Expand the Computer Node -> Right click on My Computer) -> Click on Properties -> MS DTC Tab -> Security Configuration

 

Script - Generate DBCC ShrinkFile for each datbase files

You can use below query to generate DBCC Shrikfile command for data and log file.

Query

select 'use ' + db_name(dbid) + char(13) + 'dbcc shrinkfile (' + quotename(sf.name,'''') + ' ,truncateonly)' from sysaltfiles sf
inner join sys.databases sd on sf.dbid = sd.database_id
where state_desc = 'online'


 

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Error:  Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Error clearly state that login is not associated with the trusted connection.

What is trusted connection and what is the advantage of it? Integrated authentication allows for SQL Server to leverage Windows NT authentication to validate SQL Server logon accounts. This allows the user to bypass the standard SQL Server logon process. With this approach, a network user can access a SQL Server database without supplying a separate logon identification or password because SQL Server obtains the user and password information from the Windows NT network security process.

Advantage of this process is that we don’t have to store credential with the connection string.

However sometimes you will see the error above error.

Resolution

1. Change the authentication mode to mixed mode from SSMS or Enterprise Manager

2. Check the user/login is associated with the appropriate SQL login

3. Host is in the same domain or Host machine is network, sometime rebooting of the host machine will resolve this issue.

DBCC PAGE

DBCC PAGE 

We can use the undocumented DBCC PAGE command to view the page header, data rows, and row offset table for any data page in a database. You have to turn on traceflag 3604 before running this command.  You can also use this command when you find the IO bottleneck and query is waiting Page Latches.

Permission: System Admin rights

Syntax

dbcc page ( {'dbname' | dbid}, file number, page number [, print opt={0|1|2|3} ])

 You can pass the below print option parameter as per the requirement

• 0 - print just the page header


• 1 - page header plus per-row hex dumps and a dump of the page slot array (unless it’s a page that doesn't have one, like allocation bitmaps)


• 2 - page header plus whole page hex dump


• 3 - page header plus detailed per-row interpretation

Trace Flag – 3604 Trace flag 3604 is the most commonly used trace flag. It sends the output of a trace to the client. For example, before you can run DBCC PAGE, which views data page information, you must run this trace flag. 

DBCC traceon(3604)

DBCC PAGE ( {dbid | dbname}, filenum, pagenum [, printopt] [, cache] ) 

The output from DBCC PAGE is divided into four main sections: Buffer, Page Header, Data, and Offset Table (really the Offset Array).

Shrink Log File

Just a few days back I received an issue regarding disk size is out of space. I have tried to shrink the database using DBCC ShrinkFile but there isn’t any success.

I have follow below steps to resolve this issue and it really works.

I have check the below query for

Select name,log_reuse_wait_desc from sys.databases

1. There was another database which is waiting log backup to release the space


2. There was one database who is waiting for Replication to release the space

Database who is waiting for log backup to release the log space

I have taken the log backup two times and executed the database shrink file command to release the space and it has reclaimed 40GB space from transaction log file.

Database who is waiting for Replication to release the log space

This database is in Simple recovery mode and there isn’t any replication enable on this. I have executed the DBCC OPENTRAN command to see any active transaction. I have executed the DBCC OpenTran and it has provided me the below result.

Oldest active transaction:

    SPID (server process ID): 101

    UID (user ID) : -1

    Name          : INSERT

    LSN           : (999:138204:2)

    Start time    : OCT  13 2009  1:34:47:827PM

    SID           : 0x88d52e4051a71143adee5dc7b6619f8a

Replicated Transaction Information:

        Oldest distributed LSN     : (890:2091888:1)

        Oldest non-distributed LSN : (896:2784855:1)

I don’t know the exact reason what happened internally. But from the output it seems that there is unmark distributed transaction. So I have executed Sp_Repldone command to unmark the LSN

 

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

 Than I have executed the DBCC ShrinkFile command and it has reclaimed the 400GB space

An error has occurred while establishing a connection to the server

Problem:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)

Resolution:

Check the below setting at Server Level
1. Check the firewall for any blocking (i.e TCP/IP Port:1433)
2. Check the SQL Server Instance Name, there may be chances of the spelling mistakes
3. May be remote connection is disable. Use the SAC (SQL Server Surface area configuration tool for remote connection)
4. Check the status of SQL Browser Service, it should be running to allow remote connectivity

What are the features we can enable using Surface Area Configuration Tool?

SQL Server Security Interview Questions

During the interview I always like to ask below security questions to DBA. I found that sometimes DBAs are not able to answer these questions.

Question 1: What will you do if you lost rights of your SQL Server instance?

We can use the below options

1. Dedicated Administrator Connection


2. BUILIN\Administrator Group (Incase its rights are not revoked)


3. Final Option is to change the registry value


4. You can change authentication mode via registry 

Question 2: - What is SQL Injection?

SQL Injection is developed where unhandled\unexpected SQL commands are passed to SQL Server in a malicious manner.  It is a problem because unknowingly data can be stolen, deleted, updated, inserted or corrupted. 

Question 3: - What is the Guest user account in SQL Server?  What login is it mapped to it?   

The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object.  It is not mapped directly to any login, but can be used by any login.  Depending on your security needs, it may make sense to drop the Guest user account, in all databases except Master and TempDB 

Question 4: – What is the use of BUILTIN\Administrators Group in SQL Server?

Any Windows login in BUILTIN\Administrators group is by default a SQL Server system administrator. This single group can be used to manage administrators from a Windows and SQL Server perspective

How to change Min/Max Memory in SQL Server?

Step 1: Open SSMS

Step 2: Connect to desire Server and right click on the server (for i.e. sqldbpool) and Select properties

 

Step 3: Click on memory tab and configure Min and Max memory

SQL Server 2008 Active-Passive/Active-Active Cluster Installation

Like SQL Server 2005, SQL Server 2008 supports clustering using both the Standard and Enterprise editions. The Standard edition supports a two-node cluster, and the Enterprise edition supports the maximum number of nodes that the OS supports 

The biggest changes in SQL Server 2008 cluster is that you can now use DHCP for clustering and use IP addresses from different subnets for each side of the cluster. The ability to use IP addresses from different subnets is most useful for creating multisite clusters, called geographically distributed clusters.

SQL Server Cluster Installation

Step 1: I hope you had done with all the pre-requisites required by cluster.If you're installing SQL Server 2008 in a cluster on Windows 2003, you'll need to download and install the hotfix specified in the Microsoft article at http://support.microsoft.com/kb/937444  before you can install SQL Server 2008.

Note: Installation of above hotfix requires a reboot, so you have to cancel out of the installer at this point and restart the process after installing that hotfix on all your cluster nodes.

Step 2: Open SQL Server 2008 installation executable file from your CD\DVD\Shared location

Double click on installation.exe and you will find the below screen. Now select New SQL Server Failover Cluster installation option highlighted as in below image. (Note: For standalone installation we have to select standalone option)



Step 3: Once you have selected Step 2 option, installation will check for setup support rules to identify the problem. If it is failed due to any reason, you have to correct the issue. Please find the below screen shot. If it passed click on OK command button.



Step 4: Installation wizard will install the setup support files, click on Install command button



Step 5: This step will walk you through all the required information as below. Click on Next button

1. Product Key

2. Licence Terms

3. Feature Selection

4. Disk Space Reuirements

5. Error and Usage Reporting

6. Cluster instllation Rules

7. Ready to install

8. Installation Progress



Step 6: Enter Product Key or It will take automatically. See the below image for it.



Step 7: Select required features, which you want install.



Step 8: Specify the Network Name/Virtual Name for SQL Server to failover. As we are installing Active-Passive cluster we have to specify one virtual/network name. (Note: For Active-Active clustering you have specify different network names as per the number of nodes)



Step 9: SQL Server will review the disk space requirement for the features you have selected



Step 10: This step will create a new cluster resource group for your SQL server failover. Please see the note in the image.



Step 11: Select shared cluster disk resources for your SQL Server failover cluster



Step 12: Here you have to select network resources for your SQL Server failover cluster. You have to add IP address as well.



Step 13: Here you have to specify the service account for SQL Server services. It is a best practice always keeps SQL Services in cluster in manual mode



Step 14: Specify the authentication more and SA password in account provisioning tab, configure the location of the data files, log files, temp database in data directories tab and enable file stream as per the requirement



Step 15 

Step 14: Specify the authentication more and SA password in account provisioning tab, configure the location of the data files, log files, temp database in data directories tab and enable file stream as per the requirement





Step 15: Select check boxes as per your requirement, I haven't checked any option. Proceed with the next button



Step 16: Installation wizard will check the cluster installation rules, please correct if you found any failure and proceed with the next button



Step 17: Now your SQL Server cluster is ready to install. Click on Install button



Step 18: Apply the required Service Pack/Patch and reboot the server

Step 19: Once installation is done on this node, we have to proceed with the installation of SQL Server on the other cluster nodes. SQL Server 2005 was the last edition of SQL Server to automatically install SQL Server on all nodes at once.  Beginning with SQL Server 2008, we must manually install and patch each cluster node individually. This process allows for less downtime as we are patching the cluster or upgrading your cluster from SQL Server 2005 to SQL Server 2008.

Step 20 Installation on the other nodes is similar to installing on the first node except that we have select the Add Node to a SQL Server failover cluster option from the initial menu. The same software requirements apply to the other nodes in the cluster, so the installer will handle the verification and installation of these components as well.

Because most of the settings are picked up from the other node(s) in the cluster that already have SQL Server installed on them, the process of adding a node to the cluster is bit shorter than installing on the first node 

Step 20 Installation on the other nodes is similar to installing on the first node except that we have select the Add Node to a SQL Server failover cluster option from the initial menu. The same software requirements apply to the other nodes in the cluster, so the installer will handle the verification and installation of these components as well.

Because most of the settings are picked up from the other node(s) in the cluster that already have SQL Server installed on them, the process of adding a node to the cluster is bit shorter than installing on the first node

How to move Cluster Resource/Services to proper node using command prompt?

cluster group "SQL Server Group" /move: Physical_Name_Of_Node1

cluster group "SQL Server Group INST2" /move: Physical_Name_Of_Node2

How to check cluster resources are running on proper node using command prompt?

cluster clusterName resource >> clusterResourcestatus.txt

Note: Text highlighted in Blue color is command or parameter you can execute.

Login Issue

Recently I received below error while executing XP_ReadErrorlog command from SSMS.

Msg 22004, Level 16, State 1, Line 0

Failed to open loopback connection. Please see event log for more information.

Msg 22004, Level 16, State 1, Line error log location not found 

As per the above error message I have checked event viewer log and got the below information from event viewer log. 

The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:18456, OS: 18456 [Microsoft][SQL Native Client][SQL Server]Login failed for user 'domain\service-sql'..

I have opened error log file from log directory and observed the number of login service-sql with the state 11. 

2009-09-30 22:45:31.69 Logon       Login failed for user 'domain\service-sql'. [CLIENT: 10.10.10.10]

2009-09-30 22:45:34.26 Logon       Error: 18456, Severity: 14, State: 11.

Service-SQL account has sysAdmin rights on SQL Server and it is added through SQL Server Service – Production AD group. I can see this AD group has SysAdmin rights on SQL Server. 

Resolution: Remove account from local administrator group and from SQL Server Logins. Add account again with the same privileges in local administrator group and SQL Server logins.

Shrink the log to as small a size as possible (truncateonly)

You can use the below command to shrink the database as small as possible.

DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

NOTE: if you don't know the logical filename of your transaction log use sp_helpfile to list all of your database files.

Happy Birthday to Me (It's 1-Sept)

SQL Server 2005 Backup Error Messages

MSSQL 2005 Backup or its related Error Messages

Severity level 10 messages are informational and indicate problems caused by mistakes in the information you have entered. Severity levels from 11 through 16 are generated by the user, and can be corrected by the user.

Severity levels from 17 through 25 indicate software or hardware errors (Please find the below update error messages).

We have to inform the system administrator whenever problems that generate errors with severity levels 17 and higher than it. The system administrator must resolve these errors and track their frequency. When a level 17, 18, or 19 errors occur, we can continue working, although we might not be able to execute a particular statement.



Error Messages

select message_id, severity, [text] from sys.messages where language_id = 1033 and severity <> 10 and  [text] like '%backup%'

Msg Id	Severity	Error Message
20622	11	Replication database option -sync with backup- cannot be set on the publishing database because the database is in Simple Recovery mode_
1411	16	The remote copy of database -_*ls has not had enough log backups applied to roll forward all of its files to a common point in time_
1475	16	Database mirroring cannot be enabled because the -_*ls database may have bulk logged changes that have not been backed up_ The last log backup on the principal must be restored on the mirror_
1478	16	The mirror database, -_*ls, has insufficient transaction log data to preserve the log backup chain of the principal database_  This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database_
1833	16	File _ls- cannot be reused until after the next BACKUP LOG operation_
1931	16	The SQL statement cannot be executed because filegroup __*ls- is offline_ Use the sys_database_files or sys_master_files catalog view to determine the state of the files in this filegroup and then restore the offline file(s) from backup_
2505	16	The device __*ls- does not exist_ Use sys_backup_devices to show available devices_
2515	16	Page -S_PGID, object ID -d, index ID -d, partition ID -I64d, alloc unit ID -I64d type -_*ls) has been modified but is not marked modified in the differential backup bitmap_
2516	16	Repair has invalidated the differential bitmap for database -_*ls_ The differential backup chain is broken_ You must perform a full database backup before you can perform a differential backup_
2517	16	Bulk-logging has been turned on for database -_*ls_ To ensure that all data has been secured, run backup log operations again_
3002	16	Cannot BACKUP or RESTORE a database snapshot_
3004	16	The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model_ Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS_
3006	16	The differential backup is not allowed because it would be based on more than one base backup_ Multi-based differential backups are not allowed in the simple recovery model, and are never allowed for partial differential backups_
3007	16	The backup of the file or filegroup -ls is not permitted because it is not online_ BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data_
3008	16	The specified device type is not supported for backup mirroring_
3009	16	Could not insert a backup or restore history/detail record in the msdb database_ This may indicate a problem with the msdb database_ The backup/restore operation was still successful_
3010	16	Invalid backup mirror specification_ All mirrors must have the same number of members_
3011	16	All backup devices must be of the same general class (for example, DISK and TAPE)_
3016	16	Backup of file _ls- is not permitted because it contains pages subject to an online restore sequence_ Complete the restore sequence before taking the backup, or restrict the backup to exclude this file_
3021	16	Cannot perform a backup or restore operation within a transaction_
3023	16	Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized_ Reissue the statement after the current backup or file manipulation operation is completed_
3024	16	You can only perform a full backup of the master database_ Use BACKUP DATABASE to back up the entire master database_
3033	16	BACKUP DATABASE cannot be used on a database opened in emergency mode_
3035	16	Cannot perform a differential backup for database -ls, because a current database backup does not exist_ Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option_
3038	16	The file name -ls is invalid as a backup device name_ Reissue the BACKUP statement with a valid file name_
3039	16	Cannot perform a differential backup for file _ls- because a current file backup does not exist_ Reissue BACKUP DATABASE omitting the WITH DIFFERENTIAL option_
3041	16	BACKUP failed to complete the command -_*ls_ Check the backup application log for detailed messages_
3043	16	BACKUP _ls- detected an error on page (-d:-d) in file _ls-_
3044	16	Invalid zero-length device name_ Reissue the BACKUP statement with a valid device name_
3045	16	Differential BACKUP or RESTORE is not supported on the FAT file system_ The path -_*ls is not usable_
3046	16	Inconsistent metadata has been encountered_ The only possible backup operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option_
3047	16	The BackupDirectory registry key is not configured correctly_ This key should specify the root path where disk backup files are stored when full path names are not provided_ This path is also used to locate restart checkpoint files for RESTORE_
3048	16	BACKUP LOG WITH TRUNCATE_ONLY cannot operate on database _ls- because it is configured for database mirroring_
3049	16	BACKUP detected corruption in the database log_ Check the errorlog for more information_
3050	16	SQL Server could not send the differential information for database file _ls- of database _ls\\-ls- to the backup application because the differential information is too large to fit in memory, and an attempt to use a temporary file has failed_
3051	16	BACKUP LOG was unable to maintain mirroring consistency for database _ls-_ Database mirroring has been permanently suspended_ To resume mirroring, the existing mirroring configuration must be dropped and then re-established_
3054	16	Differential file backups can include only read-only data for databases using the simple recovery model_ Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS_
3103	16	A partial restore sequence cannot be initiated by this command_ To initiate a partial restore sequence, use the WITH PARTIAL clause of the RESTORE statement and provide a backup set which includes a full copy of at least the primary data file_ The WITH PARTIAL clause of the RESTORE statement may not be used for any other purpose_
3106	16	The filegroup -ls is ambiguous_ The identity in the backup set does not match the filegroup that is currently defined in the online database_ To force the use of the filegroup in the backup set, take the database offline and then reissue the RESTORE command_
3107	16	The file -ls is ambiguous_ The identity in the backup set does not match the file that is currently defined in the online database_ To force the use of the file in the backup set, take the database offline and then reissue the RESTORE command_
3109	16	Master can only be restored and fully recovered in a single step using a full database backup_ Options such as NORECOVERY, STANDBY, and STOPAT are not supported_
3116	16	The supplied backup is not on the same recovery path as the database, and is ineligible for use for an online file restore_
3117	16	The log or differential backup cannot be restored because no files are ready to rollforward_
3118	16	The database -ls does not exist_ RESTORE can only create a database when restoring either a full backup or a file backup of the primary file_
3120	16	This backup set will not be restored because all data has already been restored to a point beyond the time covered by this backup set_
3121	16	The file -ls is on a recovery path that is inconsistent with application of this backup set_ RESTORE cannot continue_
3123	16	Invalid database name __*ls- specified for backup or restore operation_
3125	16	The database is using the simple recovery model_ The data in the backup it is not consistent with the current state of the database_ Restoring more data is required before recovery is possible_ Either restore a full file backup taken since the data was marked read-only, or restore the most recent base backup for the target data followed by a differential file backup_
3129	16	The contents of the file -ls are not consistent with a transition into the restore sequence_ A restore from a backup set may be required_
3135	16	The backup set in file _ls- was created by -hs and cannot be used for this restore operation_
3136	16	This differential backup cannot be restored because the database has not been restored to the correct earlier state_
3143	16	The data set on device _ls- is not a SQL Server backup set_
3144	16	File __*ls- was not backed up in file -d on device _ls-_ The file cannot be restored from this backup set_
3147	16	Backup and restore operations are not allowed on database tempdb_
3154	16	The backup set holds a backup of a database other than the existing _ls- database_
3155	16	The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created_
3159	16	The tail of the log for the database -ls has not been backed up_ Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose_ Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log_
3168	16	The backup of the system database on the device -ls cannot be restored because it was created by a different version of the server (-ls) than this server (-ls)_
3169	16	The database was backed up on a server running version -ls_ That version is incompatible with this server, which is running version -ls_ Either restore the database on a server that supports the backup, or use a backup that is compatible with this server_
3173	16	The STOPAT clause provided with this RESTORE statement indicates that the tail of the log contains changes that must be backed up to reach the target point in time_ The tail of the log for the database -ls has not been backed up_ Use BACKUP LOG WITH NORECOVERY to back up the log, or use the WITH REPLACE clause in your RESTORE statement to overwrite the tail of the log_
3178	16	File -ls is not in the correct state to have this differential backup applied to it_
3180	16	This backup cannot be restored using WITH STANDBY because a database upgrade is needed_ Reissue the RESTORE without WITH STANDBY_
3182	16	The backup set cannot be restored because the database was damaged when the backup occurred_ Salvage attempts may exploit WITH CONTINUE_AFTER_ERROR_
3183	16	RESTORE detected an error on page (-d:-d) in database -ls as read from the backup set_
3185	16	RESTORE cannot apply this backup set because the database is suspect_ Restore a backup set that repairs the damage_
3186	16	The backup set has been damaged_ RESTORE will not attempt to apply this backup set_
3187	16	RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information_
3189	16	Damage to the backup set was detected_
3190	16	Filegroup _ls- cannot be restored because it does not exist in the backup set_
3195	16	Page -S_PGID cannot be restored from this backup set_ RESTORE PAGE can only be used from full backup sets or from the first log or differential backup taken since the file was added to the database_
3196	16	RESTORE master WITH SNAPSHOT is not supported_ To restore master from a snapshot backup, stop the service and copy the data and log file_
3201	16	Cannot open backup device _ls-_ Operating system error -ls_
3204	16	The backup or restore was aborted_
3205	16	Too many backup devices specified for backup or restore; only -d are allowed_
3206	16	No entry in sysdevices for backup device __*ls-_ Update sysdevices and rerun statement_
3207	16	Backup or restore requires at least one backup device_ Rerun your statement specifying a backup device_
3208	16	Unexpected end of file while reading beginning of backup set_ Confirm that the media contains a valid SQL Server backup set, and see the console error log for more details_
3214	16	Too many backup mirrors are specified_ Only -d are allowed_
3215	16	Use WITH FORMAT to create a new mirrored backup set_
3218	16	Backup mirroring is not available in this edition of SQL Server_ See Books Online for more details on feature support in different SQL Server editions_
3227	16	The backup media on -ls is part of media family -d which has already been processed on -ls_ Ensure that backup devices are correctly specified_ For tape devices, ensure that the correct volumes are loaded_
3231	16	The media loaded on -ls is formatted to support -d media families, but -d media families are expected according to the backup device specification_
3232	16	The volume mounted on -ls does not have the expected backup set identity_ The volume may be obsolete due to a more recent overwrite of this media family_ In that case, locate the correct volume with sequence number -d of media family -d_
3239	16	The backup set on device _ls- uses a feature of the Microsoft Tape Format not supported by SQL Server_
3240	16	Backup to mirrored media sets requires all mirrors to append_ Provide all members of the set, or reformat a new media set_
3242	16	The file on device _ls- is not a valid Microsoft Tape Format backup set_
3249	16	The volume on device _ls- is a continuation volume for the backup set_ Remove it and insert the volume holding the start of the backup set_
3255	16	The data set on device _ls- is a SQL Server backup set not compatible with this version of SQL Server_
3256	16	The backup set on device _ls- was terminated while it was being created and is incomplete_ RESTORE sequence is terminated abnormally_
3258	16	The volume on the device -ls is not part of the media set that is currently being processed_ Ensure that the backup devices are loaded with the correct media_
3259	16	The volume on device _ls- is not part of a multiple family media set_ BACKUP WITH FORMAT can be used to form a new media set_
3265	16	The login has insufficient authority_ Membership of the sysadmin role is required to use VIRTUAL_DEVICE with BACKUP or RESTORE_
3266	16	The backup data at the end of -ls is incorrectly formatted_ Backup sets on the media might be damaged and unusable_ To determine the backup sets on the media, use RESTORE HEADERONLY_ To determine the usability of the backup sets, run RESTORE VERIFYONLY_ If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets_
3268	16	Cannot use the backup file _ls- because it was originally formatted with sector size -d and is now on a device with sector size -d_
3273	16	The BUFFERCOUNT parameter must supply a value that allows at least one buffer per backup device_
3276	16	WITH SNAPSHOT can be used only if the backup set was created WITH SNAPSHOT_
3280	16	Backups on raw devices are not supported_ _ls- is a raw device_
4202	16	BACKUP LOG is not possible because bulk logged changes exist in the database and one or more filegroups are unavailable_
4208	16	The statement -hs is not allowed while the recovery model is SIMPLE_ Use BACKUP DATABASE or change the recovery model using ALTER DATABASE_
4212	16	Cannot back up the log of the master database_ Use BACKUP DATABASE instead_
4214	16	BACKUP LOG cannot be performed because there is no current database backup_
4218	16	Bulk-logged operations exist in the database_ Perform a BACKUP LOG_
4305	16	The log in this backup set begins at LSN -_*ls, which is too recent to apply to the database_ An earlier log backup that includes LSN -_*ls can be restored_
4311	16	RESTORE PAGE is not allowed from backups taken with earlier versions of SQL Server_
4312	16	This log cannot be restored because a gap in the log chain was created_ Use more recent data backups to bridge the gap_
4319	16	A previous restore operation was interrupted and did not complete processing on file _ls-_ Either restore the backup set that was interrupted or restart the restore sequence_
4320	16	The file -ls was not fully restored by a database or file restore_ The entire file must be successfully restored before applying this backup set_
4326	16	The log in this backup set terminates at LSN -_*ls, which is too early to apply to the database_ A more recent log backup that includes LSN -_*ls can be restored_
4327	16	The log in this backup set contains bulk-logged changes_ Point-in-time recovery was inhibited_ The database has been rolled forward to the end of the log_
4330	16	This backup set cannot be applied because it is on a recovery path that is inconsistent with the database_ The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point_ Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time_  For more information about recovery paths, see SQL Server Books Online_
4338	16	The STOPAT clause specifies a point too early to allow this backup set to be restored_ Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point_
4341	16	This log backup contains bulk-logged changes_ It cannot be used to stop at an arbitrary point in time_
4343	16	The database has been rolled forward to the end of this backup set and beyond the specified point in time_ RESTORE WITH RECOVERY can be used to accept the current recovery point_
4348	16	The online restore to database _ls- failed_ It may be appropriate to perform an offline restore instead_ An offline restore is initiated by using BACKUP LOG WITH NORECOVERY_
4349	16	The log in this backup set begins at LSN -_*ls, which is too recent to apply to the database_ This restore sequence needs to initialize the log to start at LSN -_*ls_ Reissue the RESTORE LOG statement using an earlier log backup_
4351	16	Backups taken on earlier versions of SQL Server are not supported by fn_dump_dblog_
5173	16	One or more files do not match the primary file of the database_ If you are attempting to attach a database, retry the operation with the correct files_  If this is an existing database, the file may be corrupted and should be restored from a backup_
5250	16	Database error: -ls page -S_PGID for database __*ls- (database ID -d) is invalid_  This error cannot be repaired_  You must restore from backup_
7694	16	Failed to pause catalog for backup_ Backup was aborted_
7695	16	Operation failed_ Full-text catalog backup in progress_ Retry after backup operation has completed_
8309	16	BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated_ The simple recovery model should be used to automatically truncate the transaction log_
8463	16	Failed to read the message body while marshaling a message_ This message is a symptom of another problem_ Check the SQL Server error log and the Windows event log for additional messages and address the underlying problem_ If the problem persists, the database may be damaged_ To recover the database, restore the database from a clean backup_ If no clean backup is available, consider running DBCC CHECKDB_ Note that DBCC CHECKDB may remove data to repair the database_
8477	16	An internal Service Broker error occurred (error = 0x-08x)_ This error indicates a serious problem with SQL Server_ Check the SQL Server error log and the Windows event logs for information pointing to possible hardware problems_ The database may have been damaged_ To recover the database, restore the database from a clean backup_ If no clean backup is available, consider running DBCC CHECKDB_ Note that DBCC CHECKDB may remove data to repair the database_
8930	16	Database error: Database -d has inconsistent metadata_ This error cannot be repaired and prevents further DBCC processing_ Please restore from a backup_
9715	16	The conversation endpoint with conversation handle _ls- is in an inconsistent state_  Check the SQL Server error logs and the Windows event logs for information on possible hardware problems_  To recover the database, restore the database from a clean backup_  If no clean backup is available, consider running DBCC CHECKDB_ Note that DBCC CHECKDB may remove data_
14052	16	The @sync_type parameter value must be automatic, none, replication support only, initialize with backup, or initialize from lsn_
14418	16	The specified @backup_file_name was not created from database _s-_
14419	16	The specified @backup_file_name is not a database backup_
14420	16	The log shipping primary database -s_-s has backup threshold of -d minutes and has not performed a backup log operation for -d minutes_ Check agent log and logshipping monitor information_
14450	16	The specified @backup_file_name was not taken from database _s-_
14451	16	The specified @backup_file_name is not a database backup_
15012	16	The device _s- does not exist_ Use sys_backup_devices to show available devices_
15044	16	The type -s is an unknown backup device type_ Use the type disk or tape_
15061	16	The add device request was denied_ A physical device named -s already exists_ Only one backup device may refer to any physical device name_
17208	16	-s: File _s- has an incorrect size_ It is listed as -d MB, but should be -d MB_ Diagnose and correct disk failures, and restore the database from backup_
17557	16	DBCC DBRECOVER failed for database ID -d_ Restore the database from a backup_
18204	16	-s: Backup device _s- failed to -s_ Operating system error -s_
18210	16	-s: -s failure on backup device _s-_ Operating system error -s_
18273	16	Could not clear _s- bitmap in database _s- because of error -d_ As a result, the differential or bulk-logged bitmap overstates the amount of change that will occur with the next differential or log backup_ This discrepancy might slow down later differential or log backup operations and cause the backup sets to be larger than necessary_ Typically, the cause of this error is insufficient resources_ Investigate the failure and resolve the cause_ If the error occurred on a data backup, consider taking a data backup to create a new base for future differential backups_
18781	16	The value of the @backupdevicetype parameter must be one of -logical-, -disk-, or -tape-_
18782	16	Could not locate backup header information for database _s- in the specified backup device_
18786	16	The specified publication does not allow initialize with backup_
18787	16	Support for initialize with backup can not be enabled for Snapshot publications_
18790	16	Support for initialize with backup can not be enabled for non-SQL Server Publishers_
18846	16	Possible inconsistent state in distribution db: dist_backup_lsn {-08lx:-08lx:-04lx}, dist_last_lsn {-08lx:-08lx:-04lx}_
21333	16	A generation was expected to be found in -s_dbo_MSmerge_genhistory, but was not there_ If this database is a subscriber, a reinit is recommended_ If it is a publisher, a restore from backup is recommended_
21397	16	The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor_ Retry the operation again with a more up-to-date log, differential, or full database backup_
21407	16	The entire publication must be subscribed at once when setting up an -initialize with backup- subscription_
1522	20	Sort operation failed during an index build_ The overwriting of the allocation page in database __*ls- was prevented by terminating the sort_ Run DBCC CHECKDB to check for allocation and consistency errors_ It may be necessary restore the database from backup_
7909	20	The emergency-mode repair failed_You must restore from backup_
9003	20	The log scan number -S_LSN passed to log scan in database __*ls- is not valid_ This error may indicate data corruption or that the log file (_ldf) does not match the data file (_mdf)_ If this error occurred during replication, re-create the publication_ Otherwise, restore from backup if the problem results in a failure during startup_
3301	21	The transaction log contains a record (logop -d) that is not valid_ The log has been corrupted_ Restore the database from a full backup, or repair the database_
3313	21	During redoing of a logged operation in database __*ls-, an error occurred at log record ID -S_LSN_ Typically, the specific failure is previously logged as an error in the Windows Event Log service_ Restore the database from a full backup, or repair the database_
3314	21	During undoing of a logged operation in database __*ls-, an error occurred at log record ID -S_LSN_ Typically, the specific failure is logged previously as an error in the Windows Event Log service_ Restore the database or file from a backup, or repair the database_
3315	21	During rollback, the following process did not hold an expected lock: process -d with mode -d at level -d for row -S_RID in database __*ls- under transaction -S_XID_ Restore a backup of the database, or repair the database_
3316	21	During undo of a logged operation in database __*ls-, an error occurred at log record ID -S_LSN_ The row was not found_ Restore the database from a full backup, or repair the database_
3417	21	Cannot recover the master database_ SQL Server is unable to run_ Restore master from a full backup, repair it, or rebuild it_ For more information about how to rebuild the master database, see SQL Server Books Online_
3431	21	Could not recover database __*ls- (database ID -d) because of unresolved transaction outcomes_ Microsoft Distributed Transaction Coordinator (MS DTC) transactions were prepared, but MS DTC was unable to determine the resolution_ To resolve, either fix MS DTC, restore from a full backup, or repair the database_
3443	21	Database __*ls- (database ID -d) was marked for standby or read-only use, but has been modified_ The RESTORE LOG statement cannot be performed_ Restore the database from a backup_
3456	21	Could not redo log record -S_LSN, for transaction ID -S_XID, on page -S_PGID, database __*ls- (database ID -d)_ Page: LSN = -S_LSN, type = -ld_ Log: OpCode = -ld, context -ld, PrevPageLSN: -S_LSN_ Restore from a backup of the database, or repair the database_
9004	21	An error occurred while processing the log for database __*ls-_  If possible, restore from backup_ If a backup is not available, it might be necessary to rebuild the log_

Server: Msg 7391, Level 16, State 1, Line 2

Problem Symtomps
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.

Cause
The problem occurs because Microsoft Distributed Transaction Coordinator (MS DTC) is not configured for network access. By default, the network access settings of MS DTC are disabled on new installations of SQL Server 2000 on computers that are running Windows Server 2003.
Resolution

Step 1: Open Component Services Wizard using below steps
Go to -> Start -> All Programs -> Administrative Tools -> Component Services

Step 2: Expand the Componenet Service and Double Click on Computers

Step 3: Right Click on MyCompute and select properties

Step 4: Click on MSDTC Tab and then click on Security Configuration

Step 5: In the Security Configuration dialog box, check the Network DTC Access check box

Step 6: Under  Network DTC Access, click on New transaction

 Please find the below images for the same.



Figure 2



Figure 3