Skip to content

SQL server fix the orphan users issue

To fix the orphan users in database .
1) To get the number of orphan users

2) To map the orphan users with existing login


3)To create the new login for the orphaned user


1)sp_change_users_login cannot be used to map database users to Windows-level principals,
certificates, or asymmetric keys
2)sp_change_users_login cannot be executed within a user-defined transaction.
3)sp_change_users_login cannot be used with a SQL Server login created from a
Windows principal or with a user created by using CREATE USER WITHOUT LOGIN.

The server threw an exception. [0x80010105]

When I opened SQL Server 2008 R2 –
SQL Server Configuration Manager, the right hand pane had the
following error “The server threw an exception. [0x80010105]“.

After some research I found Qiao Weilin’s answer on MSDN.

The answer was quite simple. Restart the
“Windows Management Instrumentation” service.

Start -> Run -> “Services.msc”, scroll down to
“Windows Management Instrumentation”, right-click and “Restart”.

SQL SERVER move the system databases from one location to other

Hi ,

To move the system databases the following steps needs to be taken.
  • Stop the instance of SQL Server if it is started.
  •  Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.NET START MSSQLSERVER /f /T3608
  • For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.
  • If it is a MSDB
  •  Then copy the datafiles from the old location to the new updated location.
  • Stop the instance of SQL Server
  • Start the instance of SQL Server. For example, run: NET START MSSQLSERVER.



These tables make up a published API for obtaining system information from SQL Server. Microsoft maintains the compatibility of these components from release to release. The format of the system tables depends upon the internal architecture of SQL Server and may change from release to release. Therefore, applications that directly access the undocumented columns of system tables may have to be changed before they can access a later version of SQL Server.


The topics in this section describe the system tables that store information used by database backup and restore operations.

  • BACKUP file:this table is stored in a MSDB database.

select * from backupfile

  • BACKUPFILEGROUP : this table is stored in a MSDB database. Contains one row for each filegroup in a database at the time of backup.backupfilegroup is stored in the msdb backupfilegroup is new in the Microsoft SQL Server 2005 Database Engine.

select * from backupfilegroup

  • Contains one row for each media family. If a media family resides in a mirrored media set, the family has a separate row for each mirror in the media set. Media sets that exist at the time of upgrade to Microsoft SQL Server 2005 have a single row per media family. This table is stored in themsdb database

select * from backupmediafamily

  • BACKUPMEDIASET : Contains one row for each backup media set. This table is stored in themsdb database.

select * from backupmediaset

  • BACKUPSET : Contains a row for each backup set. Abackup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.

This table is stored in the msdb database.

select * from backupset

  • LOGMARKHISTORY : Contains one row for each marked transaction that has been committed. This table is stored in themsdb

select * from logmarkhistory

  • RESTOREFILE: Contains one row for each restored file, including files restored indirectly by filegroup name. This table is stored in themsdb

select * from restorefile

  • RESTOREFILEGROUP: Contains one row for each restored filegroup. This table is stored in themsdb

select * from restorefilegroup

  • RESTOREHISTORY: Contains one row for each restore operation. This table is stored in themsdb

select * from restorehistory

10)suspect_page : Contains one row per page that failed with an 824 error (with a limit of 1,000 rows). These pages are suspected of being bad but may actually be fine). This table is stored in the msdb database.

A database administrator can insert, update, and delete records from the suspect_pages table.

select * from suspect_pages

  • SYSOPENTAPES : Contains one row for each currently open tape device. This view is stored in themaster database.

select * from sysopentapes


  • SYSALTFILES: Under special circumstances, contains rows corresponding to the files in a database. This table is stored in the master database.

select * from sysaltfiles

  • SYSLOCKINFO: Contains information on all granted, converting, and waiting lock requests. This table is a denormalized tabular view of internal data structures of the lock manager, and is stored only in themaster

    select * from sys.syslockinfo

  • SYSCACHEOBJECTS : Contains information about how the cache is used.syscacheobjects belongs to the master  The following table shows cache lookup keys.

select * from sys.syscacheobjects

  • SYSLOGINS: Contains information about each logins

  select * from sys.syslogins

  • SYSCHARSETS: Contains one row for each character set and sort order defined for use by Microsoft® SQL Server™. One of the sort orders is marked insysconfigures as the default sort order, which is the only one actually in use.

   select * from sys.syscharsets

  • SYSMESSAGES: Contains one row for each system error or warning that can be returned by Microsoft® SQL Server™. SQL Server displays the error description on the user’s screen.

select * from sys.sysmessages

  • SYSCONFIGURES : Contains one row for each configuration option set by a user.sysconfigures contains the configuration options defined before the most recent Microsoft® SQL Server™ startup, plus any dynamic configuration options set since then. This table is only in the master

 select * from sys.sysconfigures


  • SYSOLEDBUSERS : Contains one row for each user and password mapping for the specified linked server. This table is stored in themaster

  select * from sys.sysconfigures


  • SYSDATABASES : Contains one row for each database on Microsoft® SQL Server™. When SQL Server is initially installed,sysdatabasescontains entries for the mastermodelmsdb, mssqlweb, and tempdb  This table is stored only in themaster database.

  select * from sys.sysdatabases



  • SYSPROCESSES: Thesysprocesses table holds information about processes running on Microsoft® SQL Server™. These processes can be client processes or system processes. sysprocesses is stored only in the master


