SQL DBA Errors and Solutions/Recommends

SQL DBA Errors and Solutions/Recommends

Error1:

Cannot Shrink Log file(db_log) Because all logical log files are in use.

LDF file Shrinking  mechanism:

1)SELECT *

FROM sys.database_files-----to know the db_log file name

Or

run

exec sp_helpfile-----to know the db_log file name

2)select name,log_reuse_wait_desc,recovery_model_desc,state_desc,* from sys.databases

--where log_reuse_wait_desc<>'Nothing '

--where name='dbname'

--where log_reuse_wait_desc='REPLICATION'

--where log_reuse_wait_desc='LOG_BACKUP'

4)BACKUP DATABASE DW-DEV

TO DISK = 'E:\bk check\ DW-DEV _bk.bak'

3)BACKUP LOG DW-DEV

TO DISK = 'F:\Backups\ DW-DEV 1.bak';

5)

ALTER DATABASE DW-DEV

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (DW-DEV _log, 1);

GO

-- Reset the database recovery model.

ALTER DATABASE DW-DEV

SET RECOVERY FULL;

Solution:

Run Step No 5 in one by one to solve the problem.

Error2:

SQL SERVER – Working with LSN for Restore and Backup Databases. (Error: Msg 4305, Level 16, State 1, Line 1)

Msg 4305, Level 16, State 1, Line 1

The log in this backup set terminates at LSN 9386000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 9417000002731000001 can be restored.

So, my explanation was:

You are trying to apply a Log file that finish with LSN 9386000024284900001 into a DB restored with LSN 9417000002731000001, so, the LSN of the Full Backup (restored) is older than the log file that is trying to apply. So, the log file is not possible to apply it.

Now, we are going to replicate that error and we are going to see how to know the LSN of each backup, in order to clarify and understand the error mentioned above.

First a DB will be created, some backups will be taken, then the DB will be restored and some log will be applied in the wrong way, then we are be able to identify the right way and the we will apply the logs.

First, we create a database and a table for testing purpose.

--Create DB for Testing

create database DB_TestLSN

GO

USE DB_TestLSN

GO

--create a testing table.

create table test(

a int

)

Now, we are going to backup the database and log:

backup database DB_TestLSN

to disk='c:\DB_TestLSN.bak'

A table2 and table3 are generated and two log backups are taken:

create table test2(

a int

)

-- backup log file

backup log DB_TestLSN

to disk='c:\DB_TestLSN_LOG1'

create table test3(

a int

)

-- backup log file

backup log DB_TestLSN

to disk='c:\DB_TestLSN_LOG2'

Restoring database….

First restore the full backup

restore database DB_TestLSN_REST

from disk='c:\DB_TestLSN.bak'

with norecovery

then we restore intentionally the wrong log file

restore log DB_TestLSN_REST

from disk='c:\DB_TestLSN_LOG2'

Msg 4305, Level 16, State 1, Line 1

The log in this backup set begins at LSN 18000000015300001, which is too recent to apply to the database. An earlier log backup that includes LSN 18000000014000001 can be restored.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

… is necessary to identify what is the correct way to restore the logfiles. For this, we can mention two manners:

1. If we have access to the server where the backup was taken.

We can check the system table msdb..backupset. To identify the chronologic order of the data backup and log backup, we can put special attention on the following columns

• backup_start_date, backup_finish_date, first_lsn, last_lsn

With that columns we can view the LSN of each of the backups taken, so our error shows that 18000000015300001 (this is the first lsn for the second log backup) is too early to apply and and earlier log backup that includes LSN 18000000014000001 must be exists, and that is correct, the first log backup includes the LSN 18000000014000001 and also this logs begins with the log_chain.

select database_name, type, first_lsn, last_lsn ,checkpoint_lsn ,database_backup_lsn

from msdb..backupset where database_name = 'DB_TestLSN'

database_name type first_lsn last_lsn begins_log_chain

DB_TestLSN D 18000000007400155 18000000014000001 0

DB_TestLSN L 18000000007400155 18000000015300001 1

DB_TestLSN L 18000000015300001 18000000015900001 0

2. If we have just the backup files.

To know the LSN of the backups we can use

RESTORE HEADERONLY from disk='c:\DB_TestLSN.bak'

RESTORE HEADERONLY from disk='c:\DB_TestLSN_LOG1'

RESTORE HEADERONLY from disk='c:\DB_TestLSN_LOG2'

In this case also is necessary to put special attention on the columns first_lsn, last_lsn in each of the backup files.

To reproduce exactly the error mentioned on the top of this article,

Msg 4326, Level 16, State 1, Line 2

The log in this backup set terminates at LSN 18000000015400001, which is too early to apply to the database. A more recent log backup that includes LSN 18000000022000001 can be restored.

Msg 3013, Level 16, State 1, Line 2

RESTORE LOG is terminating abnormally.

follow up the next steps.

1. Create DB
2. Backup Full Database (BkpDBFULL1)
3. Backup Log (BkpLOG1)
4. Backup Full Database (BkpDBFULL2)
5. Restore BkpDBFULL2
6. Try to apply BkpLOG1

Instead of use backup log, you can use backup incremental and the behavior is the same than we show in this examples.

[Questions & Answers]

Q: What is a LSN (Log Secuence Number): ?

A:

Every record in the Microsoft SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

The LSN of a log record at which a significant event occurred can be useful for constructing correct restore sequences. Because LSNs are ordered, they can be compared for equality and inequality (that is, <, >, =, <=, >=). Such comparisons are useful when constructing restore sequences.

LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.

Error3:

SQL SERVER – Error: Property Owner is not available for Database '[DB_NAME]'. This property may not exist for this object, or may not be retrievable

Problem.

Error: Property Owner is not available for Database '[DB_NAME]'.

