Tran Log Backup Question

Nov 22, 2004

Hi,

I have my tran log backup running every 30 mins. One of the log backups took 36 mins to complete. So at a time I would have two log backups taking place. It seems to me that the 2nd log back up did not happen at all ( I checked in the EM) as the first one wasn't completed by then.

I am kind of lost here as to how to proceed.

Please advise.

Thanks.

View 2 Replies


ADVERTISEMENT

How Can I Trigger Full Backup On Tran Log Backup Error?

Aug 8, 2007

Hello,
I have MS SQL 2005 server with 300+ databases on it. The application is set up that way that it creates a new database as needed (dynamically). Do not ask me why - I hate this design... So, it can create 3-4 databases a day (random time).
I've scheduled full backup of all databases to run once at night, and it runs just fine. Besides that, I have scheduled tran logs backup of all databases to run every hour. This backup fails from time to time with the following error:

Executing the query "BACKUP LOG [survey_p0886464_test] TO DISK = N'D:\backups\log backups\survey_p0886464_test_backup_200708072300.trn' WITH NOFORMAT, NOINIT, NAME = N'survey_p0886464_test_backup_20070807230002', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

So, I think what happens is since my full backup of all databases are scheduled to run only once at night, and tran logs every hour, when new database is created during the day, there is no full backup for it, that is why tran logs backup fails. Becuase after the failure, if I run full backup again, then tran log runs just fine afterwards.

I am new to MS SQL Server, I am mostly working with Sybase IQ. Do you know if I can "trigger" full backup every time when new database created to avoid tran lof failure?

Or is it possible to schedule full backup to run if tran log backup fails?
Any advice will be much appreciated.

View 1 Replies View Related

Full Backup And Tran Log Backup

Jun 14, 2007

Hi:



I have 30 databases on sql server 2005 that I need to do a full backup every morning at 7:00 and tran log backup every 30 minutes until 7:00 PM. If I create a maintenance plan for a backup using the wizard I have the option of starting a full backup at 7 am and then an option of doing tran log backups every hour using a different schedule. I plan on selecting the option to create a different folder for every database. I just need to confirm that in this way the way to restore the data would be

1. to restore a full backup

2. apply all the tran logs depending on the time they want to recover back to.



I just think this is the easiest approach to have 30 databases on the same backup scheme instead of creating a separate backup device for each database and doing a full backup on that device and appending all tran logs to that device which means just 1 bak file versus the above strategy with a number of tran log files. Please advise.



Thanks



View 1 Replies View Related

Tran Log Backup Conflicts With Shrinkdatabase?

Apr 2, 2008

Hi:

I have maintenance plan on DBABC backup log to .trn job to run every 90 minutes (daily).

in order to keep the log file small, I also set up a job (T-SQL) to run at 4:15 am to backup log ABC with truncate_only, then run dbcc shrinkdatabase (DBABC, 10)

it looks "backup log ABC with truncate_only" has conflicts with the every90 minutes backup transaction log.

Question: could I keep the backup transaction log every90 minutes, but still could shrink the log file. The log file is growing very fast.

Or I have to use differential backup instead of backup tran log?

thanks
David

View 4 Replies View Related

Big Tran Logs, "extra" Backup ?

Mar 28, 2002

I noticed our log files are getting way too big. I found that a previous
SQL guy had set up the following scheduled job with the TSQL statement:

BACKUP LOG DEV TO DevDailyTranLog with noinit

This runs every 20 minutes.

There's also a Maint Plan to do a Complete backup every night and a
Transaction Backup every hour (could be set to 20 min)

Why do you suppose the BACKUP LOG job exists ? If the maint plan were
set to backup transaction every 20 min, wouldn't the 2 jobs be
duplicating each other ?

Also, I notice that the Tran Logs specified in the database properties
seem awfully big (4 gig), shouldn't they automatically be truncated when
the daily full backup occurs ? (full recovery model)

Feel free to call me and straighten me out or to get more info.

Win2K, SQL 2K

gdunn@taunton.com
203-426-8171 x 374

View 2 Replies View Related

