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
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 ?
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:
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.
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?
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
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
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
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..."
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
(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):
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.
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?
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
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.
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.
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.
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
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
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.
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?!
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.
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.
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???
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.
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.
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.
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]
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.
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
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
