Error: Tranaction log full due to replication

I had this error in one of the databases and I was trying different possible steps to fix it.

I tried to take a transactional log back up - no luck
I tried to change the recovery model to simple - no luck
The database does not have replication but it does have CDC enabled. So I tried to disable the CDC I got another error related to metadata
I tried to change the db owner to sa - no luck still error
Then the following helped me to get rid of the error that was saying replication

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

I tried to change the db owner to sa after running the above - success
I tried to disable the CDC - success
I expanded the log file - success

This is how I was able to resolve the issue.

Error: Failed to notify via email

The above error occurs when we forget to perform the step to let the SQL Server Agent Talk to Database Mail

To perform that , follow the following steps -

-Highlight the "SQL Server Agent" in the object Explorer
-Go to Properties
-Go to Alert System options
- Check the enable profile on and select the profile  in the mail Session section.

Work adventure: I/O error 21(The device is not ready )

We had an error all of a sudden on our sql server

"Microsoft OLE DB Provider for SQL Server:
I/O error 21(The device is not ready.) detected during read at offset 0x0000a7a239c000 in file 'DataFileName'.
- HY000 - HRESULT=0x80004005 “

Since the error message mentions that the drive on which one of the data file resided was not ready, it was obvious that the underlying storage was the problem.
But the System Event Log did not have any error messages related to storage. The database was online and accessible. But when I  tried accessing the Properties of this database, the same error was thrown.


The reason for this error was not something that happened at that time. Another email chain from the Windows Administrators sent a few hours ago said it all. They had found that the drive letter associated with the Mount Point hosting this data file was missing and they had *mapped* it somehow. Not sure how they had done the drive mapping. Most likely the drive hosting this data file got disconnected while the database was online.


I fixed this by bringing the database offline and back online after few seconds

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE <dbname> SET ONLINE

query sql error log

Grab the information of sql error log in a table and query


--gather all the error in a temp table and query the temp table to find the specific error by text search or date range

CREATE TABLE #SQLErrorLog



(
 LogDate DATETIME,

ProcessInfo VARCHAR(50),

Text VARCHAR(1500)



)
 GO
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 0



go
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 1



go
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 2



go
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 3



go
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 4



go
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 5



go


 SELECT * FROM #SQLErrorLog

WHERE LogDate >= CONVERT(datetime,'06/08/2015') AND LogDate < CONVERT(datetime,'06/20/2015')

and Text LIKE '%failed%' OR Text LIKE 'Error:%'

ORDER BY LogDate DESC

DROP TABLE #SQLErrorLog

Error: Could not continue scan with NOLOCK due to SQL Server data movement

Ran  the following

DBCC CHECKDB(MY_DATABASE) WITH PHYSICAL_ONLY
I got table error something like below

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown),
page ID (1:219356) contains an incorrect page ID in its page header.
The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 144115188075921408

So I ran the following

