Connecting to the Integration Services service on the computer "SERVER_NAME" failed with the following error: "Access is denied."

TITLE: Connect to Server
------------------------------
Connecting to the Integration Services service on the computer "SERVER_NAME" failed with the following error: "Access is denied."
By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.
------------------------------
Connecting to the Integration Services service on the computer "SERVER_NAME" failed with the following error: "Access is denied."
By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

This error occurred when I upgraded SQL Server 2008R2 to SQL Server 2012.
Everything was working until then, then I realized going back to old school its permission error, after integration services installed as an administrator needs to grant access to the users and groups who were in earlier version of SQL Server 2008R2, 2005 including me who actually installed\upgraded to sql 2012.

Here is the fix that I used it works after I granted access to the users or groups as below to DCOM.
First launch Component services.
from Search type component or from command prompt  type Dcomcnfg.exe and hit enter.
And from the below figure expand DCOM Config, to the
MicroSoft SQL Server Integration Services 11.0
1) Right click and select Properties.
2) Select Security Tab
3) On Launch and Activation Permissions select Edit.
4) now Add the member or groups that needs access to Integration Services.
and Finally Restart your Integration Services and Restart SSMS.

.

Cannot see ODBC Driver after installation of Oracle 11g client.

I installed the ORACLE client tool full Administrator option
Still not able to see the ODBC drivers for Oracle?

I know I have installed full administrator. Now next thing what I did was I went back and verified what version of Oracle Client I installed, I found it was 32 bit and My server is windows server 2008 r2 is 64 Bit.
So that explains we have a compatibility issues.

Without again re-installing 64 bit Oracle 11g client.
I went into
C:\Windows\SysWOW64\odbcad32.exe and launched the ODBC data source Administrator.
Now I see all the driver of 32 bit that I installed.

In my case I need 32 bit drivers, so If you still need 64 bit drivers Please install 64 bit client Oracle 11G drivers only. 

Error: 9003, Severity: 20, State: 9 SQL Server 2012

I have got this error message earlier this morning and all my user database backups failed due to this.

The log scan number (87817:27:0) passed to log scan in database 'AdventureWorks2012' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during Startup.

These are the steps that I took to resolve the issue.

I ran DBCC CHECKDB 
and there are lot of allocations errors and database has been corrupted. 

Fix1: Restore the Previous nights good backup can fix this. 

Before you restore make sure you have good backup it can be verified by running the below.

RESTORE VERIFYONLY FROM DISK = H:\Backups\AdventureWorks2012.BAK
GO

and then restore to the earlier nights good backup.

================================================================

Fix2: 
Rename your log file or move your log file to different location. 
You can you do this by detaching the database first and then move the files or rename the files. 

USE master;
GO
EXEC sp_detach_db @dbname = 'AdventureWorks2012';

Now rename the .ldf  file or move to a different location.

CREATE DATABASE AdventureWorks2012 

    ON (FILENAME = 'D:\MSSQL\DATA\AdventureWorks2012_DATA01.mdf')
   --(FILENAME = 'E:\MSSQL\LOGS\AdventureWorks2012_LOG01.ldf') 
    FOR ATTACH;

OR

EXEC sp_attach_single_file_db @dbname = 'AdventureWorks2012', 
    @physname = 
N'MSSQL\DATA\AdventureWorks2012_DATA01.mdf';

Note: I commented .LDF file attachment, without it a NEW log file will be created in the default location.
and run the DBCC CHECKDB (Databasename) 

Evaluation period has expired, SQL Server 2012

How to upgrade Evaluation edition or use Licence key to the SQL Server Services.

Run SQL Server 2012 Setup file as administrator and select maintenance link as shown below. 

Select Edition Upgrade; Edition Upgrade meaning you are actually Upgrading Evaluation edition to Enterprise edition by entering Licence key. Make sure you have Enterprise Edition KEY only other editions keys are not Accepted. 

And finally restart services and start the Management Studio.

Role Management Tool to Install .NET FrameWork 3.5

You get this error when Installing SQL Server 2012 Enterprise edition. 

Because SQL Server 2012 ships with .NET 4.0 Framework. 

And Windows 2008R2 already has 3.5 inbuilt you just need to enable the already existed feature within WINDOWS 2008R2 from server manager as shown below. 

 

Server Manager Enable .NET FrameWork 3.5.1 Feature. 

Right click on computer and select Manager and browse to Features as shown below. 

Once selected this feature and continue SQL Server 2012 Installation it will be go smooth. 

Shrinking Database Log Files.

Often many get confused when the log grows and don't know what to do?

So here it goes.

It is recommended that you back up the log immediately before switching the recovery model of your database From FULL TO Simple.

backup log blogs_db_log with truncate_only;
You still have a point in time of recovery if your database goes down during this process.

Step1: Change recovery model of your database to SIMPLE. it will truncate the log
Step2: Shrinking the LOG file to bare minimum.
Step3: Changing the recovery model to FULL.
Step4: If you taking periodic LogsBackups from sql agnet job take a FULL Database backup of your database because your LOG backup will fail since we changed recovery model from
Full>Simple>Full.

Check your logspace on the server
dbcc sqlperf (logspace)
Check Available Free space on all drives
xp_fixeddrives

Example to Shrink your Logfiles.
Step:1
use blogs_db
go
alter database blogs_db
set recovery simple
go
 I am shriking to 2MB You may even opt for 0
(It will shrink to the initial size of your Log file which you assigned while creating your Database log file)

Step:2
dbcc shrinkfile(glogs_db_log, 2)
go

Step:3
alter database blogs_db
set recovery full
go

Optional
Step4: backup database BLogs_DB to DISK
'C:\backups\BLogs_DB.BAK'

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