** Trans Log Won't Truncate **

Oct 16, 2000

SQL7:

I have a 300MB db, and a transaction log near 1.3GB. Upon notification, I backed up the db log with truncate_only - no luck getting it smaller. Later, tried backup with no_log ( assuming the o/s was full - no diff)

I tried shinkfile (logfile,truncateonly) and no luck.

I tried dbcc opentrans to see if any pending trans. The db looks fine with dbcc checkdb. I managed to free up a mere 50MB. I checked the permissions on the db, and added backup db, and backup log in the db permissions for the user logged in (also tried this with sa)

I am unable to free up the space to the os. Can I somehow rid the log file and start off with a fresh log file? I need this space. As a patch I moved the log to a larger filesystem as a temporarily fix.

start/stop SQL- nothing? reboot -nothing? I played with waiting game. This log does not want to release space. The log grew from data loads.

Question1: Suggestions how to truncate this log? The contents are not really impt, but the space is.

Question2: Can I add another logfile, then use EMPTYFILE to transfer the contents to the newly added log file, then REMOVE The original logfile? In theory does this make sense?

Thanks,
Jason

View 5 Replies


ADVERTISEMENT

Does A TransLog Backup Truncate The Trans Log?

Dec 6, 1999

This is a silly question but I am not sure of the answer.

Each day I do a complete DB backup and houly I backup the Translog file with the append mode.

Question Is the Transaction log truncated each time I do a Transaction Log Backup?

View 3 Replies View Related

Truncate Trans Log Of DB On Linked Server

Aug 28, 2002

Does anyone know who I can truncate the transaction log of a database that
is on a linked server?
TRUNCATE LOG [linked server].[dbo].[database] WITH TRUNCATE_ONLY
does not work.
Thanks,
Kell

View 1 Replies View Related

'Truncate' Command Doesn't Truncate Enough

Dec 30, 2004

With a database size of almost 2 GB, I run the 'truncate table eventlog command' which completes successfully, but the database size only decreases by about 10 MB so stays too large - indeed the number of rows in the eventlog table is minimal, but the otehr tables in this database don't show such an amount of tables large enough to cause the size issue either. What could be the reason and how can I reduce it (possibly truncating another table but then which one, how could I determine which is too large and needs truncating?).

View 3 Replies View Related

To Truncate Or Not To Truncate: That Is The Question..

Apr 17, 2008

Hi all...

Please forgive the elementary nature of my question, but could someone please explain the differences between these two database backup types:

1. Log backup
2. Log backup no truncate



From what I understand and have read, the "no truncate" backup method keeps the entire transaction log indefinitely. Using the truncation method, the transaction log is either 1) compressed or 2) cleaned up so that any completed transactions are removed from the log. Which one of these is true?


And, for the big question: is it better to run a backup of the transaction log with truncation or not? Our current backup scheme is similar to the following:


Full backup every 24 hours
transaction log backup every hour with no truncation


Should we insert a truncation backup somewhere in here? What is the danger of removing (or compressing) parts of the transaction log? Will this affect the restore process?


Thanks in advance!

View 6 Replies View Related

TRANS Log

Oct 27, 1999

I wnat to have a log file with all the updates and inserts made to the DB.
I understand I have to do a trans-log,how?
in order to make a query a transaction do I only have to declare trans...commit ??

tanx,

eyal

View 1 Replies View Related

Trans Rep Will Not Replicate

Jun 18, 2001

I have set up a simple trans rep from a server in the office to the web. both servers are NT4sp6aSQL 7.0. Tables only.

The publication and distribution db are on the server at the office, and there is a full time connection through the firewall.

The initialization and first replication works perfectly, but after that, there is a message from the snapshot agent that "no subscriptions needed initialization", the logreader says thare are no replicated transactions and the Distribution agent says there are no replicated transactions. What am I missing?

Thanks in advance

View 1 Replies View Related

How To Move Trans Log

Dec 26, 2000

Does anyone know how to move the transaction log(s) of a LIVE database to a new location. I must move the log of a database to a new mirrored drive without any disruption to users. I cannot take the database offline or use the sp_detach_db stored procedure. Your inputs are much appreciated!

View 1 Replies View Related

Réplication Trans. And Pk ?

Feb 1, 2001

Hello, During an intantanée arrest of replication of data base sql server 7 sp2 (nt4),
I lose keys on the subscriber, how don't have it? Thank you in advance easter
pascal

View 1 Replies View Related

Trans Log Confusion

Nov 5, 2002

:confused:

Here's my situation (SQL2K)

We have a testing database we're using to convert large amounts of data from 1 system to another. We might process 5-6 million records, but don't care about being able to recover point-in-time.