Find Database For Which Tran Backup Failed As Attached

May 14, 2013

Tran Backup failed. How to find the database for which Tran Backup failed as attached?

View 2 Replies View Related

Tran Log Backups Fail 40 Minutes After The Differential Backup.

Jul 14, 2006

Running SQL Server 2005 Standard Edition.

Database is in Full Recovery mode.

I have full backups scheduled weekly, differentails scheduled daily and tran log ackups scheduled for every 15 minutes.

The tran log backups only work for the first 40 minutes or so after the daily backup. Once the daily has occured, the tran backups will work again, but for only 40 minutes.

I run consistency checks on the db daily and there are no errors.

The error returned from the tran log backup is 'BACKUP LOG cannot be performed because there is no current database backup'

Any thoughts would be appreciated.

View 4 Replies View Related

Remove Old Backup And Tran Logs Thru Maintenance Plan Cleanup Task

Mar 27, 2007

Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2.

this is transact sql running in the back ground.
EXECUTE master.dbo.xp_delete_file 0,N'F:MSSQL.2MSSQLBackupibmdir',N'"bak" & "trn"',N'2007-03-26T22:21:14',1

This DOESNT WORK.

It works if I just try to delete only one of the things ie trn or bak files.

EXECUTE master.dbo.xp_delete_file 0,N'F:MSSQL.2MSSQLBackupibmdir',N'bak',N'2007-03-26T22:21:14',1

But is there a way to delete both bak and trn files using one task ?

View 5 Replies View Related

Logistics Of Begin Tran And Rollback Tran

Dec 19, 2007

I am running an Execute SQL task that does a Begin Tran, then the next task in the sequence is a data task which imports a XML file into two tables. If i doo a Rollback Tran only one of the two tables is rolled back.

Is it possible to have both tables rolled back from one Begin tran command or do i need to split the datatasl into two and treat each import as a seperate issue ?

The connection is set to retainsameconnection

thanks

View 7 Replies View Related

Begin Tran, Commit Tran

Oct 8, 2007



Hi,

I want to rollback my t-sql if it encounters an error. I wrote this code:

begin tran mytrans;
insert into table1 values (1, 'test');
insert into table1 values (1, 'jsaureouwrolsjflseorwurw'); -- it will encounter error here since max value to be inputted is 10
commit tran mytrans;

I forced my insert to have an error by putting a value that exceeds the data size. However, I didn't do any rollback. Anything i missed out?

cherriesh

View 4 Replies View Related

Tran Log

Apr 7, 2003

I have a transaction log that is over f gig in size....what can be done with this..and what are the pros and cons if I delete it...also how can I keep this from getting that big in the future. Thanks!

View 2 Replies View Related

Tran Log

Apr 20, 2001

To All:

On my SQL 6.5 box, I have a corrupt Tran log. I do not use my Tran log but now I am getting an 1105 error, that the log is full. I run Dump tran with no log but it does not work. I cannot perform any other function without getting the 1105 error. Now I tried to reboot and now it is hanging during reboot. It is hanging while checking the partition where the tran log resides. I went in to VGA Mode. Any ideas would be appreciated.

Many thanks,

Kelly

View 1 Replies View Related

Tran Log Growth

Aug 14, 2001

Does this seem right? We have our transaction logs set to "Truncate Log on Checkpoint" and they still grow over 1GB. Is it possible that one transaction (to a checkpoint) generates this much logged information? Will transaction log backups every 5-10 minutes help me out better or is this just a poorly written application?

Thanks!

View 4 Replies View Related

Contents Of The Tran Log

Oct 11, 1999

All,

Can everyone tell me how I can view the contents of a transaction log in SQL Server 7.

Many Thanks
Mathew hayward

View 3 Replies View Related

Tran Log Problems Please Help

Aug 13, 1999

Help. I have a database with high transaction rates. THe log is 300 mbs. No matter what i do I cannot get it below 64%. I have dumped and trucated the log yet it will not budge. Being friday and it being a time card application this is my heaviest transaction day. Please help

View 2 Replies View Related

Tran Log Space Used ????

