Login invalid for Orphand User

When you try to fix Orphand users in newly restored database the following error will come if the login id is not match with current server



Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Login name 'prd_myUser' is absent or invalid.

Use user_dbname
go


EXEC sp_change_users_login 'REPORT'
go
--prd_myUser -- Orphand users

EXEC sp_change_users_login 'UPDATE_ONE','prd_myUser','prd_myUser'
go


-- error messageMsg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137


Terminating this procedure. The Login name 'prd_myUser' is absent or invalid.

--Solution
Find the restored database
Expand the Database  Tree
Expand Security
Select  Users to locate 'prd_myUser'  delete it.

Monitor SQL Server Performance and Activity

select @@connections as 'Total Login Attempts'
-- Returns the number of connections or attempted connections

select @@cpu_busy as 'CPU Busy', getdate() as 'Since'
-- Returns CPU processing time in milliseconds for SQL Server activity

select @@idle as 'Idle Time', getdate() as 'Since'
-- Returns SQL Server idle time in milliseconds

select @@io_busy as 'IO Time', getdate() as 'Since'
-- Returns I/O processing time in milliseconds

select @@pack_received as 'Packets Received'
-- Returns the number of input packets read from the network by SQL Server

select @@pack_sent as 'Packets Sent'
-- Returns the number of output packets written to the network by SQL Server

select @@packet_errors as 'Packet Errors'
-- Returns the number of network packet errors for SQL Server connections

select @@timeticks as 'Clock Ticks'
-- Returns the number of microseconds per CPU clock tick

select @@total_errors as 'Total Errors', getdate() as 'Since'
-- Returns the number of disk read/write errors encountered by SQL Server

select @@total_read as 'Reads', getdate() as 'Since'
-- Returns the number of disk reads by SQL Server

select @@total_write as 'Writes', getdate() as 'Since'
-- Returns the number of disk writes by SQL Server

select * from fn_virtualfilestats(null,null)
-- Returns input/output statistics for data and log files

How to find SQL Server information

Run the extended procedure to get sql server internal information
xp_msver

You can use parameter to get specific information
xp_msver 'ProductName', 'ProductVersion', 'Language', 'Platform', 'WindowsVersion', 'PhysicalMemory', 'ProcessorCount'

How to move SQL Server Databases to new location

When you plan to move user databases files to new location please follow the steps.

1. Make sure users are disconnected and planned down time

2. Make sure to take a Full backup of the databases

3. Find the data files and log files names and current location

use  master
go
SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('My_dbname')

4. Set the database you want to work with offline

ALTER DATABASE My_dbname SET OFFLINE

(OR)  

ALTER DATABASE My_dbname SET OFFLINE WITH ROLLBACK IMMEDIATE
go

 5. Move one file at a time to the new location
ALTER DATABASE My_dbname
MODIFY FILE ( NAME = My_Data, FILENAME = 'E:\MSSQL\Data\My_Data.mdf')
go

 6. You move  the data   (MDF) to new Drive/Location 


7. Set the database online
ALTER DATABASE My_dbname SET ONLINE
go

How to verifying SQL Server Backups before Restore the database

When you try to copy sql backup files from one server to another server always make sure to check with backup files to RESTORE VERIFYONLY Statement.

use master

go
RESTORE VERIFYONLY FROM 'E:\MSSQL\Backup\Testdb.BAK'
go

-- With Monitoring Options
use master
go
RESTORE VERIFYONLY FROM 'E:\MSSQL\Backup\Testdb.BAK', Status=10
go

  

Checks performed by RESTORE VERIFYONLY include:

 That the backup set is complete and all volumes are readable.

• Some header fields of database pages, such as the page ID (as if it were about to write the data).
• Checksum (if present on the media).
• Checking for sufficient space on destination devices.

Please Note :

RESTORE VERIFYONLY does not work on a database snapshot. To verify a database snapshot before a revert operation, you can run DBCC CHECKDB.

How to view Mirroring database information

Using Dynamic Management Views (DMVs) to view Mirroring database information

SELECT d.name, d.database_id, m.mirroring_role_desc,

m.mirroring_state_desc,m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL

Witness Server Information

The sys.database_mirroring_witnesses catalog view provides information on the witnesses server
SELECT principal_server_name, mirror_server_name,

database_name, safety_level_desc
FROM sys.database_mirroring_witnesses


Endpoints


Query the catalog view sys.database_mirroring_endpoints to find useful information regarding the endpoints, such as the status of the endpoint, encryption settings, etc.

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id

SQL Server Replication Providing High Availability using Database Mirroring

Another wonderful Whitepapers from Gopal Ashok and Paul S. Randal for SQL Server Replication Providing High Availability using Database Mirroring.

Writer: Gopal Ashok (Microsoft Corporation) and Paul S. Randal (SQLskills.com)

To download the entire paper please reference:

http://sqlcat.com/whitepapers/archive/2011/04/08/sql-server-replication-providing-high-availability-using-database-mirroring.aspx

SQL Server 2008 Service Pack 1 with Cumulative update package 10

Recently i have installed SQL Server 2008 Service Pack 1 with Cumulative update package 10 on production SQL Servers,


1. Before installing make sure to stop SQL Services from SQL Configuration Manager

2. Stop fdhost.exe and Windows Management Instrumentation form Services

3. Make sure Windows Installer service is started.

http://support.microsoft.com/?kbid=2279604
Note This build of the cumulative update package is also known as build 10.00.2799.00.

A network-related or instance-specific error occurred while establishing a connection to SQL Server

Check SQL Server Configuration Manger to SQL Services are running. If SQL Server Services are not running Please restart the services.

Fix Orphand Users in SQL Server

-- Select the user database name
Use  dbname
go


-- Find the Orphand Users
EXEC sp_change_users_login 'REPORT'
go

-- Fix the Orphand Users
EXEC sp_change_users_login 'UPDATE_ONE',
'User_Name','User_Name'

Biggest Mission Critical Projects in SQL Server