USE [master]
GO
ALTER DATABASE [MY_DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [DATABASE]
GO
DBCC CHECKTABLE(MY-Badtable, 'REPAIR_REBUILD')
GO
ALTER DATABASE [MY_DATABASE] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Then  ran the following again
DBCC CHECKDB(MY_DATABASE)


This worked, no errors!

Error handler: TRY CATCH

BEGIN TRANSACTION TRAN1

BEGIN TRY

INSERT INTO.....

UPDATE.....

END TRY

BEGIN CATCH

If @@TRANCOUNT > 0

--error happened

ROLLBACK TRANSACTION TRAN1

END CATCH

If @@TRANCOUNT > 0

COMMIT TRANSACTION TRAN1

GO

Recovery database from suspect mode

The database was in suspect mode, inaccessible Ran the following query Then restored from the latest full back up

EXEC sp_resetstatus client_367
ALTER DATABASE client_367 SET EMERGENCY
use client_367
DBCC checkdb
ALTER DATABASE client_367 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('client_367', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE client_367 SET MULTI_USER

Fix : Could not obtain exclusive lock on database 'model'

SELECT spid, loginame, DB_NAME(dbid) FROM master..sysprocesses WHERE DB_NAME(dbid)='Model' If you get the following errors - MS-SQL Database Error --------------------------- Create failed for Database 'DbName1'. An exception occurred while executing a Transact-SQL statement or batch.Could not obtain exclusive lock on database 'model'. Retry the operation later.CREATE DATABASE failed. Some file names listed could not be created. Check related errors. kill the connections

Error handling in transactions in sql 2008

BEGIN TRANSACTION TRAN1

BEGIN TRY
--put all the valid statements here--


   CREATE TABLE #test(intREsult int);

   INSERT INTO #test Values (4/0);
   INSERT INTO #test Values (1/2);


  COMMIT TRAN TRAN1
END TRY


BEGIN CATCH
--the error handling code including the rollback

    DECLARE @errorMEssage nvarchar(1000);
   SELECT @errorMEssage = ERROR_MESSAGE();
   ROLLBACK TRAN TRAN1;
   RAISERROR(@errorMEssage,16,1);

END CATCH

Error:The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Here is the sp that caused the error

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_ARCHIVE_EOM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PROC_ARCHIVE_EOM]
GO
/** ***** Version *****
* $Revision:  $
* $Date: $
* $Author: $
* $Archive: $
*
* Sample use: exec PROC_ARCHIVE_EOM
* Comments : sp to perform the monthly archive of the transactions table.
* */
CREATE PROC PROC_ARCHIVE_EOM
AS
DECLARE @myCheckdate varchar(10)
SELECT @myCheckdate = convert(varchar(10),SuccessDate,101) from dbo.JobsRunlog where JobID = 10
--SELECT @myCheckdate
If @myCheckdate = convert(varchar(10),GETDATE(),101)
BEGIN

BEGIN TRAN TRAN1

---insert into the archive table
INSERT INTO Table_Archive
SELECT p.* FROM Patient_Table p WITH (nolock)
INNER JOIN clients c
ON p.hospital = c.hospitalcode
WHERE c.active = 1
AND
 year(RCVD) = year(getdate()) and month(RCVD)< month(getdate()))

IF @@error <> 0 GOTO  Error_Handler


COMMIT TRAN TRAN1

END


Error_Handler:
RAISERROR('error occurred while archiving the patient table',16,1)
ROLLBACK TRAN TRAN1




Added @@tranCount check to avoid this error

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_ARCHIVE_EOM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PROC_ARCHIVE_EOM]
GO

CREATE PROC PROC_ARCHIVE_EOM
AS
DECLARE @myCheckdate varchar(10)
SELECT @myCheckdate = convert(varchar(10),SuccessDate,101) from data2link.statusUpdates.dbo.JobsRunlog where JobID = 10
--SELECT @myCheckdate
If @myCheckdate = convert(varchar(10),GETDATE(),101)
BEGIN
BEGIN TRAN TRAN1

---insert into the archive table
INSERT INTO Table_Archive
SELECT p.* FROM Patient_Table p WITH (nolock)
INNER JOIN clients c
ON p.hospital = c.hospitalcode
WHERE c.active = 1
AND
year(RCVD) = year(getdate()) and month(RCVD)< month(getdate()))


IF @@error <> 0 GOTO Error_Handler


IF @@TRANCOUNT > 0

 COMMIT TRAN TRAN1


END


Error_Handler:
RAISERROR('error occurred while archiving the patient table',16,1)
IF @@TRANCOUNT > 0
ROLLBACK TRAN TRAN1

Error: [SQLSTATE 42000] (Error 7391) OLE DB provider "SQLNCLI10" for linked server "" returned message "No transaction is active.". [SQLSTATE 01000] (Error 7412). The step failed.

I have a stored procedure in server2 that I was trying to call from server1 using linkedserver so that it gets executed in server2.
Server 1

• Windows Server 2008(x86)
• SQL Server 2008 (x86) Standard Edition SP1

Server 2

• Windows Server 2008 (x86)
• SQL Server 2008 (x86) Standard Edition SP1

Following  is the snippet of my code on Server1

SET  XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION 
declare @RetVal int,

exec @RetVal = linksv1.db1.dbo.usp_update_Referrals 0

COMMIT TRANSACTION

So far I have taken the following steps

1. NETWORK CONNECTIVITY

• Ping server1 from server2  -- success
• Ping server2 from server1  -- success

2. DTC CONFIGURATION

• Open Start->Administrative Tools->Component Services
•  Expand the Component Services Node untill you find Local DTC right click and select properties option
•  In the Local DTC properties click in the security section and set the configuration as shown in the image

   Note that every time you make a change in the DTC Security configuration, the DTC service is restarted automatically