Aug 22, 2002

Hi There,
How do I find the space used for the tran log of the db. sp_spaceused gives the space used for the complete database. but I need the space used for a tran log alone.
Thanks in advance.
pete

View 1 Replies View Related

Tran Logs And Backups

Oct 19, 2000

What is the advantage of taking frequent tran log backups (say every 30mins) as opposed to once a day? Say, I backup data and tran log once every night and I lost a table at 10:00am next day. Can't I recover the database to the point in time by restoring the previous night's backup and then applying the transaction log from previous night and then applying the transaction log (to the point int time) that you just dumped when the mishap was reported to you?

Thanks

View 2 Replies View Related

High CPU During Tran Log Dumps

Oct 22, 2002

We are experiencng high cpu utilization across all 4 cpu's at the top of the hour when our transaction log dump job runs. Has anyone observed this bahavior before? Is there anything we can do to mitigate this? Thank You.

View 4 Replies View Related

If @@trancount &> 0 Commit Tran

Apr 28, 2003

Hi,

Reviewing the MSSQL process info screen, I am seeing the same process appear a numer of times. It is always the same, being

'IF @@TRANCOUNT > 0 COMMIT TRAN'

Sometimes, there can be up to a hundred of these processes (listed in the process info screen). They generally have a 'sleeping' status, but nonetheless, I would like to see these processes disappear if they are not being used.

I have checked in all of the stored procedures and triggers in the application, and none have this sql statement.

When I run profiler, I get these entries, but the profiler says they belong to either SQL Enterprise Manager or 'Microsoft Windows 2000 Operating System', and not to the application I am running.

Does anyone know where these transactions come from? Can I prevent these from appearing? If no, what is the impact (other than sql server having to maintain a connection).

Thanks,

Jim

View 6 Replies View Related

Growing Tran Log File

May 16, 2006

I have a database of 22 gb in sql 2000, my database option is set to full recovery mode, the problem i'm having is the tran log is growing too fast, this morning it was 24 gb, more than the database size. Can anyone help how I can keep it in a managable size?

Thanks in advance!!

View 2 Replies View Related

Problems With Dump Tran

Mar 23, 1999

Hello,

My Database has 1024 Mb and a log with 384 Mb. The log is 72% full.
None of the options is checked in that DataBase.

I try Dump Tran with no_log, Dump Database and Dump Tran with Truncate_only, click the button of Truncate Transaction Log and more.

And the Log is always the same.

Why I canīt truncate the log? What I must to do?

TIA
[]

View 3 Replies View Related

Tran Log Back Fails

May 14, 2001

All my jobs run fine, except for a Transaction Log Backup job that fails with
the following error: Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'Server1' as 'sa' (non-trusted)
Starting maintenance plan 'MaintPlan-TLogs- AllData' on 5/12/2001 10:00:01 AM
Backup can not be performed on database 'AllData'. This sub task is ignored.
I have not change the sa or agent password.
I cannot figure out why this job started failing, it ran fine for a while.
Any insight is appreciated.
Thanks

View 1 Replies View Related

Need To Release The TRAN Lock??

Dec 19, 2007

The former programmer wrote this stored procedure. It haven't been run for a while, so I was given the assignment to get it working. When I ran the stored procedure, it took almost 9 hours. Then I found that I can't access a few tables, so my guess it there is some issues with table locking. The stored procedure use this...


Code:


BEGIN TRAN

--blah blah

COMMIT TRAN

ERROR_HANDLER:

ROLLBACK TRAN



Obviously there seem to be a logic error in the middle of the script while running the stored procedure. So, how do I cancel the transaction and unlock the table? I'm unable to access the few tables.

Also, does rebooting the computer helped to release the transaction or table locking?

Thanks...

View 3 Replies View Related

Large Delete && Tran Log

May 26, 2004

I have a table that has 110 million records, I will be deleting over 60 million records, but I dont have enough space to hold all the deletes on the trans log file. Is there a way to delete 1 million record then free the trans action then run another delete in one script.

View 1 Replies View Related

T-SQL Script For Tran Log With - In Database Name