select * from sys.sysprocesses




  • SYSDEWICES: this table provides the information about the each device connected to it. Like backup file, backup tape .


select * from sys.sysdevices


  • SYSREMOTELOGINS: Contains one row for each remote user allowed to call remote stored procedures on Microsoft® SQL Server™.

select * from sys.sysremotelogins

  • SYSSERVERS : Contains one row for each server that Microsoft® SQL Server™ can access as an OLE DB data source

select * from sys.sysservers

SQL Server IO troubleshooting

I/O requests taking longer than 15 seconds to complete on file

Following are common reasons for I/O Bottleneck in SQL Server:

  1. SQL Server is spawning more I/O requests than what I/O disk subsystem could handle.
  2. There could be an issue with I/O subsystem or driver/firmware issue or Misconfiguration in I/O Subsystem so the Disks are performing very slowly and hence SQL Server is affected.
  3. Some other process on the system is saturating the disks with I/O requests. Common application includes AV Scan, System Backup etc. So I/O requests posted by SQL Server becomes slow.

I/O Bottleneck

SQL Server performance highly relies on the Disk performance.  SQL Server I/O Bottleneck can be identified through

  1. PAGEIOLATCH_xx or WRITELOG wait types in sysprocesses and other DMV’s
  2. I/O taking longer than 15 seconds in SQL Server Error log

SQLServer has encountered X occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [L:\mssql\data\File.ldf] in database [IOTEST (7).The OS file handle is 0x000006A4. The offset of the latest long I/O is: 0x000001e616fa00

  1. By looking at I/O latch wait statistics in sys.dm_os_wait_stats

Select  wait_type,waiting_tasks_count,wait_time_ms

from sys.dm_os_wait_stats

where wait_type like ‘PAGEIOLATCH%’

order by wait_type

  1. By looking at pending I/O requests and isolating the disks, file and database in which we have I/O Bottleneck.

select database_id,file_id,io_stall,io_pending_ms_ticks,scheduler_address

from sys.dm_io_virtual_file_stats(NULL, NULL)t1,

sys.dm_io_pending_io_requests as t2

where t1.file_handle = t2.io_handle



How to troubleshoot?

Disk Perfmon counters can be used to identify which of above three is causing I/O Bottleneck.

  • Disk Bytes /sec: Total read and write to disk per second in bytes.
  • Process: IO Data Bytes/Sec: Total read and writes to disk per second in bytes by each process.
  • Buffer Manager: Page Read/sec + Page Writes/sec: Total read and writes to disk per second in bytes by SQL Server process.
  • Disk sec/Transfer: Time taken to perform the I/O operation

Ideal value for Disk sec/Transfer is 0.005-0.015 sec. If you consistently notice this counter is beyond 0.015 then there is a serious I/O bottleneck.

Look for Disk Bytes /sec immediately if it is below 150 MB for SAN disk and Below 50 MB for Single disk then the problem is with I/O subsystem engage hardware vendor.

If Disk sec/Transfer > ==0.015 consistently and Disk Bytes /sec < 150 (For San) or Disk Bytes /sec < 50 (For Local) or Disk Bytes /sec < Speed of disk as per Vendor

There is Issue with I/O subsystem or driver/firmware issue or Misconfiguration in I/O Subsystem.

Identify the process which is posting excessive I/O request using Process: IO Data Bytes/Sec.

If (Identified process == SQLServer.exe)

  • Identify and tune the queries which is Spawning excessive I/O.
  • Reads+ Writes column in profiler, Dashboard reports or sys.dm_exec_query_stats and sys.dm_exec_sql_text can be used to identify the query.
  • Use DTA to tune the query

If (Identified process! = SQLServer.exe)

  • Engage the owner of application which is spawning excessive I/O

MSDTC on server ” is unavailable

While setting up the transactional replication with uneatable subscriber. I got the below error

TITLE: New Subscription Wizard

Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication.


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


MSDTC on server ‘Server_Name’ is unavailable.
Changed database context to ‘Go’. (Microsoft SQL Server, Error: 8501)

For help, click:



After lot of R&D ,google and referred lot of links, Then  i got right fix which worked for me.

Here i am sharing those steps. It might help some else also

Go to subscription server

Run—>services.msc—>Distributed transaction coordinator service(Start it ,if its in stopped state)

Recreate the subscriber again or check the status again

Does it work?? No??


Follow the below steps

  • Go to Administrative Tools -> Component Services
  • Expand Component Services -> Computers ->
  • Right-click -> Properties -> MSDTC tab
  • Hit the Security Configuration button
  • Enable network DTC ACCESS and enable Allow remote clients and allow remote Administration and make sure that allow inbound and allow outbound options are checked(Click ok, it will restart the MSDTC service)

Later execute this procedure on subscriber side

sp_link_publication @publisher=’Publisher Name”,
@publisher_db=’Publication dadatabse”,
@publication=’Replication Name’,
@distributor=’Distributor Server


Please Note: This is the not the exact solution this is the solution which worked for me



sushil Naik


As a DBA some times we forgot to arrange backup files in a proper manner like

  • the back file name is not correct.
  • Backup belongs to which server
  • Backup belongs to which database
  • Time of the backup
  • Date of the backup
  • Who Performed the backup operation

For above problems Microsoft provided a solution “Verify the backup”

FROM DISK = ‘C:\backups\Adventureworks.bak’


Attach mdf file without LDF file

Attach mdf file without LDF file:

if .ldf file is not there and we need to attach .ldf file we can rebuild the .ldf file by following ways.

— Method 1: I use this method
EXEC sp_attach_single_file_db @dbname=’TestDb’,
@physname=N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf’

— Method 2:
(FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf’)

— Method 3:
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf’)