This property may not exist for this object, or may not be retrievable

due to insufficient access rights.  (Microsoft.SqlServer.Smo)

This a common error that happens when we try to access the database options and the database doesn’t have owner. The database can not be an owner because somebody clean the owner property, or the user (owner) was deleted, etc.

Solution / Fix / Workaround.

Is necessary to assign a proper owner for the database. To know what are the valid owners for the database, we can execute the following query:

select databases.name,server_principals.name

from sys.[databases]

inner join sys.[server_principals]

on [databases].owner_sid = [server_principals].sid

To assign the owner for the database:

USE <SID>

Go

sp_changedbowner ''

Go

Error4:

SQL SERVER – Error: The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is runn

Problem.

Sometimes when we are trying to truncate the transaction log, we can got the following error message:

Statement:

BACKUP LOG <SID> WITH TRUNCATE ONLY

Error:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

This is because some part of the transaction log is pending to replicate (publisher side), hence is not possible to truncate it from the transaction log. In this case is necessary to mark these transactions as replicated in order to be able to truncate them.

Solution / Fix / Workaround.

To mark the transactions as distributed (or replicated) we can use the following statemtent:

USE <SID>

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Use sp_repldone just for troubleshooting purpose and in emergency situations. If this SP is not used in the proper way you can invalidate pending information to be replicated.

Sometimes when sp_repldone is used, is possible that we get the following error.

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1 The database is not published

It means that our Database is not published. A workaround to this error, is to publish our database with the below statement, then try again the sp_repldone and finally execute again the sp_dboption with publish = false.

sp_dboption '', 'Publish', 'true'

Ex.

sp_dboption '', 'Publish', 'true'

EXEC sp_repldone ....

sp_dboption '', 'Publish', 'false'

Error5:

SQL SERVER - Error: 1418. The server network address can not be reached or does not exist. Check the network address name and that the ports for the l

Problem.

During the past days I was working in a Database Mirroring Configuration and one of the errors I got was the following:

Msg 1418, Level 16, State 1, Line 1

The server network address "%.*ls" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

It was presented when I run the command:

ALTER DATABASE TestMirror SET PARTNER = 'TCP://hostname:5050'

The server network endpoint did not respond because the specified server network address cannot be reached or does not exist.

Solution.

To solution my problem, I accomplished the following points:

1. The ENDPOINTS should have the status started.

select state_desc from sys.database_mirroring_endpoints

2. The system should not be behind a firewall.

3. Make sure that the principal server instance and mirror instance are listening on the right ports and whether are available.

4. Name pipes protocol enabled.

5. The same username and password were used to run SQL Server services on both machines.

6. The usernames to run SQL Server services have NT Auth Access between both machines.

7. Make sure that the username for the SQL Server services has rights on the SQL Server instance.

Error6:

Database have some open Transaction very long Time in DBCC OPENTRAN or sys.databases table

Solution:

select name,log_reuse_wait_desc,recovery_model_desc,state_desc,* from sys.databases

where log_reuse_wait_desc='REPLICATION'

If u run the above query then also u have some db undistributed transactions are wait. So kindly ensure the subscribers table all snapshot/replication pasted/copied correctlyl.

After that run command below to solve the issue.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

After run above command immediately run command below to see whether transactions are closed.
 to check any opentransaction still be there

 dbcc opentran

 
and now immediately run the transaction log backup 

see below


BACKUP LOG databasename
TO DISK = 'F:\Backups\databasename.bak'

then now run below you can see the database with replication has solved i.e if u run below query no rows return that means solved the problem.
   

select name,log_reuse_wait_desc,recovery_model_desc,state_desc,* from sys.databases

where log_reuse_wait_desc='REPLICATION'

 Error7:


Cant Take Transaction Backup due to There is no Current DB backup:



 

Solution:

If u enabled the Transaction log backup then Database full Backup's are needed by daily basis due to maintain the LSN of transaction. 

Question: Can we modify Primary key constraint??

Answer: No

Error: Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object Table1

Solution:

Earlier the table structure was as below:

But due to multiple values in column ‘C1’ for each combination of A1 and A2 column values, it was not able to insert records in table ‘Table 1’.

The solution was to include column C1 in the primary key constraints, that is to make composite primary key on column ‘A1’ ,’A2’ and ‘C1’.

For this, either one can

Drop constraint and add a new constraint on the columns A1, A2, C1 (But this will not solve the issue , as C1 column defined as NULL, and for including this column in Primary key , it should be Not Null, therefore before making changes in constraint , one should make C1 column as Not Null) As below

Or   we can drop and create table, as below

how do I make a composite key with SQL Server Management Studio?

1. Open the design table tab
2. Highlight your two INT fields (Ctrl/Shift+click on the grey blocks in the very first column)
3. Right click -> Set primary key

SSIS : 200 Type set to A.500 PORT/EPRT (Active Mode/Extended Active Mode) is not supported. Use PASV/EPSV instead of this

Error: Detailed error description: 200 Type set to A.500 PORT/EPRT (Active Mode/Extended Active Mode) is not supported. Use PASV/EPSV instead of this

500 'LPRT 6,16,0,0,0,0,0,0,0,0,96,76,140,105,19,0,0,0,2,207,204': command not understood.

Scenario:

 I was working on one SSIS package , in which I need to use FTP task to get some files from the remote server. For that I need to createa FTP connection in connection manager. But when I was executing the package it was getting stop at FTP task, and was throwing error as I mentioned above.

I checked FTP connection manager 10 times , but I was not getting any solution for this, then I see the ‘Use Passive Mode’ checkbox, which was unchecked in the FTP connection manager. So I checked the checkbox ‘UsePassive Mode’ in the FTP connection manager. It worked and the error was gone.