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.


Message
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.*, b.name

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;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  
GO  
-- Reset the database recovery model.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY FULL;  
GO  

References:
https://support.microsoft.com/en-us/kb/2922898
https://msdn.microsoft.com/en-us/library/ms189493.aspx 

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. 

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.

Workarounds:

   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

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]

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 (5^th 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.

Error: THE SERVER PRINCIPAL ALREADY EXISTS – MSG 15025, LEVEL 16, STATE 2, LINE 1

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');

go

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.

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

FOR INSERT

AS

    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

     END

     WHERE ServiceTicketTypeID    = 4

     AND  ServiceTicketID = @ServiceTicketID       

PRINT 'AFTER INSERT trigger fired.'

GO

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