Error 1456 in Mirroring during add witness server 1456

Error message:-

The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)

Solution:-

Go To My computer right click on it and go to property and click on change 

Reverse Log shipping Setup

If database size big and in different data center and we need to reverse log shipping without restore full database.

Follow below steps:-

STEP 1:-

1)      Go to Primary Server and disable backup job

2)      Go to secondary Server run copy job manually and after that run restore job manually after completed the job disable both the jobs.

STEP 2:-

GO TO primary server and take log backup with no recovery command:-

Backup log REVERSE_LOGSHIPPING to disk='d:\REVERSE_LOGSHIPPING.trn' with no recovery

After execution this command you will see your primary Server will show in with restoring mode.

 

 

Step:3

Go to secondary Server and restore log backup that we took in step 2 with Recovery option:-

Use [Master]

Restore log REVERSE_LOGSHIPPING from disk='d:\REVERSE_LOGSHIPPING.trn' with recovery

Now your database will we operational from restoring mode.

 



Step 4:-

Now you can configure logshipping again:-

Right click on the database [REVERSE_LOGSHIPPING] and configure the log shipping same way .

 

That’s all:-

If you want to see the data on both side then you have to take secondary database in operational if it’s on restoring state or if you configure as read_only then you can query easily.

If database in restoring mode you need to run the below command and after that you need to setup log shipping again better option go with stand_by\read only option.

As of now for testing purpose we are making secondary database operational from restoring state:-

 STEP 5 :-

You can see the same record exist on secondary server as well:-

See below screen shot:-

Row count from secondary Server (29)

Row count from Primary Server (29) rows

How to troubleshoot when all Login Removed No Admin Rights

No Admin right for User and sa is disable

1) sa is disable

2) Windows user [Domainname\ashish.malwal] don’t have access to create any table or database it has public role.

How to get backup sys admin right and how to give sys admin right to user?

Steps:-

1.       net stop "SQL Server (MSSQLSERVER)"

2.       net start "SQL Server (MSSQLSERVER)" /m  ---single user mode

1.       sp_addsrvrolemember [Domainname\ashish.malwal],sysadmin ----Give sys admin right existing user

2.       alter login sa enable ---enable sa  login if disable

Now check SSMS and try to create table:-

Get MDF and LDF File Statistics Using fn_virtualfilestats

You can get what is the size of the file as well how many times the reads and writes are done for each file. It also displays the read/write data in bytes with the following script.

SELECT DB_NAME(vfs.DbId) DatabaseName,
       mf.name,
       mf.physical_name,
       vfs.BytesRead,
       vfs.BytesWritten,
       vfs.IoStallMS,
       vfs.IoStallReadMS,
       vfs.IoStallWriteMS,
       vfs.NumberReads,
       vfs.NumberWrites,
       (cast(SIZE AS bigint)*8)/1024 Size_MB
FROM ::fn_virtualfilestats(NULL,NULL) vfs
INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId
AND mf.FILE_ID = vfs.FileId

SQL Server Satrtup Parameters .

SQL Server Parameters when starting Instance:

1) -m -> Single User Mode

2) -f -> Minimal Configuration Mode

3) -c -> Starts instance quickly by not contacting Service Control Manager.

4) -g -> To set MTL portion of SQL Server

5) -n -> Does not use the Windows application log to record SQL Server events.

6) -T -> Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the server with nonstandard behavior.

7) -m <ClientApplicationName> -> Only connections from a specific client application are allowed.

Manage SQL Server Services from the Command Line

You can start and stop Sql Services by cmd by following commands:-

NET START MSSQLSERVER:-

 Starts SQL Server as a service. 

NET STOP MSSQLSERVER :-

Stops SQL Server when running as a service. 

NET PAUSE MSSQLSERVER:-

 Pauses SQL Server when running as a service. 

NET CONTINUE MSSQLSERVER:-

 Resumes SQL Server when running as a service.

To manage named instances of SQL Server, use the following commands: 

NET START MSSQL$instancename :-

Starts SQL Server as a service, where instancename is the actual name of the database server instance. 

NET STOP MSSQL$instancename:-

 Stops SQL Server when running as a service, where instancename is the actual name of the database server instance. 

NET PAUSE MSSQL$instancename :-

Pauses SQL Server when running as a service, where instancename is the actual name of the database server instance. 

NET CONTINUE MSSQL$instancename:-

 Resumes SQL Server when running as a service, where instancename is the actual name of the database server instance.

You can add startup options to the end of net start MSSQLSERVER or net start MSSQL$instancename commands. Use a slash (/) instead of a hyphen (–) as shown in these examples: 
net start MSSQLSERVER /f /m 
net start MSSQL$CUSTDATAWAREHOUS /f /m

Find OS information with SQL Query

EXEC master..xp_cmdshell 'systeminfo'