SS 2000 Transaction Log

Feb 14, 2005

Hi all,

I'm having an issue shrinking a transaction log, and am hoping that one of you guys could give me some advice....

Currently, this database (DB1) has approximately 20GB of data, and my transaction log has grown to 22GB.

The database is in Simple Mode (although may have been created as Full, then changed)

I have a Maint. Plan backing up the DB daily, but noticed today that this has not been working for the last week :'(

There are 3 other databases on this same server, which are similar sizs in GB, but have tiny Trans Logs - their backups have of course been working fine.

I've backed up DB1 this morning - a whopping 42GB file - then issued a "dbcc shrinkfile", followed by a "backup log ... truncate_only", followed by another "shrinkfile", but the log is still 22GB

Any suggestions??? Please....

View 1 Replies


ADVERTISEMENT

How To Minimize The Transaction Log SQL 2000

Dec 11, 2001

My database log file is now 53M, is it a way to minimize it, delete it or erase it, this log file is to big for nothing, I try to shrink it and delete it manually, but this corrupt the database, can someone help me thx ?

View 2 Replies View Related

SQL Server 2000 SP3 Transaction Log Errors

May 31, 2008

Hi!

We have a Microsoft SQL Server 2000 SP3 running database for Microsoft
Navision 3.7

From time we encounter problems, especially when running heavy query
procedures from Navision, with the transaction log. It's actually setup as
folows:

File properties:
File growth By percent (10)
Restrict file growth (MB) 10000

OPTIONS:
Recovery model: simple
Settings: Autoupdate statistics, Auto create statistics, Autoshrink

We get the following errors (once every 2-3 months so far):

The log file for database 'ME_Prod' is full. Back up the transaction log for
the database to free up some log space..


in between numerous abovementioned messages I have the following:
Configuration option 'show advanced options' changed from 1 to 1. Run the
RECONFIGURE statement to install..

Could not write a CHECKPOINT record in database ID 9 because the log is out
of space.

Automatic checkpointing is disabled in database 'ME_Prod' because the log is
out of space. It will continue when the database owner successfully
checkpoints the database. Free up some space or extend the database and then
run the CHECKPOINT statement.

View 10 Replies View Related

MS SQL 2000- Reduce Transaction Log Size

May 5, 2004

The size of my transaction log file is out of control. I've backed up the database and the transaction log went from 120 GB to 120 MB. Now, I can't reduce the size of the transaction log file. It's still at 120 GB (w/ almost all of that being held as Free Space). I get errors when I try to manually reduce the file size. Any tips?

View 9 Replies View Related

SQL Server 2000 And Transaction Log Error

Jul 20, 2005

Hi All:I am getting an error when trying to open a recordset in SQL Server 2000.The error states that the transaction log is full. Is there any way I canclear out or empty the transaction log, or get rid of it alltogether as itis not really needed?Any help would be appreciated.Thanks and regards,Ryan

View 1 Replies View Related

Error 8525: Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

May 31, 2008

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 1 Replies View Related

Controlling A Transaction By User In SQL Server 2000

Jun 14, 2007

Hey Folks!
I have a typical requirement by my client. On submitting a Update (Bulk) button a huge database operation starts. A huge bulk update operation need to be performed. This would take 2-3 minutes some times. Client wants a cancel button in this case where he can be given a way to cancel the database Transaction.
 Please let me know in case if there is a way out.
Thanks, in advance.
Regards,
Uday.D

View 2 Replies View Related

SQL Server 2000 And Transaction Log Error - More Info

Jul 20, 2005

Hi All:I am getting an error when trying to open a recordset in SQL Server 2000.The error states that the transaction log is full. Is there any way I canclear out or empty the transaction log, or get rid of it alltogether as itis not really needed?Any help would be appreciated.Thanks and regards,RyanThe error msg is: tempdb transaction log is full. B/U transaction log tofree up space..."

View 2 Replies View Related

Load SQL 7 Database And Transaction Log Backups To SQL 2000

Jul 20, 2005

