Hi everyone,
How/when should transaction logs be truncated without breaking Log Shipping?. I'm trying to deal with the scenario of my tlogs increasing their size as I configure log shipping to 5 min. -frequency backups.
Many thanks
I have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
Thanks!
kdubb
I have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
Thanks!
kdubb
I have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
Thanks!
kdubb
Whats new for SQL Server 2005 SP2 states:-
"The sqllogship application is now supported."
Can anybody confirm that log shipping is not supported in SQL Server 2005 SP1?
Thanks
Question: I have Log Shipping setup and i am wanting to verify it will do what we need. That is when a table is changed in our production enviroment that change is replicated into our other server. IE I rename a field in a table or create a new table... You know change the structure... How do I replicate that stuff to my other servers?
View 6 Replies View RelatedWe have an environment where 3 SQL 2000 servers exist; one production servers and two standby servers that receive logs via log shipping. Everything works fine and it is stable. My client wants to get a new 64 bit server and run SQL 2005 64 bit version for the production box. Can we still do log shipping with the two remaining SQL 2000 boxes or do they all have to be on the same version and bit version?
Thanks
Bill
I am having trouble finding any documentation on how linked servers are set-up under SQL 2005 for log-shipping and replication. Under SQL 2000, the only linked servers "visible" were ones that were user-defined. Under SQL 2005, it seems when you set-up log-shipping or replication, you get these new linked servers which are automatically defined. I would like to understand them and in particular, how security is maintained. We recently had a production issue where after some problem with a server which resulted in having to restart SQL Server, and shrink msdb, log-shipping started to fail authentication when trying to run alert jobs. Only after rebooting the server, and also the log-ship target server, and waiting 9 hours, did the authentication correct itself. I would like to know how it resolved itself and what took so long?
View 3 Replies View RelatedHello,
We have log-shipping set up between a source and 3 destination SS 2000 databases. Two of the destination servers actually perform their log restores across the network from the other secondary server. This allows us to only copy the files once from a remote location. All three servers stay caught up within 15 minutes of each other.
Recently, I added a fourth server to this that has SS 2005 SP2 (X64). I wrote a stored procedure that restores log backups from the same single location as the maintenance plan jobs. The problem that I'm experiencing is that this fourth server is not keeping up with the other three. It seems to take longer to restore the same log backups. The destination servers are all on the same domain. This fourth server was previously part of the same maintenance plan configuration as the others prior to rebuilding it for SS 2005 SP2 (X64). During that time, it stayed caught up with the other servers. There is another database on the new server that I am log-shipping to in the same manner and it stays caught up, though, for the most part, the log backups are smaller. There is a file on the fourth server with a ckp extension for the database in question that doesn't seem to exist for the other databases on this server and the other servers.
Any information on this behavior would be appreciated.
I have set up log shipping between two servers with a third serving as a monitoring server. Recently the monitoring for the eight databases that I am log shipping began to indicate that everything was failing. Upon further inventigation of the log_shipping_monitor_history_detail tables on the prinary and secondary everthing appears to working properly but I have been unable to resyncronize the monitoring. I have attempted to use the stored procedure sp_refresh_log_shipping_monitor to no avail, it does not appear to do anything with respect to the data in the dbo.log_shipping_primary_databases and log_shipping_monitor_primary tables on the primary server or the log_shipping_monitor_secondary and dbo.log_shipping_secondary_databases tables on the secondary. I have also manually updated the records in these tables in an attempt to syncronize but after the next sucessful backup, copy and restore this monitoring data is not updating. Does anyone have any ideas as to what I am doing wrong or how I might rectify this situation.
View 3 Replies View RelatedHi
Im planning to setup sqlserver 2005 log shipping between two servers.The size of the database is around 50GB and we are taking transaction log backup and shipping at 30mins interval.
Suppose if the backup job couldnt take the transaction log backup with in the specified interval ie 30mins,then how does the copy and restore job works. Will it give an error saying tht the transaction log could nt be copied and restored ???
How to deal with this kind of situations where the backup is taking more time than the threshold time limit and ur copy,restore fails. Is there a way to handle this kind of situation in logshipping Sqlserver 2005.
and how does it affect the Secondary database. ??
Please help me out !!
Thnkx in advance
Regards
Arvind L
I am getting the following error " The specified servername does not exist" when I run the Log Shipping procedure. There is only a Primary server running Win Server 2003 withe Service pack 2 and SQL Server 2005 with service pack 2 and one secondary server runnung Win Server 2003 R2 with service pack 2 and SQL Server 2005 with service pack 2. All permission setting look ok . Can anyone help
View 4 Replies View RelatedHi
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
Is there any possibility of loosing data ?
Thanks
I have installed SQL Server 2005 and setup a cluster and log shipping. I decided to perform a planned failover and realized that there are no sp_change_primary_role and sp_change_secondary_role stored procedures. I checked on both the SQL Server 2005 installations and could not find them.
My question is :
"Is it safe to get these stored procedures from a SQL Server 2000 database and run them on the SQL Server 2005 database ??"
"Has anyone come across such missing stored procedures in SQL Server 2005 and know the reason why it is so (so that I can avoid it in the future if it due to a faulty installation) ?"
Cheers
NEVILLE GEORGE
Hello,
I would like to know if anyone has a recommended method for having backups to tape while running log shipping. For example, is it possible to copy the transaction log backups used for log shipping to tape and apply them to a Full Database Restore from a 3rd Party Backup tool such as Veritas?
My goal is to be able to do a point-in-time restore from tape and still be able to use SQL Server 2005 Log Shipping.
Thanks,
Erik
Afternoon,
I have a few Log Shipped DBs that are working great.
Currently they are set to fire off every 15 minutes 24/7.
My question is this ... I need to get FULL backups of the source DBs in order to restore them on certain Dev boxes.
If I were to execute the full backup on one of these Log Shipped DBs ... how would it affect the log shipping process?
Is there a special method to accomplish this?
As a side note, what would be some concerns/issues if in being able to create the FULL backups and not interupt log shipping, I were to create the backup using a 3rd party tool like Quest LiteSpeed?
I sure wish we were on Enterprise, then I could create a mirror and then snapshot off it to create my backups BUT ... that is not the case as we stand today.
Thanks
Hi,
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I appreciate any suggestion.
Thanks in advance
I have a large database and I can only do a tape backup, and I have 10 GIG log file on sql2000. Is there a way to truncate it.
Thanks
Hi.I have a multiplier that multiplies 2 floating point numbers with 7bits exponent and 10 bits mantissa.so its output has 7 bits exponentand 20 bits mantissa.now its output must return to its input in order to compute anothermultiplication.on the other hand its output must be truncated to 10bits. how can I do this? please help me.thanks.
View 1 Replies View RelatedDoes anyone have experience with truncating an expression like the Excel TRUNC?
For example in Excel, you might have something like =TRUNC(IF($AE11=0,1,X11/$AE11),5) which drops off a certain amount of the results after the decimal point. 87.5659321 becomes 87.565 instead of the result of a rounding.
What is the best way to delete ALL data in a table without the transaction log filling up? I do not need to log the deletions.Truncate Table ReportSearchRecordSets with NO_LOG?Delete * from table with NO_LOG?Thanks SQL Server 2005 newbie
View 3 Replies View RelatedCan someone please tell me where in SQL2000 I can truncate the log file and
set the database to truncate the log at checkpoint?
Thanks,
Dianne
Hi all,
This is a database on which there are continuous inserts and updates. Data is loaded into this database in bulk at regular intervals. I do not back up the transaction log. For these reasons, I have set the options, 'select into/bulk copy' and 'truncate log on checkpoint'. When the 'truncate log on checkpoint' option was not set, the log file would grow very huge and fill up the entire disk space(file growth is unlimited). But, even after the 'truncate log on checkpoint' option has been set, the log file does grow at times to fill up the entire disk. I assumed that since the 'truncate log on checkpoint' option was set, the inactive portion of the log would be truncated every 1 minute(ie, during every checkpoint). Could someone please explain the reason for this behaviour?
Thanks in advance,
Praveena
hello,
Daily, after a database backup done in the evening, around 3am we have a lot of flat files to integrate in tables and then process.
The question is : We want to free the space used by the transation log.
Then, we use "backup log with no_log" and/or "backup log with truncate_only"
We look at the size of the transaction log with "dbcc sqlperf( logspace )" but the 'Log space used %' stays the same.
Could you give us some informations or tips on this.
Thank you from Paris
Patrick
How to truncate all the tables in the databases at once,if there are 200 tables?Any help is appreciated!
Thanks.
I am trying to truncate one field within a table and replace it with data from another database. Creating the data and inserting it is no problem, does anyone have any way of completing the truncate could I use an update or a delete as an alternative method?
View 2 Replies View RelatedHi everyone,
Although I truncate the log file and I have no pending transactions, its size does not shrink at all (it stays a 0.5 GB).
Does anyone know why or what can I do to solve this issue.
Thanks,
Vasilis
Hi,
I am having a problem with growing transaction log size, it has grown to 10 gb and I need to truncate it. How can I do it without interfering the users since it's our production database with 24/7 operational service.
Thanks in advance!!!
I have a Database of size 200 MB and my transactio log is 13GB(very high).So can I truncate the log file by taking a fresh full backup?
Thanks.
Recently I start working with SQL 6.5 server on NT4 (sp6)
Before me someone make abnormal reinstallation of this SQL server.
after that server need to be restarted every day
On the database edit window I have seen that Avaluable Log Space is 0
and in the course of working with device have seen that device definition
is erroneous. the path to file is different from the name of file,
i.e. in device defined (D:aaa03_log) and actually present file
(D:aaalog_03)
and as result every operation on device get the error.
I have change the name of the file to match the sql device definitions but
first this don't helped.
I have added new device and expanded the database log on it, but Avaluable Log Space remains 0.
After a number of restarts I noticed that old erroneous device is now
become visible and I can work with it, for instance expand it. But Avaluable Log Space remains 0 and in the sql monitor I also see that log occupied 100% of avaluable space.
May be transaction log located with data and I just can't see that ?
There is option on database edit window: Truncate transaction Log
and I want run it. The organisation don't interested in log. main thing is that the data will be safe.
Is it the right thing to do?
Tanks to everyone who can help
Hi!
I have field1 decimal(11,0) containing number 1234567 and
I must get the last six digits to int-field, eg. I want field2 int containing 234567 as result. How should I do that with functions? And it should work regardless of the length of the field1, eg field1 = 123456789 -> field2 = 456789
Makkaramestari
Hi,
I'm using sp_OAMethod to write to a text file, like this:
DECLARE @i INT, @File VARCHAR(1000), @FS INT, @RC INT, @FileID INT, @Date DATETIME
SET @File = 'E: extfile.txt'
EXEC @RC = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
EXEC @RC = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @File, 8, 1
EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null, @Date
This always appends to the file. I want to truncate the file and write to it afresh.
Please inform me how to do that.
Hi,
I've set up a number of jobs (not a maintenance plan) via a script in SQL 2005. These jobs do the following:
1) Full backup every sunday night
2) Differential backup every weeknight
3) Log backup every hour
The database is obviously in the full recovery model.
The backups all seem to be running, with one issue - the log file is still growing and is not being truncated. I was under the impression that a log backup should result in the log being truncated after each full backup. However, this does not seem to be the case.
Is there anything obvious I've missed that needs to be set up, or is there a way I can check that the full backup is actually setting the appropriate checkpoint and that the log backups are 'seeing' these checkpoints?
Thanks