Unable To Shrink Transaction Log Under Transaction Replication With Sync With Backup Enabled On Distribution Db?
We have applied transaction replication servers with SQLServer2005 standard edition with SP1 running on them. Sync with backup is also enabled on distribution db. Now our transaction log is not shrinking using standard shrinking procedure(taking log backup with truncate option/shrinkfile).
select name,log_reuse_wait, log_reuse_wait_desc from sys.databases
it is showing dbname, 6, Replication respectively.
declare @db int
set @db=db_id('dbname')
dbcc loginfo(@db)
is showing 2 in status column.
tried shrinking transaction file after removing sync with backup option from distribution db. still it failed. again tried shrinking after stopping log reader, it didn't worked.
Regards,
M. Abdullah Idris
View Complete Forum Thread with Replies
Related Forum Messages:
Transaction Log Backup Did Not Shrink Ldf - Help Please
I am a bit confused. I was told that backing up the TLog cleared out all the commited transactions which shrinks the log. Is this true, becuase I started with a 72 gig transaction log and the created a maint plan to back it up as a .trn. After this was successful the 72gig .ldf was still the same size. What am I doing wrong to get this log size down? Thanks!
View Replies !
Transaction Log Backup And Replication
Hi, I'm new to SQL Server 7.0. I wonder if anybody could help me answer the following two questions - 1)Is there any replication bench marks (speed/memory usage, etc) that we can use? So that we can better configure SQL Server accordingly. 2) As for disaster recovery side of things, I was told the "NO_TRUNCATE" option when backing up a transaction log doesn't actually work in SQL Server 7.0, although it worked in 6.5, is this true? This will mean a full data recovery can not be achieved in the event of a system failure. Help on this would be greatly appreciated. Thanks, Shaunak
View Replies !
Backup Transaction Logs And Replication Simultaneously
Is there any problem with implementating some kind of replication andbacking up transaction logs at the same time? SQL Server 2000 StandardEditionThe server is configured to back up log files regularly, with thepresenceof a(New) Disaster recovery server and with an inability forstandby(Standard Edition)I am planning to implement some kind ofreplication hopefully still backing up the transaction logs to enablea point in time recovery.Thanks in advance for your great ideasVincento
View Replies !
Can I Add A Transaction Log File While Mirroring Is Enabled?
Hi, I'm trying to add a new transaction log file to a database with mirroring (high safety, automatic failover). When I add the new transaction log file via SQL management studio, the mirroring status becomes suspended. When I try to restart mirroring, it attempts to re-synchronize and then goes back to the suspended state. Only after I delete the new transaction log file can I sucessfully restart mirroring. Can you add a transaction log file to a mirrored database? Any ideas would be appreciated. Also, I'm not sure if this matters, but I am trying this in my development environment and all 3 instances (principal, mirror, witness) are on the same server. It's a long shot, but I thought maybe both the mirror and principal are trying to create the file at the same location on disk. Thanks, Don
View Replies !
Error 8525: Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
Hi All I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link. If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser. I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off. set XACT_ABORT ON Begin distributed Tran update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 and DONE = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 COMMIT TRAN It's got me stumped, so any ideas gratefully received.Thx
View Replies !
Cannot Shrink The Transaction Log
Hi Friends,I have tried almost everything but I cant seem to shrink thetransaction log.Executing DBCC SQLPERF(LOGSPACE)gives me this info:Database Log Size (MB) Log Space Used (%) StatusMY_eems 368.49219 16.034182 0I made a complete backup of the database and transaction log and thenexecuted this statement:DBCC SHRINKFILE (MYeems_log, 1)and this is the message I gotCannot shrink log file 2 (ghgeems_Log) because all logical log filesare in use.DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages------ ------ ----------- ----------- ----------- --------------17 2 42880 34561 42880 34560(1 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.How can I get the transaction log to its minimum size?Any help will be appreciated,ThanksA.B
View Replies !
Cannot Shrink Transaction Log
Hi, I have a t-log on one database which is 400mb when the database size is only 30mb. dbcc shrinkfile does not work and dbcc opentran shows no open transactions. When I tried to do a Backup log with truncate_only the following message displays: 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. The database in question is not a publisher and only receives 5 subscribed articles, none of which are changed very often. The database operates 24 x 7. Does anyone have a solution to this dilemma? Thank You
View Replies !
Shrink Transaction Log
I'm loading data on what is now a development db (but soon to go into production). During my experiments, I'm getting HUGE transaction logs. I'm trying DBCC ShrinkDatabase and DBCC ShrinkFile on the log, and I've even resorted to using the GUI -- but the sizes aren't changing, even after explicit CHECKPOINTS and re-starting the SQL Server service. Is there some sort of lag time that I have to deal with? How long should I have to wait?
View Replies !
Shrink SQL 6.5 Transaction Log
Is it possible to shrink the size of a transaction log file in SQL 6.5. Normally in SQL 7.0 I use detach and reattach but this is not supported in 6.5. The log file for a 200Mb database is 749Mb with no unused space and no open transaction. Thanks
View Replies !
Transaction Log Will Not Shrink
I have 4 large sql dbs. All 4 have been trimmed down by deleted recs from the db, then shrinking. 3 out of the 4 shrunk successfully and the log files are much smaller than the data files. On the 4th the data file shrunk to 14G, but the log file stayed at 47G. I have tried truncating and then shrinking, but it never gets any smaller. Any suggestions on why this particular one will not shrink?
View Replies !
Distribution Transaction Retention
I currently have a simple transactional replication setup for a database. My publisher and distributor are on the same box. The subscription is setup using a push agent. My question is related to recovery of the subscriber. So lets say replication is setup and working fine. Suddenly we had a failure on the subscriber database. Now I could just reconfigure the subscription, and the subscribing database would be back up and good to go, but the problem is that over time, we have made some changes to the subscribing database that are not made in the publisher. For example, the tables have different indexes. Just reconfiguring the subscritpion would not recover these objects. So I have to acutally restore the subscriber database. So I do that, and apply transaction logs up to the most recent transaction log backup. Now, consider that my transaction log backups on the subscriber happen every 4 hours, and the most recent transaction log backup I had was from 3 hours ago. So now at this point, my subscribing database is 3 hours behind my publisher. Now, will the distribution agent resend the missing 3 hours of transactions? In the distribution agent properties, there are two settings for transaction retention, "at least" and "but not more than". Currently they set to 0 and 72 hours respectivly. Now I would assume that if I set the "at least" setting to the subscriber transaction log backup period, in this case 4 hours, I would be covered, and the distribution agent would indeed re-replication the transactions that happend since the recovery point 3 hours. I just wanted to verify that this is acutally what these settings are refering too, and that if I set the "at least" setting to 4 hours, I would be covered. - Eric
View Replies !
SSIS, Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure. for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR. I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties. if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. and Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database. Please help me it's very urgent.
View Replies !
SQL Server 7 Transaction Log - Can't Shrink!
For some reason (I'm sure it was my code) my transaction log segments grew to a huge size (>1 GB each). They are normally in the area of 150 MB each, so I wanted to shrink them back down. But I have one transaction log segment that won't shrink! I'm a relatively new administrator, but I've tried shrinking the database (which worked for all but one segment) and truncating the logs, I've tried issuing a CHECKPOINT, I've tried stopping and restarting SQL Server, but I can't get that last segment to shrink to a normal size. Any ideas? Thanks, Robin
View Replies !
Can't Shrink Transaction Log From 30GB To 1GB
We have a user database that was initially allocated 90GB for data file, 30GB for transaction log file. We decided to shrink the data file to be 2GB, log file to be 1GB using 'DBCC SHRINKFILE'. The result of data file is 2GB as we expected. But log file is 5,764 MB no matter how many times we shrink it or run 'backup log with truncateonly' against it. Can this be something to do with how transaction log allocate its segments called virtual log files? Is 5GB the smallest unit of log segment that can be shrunked? Is there any way around this? We are in a production enviroment, and this is pretty urgent. Thanks in advance. Tung
View Replies !
Transaction Log Doesn't Shrink
Hi Everyone, I have a SQLServer 7.0 conversion db. Data file is 89 mg. The log has grown into 238 mg. Db and log are backuped nightly. Optimization and integrity jobs run weekly. The transaction log keeps growing! Truncate log on checkpoint is on and autoshrink is on. What else can I do to truncate my log? LT
View Replies !
SQL 7.0 Transaction Logs Won't Shrink!!!
I have seen quite a few people post this type of problem, but I am finding few solutions. Your advice and/or experiences are greatly appreciated. Here's my scenario: Environment: Windows NT 4.0 SP 6, SQL 7.0 (set up for Transactional Replication) Problem: We have several remote dB machines configured for full recovery. On these machines are several dB's that are capturing aprox. 280 data points per second per unit. (Each dB represents one unit, and we have 21 units) No problem here. The problem is the transaction logs, that obviously grow profusely, will not SHRINK after, backups, dbcc commands and TSQL has been issued in failed attempts to shrink the logs. (in other words we've tried everything) My questions are: 1. Because we are replicating, is it absolutely necessary to configure dB's for FULL recovery? How do I check in 7.0 if the dB is certainly in FULL vs. SIMPLE recovery mode? 2. I work with SQL 2000 and shrinking files is no problem. How can we shrink these log files in SQL 7.0? It is rather urgent I find a solution as we are running out of hard drive space on our remote machines. Please help :-( Christine
View Replies !
Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
Hi all, I've already read the thread by the same topic, but I found Kevin Yu's comment a bit confusing. I don't know how to solve my problem. I have two stored procedures in sql server. First one inserts into a bunch of tables so it has Begin Transaction and Commit in it. Second one just inserts into one table so it doesn't have the begin transaction statement in it. From my data access class in c#.net, I have a function which calls both of these stored procedures from within a "using transactionscope" block. So when it makes the call to the first stored procedure, it returns fine, but when it calls the second one, I get the above error. I think it has something to do with me declaring transactionscope in my application code, and then calling a stored procedure which also has its own begin transaction. But I don't know how to fix this. Any ideas? Thanks
View Replies !
Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction
I'm using TransactionScope object and it is giving me problems. The situation is like this. I've 2 tables in sql server express, say student and courses and other one studentcourse for courses a student has opted for. An aspx page displays list of users in listbox, when user chooses student, it fetches courses he has opted for and in the checkedlistbox (for courses) these courses are checked. Now when uses makes any changes to courses and clicks update button, i've two functions at backend first one removes the courses which were previously selected but now not selected in postback, next it inserts courses which was not selected first but now are in this postback. I use following code: protected void Update(object sender, EventArgs e) { /* get selections, existing courses etc. */ using(TransactionScope tscope=new TransactionScope()) { try { RemoveCourses(/*Student*/ s, /*IList*/ oldCourses, /*IList*/ newCourses); AddCourses(/*Student*/ s, /*IList*/ oldCourses, /*IList*/ newCourses); tscope.Complete(); } catch(Exception ex) { lblMessage.Text=ex.Message; } } } If any exception occurs during update and page is reload or I go to any other page where some data is fetched from sql server I get the exception: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction Please help!
View Replies !
Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
I am getting this error :Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. 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.Data.OleDb.OleDbException: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.have anybody idea?!
View Replies !
Shrink The Transaction Log File In SQL7
I have a database (sql server 7.0) that has some big unused space in the transaction log. I tried to shrink the transaction log, but it seems it is not working. I used the same procedures to successfully shrink the log file for database of SQL Server 2000. Here are what I tried: shrink database from the Enterprise Manager. or dbcc shrinkfile (Spoper_Log, 40) backup log Spoper with truncate_only Does anybody know how to shrink the transaction log in SQL Server 7? This is a production database and I can't afford to lose any data.
View Replies !
Distribution Job Step Retries All In One Connection And/or Transaction?
When a distribution job step "Retry Attempts" is > 0 and the step has certain problems, the step is "retried" after "Retry Interval". But I am uncertain as to the details. Are the retries within one database transaction? Does each "try" get its own transaction? And what about connection? Is the "retry" done with the same connection? I know this may sound funny but I would like to know exactly what is going on here. Thanks,
View Replies !
Transaction Log Does Not Truncate And Shrink Using A SQL Server 2005 Maintenance Plan
We are using SQL Server 2005 (SP1). I have created a maintenance plan that backs up up the datebase every night. The problem is that the transaction log is continuing to grow. I have been told that a full backup will automatically truncate and shrink the transaction log. However, this is not happening. How can I truncate and shrink the transaction log after a full backup as part of our maintenance plan. Thank you.
View Replies !
Replication - Sync Subscriber Using Backup
Hi Guys, I have tried backing up the replication from Primary Server (Publisher) A and copying over to Subscriber B. When I sync. I am missing the data. I don't know what step I am missing or it is bug on SQL 2K SP3. I don't know what i am doing wrong. Can anybody give me a step by step detail how to backup the publisher database and copy to a subscriber and sync by saying the schema and data already available. As we have WAN problem all the times, and when I try the full snapshot of 20 gig it was taking more than 24 hrs and also if the connection fails my snapshot also fails Please help. Thanks in Advance, -Anu
View Replies !
Unable To Truncate The Transaction Log
Hello; Within SQL Ent Manager - I am unable to truncate the tran log using the shrink file option window (although I can shrink the database file) - nor can can I truncate the log using command line sql in a query analzer window (dump tran < > with truncate only).... Anyone out there offer any suggestions?? Thanks.
View Replies !
Unable To Begin A Distributed Transaction
I have this code DECLARE @tempTableName VarChar(50)SET @tempTableName = NEWID() CREATE TABLE #@tempTableName( State Char(2), Billed Money, AslCode VarChar(10))INSERT INTO #@tempTableName EXEC GetRecords '2/1/2008' which gives me this error when I run The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. GetRecords does a select * from a linked server's table. Which is working fine but if I try to do an insert (into temp table) then I get the error. Any help?
View Replies !
Transaction Unable To Rollback Trigger
Hi, i have an application where it stores new data into a table and this table have a trigger to copy the current data to other table.I am unable to roll back my application when error occurs in my trigger, it said that my Sql connection transaction is no longer in use which i dont understand why?? can some 1 explain..
View Replies !
Unable To Begin A Distributed Transaction
Having a SQL Server 2K (SP3a) with a link to another SQL Server 2K (noservice packs), distributed transactions works as expected most of thetime.But occasionally the MSDTC(s) seems to fall in a state of failure,causing the following error when involving distributed transactions:"The operation could not be performed because the OLE DB provider'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist inthe specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a]."The only way out of this state is to stop and start both of the SQLServers (stop/start of the DTC's doesn't help).Is this a common problem?Guess I will be recommended to install SP on the server withoutservice packs, but will this positively solve the problem describedabove?Could general network failures cause the error state described above,and if so - is there a way to make the MSDTC's survive or recover fromthe error situation automatically?--Morten Haugen
View Replies !
Unable To Begin A Distributed Transaction
I am using MSDASQL provider to create a linkedServer and is using Distrubuted Transaction in the application...but it is throwing out the following error... Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction. [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Distributed transaction error] OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a]. What keeps me guessing is that the same code worked in the development environment and had no problems in starting the Distributed Transaction... Note: DTC is running on both the server and I am not using a loop back server!!!! Please Help
View Replies !
Unable To Take Differential Or Transaction Log Backups
Hello SQL Server 2005 SE sp1. Unable to take differential or transaction log backups. Get the error message a full backup need to be down. Have done a full backup. Immediately upon completion of full have tried diff and transaction log. Still no luck Other databases on server are fine. Full backup will restore. Thanks, Joe
View Replies !
Unable To Kill Transaction In Msdb
Hello! I am trying to troubleshoot weird problem. I noticed that msdb log size was growing. I have discovered jobs executing particular stored procedure that had an open transaction in msdb and killed spid in question. This stored procedure was executing sp_send_dbmail at the time I killed spid and fn_get_sql returned xp_sysmail_format_query as currently executed statement. SPID is showing KILLEDROLLBACK status but it hasn't changed for hours and msdb log continues to grow. I am running SQL Server 2005 SP1. I was wondering if anyone has expeienced similar problem. Any advice is greately appreciated. Thanks, Igor
View Replies !
Commit Transaction Gets Deleted - Unable To Save SP
I've re-written a stored procedure and when I post the following codeinto the existing SP in EM, is saves OK. However, when I re-edit theSP, the last line 'Commit Transaction' has been removed.I cannot save the remainder of the SP as it throws error 208 (InvalidObject name #Max) about two of the temp tables I use when I post theentire script. It shows in a message box with the header : 'MicrosoftSQL-DMO(ODBC SQLState:42S02)I haven't posted the full SP nor the structure as it's quite large(2000 lines), so hopefully I have given enough detail, but my questionsare :Why does it now have problems with (temp) #Tables ? The use of thesehas not changed. All I have done is wrap the script into varioustransactions as this helps a lot for performance and tweaked a fewparts later in the SP again for performance.Also, why does the line get removed once I save the SP ?If I run this in QA, I get the same errors, so I suspect it's myscript, but don't know where I'm going wrong.SQL2000 (Need to upgrade the service pack as recently installed on myPC, so this may help)Thanks in advanceRyanCREATE PROCEDURE [dbo].[JAG_Extract] (@ExtractYear INTEGER,@ExtractMonth INTEGER) ASBEGIN TRANSACTIONSELECT 0 AS MaxYear, 0 AS MaxMonth INTO #MaxUPDATE #Max SET MaxYear = @ExtractYearUPDATE #Max SET MaxMonth = @ExtractMonthPRINT 'Stage 1 - ' + Convert(VarChar, GetDate())CREATE TABLE #Extract ([DEALER_SOURCE_DATA_ID] INT,[DSD_YEAR] INT NULL,[DSD_MONTH] INT NULL,[DEALER_CODE] VarChar(20),[FranDealerCode] VarChar(20) NULL,[Line_No] VarChar(75),[Current] [numeric](15, 5) NULL,[YTD] [numeric](15, 5) NULL,[12Months] [numeric](15, 5) NULL,[24Months] [numeric](15, 5) NULL,[Average_YTD] [numeric](15, 5) NULL,[Average12Months] [numeric](15, 5) NULL,[Average24Months] [numeric](15, 5) NULL,[Last_YTD] [numeric](15, 5) NULL,[Current_STATUS] INT,[PD1] [numeric](15, 5) NULL,[PD2] [numeric](15, 5) NULL,[PD3] [numeric](15, 5) NULL,[PD4] [numeric](15, 5) NULL,[PD5] [numeric](15, 5) NULL,[PD6] [numeric](15, 5) NULL,[PD7] [numeric](15, 5) NULL,[PD8] [numeric](15, 5) NULL,[PD9] [numeric](15, 5) NULL,[PD10] [numeric](15, 5) NULL,[PD11] [numeric](15, 5) NULL,[PD12] [numeric](15, 5) NULL,[PD13] [numeric](15, 5) NULL,[PD14] [numeric](15, 5) NULL,[PD15] [numeric](15, 5) NULL,[PD16] [numeric](15, 5) NULL,[PD17] [numeric](15, 5) NULL,[PD18] [numeric](15, 5) NULL,[PD19] [numeric](15, 5) NULL,[PD20] [numeric](15, 5) NULL,[PD21] [numeric](15, 5) NULL,[PD22] [numeric](15, 5) NULL,[PD23] [numeric](15, 5) NULL,[PD24] [numeric](15, 5) NULL,[PD25] [numeric](15, 5) NULL,[PD26] [numeric](15, 5) NULL,[PD27] [numeric](15, 5) NULL,[PD28] [numeric](15, 5) NULL,[PD29] [numeric](15, 5) NULL,[PD30] [numeric](15, 5) NULL,[PD31] [numeric](15, 5) NULL,[PD32] [numeric](15, 5) NULL,[PD33] [numeric](15, 5) NULL,[PD34] [numeric](15, 5) NULL,[PD35] [numeric](15, 5) NULL,[PD36] [numeric](15, 5) NULL)INSERT INTO #ExtractSELECT DISTINCTSD.DEALER_SOURCE_DATA_ID,SD.DSD_YEAR,SD.DSD_MONTH,DN.DEALER_CODE,DN.FRAN_DEALER_CODE,DV.FIELD_CODE,0,0,0,0,0,0,0,0,SD.STATUS,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0FROMDEALER_NAW DN WITH (NOLOCK)INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)ON DN.DEALER_CODE = SD.DEALER_CODEINNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_IDAND SD.STATUS < 4096INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)ON DN.DEALER_CODE = GR.DEALER_CODEAND GR.FIXED_GROUP_ID IN(11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,4 9,50,51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,1 12,113,114,115,130,131,132,133,134,135,136,137)GOCOMMIT TRANSACTION
View Replies !
Unable To Begin A Distributed Transaction (Msg 7391)
I am receiving the following error when I run a stored procedure:Server: Msg 7391, Level 16, State 1, Procedure spXXXXThe SP inserts data into a local (sql 2000) table from a remote sql2000 database. The SP works fine until I add a trigger to thedestination. The contents of the trigger do not appear to matter, as Ihave tried removing everything but one line, which is just a declarestatement. Updating the table with the trigger (even the full trigger)in place does not cause an error. Running the SP without the triggeron the table does not cause the error.I have the exact same setup on another server. Both the SP and thetrigger are IDENTICAL. This setup works on one server, but not theother. The only difference I can see is that the non-working server isWindows 2000 Server, while the working server is Windows 2003 Server.Any idea what's going on here?Thanks in advance.Eric
View Replies !
Unable To Reduce Transaction Log File Size
HELP !!! I have a 1.2GB transaction log that I cannot reduce. I have tried SHRINKFILE, SHRINKDATABASE, restoring the file into a new data base, etc. The log file is only using 15MB and will not release the remainder to the OS Any and all suggestions would be helpful Thanks, Chad
View Replies !
'SQLOLEDB' Was Unable To Begin A Distributed Transaction
SQL2000 / W2K I'm having issues with a query to a view that references a view on a linked server, that in turn references a view on a third server. Query to view on Server A --> View on Server B --> View on Server C I can query from A to B using the linked server. I can query from B to C. I can query from A to C. All servers at the same location on the same domain. I cannot query a view on A, that references a view on B, where the view on B references a view/table on C Each linked server configuration uses "Be made using this security context", with the user specified being 'sa'. I was hoping to get security out of the equation for debug purposes, hence the sa use. I have tried from Query Analyzer using both windows and sql auth. MSDTC is running on all servers as local system. The full error returned is: Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] Thanks for any support, Chris
View Replies !
|
TRACKER