The final step is make shure the DTC service is started and configured to start when windows start.

3. DTC SERVICE IS STARTED

• Open Start - > Open Start->Administrative Tools->Services
• Find the Distributed Transactions Coordinator Service
• Make sure the status is “Started” and the Startup Type is “Automatic”.

Follow the same step in Server2

After the DTC is configured , checked the lnked servers
Linked Server Configuration on Server 1

 OKC is the linked server pointing to my Server2
Highlight the linked Server
Right Click and go to properties
Select the Server Option in the properties window and make sure the following settings are applied
Data Access: True
RPC: True
Enable Promotion of Distributed Transactions: True

All the settings were double checked ...will update in few weeks if we still get the "No transaction is active" error...

error:[OLE DB Source [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.

I ran into the following error when calling a stored procedure from SSIS. The stored proedure dumps the SELECT query result into a table variable and I was doing a final SELECT from the table variable.

[OLE DB Source [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.

The error was eliminated after putting SET NOCOUNT ON in the begining of the stored procedure

SSIS Error: Error: ForEach Variable Mapping number n to variable "User:: n1 " cannot be applied.

The exact error I got was
Error: ForEach Variable Mapping number 2 to variable "User::i_HospitalCode" cannot be applied.
I spent almost 2 hours trying to find out what the problem was. It was the problem in the data type. I was declaring the data type of  variable User::i_HospitalCode as string where as the resultset ( returned by the stored procedure) was integer.
I changed the data type of the variable  User::i_HospitalCode  in the variable declaration window and it worked!

Error: SSIS package : A rowset based on the SQL command was not returned by the OLE DB provider

I am trying to use a stored procedure in the OLE DB source, I am using the SQL Command for the Data Access mode.  It returns values when using the preview but when I test the SSIS package I receive the error, "A rowset based on the SQL command was not returned by the OLE DB provider


Solution

I just had to add SET NOCOUNT ON in the begining of the stored procedure that I was using.

Error:System.Data.SqlClient.SqlException: profile name is not valid

We were getting the following error

System.Data.SqlClient.SqlException: profile name is not valid

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

The profile name is profile1@abc.com

user account associated to it was accountA@abc.com

We had to do the following to get rid of this error

Make sure that the account  accountA@abc.com is a role member of the Role DataBaseMailUserRole in the msdb database.This role is found under Security->Role in the msdb database

Set the profile profile1@abc.com to  public

use msdb

GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name =  'profile1@abc.com'
@principal_name = 'public',@is_default = 1;


Once we did the above two setps after creating the profile the error was gone and everything started to work as normal.=

Error:Row handle referred to a deleted row or a row marked for deletion

We got the error "Row handle referred to a deleted row or a row marked for deletion" from a sql job ( that calls a stored procedure) after we moved to sql 2008 R2. When we were running this job in sql 2000 it was running fine.
I had been looking for a solution to fix this and most of the internet articles points to the "mising primary key" issue.
The stored procedure moves the data from TABLE1 to TABLE2. TABLE2 did not have any primary key as this was just used as an intermediate table. I added the primary key to the TABLE2, but the job still continued to fail now and then with the above error.
Upon further look at the code I saw the following code in the stored proc that the job was calling

BEGIN TRANSACITON TRAN1

delete w from DATA2LINK.statusupdates.dbo.workqueue w
INNER JOIN DataUpdate_DTS d2
ON w.ptno = d2.ptno
AND status_Id = 6



SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO Error_handler


--remove any deleted(declined) Data update request from workqueue
DELETE w FROM DATA2LINK.statusupdates.dbo.workqueue w
INNER JOIN DATA2LINK.statusupdates.dbo.DataUpdate d
ON w.ptno = d.ptno
AND w.status_Id = 6
AND d.deleted = 1


The culprit seems to be the second delete query because we are referring already marked for deletion row(in the first query) . I removed the second delete statement .

The job has not failed since then, though it has been only few days since the code change. I will revisit again, but for now I consider this as the resolution for the above error.

Error : The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

Once we migrated to the SQL 2008 R2 was getting the following error

The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

I had to do the following to resolve this

The following example shows how to view the current setting of OLE Automation procedures.

Copy
EXEC sp_configure 'Ole Automation Procedures';
GO

The following example shows how to enable OLE Automation procedures.


sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO


http://msdn.microsoft.com/en-us/library/ms191188.aspx