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



Monday, May 23, 2016

The transaction log for database '' is full due to 'AVAILABILITY_REPLICA'. [SQLSTATE 42000] (Error 9002). The step failed. SQL 2014

I have noticed that our SQL jobs are failing due to this error.

Executed as user: ''. The transaction log for database '' is full due to 'AVAILABILITY_REPLICA'. [SQLSTATE 42000] (Error 9002).  The step failed.

Below is the SQL configuration :

SQL version is 2014
One Primary and one secondary replicas
Recovery mode is full
Transaction Log file is set to auto grow with limited size.

I have verified the transaction log backups and those were running fine. Not sure why the transaction log was full and this issue I see first time in my environment.

Below queries help to see the Alwayson status, also dashboard gives the same details.

select a.*,
from sys.dm_hadr_database_replica_states a

LEFT JOIN sys.databases b ON a.database_id = b.database_id

Fix: As the always on is not in sync I have taken the database out of always on shrink the database files and add it back to always on. Used below syntax to shrink the databases.

I have explained removing and adding back databases in to Alwayson in another post.

USE AdventureWorks2012;  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE AdventureWorks2012  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  
-- Reset the database recovery model.  
ALTER DATABASE AdventureWorks2012  


Saturday, May 21, 2016

Which method is best to import data from Oracle database to SQL

Hi Guys,

I would like to start a discussion through my blog to know more about this topic.

Below options I know to import data from Oracle to SQL..

1) Import/Export
2) Linked Server (Using OpenQuery)
3) SSIS Packages

The fastest among these 3 I noticed is the 3rd one and the slowest is the second one. But when we need very frequent refresh between systems using SSIS is not a good idea. Did anyone gone through all these and found the best method..if yes, please explain. Appreciate your response. 

Friday, May 20, 2016

Identity increment is jumping in SQL Server database

Please look at the below screenshot to understand what jumping of identity value is (It is taken from SQL 2014 Database table.)

Observe records below the red line..the identity value jumped from 17 to 1014 and started continuing from there.

Cause: This is not actually the issue or bug. It is by design in SQL versions from 2012
SQL Server was assigning values to identity column individually like by adding 1 to the existing ID till SQL 2012 versions. From SQL 2012 the SQL server allocating a batch of identity values like 1,000 or 10,000 numbers to the cache to speed up the process.

When ID Jump happens: when unexpected SQL services or server restart happens.

Why: There can be many reasons but mainly when SQL Server is restarted without the database containing the identity object being check pointed on shut down, any remaining unused values in the cached range are lost, this caused the jumping.

   1)      Creating Sequences to the identity columns. Click 

   2)      Enable Trace Flag 272 in startup parameters.
Ø  Open SQL Server Configuration Manager.
Ø  Right-click SQL Server and select Properties.
Ø  In the opening window under Startup Parameters, type -T272 and click Add, then press Apply button and restart SQL Services.

Note: Shutting down SQL services in proper way prevents this scenario. shudown

Wednesday, May 18, 2016

Table changes not automatically reflected in a SQL Server View

For example you have added new columns to the tables which are used by SQL view. Nothing is changed in the view but when you execute the view or any other object which is using the view you will see columns not found error message.

To fix that, we need to refresh the view using below T-SQL command.

exec sp_refreshview [dbo.ViewName]

Friday, May 13, 2016

Check In Database Scripts in to Team Foundation Server (Using VS 2015)

After TFS server is ready with folders created for databases, you need to take database scripts in your own style and check in to TFS. This is one of the database version control methods.
 Install Visual Studio 2015 and once you launch you need to click on Team (5th tab from left) button of Visual Studio main page. Select Connect to team foundation server to make a connection to TFS server for the first time or click Manage Connections if it is already configured.

Below is the structure created in my Team Foundation Server:

To upload database scripts to these folders you have to create similar structure in your local drive as below screenshot shows

Create subfolder as Databases in SQL folder and one folder for each database again. Create 3 subfolders in each database similar to the TFS structure for example Dev, Integration and Main. It is explained in the below screenshot.

Again under each environment I have created folders for each database objects separately.
Take scripts separately and check in using below instructions.
When you highlight the folder left side you will see the location of the folder you created locally as Local Path.

After taking database scripts, copy those in their respective folder in your local drive.
To check In those scripts, right click on the middle panel and click Add Items to Folder option.