Is it possible to load both the SQL 7 database and transaction logbackups to SQL 2000 ? I assume it will perform the upgrade during theload.Thanks,James

View 4 Replies View Related

SQL Server 2000 - Table Transaction Date

Aug 9, 2007

(I may be in the wrong forum.)
How do I obtain/find the properties of a table using SQL Query Analyer (SQL Server 2000)? Specifically, I would like to run a query to find the most recent date of any transaction on a table. I have a script that I use for SQL Server 2005 but it doesn't work in 2000. I don't know 2000 but I'm guessing that the syntax is different?

Here's the 2005 SQL Server script (stolen from 2005 Books Online BTW):

CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));

GO

CREATE TRIGGER ddl_log

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @data XML

SET @data = EVENTDATA()

INSERT ddl_log

(PostTime, DB_User, Event, TSQL)

VALUES

(GETDATE(),

CONVERT(nvarchar(100), CURRENT_USER),

@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

GO


Here's the error:

Msg 156, Level 15, State 1, Procedure ddl_log, Line 2

Incorrect syntax near the keyword 'DATABASE'.


Remember, I want to do the same thing in SQL Server 2000.

Thanks in advance for any assistance you can provide.

View 1 Replies View Related

Distributed Transaction On Windows 2000 Professional

Jun 19, 2006

Just curious if anyone out there has had success running a Distributed Transaction on Windows 2000 Professional SP4 lately?

I have come to the conclusion that is is virutally impossible. I am running MSDE on a Win2k Pro machine and trying to run a distributed transaction via linked server and am getting the

"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction."

error. Yes I have been scouring the web looking for answers and have implemented pretty much everything I have run accross... and Yes the MSDTC service is started on both machines... BTW: If I run the procedure on XP it works great!!!

I am wondering if anyone even uses Win2k Pro anymore let alone trying to run a distributed tarnsaction on the darn thing. 

If anyone out there has it running let me know I would love to chat with you for a minute or two.

 

View 3 Replies View Related

Implicit Transaction Mode In SQL Server 2000

May 17, 2007

Hi all:



I know i can use the sentence SET IMPLICIT_TRANSACTIONS ON in a Stored Procedure to force SQL Server to set the connection into implicit transaction mode.



Have i a sentence or configuration to force all SQL Server connections to implicit transaction mode?



Thanks in advance.

View 2 Replies View Related

Distributed Transaction Between 2005 && 2000 Failing

Sep 19, 2007



Hi Folks,



I have been struggling with a problem for the last couple days now regarding MSTD and distributed transactions. The main issue is that 3 servers are each sitting in a different domain. Non of the domains trust each other.

As of yesterday, I could get all the SQL 2005 chatting to each other using MSDTC without a problem. However, I have a SQL 2000 box which refuses to work. A normal query across a linked server works fine. A distributed transaction will not work. This is the case from A to B and B to A.

I have tested with DTCPing and it says all is ok.



The error messages are:

SQL 2000 > SQL 2005

OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" returned message "No transaction is active.".

Msg 7391, Level 16, State 2, Line 2

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" was unable to begin a distributed transaction.

And the other way round:

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

Msg 7391, Level 16, State 1, Line 2

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

If watching the DTC monitor, it shows an active transaction then goes back to 0.

All instances are on a 4 node cluster with 3 nodes turned off for the testing.

I have run out of things to try. Most of the symptoms for the above messages are to do with DTC security, the Turn RPC Security Off reg hack. All instances and OS (W2K3) are the latest updates / patches.

As for the 3rd server, SQL 2005, it is happy to talk

Anybody got a gem which I can try?

Edit: Both SQL 2005 servers are x64 while the 2000 is x86




Cheers,
Crispin

View 3 Replies View Related

SSIS, Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Feb 22, 2007

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 3 Replies View Related

How To Alter The Transaction Logfile Size In MSDE 2000?

Dec 1, 2004

Hi there,

I recently saw that the transaction log files of user dbs grow undefinitely in SQL Server 2000 - one of our customers had a 11 GB log file which totally slowed down the server.
Another customer of ours uses one of my applications logging all actions in a MSDE database file and I fear that the corresponding transaction log file will grow and block the system too - is there any way that I could shrink and set the max size of the transaction log file through SQL?

I already know the command "SHRINK FILE ('filename')" but I haven't found a SQL command to set the max size.

Thank you for any hints!

Sascha

View 3 Replies View Related

Newbie - Sql Server 2000 Transaction Log Size Limit

Jan 12, 2004

Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?

Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.

View 3 Replies View Related

Control Transaction In Sybase Database Using Sql-server 2000

Apr 20, 2004

Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction


Create procedure myCurrentDataBaseProcedure
as
begin

begin tran
insert into mytable values(1)
if @@error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @@error <>0
begin
rollback transaction
return
end
commit transaction
end

The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.

But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions

Pl.Help

View 5 Replies View Related

Transaction Replication && Data Archiving On SQL Server 2000

Jul 23, 2005

Hi techiesI have set up a Transaction replication from My Primary Server toSecondary Server on Orders table.Thousand of records gets inserted on Orders every hour which getreplicated on the secondary server. it works finereporting apps uses Secondory server's Orders table data for generatingreports .The Problem :Let say if i want to Remove older records from Orders table in theprimary serverwith out reflecting this change on the secondary server.is there a way to PREVENT this operation /transaction to be propogatedto the secondary server.Note : i am moving the records to another table (orders_Archive ) anddeleteing the rows from orders table . Also I need all the rows to bepresent on the secondary server table.Please advice ASAPRegards,Raj

View 4 Replies View Related

SQL Server 2000 - Run Transaction Log Backup Using Windows CMD File

Mar 19, 2007

How I can run a SQL Server Transaction Log Backup using a Windows CMD File.  It's that posible?  Anyone know the command or how do this?

We already have a application monitoring our SQL databases.  Everytime the transaction log get full, it sends us an alert.  Would be better if our application repair the problem itself, executing a command (Windows CMD File).

I'm not a expert using SQL databases or MS DOS Prompt commands.

Any help will be appreciate.

View 1 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Feb 6, 2007

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 1 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Dec 22, 2006

i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.

Message is like this:

Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

View 8 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction. (HELP)

Jan 8, 2008

Hi,

i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".

my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.



Thanks in advance.


View 2 Replies View Related

How To Skip Snapshot In Transaction Replication (from SQL Server 2005 To 2000)

Jun 13, 2006

We have two SQL Server 2005 production DB at remote sites. Due to network bandwidth issue, we need to replicate these DBs (publishers and distributers) to central corporate SQL 2000 DB (subscriber for backup and possible reporting (and in rare case as a failover server).

We would start out with backup from SQL 2000 db restored on remote SQL 2005 DBs. When we have DB issue on remote 2005 DB, we want to restore it from central corp. 2000 DB backup. Since two DBs are replicating to central DB, we DO NOT want combined db back up data on restored remote 2005 db. We can restore the db and delete unwanted data before we turn on replication from this restored server. So, this is not a problem.

The real problem is how to avoid snapshot replication (during initialization) when we create a transaction replication on this restored server to avoid over writing data on the central subcriber sql 2000 DB???

HELP!!



View 5 Replies View Related

Transaction Left In Open State On SQLServer 2000 And JDBC

Sep 6, 2007

I have a problem where my connection always has an open transaction as viewed by activity monitor.

I am using the latest MS SQL Server 2005 JDBC driver (1.1) and connecting to SQL Server 2000.

This issue arises only when selectMethod=cursor and autoCommit is set to false. This issue does not exist when I connect to SQL Server 2005.

Has anyone else seen this issue?
How can this issue be resolved?

Thanks

Peter

View 3 Replies View Related

SQL 2000 Transaction Log &"incorrect Parameter&"

Jun 13, 2006

Ok, I'm somewhat familiar with Exchange, but I'm totally new to SQL.

Here's my problem:

Each night I have a batch file shut down the SQL processes. I then copy the relevant files to another hard drive for back up purposes. I have 2 databases. The first database file and transaction log goes just fine and both files copy. The second database file goes just fine as well, but the transaction log returns "incorrect Parameter". It does this even if I simply try to right click & copy the file in windows with the SQL processes down so I've ruled out it being the syntax of my batch file.

The .LDF for this database is huge (to me at least), it weighs in at +50GB's and is growing 2-4 GB's a week. Is it safe to simply delete this transaction log and start a new one since I have a good backup of it's associated database? I'm not exactly sure what caused the incorrect parameter errors either. I inherited this problem so my background info is limited.

Any help is greatly appreciated!

Thanks in advance!

View 10 Replies View Related

SQL Server Admin 2014 :: Restore Lost Transaction From Transaction Log File

Jun 10, 2015

I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.

View 8 Replies View Related

I Need Away To Show The Pending Transaction From Transaction Replication In A User Friendly Format.

Jul 11, 2007



I want to list out the pending transaction for transaction replication by publication.



Help needed.

View 1 Replies View Related

TRANSACTIONS In SSIS (error: The ROLLBACK TRANSACTION Request Has No Corresponding BEGIN TRANSACTION.

Nov 14, 2006

I'm receiving the below error when trying to implement Execute SQL Task.

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'

I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.

Anyone know of the reason?

View 1 Replies View Related

Analysis :: Find Amount Distribution Across Different Transaction Types Under Spend Transaction

Jul 27, 2015

I created a Calculated measure in cube something like this : ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount]). To get only spend transactions. Now, I want to slice this measure with same hierarchy to find the amount distribution across different transaction types under spend transaction. But this query behaving like the measure doesn't have relation with measure.

you can think this as below query:
WITH
MEMBER SPEND AS ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount])
SELECT NON EMPTY {SPEND} ON 0
,NON EMPTY ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent]) ON 1
FROM [CUBE]