I set recovery mode to simple, do a full backup every night. I keep getting large transaction logs. I manually run Shrink Database when I realize the logs are big

What can I do to prevent the logs from getting big in the first place ?? Can I prevent logging from happening ?

I keep reading various books and BOL, but I guess I don't quite "get it" yet ......

Any plain spoken, detailed suggestions would be very appreciated .... thanks in advance.

View 5 Replies View Related

Db Backs Up Ok, But Not Trans Log

Jun 12, 2002

My database backs up just fine, but the transaction logs
are not. I created a maintenance plan for the database.

Any suggestions ?


Agent Job for transaction log backup

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0A83F1BC-EEAA-
4BDF-9374-E7FF5C1215F8 -
Rpt "d:sqldataMSSQL$PRODUCTIONLOGzeon_live_commerce DB
Maintenance Plan16.txt" -DelTxtRpt 1WEEKS -VrfyBackup -
BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2DAYS -
CrBkSubDir -BkExt "TRN"'


The error log file for the transaction log backups is...

Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'ZCHQ_SQLPRODUCTION' as 'ZCISQLSERVICE'
(trusted)
Starting maintenance plan 'zeon_live_commerce DB
Maintenance Plan1' on 6/12/2002 8:15:03 AM
Backup can not be performed on
database 'zeon_live_commerce'. This sub task is ignored.

Deleting old text reports... 1 file(s) deleted.

End of maintenance plan 'zeon_live_commerce DB Maintenance
Plan1' on 6/12/2002 8:15:03 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)

View 2 Replies View Related

Begin Trans

Nov 15, 2004

If you are set up for AutoCommit why would you or should you set a explicit transaction? I have noticed that in some called stored procudures from a "container" stored procedure. (Hope I got that right) that in the called stored procedure a Begin tran is used. Can anyone help with the why and what fors? It seems to me that you want to let SQL Server handle this becuase of the danger of leaving out a Commit or Rollback? But thats me. I may be very wrong? Thanks.

Tom

View 1 Replies View Related

DBCC To Trans Log

Aug 22, 2005

Hi,
I was just wondering, if I have a DB for log shipping, and change the recovery model to bulk-insert before I do a dbreindex, does the log still grow as big as the full recovery model? as in when the DB in bulk-insert model, will dbreindex still writes to the log? What i'm trying to do is try to make the log files smaller for log ship when i'm doing the db reindexing job.
Thanks.

View 1 Replies View Related

Cycling Thru Trans-SQL

Aug 31, 2005

I’m really scratching my head with this Transact-SQL, say you wanted to cycle through a set of rows, then perform an operation on each row, so in VB/DAO it might look like this:
Dim rsTables As Recordset, rsIndex As Recordset
Set rsTables = dbSource.OpenRecordset("SELECT * FROM INFORMATION_SCHEMA.tables")
Do While rsTables.EOF
Set rsIndex = dbSource.OpenRecordset("Select * From SysIndexes Where Name = '" & rsTables!Table_Name & "'")
DoSomethingToIndex rsIndex!Name
rsTables.MoveNext
Loop

How can you do this cycling with Trans-SQL?

View 10 Replies View Related

Control The Filesize Of Trans Log

Dec 18, 2001

Hi all,
Just wondering if the virtual filesize of the transaction log can be changed to suite us.
run dbcc loginfo(sagent_dev) display the followings:

FileId FileSize StartOffset FSeqNo Status Parity
213041664 8192 2110128
213041664 130498561960128
213041664 26091520197064
2524288039133184201064
2524288065404928204064
2524288070647808203064
25242880102105088210064
25242880107347968000
25242880112590848214264
25242880117833728213064
25242880123076608212064
Note that the virtual filesize can comprise of different configuration. In other database I can specify this logical size to whatever I like.

cheers
dave

View 2 Replies View Related

Can You Trans. Replicate From SQL 7 To SQL 2000

Mar 28, 2001

We would like to replicate from a SQL 7 DB some data onto a SQL 2000 Server running SQL 2000. We plan to take some data off the SQL 7 DB's and create a Data Warehouse on the SQL 2000 Box.

Question: Will the data replicate successfully from the SQL 7 box onto the SQL 2000 box?

-thanks
-tom

View 1 Replies View Related

Disable Trans. Replication - HELP!

Mar 20, 2000

Hi,

I am trying to disable transactional replication, but am having some problems. I used the wizard, however, it has taken 7 hours so far and is still not done. SQL Server (7.0) is showing the connection as runnable but it seems as if nothing is being removed. Am I missing something? Should I have done something else before running the wizard? I can't even kill the SPID.

