Restore table from Backup file SQL Server 2005

Few days before I was asked to restore a Table from a backup file? Can you really attain this in sql server 2005? Which is currently under LogShipping.

And then I suggested easiest way to achieve your goal is

1)  Restore backup to another server and apply all the transactions logs with NO RECOVERY and last one with RECOVERY until where you want the data to be recovered.

2) STOP LogShipping.

3) Rename the Table which is corrupted or which you want to restore from Backup file.

4) Export the Table from the RESTORED Database which is in another server to the CURRENT production server.

5) Enable Log Shipping.

You should be good to go.

How to Remove LogShipping SSMS

Ghost Process or Ghost Cleanup Tasks in SQL Server Blocking

Yesterday I got a call from a user about Database not being connected or getting timed out, well actually there are many different solutions for resolving performance issues. 
But the issues what I had was not actually realated to performance there is something else I noticed behind the scene when I ran sp_who2 I had no clue. 

Then I ran select * from sysprocesses where blocked<>0 

to give me the list of blocked processed. 

Then I noticed GHOST CLEANUP process was constantly running and blocking the other SIPD and using most of the resources. 

First lets understand what is Ghost Process  or what are Ghost records. 


Ghost records are the records which are logically deleted but still they exist physically deleted in a page.  Just to give a brief overview, having ghost records simplfies key-range locking and transaction rollback.

The Ghost record is marked with a bit that indicates it's a ghost record and cannot be physically deleted until the transaction that caused it to be ghosted commits or until the transaction is committed. As soon as it is committed, records are deleted by an asynchronous background process (called the ghost-cleanup task) or it is converted back to a real record by an insert of a record with the exact same set of keys.

To Resolve this issue quickly, I disabled the Auto Create Statistics and Auto Update Statistics options on the database. As soon as I hit the apply button the GHOST CLEANUP process stopped.

To me it looks like when SQL tries to create stats or update them, it will run the Ghost Cleanup process first to cleanup the indexes and tables, before getting the stats.

But don't forget to create a job when you are disabling these tasks on the database. Create a job during non-business hours or when the load on the database is minimum.

Query to Find out Orphaned Users and Fixing them

When a restore of any database has been performed for example; SERVER A to SERVER B, Users cannot access the database on the SERVER B because they become Orphans even if they have Login ID on the SERVER B, to fix this. 

Run this to find out the list of Orphaned Users

EXEC sp_change_users_login 'Report'




After finding the orphaned users report, run the following command to Fix the Orphaned Users.

EXEC sp_change_users_login 'Auto_Fix', 'user'



To create a new login with password for user it can be achieved by running the below command. 

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Monitoring SQL Server Database Backups

This scripts will give you when was the last time database has been backed up.

select    sdb.name as databasename,
        coalesce(convert(varchar(12), max(bus.backup_finish_date), 101),'-') as lastbackuptime
    from
        sys.sysdatabases sdb
left outer join
        msdb.dbo.backupset bus
    on
        bus.database_name = sdb.name

    group by sdb.name