Monday, December 12, 2016

Finding Default and User Traces running in SQL Server

I have heard one issue from my manager saying one of SQL instance is taking almost all memory, what is causing this high load.

I starting thinking what can create this load, no SQL jobs running at this time. Then I got light which is any of developer running SQL trace to fix performance issues as it is non-production server.

I was searching to find the trace details, first query I used and got the default trace information. I thought it was the user trace and tried to stop that. But It gives me error message saying, you cannot kill default trace use sp_configure to change the status. I started searching again and found the second query, it is right one to find all user traces.


SELECT * FROM [sys].[fn_trace_getinfo](DEFAULT)

declare @sp_who2 table (
    SPID    int,
    status  varchar(50),
    login   varchar(100),
    HostName    varchar(100),
    BlkBy   varchar(100),
    DBName  varchar(100),
    Command varchar(100),
    CPUTime int,
    DiskIO  int,
    LastBatch   varchar(50),
    ProgramName varchar(150),
    SPID2   int,
    REQUESTID   int
insert  @sp_who2
exec sp_who2

select  *
from    @sp_who2

where   ProgramName like 'SQL Server Profiler - %'

Sunday, December 11, 2016

Attach .mdf files to create Database in SQL Server

I have got a request to create databases by giving .mdf and .ldf files. Used below steps to create databases.

Attach database method.

Connect to the SQL instance you want to create these databases and right click on database and click on Attach... option.

Click on Add button and give the path where SQL .mdf file is located.

SQL server will automatically attach the related log file and the window will be like below.

Click on to attach the database. You can verify the new database in object explorer once this process is done.

Saturday, December 10, 2016

Steps to reset sa password of SQL Server instance

Connect SQL Server Configuration Manager to set startup parameters to start SQL server in single user mode.

Click all programs and click on SQL Server, go to Configuration Tools folder and click on SQL Server Configuration manager.

Right click on SQL Server and click properties. Click on Startup Parameters tab. Type –m on Specify a startup parameter box and click add.

Click ok and you will see –m added. Click add and restart SQL services because this change won’t apply until you restart. That you will come to know by looking at the warning.

Restart SQL services.
Now connect SQL from SSMS as administrator. Right click on SQL Server Management Studio and select Run as Administrator option.

Connect SQL using windows authentication and navigate to sa login. You can reset the password on this account. Note it down once you reset it.

To test the new password, connect SQL server using sa account. It will prompt by saying the service is in single user mode. Go to configuration manager and remove the startup parameter we have added which is –m and restart services. Now test the connection with sa account with new password. 

Tuesday, December 6, 2016

Oracle Linked Server errors

Stuck with the below error right now..any ideas

Trying to pull data from Oracle view using linked server and getting below error. It was only when I try with particular Oracle instance, when I try to pull data from other instance using the same view it is working fine.

Any insights??

Msg 7399, Level 16, State 1, Line 82
The OLE DB provider "OraOLEDB.Oracle" for linked server "LinkedServer Name" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 82
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "LinkedServer Name".

Friday, December 2, 2016

Error:  The server principal "IncidentManagement" is not able to access the database "DatabaseName" under the current security context.
for conection string: "Data;Initial Catalog=DatabaseName ;User Id=IncidentManagement ;Password=wuwaye6U5+uz;" providerName="System.Data.SqlClient"

Fix: This error you receive when the service account using from .net application doesn’t have sufficient permissions to access the database. Very simple to fix. Go to the login properties and see whether this account has access to the database or not and grant it.

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

Issue: I see this issue when I try to expand the databases and any other things in object explorer of secondary replica of AlwaysOn SQL instance. I have seen few blogs which suggests to see sp_who2 results and kill blocking. Unfortunately this is  read only database and no user connections found.

However I have noticed many system sessions running from many days by taking much CPU and IO but I cannot kill them. There is reason for this, this database is out of sync with primary and DPM failing to take log backups from many days.


TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click:


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

For help, click:



Fix: As I cannot kill the sessions I had to restart the SQL services as there is no impact with this option. Restart taken quite while approx 5mins. which is very long with the restarts I made before.

Comments: SQL Server version is

Microsoft SQL Server 2014 (SP1-GDR) (KB3194720) - 12.0.4232.0 (X64)
                Sep 23 2016 18:45:14
                Copyright (c) Microsoft Corporation

                Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Adding database back to Always On High Availability Group

Summary: If databases in secondary replica are out of sync with primary then below is the workaround which helps to fix.

1)      At very first take the database out of Always on availability group on primary replica by right click on the database you want to remove from always on and click on Remove Database from Always on group.

AlwaysOn High Availability-> Availability Groups->AGGroupName (Primary)->Availability Databases

2)      Taking full backup of primary database is the prerequisite. Also the database recovery model should be Full.

3)      When you right click on Availability Databases and select Add Database the SQL server will give all databases list and validated results against each DB. Please refer below screens..observe different statuses against each database.

4)      Next step is Select Initial Data Synchronization. Full is when you want to add the database for the first time or reconfigure it from scratch. I have used this option to avoid manual backups and restores. We have to create one share folder and give access to help SQL server to take backups from primary replica and use it to restore in secondary replica.

Refer below link to know the synchronization options.

5)      Received below error as the share location I gave is not valid network file location. 

6)      Next step is connecting secondary replicas. I have only one replica which showing in the below screen. Click connect which helps to connect to the replica.

7)      Next is validation, validation details are explained and results against each validation. I didn’t delete the database in secondary replica (already configured Always on before in this environment) so I have received below error message so make sure you delete it in secondary replica before setup with Full synchronization.

8)      Next option is disabled until all validations are successful. See below screen.


9)      When you click next and expand for more details it will show the steps of backups restores (with completion percentage ) and joining the database to the secondary replica.

 10)   It is done now.

Make sure to check the dashboard and see the synchronization statuses on secondary replica. Delete the backups of databases created temporarily in network folder to save the disk space.

VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)

Issue: I had to create few users for the first time and give access to execute views from user database. I have created logins for them and gated access to select from views and tables at object level but see below error when they select from views.


TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)

For help, click:



Fix: Granted view server state permission which fixed the issue.

Comments: SQL Server version is Microsoft SQL Server 2014 (SP1-GDR) (KB3194720) - 12.0.4232.0 (X64)
                Sep 23 2016 18:45:14
                Copyright (c) Microsoft Corporation
                Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

 Is it a known bug?

Query Used