Can anyone help or offer some advice?

Thanks in advance...

View 1 Replies View Related

Trans Log Backup Fails

Nov 4, 2005

I have a trans log backup that runs every 15 minutes on san, works fine until I do a backup after a large load. I get the below error message. Anyone had this before?

Executed as user: DPSCSDOMsqlexec. The file on device 'n:sqllogsmdentallgdmp' is not a valid Microsoft Tape Format backup set. [SQLSTATE 42000] (Error 3242) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step fail

View 4 Replies View Related

SQL 2000 Trans Logs Too Big

Aug 10, 2001

We're running Lawson software on our SQL 2000 box. We're using Veritas
Backup Exec to back up the databases to tape. I'm also using a Maint
Plan for an extra backup (kinda redundant, but I need the practice at
all this). I just added transaction logs to the maint plan (so I
thought) but I only see a log for 1 of the 3 databases.

Also, my trans log file is 11 gig. I thought backing up the trans logs
would get the trans to delete afterwards (databases are recovery
model=full). Can anyone point a newbie in the right direction.
Fortunately it's not in production yet, and we haven't had any disasters
(yet).

View 1 Replies View Related

Should This SP Have A Begin/end Or Commit Trans

Aug 10, 2001

No I did not write this below, this is from a vendor, I used profiler and I believe their SP is causing a blocking problem on their vendor supplied DB. It thought at the least always have a begin end or a begin trans commit trans. ANy quick opinions greatly appreciated

create procedure write_planned_service_rec
@p1 varchar(20),@p2 varchar(20),@p3 varchar(20),@p4 varchar(20),@p5 varchar(20),
@p6 varchar(20),@p7 varchar(20),@p8 varchar(20),@p9 varchar(20),
@p10 varchar(20),@p11 varchar(20),@p12 varchar(20),@p13 varchar(20),@p14
varchar(20),
@p15 varchar(20),@p16 varchar(20),@p17 varchar(20),@p18 varchar(20),@p19
varchar(20),
@p20 varchar(20)
AS
IF @p20 = 'P'
update patient set date_insurance_updated = getdate() where patient_id =
@p1 and practice_id = @p13

View 1 Replies View Related

Backup Of Database And Trans Log.

Jan 20, 2005

Is there anyway in SQL to backup the Database and Transaction log without truncte the Transaction log? :o. I would like to keep all or as much of the Transaction log info as possable. I know the Trans log will get large but HD space is not a problem. I use the transaction log to report field changes usung Log Explorer.

Thanks

View 10 Replies View Related

Backing Up Trans Log Of System Dbs

Feb 20, 2007

Guys,

Should the transaction log of the system databases be backed up and if so, how often should this be done, generally?

Currently, we are backing up the system databases daily. The transaction log of the system databases, however, is not backed up.

Recently, I was wondering what exactly could happen, which would imply that we need to back up the transaction log of the system databases.

As far as I understand, 'msdb' contains things like jobs, dts. As such, if no jobs/dts are added in the middle of the day, there is no need to back up the trans log of this db - there could even be no reason, in such case, to back this db up at all!

'master' db would change if we add/modify objects/properties and so on ... so unless many changes are made, no need for backing up its trans log.

Having said that, could anyone enlighten me as to whether it is necessary to back up the transaction log of the system dbs and/or what it depends on and/or when it is suggested to do so?


Thanks you

View 2 Replies View Related

Begin Trans With Inserts

Aug 12, 2014

Writing SQL scripts with insert trans. If one of the inserts failed rollback all previous inserts. Here what I come up with but I don't think it will work.

SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
Insert 1
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0

[Code] ....

View 1 Replies View Related

Trans Log Failed While DB In Standby

Mar 12, 2008

May I know any reason why my Transaction Log Backup job keeps failing while the particular database set to "Standby /Read-Only"?

Kindly help. Thank You.

Regards,
Jimmy Liew

View 7 Replies View Related

Can I Shrink Trans Log Even If Doing Log-shipping?

May 3, 2007

I have a 2000 DB which is log-shipping to another server. The log has grown huge and needs to be made smaller. Can I use DBCC SHRINKFILE without damaging the log-shipping? I know that truncating the log destroys log-shipping, but shrinking should logically not cause any harm. Do you know? Is there a better way to reduce the size of the log file without damaging log-shipping??



Thanks!

View 7 Replies View Related

Is There A Way To Trans Rep To Machine Of Different Collation Seq?

Feb 5, 2007

Hi!