If you already add scripts to your local folder you will see the script file to add like below.

Highlight the script file and click next or finish to check in. You will see the added file like below

Right click on the file and click check in pending changes option. It will complete the check in process in to TFS.
Best Practice would be to always Right click each branch and click on the ‘Get Latest Version’ as shown below and then right click on the Sub-Folder and click on ‘Add Items to Folder’ option, select the scripts and click on ‘Finish’ button’…In the Solutions Explorer highlight each script, hit the F4 button and make sure the Build Action is set to ‘None’….then right click on the project and click on ‘Check In Pending Changes’ option and click on the ‘Check In’ button.


I cannot find the login physically though but getting the error while trying to create a new login. 
Use below scripts to fix the issue

 select suser_sid ('Domain\loginName');

Result: 0x0105000000000005150000000C04XXXXXXXXXXXXXXXXXXXXXXXXX

select * from sys.server_principals sp
where sid=0x0105000000000005150000000C04XXXXXXXXXXXXXXXXXXXXXXXXX

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Domain\LoginName')
    DROP LOGIN [Domain\LoginName]

Able to create a new login now.

Thursday, May 12, 2016

Default Value or Binding blank vs single quotes with no space in the middle.

Got an issue in the SQL table that it is by default inserting NULL values but I need blank values instead NULLs.

You would see this when you right click on table on object explorer. Right Click on table name and click design and highlight the column you are looking for and see the column properties at the below. Change the Default Value or Binding from blank to ‘’ (single quotes without empty space in the middle)

Verify the fix by inserting new records in the table and observe. 

Common Mistake while creating Operators in SQL Server Agent services

The common mistake people do when creating operator for the first time or adding new email IDs in the Email-name list.

Creating Operators:
Below screenshots give over view on creating Operators in SQL Server.

    1)      Right click on Operators folder and click New Operator.
    2)      Provide name to the Operator.

   3)      Against E.mail name add the email list of people..main mistake done here is adding , between the emailIDs.
   4)      Separate emailIDs with ;( semicolon only). Adding , ( Comma) will fail sending notifications to all users added after it.

Basic Insert Trigger Example

Below is the trigger which triggers every time a record insert in to the table to update below mentioned columns.

CREATE TRIGGER Trg_ServiceTickets ON DBO.ServiceTickets
    DECLARE  @ServiceAddress         VARCHAR(481)
             ,@ServiceAddressCity     VARCHAR(60)
             ,@ServiceAddressState    VARCHAR(60)
             ,@ServiceAddressCountry  VARCHAR(60)
             ,@ServiceTicketID        INT
             ,@Requester              VARCHAR(100)
             ,@TriggerAction          VARCHAR(500)

       SET @ServiceTicketID       = (SELECT  I.ServiceTicketID FROM inserted i)
       SET @ServiceAddress        = (SELECT  I.ServiceAddress FROM inserted i) 
       SET @ServiceAddressCity    = (SELECT  I.ServiceAddressCity FROM inserted i) 
       SET @ServiceAddressState   = (SELECT  I.ServiceAddressState FROM inserted i) 
       SET @ServiceAddressCountry = (SELECT  I.ServiceAddressCountry FROM inserted i) 

       SET @Requester             = (SELECT  I.Requestor FROM inserted i) 
       SET @TriggerAction='Insert Trigger to update the ServiceAddress, City, State, Country if they are NULL to '' ';

       UPDATE ServiceTickets
       SET    ServiceAddress          = ISNULL(@ServiceAddress,'')
             ,ServiceAddressCity      = ISNULL(@ServiceAddressCity,'')
             ,ServiceAddressState     = ISNULL(@ServiceAddressState,'')
             ,ServiceAddressCountry   = ISNULL(@ServiceAddressCountry,'')
              ,LastModifiedBy          = CASE WHEN @ServiceAddress IS NULL OR @ServiceAddressCity IS NULL OR @ServiceAddressState IS NULL OR @ServiceAddressCountry IS NULL THEN 'Updated By Trigger : Trg_ServiceTickets'
     ELSE @Requester
     WHERE ServiceTicketTypeID    = 4
     AND  ServiceTicketID = @ServiceTicketID       

PRINT 'AFTER INSERT trigger fired.'

It will also update the lastmodifiedby column with the information as the record is updated by the trigger.