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
ADVERTISEMENT
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
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
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
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
Nov 6, 2006
We're planning to implement log shipping on our databases, and I have been toiling with it all weekend trying to get it to work on some test databases. The result is the same whether I do it via the wizard or manually via T-SQL.
I am using 3 servers, all SQL Server 2005 Standard SP1. All 3 SQL Servers are configured identically.
When I setup log shipping, it initializes with no problems. When it processes the first tran log file, it restores it with no problem. Every successive log file thereafter is not restored. No errors are generated. The only outright indication of a problem is that the monitor server shows that there has not been a recent restore.
The backup and copy both suceed. The restore claims to suceed. If I review the job history for each step, it says that it skipped the log file and then reports that it did not fina any log files to restore.
Message
2006-11-06 05:00:01.92 Skipped log backup file. Secondary DB: 'MyDemo', File: 'D:MSSQLBackupMyDemoMyDemo_20061106115619.trn'
Message
2006-11-06 05:00:01.95 Could not find a log backup file that could be applied to secondary database 'MyDemo'.
2006-11-06 05:00:01.96 The restore operation was successful. Secondary Database: 'MyDemo', Number of log backup files restored: 0
Any ideas or suggestions?
View 3 Replies
View Related
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
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
Jan 10, 2008
Have a small shop and a maintenance plan that does a full backup on all user databases every night. I would have thought that a full backup (transaction log, too), would reduce the size of the transaction logs? Isn't that the point of a full backup? Mine seem to be growing larger. I do the shrink, rebuild indexes, backup the databse, backup the transaction log, clear history and folders older than 7 days. For example, the following db decreased only once during the period. But, note how the transaction log size continues to increases most of the time.
db, trn
253,196 - 394,245
253,193 - 365,695
253,191 - 360,572
254,219 - 381,699
254,223 - 406,346
254,223 - 411,018
Can someone explain this to me? Am I doing something wrong or just not understanding the process correctly?
Thanks,
Janet
View 4 Replies
View Related
Nov 28, 2001
I have a Mantenance Plan running on my Production Server.
Each job has one step.
-Optimization (each week)
-Integrity Checks (each week)
-T-Log Backups (each hour)
-Full Back up (once a day)
The Job is saying the last three are failing but niether the SQL Server Logs or MS Application Log are reporting a success or faluire. "write to windows application log on faluire" is set for all of them. The backups are occuring, as the files are in thier destination.
Anyone experience this before? If so any advise?
Thanks,
Matt
View 2 Replies
View Related
Feb 28, 2008
Regarding backups.
The first available time to do a full backup is at 11:00pm which also applies to diff. backups.
How often does the trans. logs need a back up?
View 5 Replies
View Related
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
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
Aug 6, 2015
OS: Windows 2012 Enterprise
SQL Server: 2012 Enterprise
I was wondering if there is any way all SQL Server error log entries could be automatically written to Windows Event Log.Â
View 3 Replies
View Related
Feb 12, 2007
Hi,
In my case I have to log the errors raised by any task in a package to either windows event log, text file or SQL server. Also I need to send an email notifications to a group of people telling them about the error.
Now can I use SSIS package logging for logging the errors into the required destinations. I mean right clicking on the package and selecting Logging, then adding the required log providers and enabling the events for logging into those. I think I have to upfront select the log providers to log the error, I will not have the liberty to log the error to the destination, the name of which is passed as a variable to the package. This is okay with me though.
Now what will a custom log provider help me to do in this case. Also can I somehow configure my package to call the send mail task everytime an error is raised.
Also, one more option can be developing a package that only does the error handling. It will take in the paramters or the error codes and descriptions, the destination to write to and a flag to send mail or not for that particular type of error.
What do you think? Kindly advise.
Thanks in advance for your help and time.
Regards,
$wapnil
View 3 Replies
View Related
Nov 15, 2015
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies
View Related
Aug 28, 2001
When looking at SQL Server error logs, I noticed that the current error
log had grown to 1MB whereas most of the files are only a few KB.
QUESTION 1: Can I set the max size for an error log file? If so, how?
QUESTION 2: If not, is there a workaround?
Thanks
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
