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
USE DB_NAME
EXEC SP_CHANGE_USER_LOGIN ‘report’

2) To map the orphan users with existing login

USE DB_NAME
EXEC SP_CHANGE_USER_LOGIN ‘UPDATE_ONE’, ‘USER_NAME’,’LOGIN_NAME’

3)To create the new login for the orphaned user

USE DB_NAME
EXEC SP_CHANGE_USER_LOGIN ‘AUTO_FIX’, ‘USER_NAME’,’NULL’,’DESIRED_PASSWORD’

Limitations:
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
  • ALTER DATABASE msdb MODIFY FILE( NAME = MSDBData , FILENAME = ‘D:\DATAFILES\MSDBData.mdf’ )
  •  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.

SQL SERVER SYSTEM TABLES

SYSTEM TABLES:

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.

BACKUP and RESTORE RELATED SYSTEM TABLES:

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

SYSTEM TABLES WHICH PRESENT ONLY ON MASTER DATABASE:

  • 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.

——————————
ADDITIONAL INFORMATION:

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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.5500&EvtSrc=MSSQLServer&EvtID=8501&LinkId=20476

——————————
BUTTONS:

OK
——————————

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??

then

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’,
@security_mode=0,
@login=’Login’,
@password=’password’,
@distributor=’Distributor Server

 

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

 

Cheers

sushil Naik

Aside

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”

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