Database mirroring Administration in SQL SERVER

— Query to check mirroring status

Select DB_NAME(database_id),mirroring_state,mirroring_state_desc,mirroring_role_desc,


from sys.database_mirroring

where mirroring_guid is not null



State of the mirror database and of the database mirroring session.

0 = Suspended

1 = Disconnected from the other partner

2 = Synchronizing

3 = Pending Failover

4 = Synchronized

5 = The partners are not synchronized. Failover is not possible now.

6 = The partners are synchronized. Failover is potentially possible.

NULL = Database is inaccessible or is not mirrored.



Current role of the local database plays in the database mirroring session.

1 = Principal

2 = Mirror

NULL = Database is inaccessible or is not mirrored.


Safety setting for updates on the mirror database:

0 = Unknown state

1 = Off [asynchronous]

2 = Full [synchronous]

NULL = Database is inaccessible or is not mirrored.


Creation of database mirroring endpoint


 CREATE ENDPOINT endpoint_mirroring








 Change Transaction Safety in a Database Mirroring Session (Transact-SQL):

  • By default, the level of transaction safety is set to FULL (synchronous operating mode).
  • Turning off transaction safety shifts the session into asynchronous operating mode, which maximizes performance.

Ø  To turn on transaction safety

Connect to the principal server.


Database – Mirror Server database

  • To Turn off transaction safety

Connect to the principal server.


Database – Mirror database

Manually Fail Over a Database Mirroring Session:

Connect to the principal server.

Use master



database_name  is  the mirrored database.

Force Service in a Database Mirroring Session(disaster recovery method):

If the principal server fails while the mirror server is available, the database owner can make the database available by forcing service to fail over (with possible data loss) to the mirror database. This option is available only under all the following conditions:

  1. a.      The principal server is down.
  2. b.      WITNESS is set to OFF or is connected to the mirror server

Connect to the mirror server.


Pause a Database Mirroring Session:

At any time, the database owner can suspend a database mirroring session, which might improve performance during bottlenecks.

Connect to either partner


Database_name is mirror server database




Resume a Database Mirroring Session:

At any time, the database owner can resume a suspended database mirroring session.

Connect to either partner.




Remove the Witness from a Database Mirroring Session:

o   Turning off the witness changes the operating mode in accordance with the transaction-safety setting:

o   If transaction safety is set to FULL (the default), the session uses high-safety, synchronous mode without automatic failover.

o   If transaction safety is set to OFF, the session operates asynchronously (in high-performance mode) without requiring quorum. Whenever transaction safety is turned off, we strongly recommend also turning the witness off. For more information

o   The transaction safety setting of the database is recorded on each partner in the sys.database_mirroring catalog view in the mirroring_safety_level and mirroring_safety_level_desc columns

select * from sys.database_mirroring


To turn off the witness:

Connect to either partner


Database_Name is mirror server database name


Remove Database Mirroring:

the database owner can manually stop a database mirroring session by removing mirroring from the database.


Connect to either partner.


Database_name is mirror database




Data storage format in SQL server.

Thanks for reading this.


Data in SQL server stores in the form of pages.

Page: Data storage format each page is of 8KB.each page contains 96 bytes of
header which is having the information about the page.

Extents: pages are again grouped called as extents. Each extent contain 8 pages .

Page= 8KB

Extent=8 pages= 64KB

There are again 2 kinds of extents

1) Mixed extent: contains various database objects ex:tables,views,stored
2) Uniform extents: contains only one type of database objects ex: only tables
or only views etc.


File: the collection of pages is known as file. There will be a reserved page in each
file which contains attributes of the file like memory and address information.

There are 2 kinds of files

1) Data file: data file stores the data .which ends with the extension (.mdf)
There will 1 primary data file and many number of secondary data files\
Secondary data file ends with the extension (.ndf)

2) Log file: log file stores the information about the log transactions. Which
ends with the extension (.ldf).there will be only one log file for single

NOTE: whenever any DATABASE is created there will be creation of DATA
FILE and LOG FILE in the following location with the extension (.mdf),(.ldf)
respectively “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data” data
file can be many but log file is only one for a any database.

File group: collection of files is known as file group

There are 2 kinds of file groups

1) Primary file group

2) User defined file group