Multiple Transaction Logs
Nov 1, 2004
I have just come from an Oracle background and am trying to equate SQL server methodologies to Oracle. I have a couple of questions based on the same principle which is the usage of multiple transaction logs. This is what I have been able to find out from the docs and other posts:
If you have multiple transaction logs they will all be used in a sequential manner before being wrapped around. That is, they will all be filled before they are reused.
My first question is will they be used sequentially or concurrently. SQL Server seems to stripe everything so I am inclined to believe the latter. This question has been asked before but from other posts/docs there seems to be a difference of opinion. I am of course equating transaction logs to redo/archived logs so I want to know categorically (links would be greatly appreciated) as it will have a huge impact on any disaster and recovery plans that I implement.
The second question is a spin off and may or may not be relevant dependant on the answer of the first. If the transaction logs are written to concurrently, that is, entries are striped, why bother with multiple logs? I don't see any benefits. In fact, in a recovery scenario recovering transactions from mutliple striped logs would appear to adversely affect MTTR.
Feel free to point out the errors of my ways as I am all ears and eager to learn.
Thanks in advance.
View 10 Replies
ADVERTISEMENT
Jan 7, 2005
I am currently running MSSQL on DB1 server with 12 DBs
I am running a full backup nightly at 8:00pm and coping it over to DB2.
I am sending transaction logs every 15 minutes to DB2.
Is there a script or procedure for rolling in all the logs once the restore is finished. I realize the basic way to do this is here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_7veh.asp
But writing a script with say 22 files each for each db is fairly time consuming.
I have contemplated writing a script that takes the content of the logs directory, parses it based on db name and builds the sql statements but did not want to go through all of it if someone else has already done the leg work.
Any help on this would be appreciated.
Chris
View 2 Replies
View Related
Apr 24, 2002
What is the best way to clear the transaction logs. My backup job each night is ending because it says it is running out of disk space and I need to clear up the transaction logs. Any help is appreciated. I see many different options (trucate option, auto shrink, etc.), just need some assistance tosome more specific best approaches. Thanks
View 3 Replies
View Related
Aug 13, 2001
Im having issues truncating my transaction logs. I have logs in excess of 40 gigs. All the info in the BOL is very vague. Any assistance would be apreciated.
Regards
SeTi Ni
View 5 Replies
View Related
Jun 22, 2000
When we backup the transaction log,will it truncate the log or it will grow from there?Truncate log on check point is desabled.
Thanks!
View 2 Replies
View Related
Aug 30, 2000
to take the transaction log back up regularly I should have truncate log on check point false. If I do so then how will I truncate the log.
regards,
Renu
View 3 Replies
View Related
Aug 20, 1998
I am trying to import records via bcp (about 1,500,000 records) and I keep running out of disk space. Is there any way to limit or do away with the transaction log (and still be able to import)?
View 2 Replies
View Related
Dec 14, 1999
how do i decrease the transaction log example 500mb to 100mb, can you help me with the syntax pls!!
View 1 Replies
View Related
Sep 2, 2004
I need to move transaction logs to a different drive and am looking for a way to do it without detaching the db.
Can I create a second transaction log file for the DB and then delete the original log file?
What is the best way to do this for db's that are being replicated?
Thanks,
Ken Nicholson
View 3 Replies
View Related
Jun 13, 2002
I need to seperate my SMS data files and transaction logs onto two seperate drives. Can anyone assist me with this?
thanks...
View 1 Replies
View Related
Aug 2, 2004
HELP!!!!
When I look at the Database maintience plan history entry for backup I have a message that reads: "Backup can not be performed on this database. This sub task is ignored".
Have anyone come across this error before?? As part of the Maintenance plan some transaction log are being backup and some aren't instead they receive the message above.
Thanks
Lystra
View 2 Replies
View Related
Nov 19, 2004
Has anyone ever had the problem of the IT department not backing up thier transaction logs simply because they didnt find it necessary??
View 3 Replies
View Related
Jan 8, 2004
i have several sql servers doing maintenance plans and backing up the transaction logs to tape. unfortunately it seems that the server keeps adding the transaction logs to the same tape, without overwriting them. Nowadays a transaction log backup to disk takes 2 minutes but when done to tape it is taking up 1hr54minutes. What can i do so that the tape is automatically initialised without having to do it manually... Thanks
View 1 Replies
View Related
Apr 9, 2008
i was wondering if smeone could give me a breif over view how far back do these go
View 12 Replies
View Related
Jun 11, 2008
HI All,
Is this is a good idea or not?
If i attached my SQL Server to SAN only for DATA and LOG (Ldf) drives
through the FC. then we put *.bak and *.trn on to local disk RAID 1 drive?
Any performance isues?
View 3 Replies
View Related
Dec 7, 2007
Is there a way to view the transactions from a .TRN transaction log file? If so can I overwrite some of the transactions on the file and then restore from it? I am just curious.
View 2 Replies
View Related
Mar 11, 2007
Hi, I know just about nothing about SQL Server. I am getting this error:The log file for database 'my_database' is full. Back up the transaction logfor the database to free up some log space.I can't access the transaction logs to back them up. I am told that my ISP'stech support should have it set up to shrink those logs automatically everyso often. Is that true?Why are they needed? Up till Monday, the logs are just of our getting SQLServer set up, so couldn't the logs just be deleted? Most of my site can'trun with this problem.I'd appreciate any enlightenment anyone can give me!Thanks, Jill
View 1 Replies
View Related
Jul 20, 2005
This seems like it'd be a really stupid question, but for the life of me Ihaven't been able to find an answer that works....I have a database that's approximately 400MB when shrunk... and thetransaction logs are at about 4.8GB when shrunk... I can't seem to get thetransaction log any smaller, no what I try. How can I get the log downbelow 1GB and keep it there? I've only got an 18GB hard drive and I need torun 2 copies of this database.-steve
View 4 Replies
View Related
Jan 8, 2008
Hello,
I am trying to figure out the time a certain store procedure was executed. I know the SP's name and approximetly the time it was executed. Is this possible to do?
Honestly, i am not sure if SQL server 2005 is smart enough to keep track of commited transaction on the server.
Any suggestins?
Thanks
View 2 Replies
View Related
Jun 5, 2002
I have seen quite a few people post this type of problem, but I am finding few solutions. Your advice and/or experiences are greatly appreciated.
Here's my scenario:
Environment: Windows NT 4.0 SP 6, SQL 7.0 (set up for Transactional Replication)
Problem: We have several remote dB machines configured for full recovery. On these machines are several dB's that are capturing aprox. 280 data points per second per unit. (Each dB represents one unit, and we have 21 units) No problem here. The problem is the transaction logs, that obviously grow profusely, will not SHRINK after, backups, dbcc commands and TSQL has been issued in failed attempts to shrink the logs. (in other words we've tried everything)
My questions are:
1. Because we are replicating, is it absolutely necessary to configure dB's for FULL recovery? How do I check in 7.0 if the dB is certainly in FULL vs. SIMPLE recovery mode?
2. I work with SQL 2000 and shrinking files is no problem. How can we shrink these log files in SQL 7.0?
It is rather urgent I find a solution as we are running out of hard drive space on our remote machines. Please help :-(
Christine
View 1 Replies
View Related
Jun 7, 2001
I am having trouble Truncating a Transaction Log. I`ve tried everything in Book Online.
I`ve backed up the database, I`ve tried DBCC SHRINKFILE, DBCC SHRINKDATABASE, BACKUP LOG TRUNCATE_ONLY ...etc, but it will not shrink. Any suggestions ? Thanks.
View 1 Replies
View Related
May 4, 2000
Currently, my databases and their corresponding transaction logs are all on the same disk array. I finally was able to acquire a seperate disk, specifically to seperate the logs, in case of failure, etc.
Now, I need to figure out how to go about moving the tansaction logs off the current disk array and on to the new disk.
In Enterprise Manager, I brought up "Properties" for the first database, and went to the "Transaction Log" tab. Clicked on the "browse" or "..." button in the location field and got the following message:
"A transaction log file's physical name cannot be changed once the transaction log file has been created". :(
I have been looking through Books On Line, but have been unable to find anything helpful yet.
Can anyone help me figure out how to go about moving a transaction log's location? There has to be some way.
Even if it involves shutting down the server, altering system tables, etc. I need to get these moved.
Your help is appreciated. Thanks.
View 2 Replies
View Related
Jan 19, 2001
Hi all,
I have set up a maintenance plan to backup my databases but when I view the maintenance plan history the transaction log backup steps have a success tick but a message saying :
"Backup can not be performed on this database. This sub task is ignored"
I have looked in my backup directory and only see *.BAK files and no *.TRN.
The transaction logs are supposed to back up at 1AM and the databases at 2AM
All my databases being backed up have the truncate log on checkpoint option set. Is this best practise according to my backup schedule ?
Help !!!!
Regards,
Tim
View 4 Replies
View Related
Jun 15, 2000
Is it possible to move .ldf's to another drive. For example I would like to move a database transaction log file from C:mssql7data to D:mssql7logs.
How can this be done?
TIA!
JJ
View 1 Replies
View Related
Aug 28, 2000
Hi all,
I am restoring database from Transaction Logs. I followed all the steps
mentioned in the book. Just before the last step there are three options
1. Leave Database Operational. No Additional Logs can be Restored.
2. Leave Database Nonoperational But Able to Restore Additional
Transaction Logs.
3. Leave Database Read_Only And Able To Restore Additional Transaction Logs.
Option 2 and 3 aresupposed to set on the NORECOVERY flag.
I tried both options 2, and 3 , But still got messages that I did not
specify WITH NORECOVERY or WITH STANDBY.
Can anyone please tell me what I am doing wrong?
Thanks in Advance.
Attaullah
View 2 Replies
View Related
Jul 11, 2000
I am currently having a problem with a transaction log not emptying even when backed up and truncated. I have done a full database backup and then a transaction log backup expecting this to flush the log however the log is not emptied and it is growing larger and larger. Even when I truncate the log it still doesn't free any space up. Can anybody out there spot the fundemental error in my working??? most grateful. Andy (SQL allegedly)
View 1 Replies
View Related
May 24, 2003
I am new to sql2k
previously i used to work with sql7
how do i clear the transaction logs in sql2k
pavan
View 2 Replies
View Related
Aug 20, 1999
I have a transaction log with 22mb of used space and 2,632.50MB of free space. I tried using the following statement to shrink the log and it did not do anything:
USE DB
GO
DBCC SHRINKFILE (DB_log, 50)
GO
CHECKPOINT
Can anybody help me understand what is going on here?
View 1 Replies
View Related
Aug 11, 1999
I have a MS SQL Server 6.5/SP5A database running on NT 4.0/SP4 Enterprise Edition with MS Cluster Server. On my Production box, I dump the transaction logs every 30 minutes. I need to recreate a point in time on a separate TEST box with the same hardware/software. I created a "DBA database" from a previous night of Production - no problem. However, when I start applying the transaction logs, it complains about being out of sequence. The timestamps that it is reporting are accurate for the transaction logs, but it is basically using the database load time to compare it with. (Granted I am moving from one box to another, but I didn't think this would be a problem). I created a "DBA database" on Production, went through the same steps, and it takes the database load and the transaction logs. I am assuming that the sequence numbers on the transaction logs are identified internally on the file, not per server? Ideas as to why it would complain on an out of sequence condition (Error 4305) when I move to a different box?
View 2 Replies
View Related
Aug 31, 2001
when i try to delete the records it gives the errror
suggest me how to deal with this problem
=====
Msg 1105, Level 17, State 2
Can't allocate space for object 'Syslogs' in database
'armaster' because the 'logsegment' segment is full. If you ran out of
space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE
or sp_extendsegment to increase the size of the segment.
View 1 Replies
View Related
Nov 5, 2004
All,
I am new-ish to SQL servers, and I am having a real issue with my tansaction logs. I am running a SQL 7.0 server, on a NT 4 platform, with SQL SP4 and NT SP 6a.
I recieve error 4213, which according to the Microsoft website means that a non-logged operation has occured. So, first question, what is a non-logged operation?
I understand that essentially the error is saying that by performing the transaction log it will not be able to restore the DB to a point in time (i.e. the point of a TRN backup), but I am at a loss as to what could have caused this. Any Ideas?
I have scripted the job (which can be seen at the end of this thread), and I don't think that I have done anything wrong, but if you can check it would be most helpful. I have also ensured that the 'Truncate on checkpoint' is not selected.
Please can someone start pointing me in a direction that can help.
Thanks in advance.
Stewart
Script:
set nocount on
declare @dbname varchar(40)
declare @dumplogname char(80)
declare @dumppath varchar (30)
select @dumppath = 'D:MSSQL7BACKUP'
declare dblist_cursor cursor
for select name
from master..sysdatabases
where name not in ('model', 'Northwind', 'pubs', 'tempdb', 'msdb', 'master', 'distribution')
open dblist_cursor
fetch next from dblist_cursor into @dbname
while @@fetch_status = 0
begin
select @dumplogname = @dumppath + @dbname + '_tlog_'
+ cast(datepart(year, getdate()) as varchar(10))
+ replace(str(cast(datepart(month, getdate()) as varchar(10)), 2, 0), ' ', '0')
+ replace(str(cast(datepart(day, getdate()) as varchar(10)), 2, 0), ' ', '0')
+ replace(str(cast(datepart(hour, getdate()) as varchar(10)), 2, 0), ' ', '0')
+ replace(str(cast(datepart(minute, getdate()) as varchar(10)), 2, 0), ' ', '0')
+ '.TRN'
BACKUP LOG @dbname
TO DISK = @dumplogname
WITH INIT, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @dumplogname
WITH FILE = 1, NOUNLOAD
fetch next from dblist_cursor into @dbname
end
deallocate dblist_cursor
View 1 Replies
View Related
Dec 14, 2004
Hi All,
Just after some feedback on a scenario where we have full logging setup on one of the databases, and the transaction logs are backed up every 60min. At 0000-0100 the log jumps from being a few thousand k up to over 1.7gb.
I did some profiling for this time, and it appears that this jump is related to the reindexing of the indexes on the database.
Is this normal for the log file to jump in size so much? Or is this an indication of some other issue (potentially with the indexes)?
Is there any way that the reindexing can be excluded from the log files or is this a necessity?
Thanks in advance for your help.
Cheers
Troy
View 4 Replies
View Related
Oct 12, 2006
I am trying to backup my Transaction Logs using T-SQL with the aid of a job. The problem I am running into is that I need it to backup every three hours and retain one days worth of transactional logs. This is the query I got so far.
BACKUP LOG CA001 TO DISK = 'S:AZTARGPCA001ca001_TRN_bkuplog.trn' with retaindays = 1
Any suggestions welcomed.
View 11 Replies
View Related