View 6 Replies View Related

How Do I Make Use Of Begin Transaction And Commit Transaction In SSIS.

Jun 1, 2007

Hi



How do I make use of begin transaction and commit transaction in SSIS.

As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:

Hresult:0x80004005

descriptionyntax error or access violation.



its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.



Thanks,

Prashant

View 3 Replies View Related

Sql2005:Cannot Use SAVE TRANSACTION Within A Distributed Transaction

Oct 10, 2005

Error returned when trying to commit the transaction to a database that is a replication distributor. (sql2005 ctp16)

View 10 Replies View Related

Problem With SSIS Transaction...Transaction Scope

Jun 1, 2006



Hi,

I am having some problem with SSIS transaction. Eventhought I tried to imitate the concept that Jamie presented at http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp

. My workflow is as followed

*********************************
For Each ADO.Record in Oracle (transaction=not supported)

If (Certain_Field_Value = 'A')


Lookup Data in SQL DB with values from Oracle (transaction=not supported)

DO Sequence A (Start a Transaction , transaction=required)


INSERT/UPDATE some records in SQLDB(transaction=supported)
Finish Sequence A ( transaction should stop here)
UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)
If (Certain_Field_Value = 'B')


Lookup Data in SQL DB with values from Oracle (transaction=not supported)

DO Sequence B (Start a Transaction , transaction = required)


INSERT/UPDATE some records in SQLDB (transaction=supported)
Finish Sequence A ( transaction should stop here)
UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)
If (Certain_Field_Value = 'C')

------------
------------
End ForEach Loop
*************************************
My requirements are that I want separate transaction for each Sequence A, B, C, etc... If Sequence A transaction fails, the other should still be continuing with another transaction.
But I am getting an error regarding the OLEDB Error in next Task (e.g in Certain_Field_Value = 'B') "Lookup Data in SQL DB with values from Oracle ", the error message is ".......Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. ".
What is it that I am doing wrong?
Regards
KyawAM

View 12 Replies View Related

New Transaction Cannot Enlist In The Specified Transaction Coordinator

Nov 28, 2004

I'm getting this error:

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

http://support.microsoft.com/kb/839279 this didn't help

any suggestions?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved