.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Issue with non-replicated transaction in Log file

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server

Hi All,

I'm trying to shrink my Transaction log. Its taking 8 GB of space and I'm getting out of space on this drive. The database is in SIMPLE recovery model.

The problem is that when I see the log info using DBCC LogInfo() it shows 2723 rows with Status of 2. I issued the Checkpoint command many times as well. There are very few rows with Status 0 which are removed when I shrink the file. But these 2723 rows with Status of active-tran are taking up the most space and are not getting cleared.

I had restared the sql server service manytimes (just for experimenting). Eventually, I exectued the DBCC OPENTRANS command; which tells me that there is a non-distributed transaction in T.Log. This transaction is the very first transaction in T.Log file with Status 2 and Parity 128.

This server used to work as Publisher few months back. All the Subscriptions have been deleted and the relavant Distributor and Subscriber have been re-formatted and re-installed since then. Now when I try to drop the Local Publications from this local server it gives me error for relevant Distributor or Subscribor machines which do not exist any more.

How should I truncate this Log file and unmark this pending non-distributed transaction from T.Log.

Kindly help me out with this :(.

View Complete Post

More Related Resource Links

User validation Issue in xlsx file



I am validating user from Login.xlsx file. It was working fine by validating email and password from that file. For testing i got new file same contents of file but added some more user names.

So after that when i validating the user i am getting the following error "Data type mismatch in criteria expression".

But when i replaced the new to old one it is working fine. The contens of both files are the same. So why this kind of error happened.


Issue: Load {NUL} from flat file to "Flat File Source" faild.

Hi guys, I've met a strange issue when I was working to use SSIS to load data from flat file to database. It is a story about {NUL}. Sample Flat File: FIELD_SAMPLE|OTHERS ABC{NUL}DEFG|Any Others   I placed an Flat File Source to load these into package, and the configuration of the Connection Manager are as following: FIELD_SAMPLE: Unicode string [DT_WSTR], Length 8   Then I click the "Preview" button and I can see this data in the "Preview" window correctly (with the value "ABC{NUL}DEFG"). But when I tried to run this package, I've got an error that the "ABC{NUL}DEFG" is too long for this field. Then I changed the length to 20 and it was loaded into SSIS package. But I found the value in SSIS package is "ABC", and "{NUL}DEFG" was not there~ I have no ideal why it is ok in Preview but it is not ok when the package running~     Anyway, I hope to load the {NUL} from the flat file into target database. So that is there anybody could give me a hand?

"Mixed mode assembly is built against version 'v2.0.50727'.... " issue and use custom config file

Hi, My Winform applilcation has used Microsoft.SqlServer.Management.Common namespace(assembly :Microsoft.Sqlserver.smo.dll/Microsoft.Sqlserver.ConnectionInfo.dll) and developed in .NET 4.0. Problem:Application is throw an exception when it tries to use above assembly reference methods. Full error text is shown below, "Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information. " Error is resolved when we include "app.config" file with below code, <?xml version="1.0"?> <configuration> <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/> </startup> </configuration> But, I need to keep configuration file as "renamed.config" and not to include app.config file as of the project requirement.I have set configuration file as "renamed.config" file using below code, //AppDomain.CurrentDomain.SetData("APP_CONFIG_FILE", "d:\\reanmed.config" ); I have also included <Startup> element in this "renamed.config" configuration file and removed the app.config file.But it raise me below error, it always expect app.config file with <Startup> element to resolve this iss

export to Txt file - Null value issue

My query: SELECT   PRIMSPEC, MI, Case   when PrimSpec='040' then MI else null end as MI2 FROM   dbo.PROVIDER WHERE   PrimSpec in ('040','041') My results when PrimSpec in 041: 041 W NULL When the results are saved to a tab delimited file the "Null" is exported as the text "NULL".  I need these values export as null (nothing), not the word null. Any ideas?

AjaxContorlToolkit - issue: Could not load file or assembly

Hi everyone. Can somebody please tell me how to solve this problem with my ajax controls ?I've tried with copy the .dll's files in the project, and seems they are working fine in the visual studio with drag&drop, but when I'm trying to start the project i got this error. Thanks a lot.--------------------------------------------------- Server Error in '/' Application. Could not load file or assembly 'AjaxControlToolkit.resources.ar' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IO.FileLoadException: Could not load file or assembly 'AjaxControlToolkit.resources.ar' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Assembly Load Trace: The following information can

Convert Data from TXT file and face a length issue! Need help!

Hi All, I am trying to convert some TXT files in to sqlserver database by using SSIS.  The problem I am facing right now is that some TXT files I have are fixed with line length 286.  However, for the some other TXTs they have 296 characters per line. The reason why this happened is some one added one more field before generating those TXTs.  Instead of creating two SSIS packages for converting those TXTs, can I find another way to get around this?   

Transaction Log Management issue

I'm trying to load A LOT of data to a table, -- about 64million records.  The load process itself will take hours to run and every time I tried it, I got "log is full" error.  I've set the max size to 80G now for the transaction log.  I also scheduled a job to run every 10 mins to shrink the log file.  But it doesn't help at all.  Here are my questions: 1. how to estimate the max size of the log file?  I don't want it to grow unlimited. 2. how to keep the log file as small as possible during the load process?  It'll be the best to me if it can be turned off.  Because I don't need the transaction log at all.   Please help.  It's pretty urgent as I'm very frustrated because of this log issue.  I've tried many times, and the job failed every time.  I'm getting nowhere. Thanks.

Very high transaction log file growth in SQL server

Hi,   I am using with below version of SQL server. Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2).   One of my database transaction log file is getting full very frequently on PROD database,which i have truncated ones and shrunk ones after that i have taken full backup immediately.Again the Db tran log file grown to very big size. We are taking the transaction log backup for evry one hour. and The column shows log_reuse_wait=2 & log_reuse_wait_desc=LOG_BACKUP from sys.databases. Can you please suggest me free the transaction log space without truncating the log file.   Thanks, Gangadhar  

