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 RepliesWe use SQL PE 2000 in our lab to store sampled data (lots of data collected at high sampling rate) in real-time. i know this may be an uncommon use of SQL server. For this particular application, performance is much more important than data recoverability. In other words, once-in-a-while data loss is acceptable. Actually we have never used the transaction log for recovery since we started using the SQL PE in this way a few years ago. Certain transaction logging such as bulk deletion is particularly wasteful of the resource. As my understanding from reading the online book and some threads on this forum, the best thing we can do is using bulk-logged recovery model. I am not sure if that helps us at all because it seems that bulk deletion (i.e. DELETE FROM MyTable WHERE ..., may affect hundreds of thousands of records) is not on the list of minimally logged transactions.
I am wondering if anyone could share some good advice.
Thanks in advance!
Hello,
I am wondering if RS has the capability to minimize the parameter portion on the top. I see that parameter can be hidden, but I would need the capability to just minimize and if the user needs to change the parameter, they can still click on a button to un-minimize.
Does that make sense?
-Lawrence
SQL Server 2005
We need to set up a database with minimal log file growth or none at all. This database is used as an intermediate step in the data extraction process, i.e. ( daily inserts and truncations) - data recovery is not an issue, but the size of the log file is. How should I set this database options?
Can I set log file growth to 0 ( none)? Will it affect inserts?
Thank you
If batch processing and normal transactions need to run in parallel, how can I solve or minimize locking issue? Since batch processing will use PAG or TAB most of the time, how can other users to access data at that time?
Thank you for any suggestion.
I'm preparing a checklist for myself before getting ready to migrate from 2005 to 2012. Our largest database is a nice one at over 250GB. I'm thinking my best bet to minimize any downtime would be to Restore the DB (NORECOVERY) on the new server and keep rolling it forward with the transactional logs. Eventually I'll need to bring the old DB offline and do one last backup and apply that one to the new server but that should be a small time frame given the whole process could take several hours.
View 5 Replies View RelatedIn order to minimize the number of connection strings I have to use toaccess different databases on the same Sql Server, I was consideringstoring all stored procedures in just one database. I want to do thisbecause connection pooling in my application - ASP.NET is based onthis connection string. So if I need to access 6 different databaseson one sql server & set 6 different connection strings, I end upcreating 6 different connection pools.Other than it might create more management work for the DBA, are thereany performance implications with implementing this scheme? Do storedprocedures run any slower if they access tables that are stored indifferent databases within the same server?Any comments/suggestions are appreciated.TIA,Minh Tran
View 4 Replies View RelatedHi 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....
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.
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 RelatedHi 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 RelatedHi 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
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..."
View 2 Replies View RelatedIs 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(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.
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.
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.
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
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.
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
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
Pl.Help
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 RelatedHow 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.
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.
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.
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!!
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
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!
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 want to list out the pending transaction for transaction replication by publication.
Help needed.