Jul 23, 2005

Hi,This is probably a trivial question, as I am simply trying to execute aBACKUP LOG database WITH NO_LOG. Except T-SQL will not accept anythingI type when database has a - in it, for example data-base. I havetried:BACKUP LOG 'data-base' WITH NO_LOGBACKUP LOG "data-base" WITH NO_LOGBACKUP LOG (data-base) WITH NO_LOGBACKUP LOG N'data-base' WITH NO_LOGBACKUP LOG N"data-base" WITH NO_LOGBACKUP LOG N('data-base') WITH NO_LOGBACKUP LOG N("data-base") WITH NO_LOGAll T-SQL will tell me is "syntax error" and Books Online has noinformation. So... ???Thank you!

View 1 Replies View Related

BEGIN TRAN . . . WITH MARK . . .

Mar 29, 2006

Hallo All,

Can somebody explain why the same function works different with MS SQL 2000 and MS SQL 2005?



On both systems 2000 and 2005 I have 2x databases named ACCT and PROD (actually only a test environment).



On both systems I try to execute the following statements:



BEGIN TRAN TRAN_01 WITH MARK 'My TRAN_01'

USE PROD

INSERT INTO [PROD].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])

VALUES('PROT_COMMENT', 1004, 1004)

USE ACCT

INSERT INTO [ACCT].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])

VALUES('PROT_COMMENT', 1004, 1004)

COMMIT TRAN TRAN_01



After executing the statements I start the following query:



SELECT * FROM [msdb].[dbo].[logmarkhistory]



On MS SQL 2000 I get as results:



PROD TRAN_01 My TRAN_01 SUPPORTAdministrator 3944000000107600001 2006-03-29 17:15:13.930

ACCT TRAN_01 My TRAN_01 SUPPORTAdministrator 8000000009200001 2006-03-29 17:15:13.930



Seems to be correct. I think it is the way it should work according to the documentation.



On MS SQL 2005 I only get the following results:



PROD TRAN_01 My TRAN_01 SU 29000000107800001 2006-03-29 17:31:32.283



There are no entries in the table for the ACCT database and the account / user_name is shown incorrectly.



It seems to be an ERROR in the processing of such marked transactions in MS SQL 2005.

View 3 Replies View Related

Deletes Causing Tran Log To Fill

Aug 2, 2000

I seem to be having a problem on all of my SQL servers. WHen I or a developer attmept to do a delete on a table i get a Log file for database is full. I truncate the log try again and get the same error. IT doesnt seem to matter how much is being deleted or how big the table is. THis is very strange and very frustrating.

Thanks
David

View 5 Replies View Related

Restoring Tran Logs To Standby

Jan 22, 2001

I have recently implemented a backup solution that keeps our standby server up-to-date with nightly database backup and restores. Ironed out all the problems with syslogins and orphan users. It's been working very well over the past few weeks.

On the production server we do hourly tran log dumps every hour between 8am and 10pm. I would like to implement some form of "log shipping" to bring the window of vulnerability down to 1 hour. By making some alterations to my current process I been able to incorporate the hourly log dumps in the same process.

However, when I try to restore the log on the standby servre I get the following message,
Server: Msg 4305, Level 16, State 1, Line 1
Specified file 'HODB1SQLBACKUPRace_Prd_T_dump.200101231513' is out of sequence. Current time stamp is Jan 23 2001 2:01PM while dump was from Jan 23 2001 3:01PM.

After a bit of investigation I found the dumptrdate field in the sysdatabases table and it was set to Jan 23 2001 14:01. After updating this field to Jan 23 2001 14:01, I ran the load command again but received the same error message.

Is there something else that needs updating on the standby server? Is what I'm trying to do possible in 6.5? Any help or ideas would be greatly appreciated.

Thanks
Phill

View 2 Replies View Related

Missing Records After COMMIT TRAN

Mar 1, 2006

Hi

Can you think about any reason for why when using a transaction after the COMMIT TRAN the inserted new record is not in the table and there is a gap in the identity????