LINQ to SQL Compact db, file copy issue

Our application uses LINQ and SQL compact database .sdf file as datasource.  It is using Miscrosoft SQL Server Compact 3.5 SP 1 and .NET Framework 3.5 SP1. We store our application's project data into a .sdf file.  When our application opens a .sdf file (e.g. projectA.sdf), it will make a temporary copy of it and open the temporary file instead of the original file.  If we change one table cell value (i.e. one property value of an object that is created by LINQ), and call DataContext.SubmitChanges(ConflictMode.ContinueOnConflict), and then call File.Copy(DataContext.Connection.Database, filename, true); to copy the temporary file back to the original file (replacing the original one).   We found that the table value is not changed in projectA.sdf.  But in the temporary copy, the value is changed.  If we re-open db connection by calling DataContext.Connection.Close(); DataContext.Connection.Open(); right before calling File.Copy(), the changed value is copied successfully to projectA.sdf. If we do a lot of value changes in the database, or adding / deleting table row, then submit changes.  Without re-opening the db connection, after File.Copy(), the projectA.sdf have those changes copied. This strange behaviour looks like the temporary db file is cached.  For a small change, the cached version is not changed.  The File.Copy() use t

A transaction sql issue, Please see and suggest,,,,,,,,


There is a table

ID        Grade

1            a

1           b

2           a

3          a

3          b

4          a

4          b


I want to retrive the data and output as

ID      Grade

1         a|b

2          a

3         a|b

4         a|b

It looks that the data can not be  [group by] and join the string  in the same group by any existing system function,

We can use cursor to join those data by comparing the id

Do you have any better solution?


Delivering Replicated Transaction take very long time


Hi All

I have pull transactional replication under SQL 2000 to SQL2005, SQL 2000 as publisher and SQL2005 as Subscriber.

I have setup replication with 28 distribution job agent,i used pull transactional replication. every agent contains 1 or more table to be replicate. i have problem with 1 agent replication with 1 table to be replicated, it call ARAccount_Receivable. since initial snapshot distribution agent show message "Delivering Replicated Transaction". the distribution agent was running for 11days, but the data have lastupdate since 3 days ago, means replication was running but very long time delay. this table size have only 32 million data, if compared to the other table with have size more than 100 milion data was running normal.

Below the agent distribution profile 

-BCPBatchSize : 100000

-CommitBatchSize : 1000

-CommitBatchTreshold : 1000

-HistoryVerboseLevel : 1

-KeepAliveMessageInternal : 300

-LoginTimeOut : 1000

-MaxBcpThreads : 10


Opening vs2008 solution file in vs2010 - Framework targeting issue


Hey guys,

I am opening a vs2008 .sln file inside vs2010 - the conversion wizard shows up, and it goes through with no errors other than a pop up warning me that this solution needs .Net Framework 3.5 and that it is "not installed on this machine" - I chose to target it to .Net Framework 4.0 and to try to switch it later as an option.