We have a vendor's case-sensitive db which we have to Trans Rep our case-insensitive data into. Can't do it directly as it violates the Rep rule against different collations. Is there some trick or work-around we can use to implement this?



Thanks,



Michael

View 1 Replies View Related

How Do I Shrink The Log Of A DB Used In Trans Replication?

May 2, 2006

I am sure this has been asked multiple times before, but I have a DB (SQL 2K) that is involved in transactional replication. The log periodically grows to a seriously big size. How can I shrink it without damaging replication? I have opened SSIS (SQL 2005 is the subscriber) and tried to shrink the file, but to no avail. It reckons I could shrink it to 0MB, but I feel this would throw out replication.

Any help appreciated.

View 3 Replies View Related

Trans Log Backup Failure (Very Urgent)

Aug 28, 2001

Hi Every one,

According to our stratergy we are taking trans log backup of every user database everuy two hours.things seem to work great and with success at the day time and at night once at 12.03 and 2.03 they fail and when i checked the error log i found that there is only one database for which its failing and it gives the following error.

Database ors: Transaction Log Backup...
Destination: [D:BackupTranLogorsors_tlog_200108280203.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'ors_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.

We had a option called select into/bulccopy set which i disabled once this failure has occured but still the problem persists....there are no other options set...i am out of resources i donno what to do..could you suggest me something and how to solve this problem its happening in prod.

Thanks for your help.

Kavitha

View 1 Replies View Related

Trans Log Backup Failure (Urgent)

Aug 29, 2001

Hi Every one,

According to our stratergy we are taking trans log backup of every user database everuy two hours.things seem to work great and with success at the day time and at night once at 12.03 and 2.03 they fail and when i checked the error log i found that there is only one database for which its failing and it gives the following error.

Database ors: Transaction Log Backup...
Destination: [D:BackupTranLogorsors_tlog_200108280203.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'ors_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.

We had a option called select into/bulccopy set which i disabled once this failure has occured but still the problem persists....there are no other options set...i am out of resources i donno what to do..could you suggest me something and how to solve this problem its happening in prod.

As Said by someone i took a full backup...tooooo it does not solve the issue.

Thanks for your help.

Kavitha

View 6 Replies View Related

Trans Replication - Filtering && Deleting

Jul 14, 2003

Here's what I'm trying to do (Maybe there's a better way)

I'm using Transaction Replication and a pull subscription to get all the transactions from the ORDERS table within the last year from Server_A to Server_B. I filter the rows "WHERE (ORDR_DATE > (GETDATE()) - 365)"

That works fine, BUT, the old rows in the subscriber's ORDERS table do not get deleted, so I have all the records for the last year, plus the older records that I don't want any more. Do I need another step some where that does a "Delete WHERE (ORDR_DATE < (GETDATE()) - 365)" But then I have to maintain my date logic in more than 1 place if I ever want to change it.

Any suggestions, thoughts, improvements will be appreciated .... Thanks a lot

View 1 Replies View Related

Trans Replication Agent Error

Aug 4, 2003

What does this error message mean ?

The process could not bulk copy out of table '[dbo].[syncobj_0x3732313945323046]'.

I ran transaction replication successfully last night. Then it was scheduled to run again in the morning, but failed .... any ideas ?

SQL2K SP2

View 2 Replies View Related

Empty Trans Log In Simple Model

Oct 13, 2006

I have a database SQL Server 2005 Express, that's
using the Simple recovery model.
There's quite a big transaction log for this database,
how do I empty it when it's the Simple model?
Besides, the log file hasn't been updated (acc. to
the Windows file date) since yesterday while the MDF
file has. Is this normal?

View 2 Replies View Related

Temporarily Avoiding Writing Into Trans Log

Mar 9, 2007

Guys,

Is there a way to temporarily disable logging into the transaction log.

In our system, we perform purging of our database every night, where the purging consists of 2 steps:

1. For each table, insert the data, to be deleted, into a corresponding "purged" table, to remain there for one day only.

2. For each table, delete the unnecessary data (i.e. same data stored in purged tables in step 1)

During these 2 steps, the transaction log grows, and since we perform the transactional log back up, the back up at that time is huge. We are running a bit low on the hard disk space and I'd like to disable logging into the transaction log when these operations are performed.

I really don't care about being able to recover this data.

I thought that one option is to set the database to simple recovery, then perform the purging of the database, and then change back to full.

However, I think that trans log can grow even if recovery model is simple [although you won't be able to retrieve any changes].

So, is there a way to delete a portion of a table [or insert into it] so that no data is written to a transaction log (I know that we can use TRUNCATE if we need to remove whole table without logging)?


Thanks a lot

View 13 Replies View Related







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