How to resolve/ fix Orphaned users from SQL Database.

 Below is a small snippet of cursor created to identify all the users who have the flag of IsSQLUser except for SID not Null & SID <> 0X0 (because they dbo, Guest, Information_Schema & sys accounts) and run Auto fix the orphaned users.

DECLARE @USRNAME VARCHAR(100), @COMMAND VARCHAR(100)
DECLARE CURSORS INSENSITIVE CURSOR FOR

SELECT NAME AS USERNAME FROM SYSUSERS
WHERE ISSQLUSER = 1 AND (SID IS NOT NULL AND SID <> 0X0)
AND SUSER_SNAME(SID) IS NULL ORDER BY NAME

FOR READ ONLY

OPEN CURSORS
FETCH NEXT FROM CURSORS INTO @USRNAME
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @COMMAND='SP_CHANGE_USERS_LOGIN ''AUTO_FIX'', '''+@USRNAME+''' '
EXEC(@COMMAND)
FETCH NEXT FROM CURSORS INTO @USRNAME
END

CLOSE CURSORS

DEALLOCATE CURSORS.

How To Shrink The TEMPDB Database in SQL SERVER ?

Occasionally, Every DBA will bump across this issue, Tempdb database is getting full, Tempdb Drive has exceeded the disk threshold and need to take action to reduce the file size.

DBAs having little experience would think of shrinking the database using the GUI, Right Click on the Database > Tasks > Shrink > Database / Files and then shrink the database accordingly.

DBAs with some experience would think of running the command DBCC Shrinkfile ('Filename', target_in_MB)

However, with both the Solutions / Steps,  files / database doesn't shrink  and you can see that the file size is not having any impact.

So, here  is a couple of solutions you can try to shrink the Tempdb database and reduce the size on the drive accordingly.

1.  Try running the following command :-

USE TEMPDB
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ('ALL')
GO

and then run the command.

DBCC SHRINKFILE ('FILENAME', TARGET_SIZE_IN_MB)
GO

If there are more than one Tempdb data ( Tempdev )  file , you can use those files seperately to run the DBCC Shrinkfile command to shrink the file size after clearing the DBCC System , Session and Proc Caches

These are the DBCC commands to free up some of the SQL SERVER Memory Caches.

DBCC FREESYSTEMCACHE ('ALL')
Clears all unused cache entries from all the caches.

DBCC FREESESSIONCACHE
Flushes all the distributed query connections' cache that are used against the instance of Microsoft SQL Server.

DBCC FREEPROCCACHE
Clears all elements from the procedure cache.
Freeing the procedure cache would cause, an ad-hoc SQL statement to be recompiled rather than reused from the cache.


2.  Here's another solution which can help to shrink the tempdb database.

Add a few MB to the data file and then try shrinking the file., it will release some space to the OS.

For Example:- when you find the tempdb is growing and has a couple of hundreds of MBs to reach the threshold. You can try shrinking the database normally. If there's any bulk transaction running against the instance or If there's a replication set up on the database.
Then, you can increase the tempdb data file to a few MBs and then try shrinking the file using the DBCC command to claim more space.

Troubleshooting the 'KILLED/ROLLBACK' sessions in SQL Server

One of the not so frequent issues encountered off lately which doesn't has any other solution than to restart the SQL Service ( Recycling ) is when you have the Command status as 'KILLED/ROLLBACK' for any of the sessions in sysprocesses.

The Command of the sysprocesses session 'KILLED/ROLLBACK'  happens when there's any DML ( INSERT , UPDATE, DELETE ) activity happening against any database and it is being interrupted or killed before the process is complete.

Some of the scenarios where u might encounter this situation :-

1.    Database backup using any third party software is taking longer than expected and the process is being killed. This session will lead to 'Killed/Rollback' status.
2.    Any DML statement running via linked server connecting from another server and the process is being killed from either end will lead to the 'Killed/Rollback' Status.
3.    Any DMLs being killed from the application end before the process completes, will lead the process to 'Killed/Rollback' State.

To determine which process is hung or in the state of 'Killed/Rollback' , You can use the following commands :-

SP_Who2

OR

SELECT spid
,kpid
,login_time
,last_batch
,status
,hostname
,nt_username
,loginame
,hostprocess
,cpu
,memusage
,physical_io
FROM sys.sysprocesses
WHERE cmd = 'KILLED/ROLLBACK'

After figuring out the spid and kpid, run the DBCC INPUTBUFFER (<SPID>) to know what is the batch running under this session.

There are a few blogs recommending to kill the kpid session from the task manager.
** Please be careful about what you are doing. This is not recommended, especially,If there's any third party tool involved in the process. You may hang the third party tool's service as well, while trying to kill this session from the task manager**.

You can use this technique of killing the kpid to release the session, If there's any stored procedure or a batch running against the database directly and due to some reasons it was killed and the session moved to 'Killed/Rollback' status, that should work., However, that's never going to be the case.

So, we can try using the below command to see the status of the session.

KILL  <SPID>  with STATUSONLY

If you would get the output message as follows :-
   
SPID <spid> : transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

Only if you find the percentage complete is any number other than 0% or 100% and the time remaining is other than 0 or 554 seconds, only then, you still have a hope of getting this command completed by the time displayed, else, this process can go on hung until you restart the SQL Server service.

If there's a linked server or replication process involved in this session, here's what we can do :-

Try restarting the DTC ( Distributed Transaction Coordinator) service, which could release the session from 'Killed/Rollback' Status.

This could work if there's any Linked Server or Replication service involved in the process, OR, if there's any application triggered stored procedure which was closed from the front-end before the transaction committed.

Considering this issue occurring on the Production server where you would not have the leverage of restarting the SQL Server Service frequently.

There's one more option which you could try before giving up or concluding to restarting the SQL Server service :-

KILLING THE PROCESS USING THE Dedicated Administrator's Connection (DAC).

You need to have the DAC enabled / activated before your can think of using this option.

So, if we have the DAC enabled and if you are able to connect to the DAC session, Please go ahead and run the appropriate queries to determine the spids associated with the command 'KILLED/ROLLBACK' and kill them. This could resolve the issue.

If the above said scenarios or options didn't work for you, please don't think anything else, go ahead and have the SQL Service restarted to release these sessions.

What is SQL Server Log Shipping:

 Log Shipping allows replicating databases using the database transaction log. The transaction log contains all the transactions in the database. The main idea is to copy the transaction log to a secondary database in order to replicate the information. So, you need a Primary Server with a database, configure log shipping to have some transactional log backups, and then copy it to a secondary database to have a replica.

Log Shipping Involves below steps

Backup Job – Backup the transaction log file on the primary SQL Server Instance or any backup share.

Copy Job – Copy the transaction log backup file across the network to one or more Secondary SQL server instances

Restore Job – Restore the copied transaction log on the secondary SQL server instance.

Terms related to Log Shipping

Primary Server -The instance of SQL Server, which is your production server is called as the primary server.

Primary Database - The database on the primary server which you want to backup to another server is called as the primary database.

All Log Shipping administrative activities are performed from the primary database with the help of SQL Server management studio.

Secondary Database - The worm standby copy of the primary database is called as secondary database.

The secondary database can be in below two states

• Recovering
• Standby

Monitor server

Monitor Server tracks all the details of log shipping. It includes below things

• When the transaction log on the primary database was last backed up.
• When the secondary server last copied and restored the backup files.
• Information about any backup failure alert.

Monitor server is optional.

Note – We cannot make any changes in monitor server configuration without removing log shipping.

Operating Modes

There are two modes and they are based on the state in which the secondary database will be

• Standby mode – The database is available for querying and users can access it, but in read only mode. The database will not be available when restore process is running.
• Restore mode – The database is not accessible

TUF file

TUF stands for “Transaction Undo File”.

This file contains information regarding any changes that were made as part of incomplete transactions at the time the backup was performed.

It is required if a database is loaded in read state. In this state further transaction log backups may be applied.

It is created while performing log shipping to a server in standby mode.

If TUF file is corrupted or lost, log shipping will not work, and we need to setup it again.

WRK File

The WRK files are produced when the transaction log backups are copied from the backup location to a secondary server.

Once the copy operation completed successfully file renamed to “.trn” file.

It ensures that the files are not picked up by restore job until it successfully copied.

Permissions

The usage of sqllogship involves the use of Windows Authentication . The Windows Authentication account running the command must have access to the Windows directory and fulfill the requirements of SQL Server Permissions , which vary depending on whether the command includes the -backup, -copy, or -restore option.

Option	Directory access	Permissions
	-backup	Needs both read and write permissions for the backup folder.	The permissions needed are identical to those required for the BACKUP statement. Additional details can be found in the Transact-SQL documentation for BACKUP.
	-copy	Necessary permissions include the ability to read from the backup directory and the ability to write to the copy directory.	The permissions needed are identical to those required by the stored procedure sp_help_log_shipping_secondary_database.
	-restore	Access to both read and write functions is necessary for the copy directory.	The permissions required for this are identical to those needed for the RESTORE statement. Additional details can be found in the RESTORE (Transact-SQL) documentation.

 

sqllogship Application:

The sqllogship application performs a backup, copy, or restore operation and associated clean-up tasks for a log shipping configuration. The operation is performed on a specific instance of Microsoft SQL Server for a specific database.

 

sqllogship Application:

Primary Node: Log Shipping: SQL Jobs: sqllogship (Application Executable):

C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Backup ...

Secondary Node: Log Shipping: SQL Jobs: sqllogship (Application Executable):

C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Copy ...

C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Restore ...

Failing Over:

To fail over to a secondary database:

1.      Copy any uncopied backup files from the backup share to the copy destination folder of each secondary server.

2.     Apply any unapplied transaction log backups in sequence to each secondary database. For more information, see Apply Transaction Log Backups (SQL Server).

3.     If the primary database is accessible back up the active transaction log and apply the log backup to the secondary databases. You may need to set the database to single-user mode to obtain exclusive access before issuing the restore command, and then switch it back to multi-user after the restore completes.

4.      If the original primary server instance is not damaged, back up the tail of the transaction log of the primary database using WITH NORECOVERY. This leaves the database in the restoring state and therefore unavailable to users. Eventually you will be able to roll this database forward by applying transaction log backups from the replacement primary database.

5.     For more information, see Transaction Log Backups (SQL Server).

6.      After the secondary servers are synchronized, you can fail over to whichever one you prefer by recovering its secondary database and redirecting clients to that server instance. Recovering puts the database into a consistent state and brings it online.

7.      After you have recovered a secondary database, you can reconfigure it to act as a primary database for other secondary databases.

8.     If no other secondary database is available, see Configure Log Shipping (SQL Server).

Different ways to monitor Log Shipping for SQL Server databases

Log Shipping is a basic SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover or reporting purposes.

You can use the below items to investigate if there are any issues with your databases that are setup for Log Shipping.  We will cover each of these items and how they can be used.

• SQL Server Error Log
• SSMS Built In Report
• System Stored Procedures
• Query the MSDB database
• Application/System EventViewer Log

 

I’ve gotten a lot of great questions about log shipping, so I’ve put together an FAQ.

• What editions of SQL Server is log shipping available in?
• 2012 – Enterprise, Business Intelligence, Standard, and Web
• 2008R2 – Datacenter, Enterprise, Standard, Web, and Workgroup
• 2008 – Enterprise, Standard, Web, and Workgroup
• 2005 – Enterprise, Standard, and Workgroup
• Does the secondary need to be licensed?
• I am not the licensing police, and I am not Microsoft – check with your licensing representative to clarify your exact situation. Generally, you can have one warm standby server. However, the second someone starts using it for reporting, testing, or anything else, you need to license it like any other server.
• Log shipping is compatible with backup compression. What edition of SQL Server do I need to take advantage of compression?
• 2012 – Enterprise, Business Intelligence, or Standard
• 2008R2 – Datacenter, Enterprise, or Standard
• 2008 – Enterprise
• 2005 – Not available
• When log shipping is set up, Agent jobs are created to alert me if a backup, copy, or restore fails. How do I get notified?
• You need to go into the Agent job, pull up Notifications, and choose your method – email an operator, or write to the event log, for example.
• Are my logins shipped from the primary to the secondary?
• No, they are not. You’ll need to set up a separate method to sync the logins.

• Does this replace, or can it be combined with, our existing daily full and log backups?
• TL; DR – no.
• You’ll still want to take regular full and/or differential backups. Log shipping only takes one full backup – at the beginning – and that’s only if you specify that it does so. It can also be initialized from an existing full backup.
• Taking two log backups in separate jobs will break the log chain, however. If you implement log shipping, it will replace your current transaction log backup job.
• What’s the difference between the secondary being in “Restoring” vs. “Standby”?

·  Restoring means the database is not accessible. Standby means it is read-only. You make this decision when you set up the log shipping.

• If the database is in Standby mode, users can query it – except when a log backup is being restored. You need to decide if a restore job will disconnect users, or if the restore is delayed until after the users are disconnected.

What are the phases of sql server database restore process?

There are two types of recoveries:

1) Restart Recovery

·        Every time an instance is restarted/ started the consistency of all the databases including master, model, msdb and tempdb is checked.

·        This process is an internal operation and initiated just to keep the entire instance clean and with integrity.

·        Taking database offline/online involves restart recovery for that database.

2) Restore Recovery

·        As per backup strategy whenever a restore is started and recovery is done per backup sequence.

·        This entire process of recovery initiated manually is called as Restore recovery.

Following the data copy phase involving copying of all the data, log, and index-pages from the backup media of a database to the database files, four consecutive phases that take place during SQL Server recovery are

1.       Discovery – is to find the logical structure of the Transaction log file.

2.       Analysis – is to find the best LSN starting from which rolling forward can be done during redo phase.

3.       Redo – is the phase during which the changes caused by active transactions (at the time of crash) are hardened onto Data files.

4.       Undo – is the phase where in, rolling back of the active transactions for consistency, takes place.

Discovery Phase:

The first phase of recovering a database is called discovery where all the VLFs are scanned (in serial and single threaded fashion) before actual recovery starts. Since this happens much before the analysis phase, there are no messages indicating the progress in the SQL Server error log. Depending on the number of VLFs this initial discovery phase can take several hours even if there are no transactions in the log that need to be processed.

This is the reason why it is preferred to have optimal number of VLFs in a log file.

For further information on influence of VLFs in Transaction Log throughput please refer point #8 in Kimberly Trip’s blog http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

 

Analysis Phase:

Preparation of ‘Dirty Page Table’ (DPT) and ‘Active Transaction Table’ (ATT) are the prime motives of Analysis phase. These two tables are put to use by SQL Server during subsequent redo and undo phases respectively.

To create DPT, SQL Server requires to make a note all the pages and their LSNs that might have been dirty (à not yet hardened) at the time of crash, from the transaction log (.ldf), so that during redo phase all such pages will be rolled forward and at the end of redo phase the database would be in such a state as if it was just before crash.

As all the pages prior to last checkpoint would have been already hardened and the pages after the last checkpoint are the ones that are dirty but yet to get hardened. Hence analysis phase starts (in the sense SQL Server starts reading using the .ldf) from the last checkpoint LSN till end of transaction log.

Scanning through the transaction log from the latest checkpoint till end of transaction log prepares the list of all pages that are dirty and obviously not hardened as they are after checkpoint. This list is the DPT. The minimum of all the LSNs available from DPT will be the minimum recovery LSN. Similarly using transaction log file, active transaction table is generated.

Redo Phase:

“Rolling forward all the changes that took place after the checkpoint and just before the crash so that at the end of redo phase the db would be in a state as if it was just before the crash”, is the intent of redo phase.

Hence making use of minimum recovery LSN obtained from DPT, starting from the minimum recovery LSN and till the LSN at end of transaction log, SQL server rolls forward (hardens) all the changes that are present in all the dirty ( not yet hardened) pages and brings the db to the desired state.

Undo Phase:

Ensuring that the data integrity is not hampered so that db can be opened for access is the aim of Undo phase. For this, all the changes made by all the transactions that were active at the time of crash are to be rolled back.

Hence, SQL server, using ATT , starting from LSN at the end of transaction log will rollback all the changes caused by all the active transactions till the LSN of beginning of oldest transaction(among the active transactions present in ATT), which is available from transaction log and opens the database for user access.

https://dba.stackexchange.com/questions/172003/very-slow-sql-server-database-recovery

 

RECOVERY vs NORECOVERY vs STANDBY

 

RECOVERY

RECOVERY is The default option. This indicates that the restore is complete and that once RESTORE is complete it is fully available. No further backups can be applied to the database as the db engine cannot guarantee consistency between what has changed in the fully recovered database and if the further backup files can be restored successfully.

use master

go

restore database adventureworks2014 with recovery

NORECOVERY

The database is in a state of RESTORING even after this RESTORE is complete as there are further RESTORE statements expected (eg t-log or differential). I’m just going to specify NORECOVERY here rather than restore from a full backup then a log backup as I do this in a further demo below.

use master

go

restore database adventureworks2014 with norecovery

 

STANDBY

Similar to NORECOVERY except that the database will accept read only connections. To do this any uncommitted transactions in the backup will be rolled back and stored in a transaction undo file (tuf.) Whilst users are running queries against the database no further restores can continue until all queries are complete (though this is not the case with log shipping.) When the next restore occurs, those uncommitted transactions in the tuf file will be rolled forward and the next log is restored.

use master

go

restore database adventureworks2014 with standby