I then bring up Properties - >Application -> Target Framework and in the dropdown, .Net Framework 3.5 is not listed - which is strange since in "Add or Remove Programs" I can see .Net Framework 3.5 is installed on my machine.

How can I get Framework 3.5 to show up in the dropdown so I can target this project to that framework?

Can't say I've ever done this, so I'm pretty sure I'm doing something wrong.


Update: I tried Method #1 here (http://blogs.msdn.com/b/robvi/archive/2004/02/17/75272.aspx) and I cannot see v3.5 - so where did it go? I'm confused why it is not in my registry but shows up in "Add or Remove Programs" list...


Multiple databases backup/restore in a transaction, if possible, into only one file


Hi dear,

I have three databases named XDW, XOLTP, XOLTPSchema. All of these are for only one application. In the application, user should do backup/restore using GUI.


1. How I should do backup/restore in one transaction? i.e. all of three actions should be success elsewhere all of them should fail.

2. Is it possible to backup them into only one file e.g. X.bak?

3. Or, What is the standard solution in this condition?

Thanks in advance.

How To: Joining multiple DataTable using “LINQ to DataSet”

Themes/Generic.xaml issue when file is Link from another project


I have two Project A and B that use a Themes project. In the Themes project, I defined a Generic.xaml file. If I run the program on the A project, it works.

But in the second project (B) I don't create the Generic.xaml file but link (add existing item -> add as link) it from the first project (A). When I run this one I got this error :

'/Theme;component/Themes/Generic.xaml' value cannot be assigned to property 'Source' of object 'System.Windows.ResourceDictionary'. Cannot locate resource 'themes/generic.xaml'.  Error at object 'System.Windows.ResourceDictionary' in markup file 'AssemblyProjectB;component/app.xaml' Line 9 Position 37.

If in the second project (B) I create the file instead of linking the generic.xaml created in the first project (A), it works.

Any idea ?


LINQ to SQL Classes (dbml file) across multiple websites issue



I have a single LINQ to SQL Classes (dbml file) compiled in a Class Library that's referenced by 3 websites. Inside that Class Library there's also a class that's using the DBML to manipulate data.

At one point it needs to save in a user table the user info (firstname, lastname, email). SOMEHOW (!?) two of the 3 webapps saves email instead of firstname and viceversa while the other 1 saves them correctly ! The one compiled last seems to be the one saving correctly.

There's no code error as I have debugged this multiple times. The values are assigned correctly but before the SubmitChanges() call the data inside the database object is corrupted.

Now, I have already fixed this locally by putting every website in a separate AppPool (which also needs to be set to "Classic" and not "Integrated". However I really don't want to bug the client with such IIS changes so I'm looking for another workaround !

Please Help!



Log file issue


We are running sql 2008 on server 2003 enterprise. 

The db has gone into recovery due to the servers going down unexpectedly, the disk where the log writes to is full (1TB) and (232KB left) due to two log files of circa 500gb each (I know far to big). Consequently the db cannot recover itself as it can't write logs.  We are aware we need to shrink the log and planned to but the server when down before we had chance (electricity company cut through a cable) but cannot do this while in recovery.  We cannot move or delete any ldf files from the drive as it says 'they are in use by another program' aka sql trying to recover.  And we have not got room to add more space to the disk it, so it would appear we are in catch 22.

Does anyone have any suggestions as to how we could resolve this issue?


StreamReader issue with flat file



Please observe this flat file image: http://screencast.com/t/YMpeI5pOlI

You will on the bottom of the text file, line numbers shown by MSVS 2005 that tell me that a line exists, yet with no TEXT along side it, the text file has too many carriage returns at the bottom. Lets call these BLANK ROWS of data in my text file.

The issue is when I import this data like (vbNET):

Dim oRead As StreamReader
Dim str As String
Dim rows(0) As String

oRead = New StreamReader(FullPath)
str = oRead.ReadToEnd()
str = str.Replace(vbCr, "")
rows = str.Split(ControlChars.Lf)

The rows array has entries within it for the BLANK lines I show in the image attached. And this mucks up my indexes when I manipulate that rows data.

Can I do a 'Replace' or something to clear these BLANK lines before they get into my rows array ??

At the moment I just clean up the text file before import, but I need a coding solution to make my life easier !

Any ideas ! What do you do ?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend