TRUNCATE_ONLY is not a recognized BACKUP option

Executing the code below in SQL Server 2008 will throw an error message:

 

BACKUP LOG ‘MyDatabase’ WITH TRUNCATE_ONLY

Msg 155, Level 15, State 1, Line 44
'TRUNCATE_ONLY' is not a recognized BACKUP option.

It was deprecated in SQL Server 2008.

 Some options for SQL Server 2008 are :

 1)    BACKUP LOG [DBNAME] TO DISK='NUL'

This will break the  transaction log chain. Therefore a Full backup is required after the command.

2)    Switch to Simple Recovery and then switch back to Full Recovery. This will required a Full backup to restart a new  transaction log chain

 3)  DBCC SHRINKFILE(Test_Log)    . This option causes fragmentation , but does the job when it comes to reclaiming space.   You might find it doesn’t shrink – in that case check for open transactions.

4) Take regular Transaction Log backups while maintaining a small log file size

 The choice will depend on the circumstances.

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server Error 26 troubleshooting checklist

SQL Server Error 26  relates only to SQL Server Named instances. Not default instances

The main reason SQL Server Error 26  error message appears is that the client  can’t receive SQL Server Resolution Protocol(SSRP) response UDP packet from the SQL Browser Service

1)Check the server name is correct?Check spelling.

2)Check the SQL Server instance name , is there such an instance on the target server?

3)Can you ping the server?Is DNS resolvable?Sometimes ping isn’t possible as the administrator  disabled the ping service.

4)Is SQL Browser Service turned on?

5)Is the frewall enabled?If so , place sqlbrowser.exe or port 1434 into the exception list.

6)Is you server part of a Cluster?

 If after these steps the connection is still failing try:

tcp:SEVERNAME\INSTANCE_NAME

Add a comment , if you have a problem – and I’ll assist in troubleshooting.Or another SQL Server DBA will help

 This will give you some extra information to troubleshoot the SQL Server Error 26 message

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security

I received a phone call from the ETL manager  - with the following message in the SQL Server Logs.

 SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.111.111.21]

 The causes I’ve seen in the past are:

1)Connection problems between the server and domain controllers

2)Insufficient permissions to AD

3)Infrequent occurrence points to SQL Server losing connectivity with the Domain Controller.

For example, in the process of a DC failover, network issues, heavy load

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server recompile error after upgrade from 2000 to 2005

Upon upgrading a SQL Server 2000 to SQL Server 2005 Standard Edition instance –  this error message appeared on a SQL Server trace error event:

Msg 2801, Level 16, State 1, Procedure myTrigger, Line 13

The definition of object ‘myTrigger’  has changed since it was compiled.

The statement has been terminated.

Using a trace I isolated the trigger which caused this error. The developers had created the trigger in the following format:

 

Alter table appEvent DISABLE Trigger ALL

 <some dml statements >

 Alter table appEvent ENABLE Trigger ALL

When commenting out the DISABLE TRIGGER ALL  and ENABLE TRIGGER ALL  the code works without error – but does have a  data knock-on effect. The developers  explained they wrote this code to avoid creating duplicates in the target data set – fronm other triggers executing.

 The error is occuring because : the ALL option is used which affects all triggers on the table , and as an INSERT statement is used which fires the trigger which actually is doing the ENABLE\DISABLE. This creates the error message as trigger recompilation needs to occur .

 I advised them to use either application logic to prepare data adequately or add other constraints at the table level. 

 

Source:Jack Vamvas (http://www.sqlserver-dba.com)

 

Account Locked out

One of the application owners sent me a message the other day: Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

The following code will solve the issue

--------------------CODE START----------------------------------

use master

ALTER LOGIN sa WITH PASSWORD = 'password' UNLOCK

-----------CODE END-------------------------

Source:Jack Vamvas (http://www.sqlserver-dba.com)