Issue with non-replicated transaction in Log file

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.

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?

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

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?

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

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?

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.

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

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

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.

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?

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...

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 !

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?

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.