I'm using SQL 2000 SP3, there are no triggers are on the table and it happanes only under heavy load.

Thanks,
Inon.

View 9 Replies View Related

Message Logging Of Backups (full And Tran.)

Mar 16, 2001

Hi All,

Is there a way to suppress or re-direct the messages that result from a tran. log dump? We're running a tran. log dump every 15 minutes in prod. and the errorlog is really cumbersome to look at.

(In another RDBMS I worked with all backup messages went to a separate error/message log.)

TIA!

View 2 Replies View Related

How To Make A Db Readonly To Accept Tran Logs?

Jun 21, 2004

Hi experts,
Pls let me know , how to make normal db to readonly + accept tran logs

Rgds
Srinivas Varanasi.

View 2 Replies View Related

Super Green Newbie Dba - Tran Log Problem... Help, Please?

Oct 9, 2004

Well, I can't backup up my tran log - too big. I knew enough to know you have to back it up, didn't know enough to know that tran log backup is not included in "Full backup". Contractor installation set Growth to "un-restricted". Me, too swamped to delve deeply in time to learn this is a recipe for disaster, and then it was too late - System down because tran log filled up the disk...

(Ironically, this happened the morning of the first day of some training they sent me to - Microsoft's Programming a Microsoft SQL 2000 Database...)

So, some details... The drives on the system :
___ c: size 12G, free 7G
___ d: size 44.9G, free 10M
___ e: size 44.9G, free 13G

Tran log info :
___ filename .... d:Microsoft SQL ServerMSSQLdatahorizon_Log.LDF
___ name ........ horizon_Log
___ size ........ 5606392
___ maxsize ..... -1
___ growth ...... 10
___ status ...... 1081410

___ filename ...E:Horizon_Log2Horizon_Log2_Temp
___ name ........ horizon_1_Log
___ size ........ 167168
___ maxsize ..... 640000
___ growth ...... 640
___ status ...... 32834

(there are 2 because the other totally green newbie dba added 1 the day the system went down...)


I *thought* the addition of the 2nd tran log file meant the 1st file was no longer part of the picture, and that my available free disk space was sufficient to backup the 2nd tran log file.

Clearly I'm missing something - when I try to back up, I get this in SQL server error log :
___ Operating system error 112(There is not enough space on the disk.)


:confused:___ Does that mean it's trying to backup both the 1st & the 2nd tran logs?
:confused:___ Or is it only backing up the 2nd log, but trying to use D: (with only 10M)?
:confused:___ Or, is it using C: or E:, but tran log backups need some multiple of the size of the actual log, and there's not enough space even though there are multiple G?


I've been reading & googling. So far tried to truncate both files.
( dbcc shrinkfile('horizon_1_log',truncateonly) )
No luck, got these :
___ Cannot shrink log file 2 (horizon_Log) because all logical log files are in use.
___ Cannot shrink log file 3 (horizon_1_Log) because all logical log files are in use.

:confused:
:confused:___ How do I get them to be not "in use"?
:confused:___ Must the system be down for that? (certainly seems to...)


Also, checked out the EMPTYFILE option. Books Online says :
"Migrates all data from the specified file to other files in the same filegroup"

:confused: ___ Migrates to which "other files", exactly?
:confused: ___ Is this the right way to go?
:confused: ___ If so, how would I proceed after achieving a successul EMPTYFILE operation?


Also...
:confused: ___ In the database's Properties dialog, Transaction Log tab, there's a Delete button. Could that button be used to get me out of this difficulty?


Last, but not least... If I'm going about this altogether wrongly, can I get a pointer or two?

TIA for any and all feedback...

View 10 Replies View Related

ROLLBACK TRAN Destroying My SQL Server 2000

Mar 9, 2004

Good morning all!

I've created a a new column on a table:

CD_Line int identity


This ADD COLUMN command gave me an error on this morning, we're talking about 40 million rows on this table.
The ROLLBACK TRAN is destroying my system that can't login on the SQL Server and no application can by Database at this moment.

Do anybody know how to STOP this situation and recovery the system?

Tks!
Rafael

View 2 Replies View Related







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