Issue With Log Shipping Datetimes
Sep 17, 2007nations_document_sts_20070917144501.trn
9-17 9:45:01 am
Has anybody else noticed this
ADVERTISEMENT
How To Tell DB Not To Store Datetimes In 24 Hr. Fo
Apr 11, 2007There's gotta be a way to tell a DB not to store datetime fields as 24 hour format but rather standard time notation.
So, I don't want this: 2007-04-11 02:01:31.000
I do want this: 2007-04-11 02:01:31 AM
This has to be possible but I can't find it.
Thanks!
--PhB
Compare Datetimes
Jul 23, 2005HiI need to retrieve data from a table comparing datetimes. I mean I've atable with a datetime data and I'll need to retrieve rows with date time[color=blue]> now and date time < now + 5 minutes...[/color]Is this posible ? Which is the best / easy way ?Thanks in advanceJ
View 2 Replies View RelatedDealing With Empty Datetimes?
Apr 20, 2007What methods work for storing empty dates? I've read that some people pick an old date and use it to represent empty. I'm not fond of the idea, because then I'll have to strip that date whenever I display the field in my UI.
Any other ways to do this? I'm using SQL Server 2005 and C#.
Thanks!
Query List Of Datetimes For Same Date
Jul 7, 2007Hi:I have created a table that has a column of smalldatetimes. For any given day, there may be different records with the same date but with different times. I've created the query SELECT Event FROM Events where Start=@Start and I input start as a selected date from the calendar control. Because of the different times, I do not get any matches. Could someone help me with a query that will get all of the events on the same day? Regards,Roger
View 2 Replies View RelatedJoining Records With Nearest Datetimes
Mar 19, 2013How would I match datetimes in records structured as follows:
Code:
Record1 AccountNo StartDateTime EndDateTime
1 1234 4/30/2012 8:00 AM NULL
2 1234 NULL 5/15/2012 8:00 AM
Desired Result:
Code:
AccountNo StartDateTime EndDateTime
1234 4/30/2012 8:00 AM 5/15/2012 8:00 AM
Of course there are multiple accounts, about 2100 in this case but they very by time periods, and multiple start and stop dates for an account. I need to get the start times and match them w/ the nearest end times but AFTER the value of the start time. Nearest end times must be forced to correspond to the nearest start time but there are some start times w/o end times and end times w/o start times due to user data entry errors. I need a solution that handles this. It is ok w/ the customer to make the assumption of nearest times supposedly going together so they can show the users the errors. I am on SQL Server 2008 R2.
Appending Two Datetimes Which Were Converted From Strings
Nov 21, 2006can anyone teach me how to append to datetime data into one.the first is a date and the 2nd one is a time.the time is converted from string.thanks
View 4 Replies View RelatedConverting Integer Values To Datetimes
May 23, 2006We have inherited an appointments database that has a table tblAppointments
Within this table there are 2 fields, ApptFrom & ApptTo, these are the appointment start & finish times.
My problem is, the values in these fields are held as integers ranging from 0 - 288. I have worked out the scale for the integers, it is based on a 24hr clock with each segment representing 5mins
Eg: 0 = 0:00
12 = 1.00
24 = 2.00
36 = 3.00
€¦
...
288 = 24:00
What I need is a piece of T_SQL that will translate these into times that can be read by a normal user
So, say I have a record with an ApptFrom = 36 & ApptTo = 42, I need to be able to show these as ApptFrom = 3.00 & ApptTo = 3.30
Any help is greatly appreciated.
Cheers,
Craig
Comparing To DateTimes In SQL-Select-Statement When One Date Can Be Null
Aug 25, 2007Hello! I have a field "End" in my database that is mapped as DateTime and allows nulls. Now I want to do a SQL-Select (in a SqlDataSource) like SELECT * FROM My_Table Where (([End] = @EndDate) OR ([End] = null)) @EndDate is a valid DateTime, but the second OR condition doesn't work. What is the best way to check if the [End]-field is empty or null? Thank you very much!
View 1 Replies View RelatedCalculate Durations From Start/stop Datetimes (was Reporting...)
May 2, 2006gday, how do i see what i wanted to see
View 2 Replies View RelatedSQL 2012 :: Calculating Elapsed Time Between Datetimes For Travel?
May 20, 2014I have a requirement to be able to calculate the transit time between international locations when I have the flight departure time and flight arrival times and departure and arrival locations.
We have a problem though, in that the datetime stored are local times, and the journeys can involve crossing the international date line in either direction, so it is possible for the arrival time to be earlier than the departure time!
To give a famous example, if you flew Concorde from London to Washington, you could depart at 10am on the 1st of June (London time) and arrive at 8.00am on 1st June (Washington time)
Even worse, you could leave Brisbane at 2.00 am on the 1st June, cross the International Date Line and arrive at San Francisco at 23:00 on 31st May!
Log Shipping - Switching Recovery Model In Log Shipping
May 13, 2007Hi
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
Log Shipping: How To Failback After A Failover Log Shipping?
Jun 8, 2006Hi,
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
Log Shipping
Jun 21, 2002Could somebody please guide me on how best to Implement SQL Server Log Shipping. I have gone through BOL without luck. Thanks.
View 2 Replies View RelatedLog Shipping
Aug 1, 2000I need to create a RO copy of a production DB owned by an outside company. We are connectd via a WAN link, but cannot use replication. They are proposing using an initial load via tape, and sending us a text file nightly with the days changes to the DB. We will then need to load that data using BCP, DTS or some other method. Does any one have any ideas on using log shipping instead of the text file. It would only be practical to get a fresh load of the entire DB once a quarter or once a month at most. It is a 40+ GB database and we are expecting 100 to 200 MB of logs per night. For business reasons, we are limited to some type of file transfer mechanism for the data transfer, and cannot really change their backup schedule which is nightly fullbackups and tlogs every 30 minutes.
View 1 Replies View RelatedLog Shipping
Nov 28, 2000Can anyone point out me , What does Log shipping mean ?
Is it backup of Log file ? or moving backup logs to any other destination ?
Thanks
Karthik
Log Shipping
Mar 27, 2002Hi all,
I am using SQL 2k EP Editions with SP2 on Win 2k Advance servers. Since more than week or so I am trying to establish log shipping between two servers. But its not working.
I am using database maintainence plan wizard to set up log shipping. Every thing works fine as far as wizard is concern, it creats plan for log shipping. But my log shipping is not working. The plan to back up log on source database is working fine. I can see the job history and the log files in the backup folder. But I have found that the job on the standby server to copy log file on network folder is failing and so the job to restore log on stand by server. I get the following message
"sqlmaint.exe failed with error state....."
Little reaserch on the standby server found that sql server is using maintainence plan to copy and restore log files, but i do not see any database maintainence plans on standby server as well as I have checked that there is no plan id in sysjobs table on either server.
I have sa rights. The account used by sql service and sql agent have admin rights and they do have rights to access the network folder for both the servers. So there is no rights problem.
I have followed all steps published in white paper for setting up log shipping on microsoft web site.
I have searched microsoft KB but it is of no use for sqlmaint.exe.
Any help is highly appreciated.
thanks in advance.
Minesh.
Log Shipping
Sep 6, 2000This might end up being fairly lengthy...I'm in the midst of implementing log shipping as a "warm stand-by" solution at my company. All the components appear to be in place: I'm using cmd shell to copy the backup device file to a remote server and then execute a RESTORE stored procedure on the remote server.
The copy and restore work just fine. The problem I'm having is with the transaction log dumps and restores.
We normally dump transaction logs (and then truncate) every hour. With the log shipping being implemented, we're going to want to do separate log dumps every ten or fifteen minutes, copy that dump over to the remote server, and then apply that log to the database.
Here's the question: for the log ship portion, I don't truncate the log. But after the "normal" log dump occurs, things get tossed out of whack. When you try to apply a log, I get the message "database has not been rolled forward enough....".
Has anyone encountered this type of issue and if so, how did you work around it? I'm assuming it's a simple of issue of certain options you set on your dumps and scheduling....
I'd appreciate any help....
Thanks!!!
Log Shipping
Sep 24, 2002Does anyone know if Log Shipping in SQL 2000 Ent also ships over database schema changes?
View 2 Replies View RelatedLog Shipping
Oct 14, 2002We are considering implementing log shipping. Do the sql server logs keep track of the logs that are shipped and applied through log shipping? Or is there some other way to make sure that all logs have been shipped and applied?
View 1 Replies View RelatedMore Log Shipping
Jul 25, 2004I have been successful in getting log shipping working but still have some nagging questions that I cannot find answers to.
1. I had a situation where the copy for one TranLog took much longer than the 15 minute interval I have it setup for. It seemed to get stuck on that copy. Is that how it is supposed to behave.
2. Related to the question above, weekly, I have jobs that reorganize, check integity, recalc statistics. Would these jobs create very large log files? If so, how do others deal with this?
3. Is there any documents available that discuss testing converting your secondary server/database to your primary and back again?
4. Is there a way to setup Email notification to report out-of-sync conditions?
Any other gotcha's that people have run into.
Any help would be greatly appreciated.
Thanks,
Ken Nicholson
Log Shipping
Feb 2, 2005Hi
While configuring log shipping, if i choose the "allow database to assume primary role" then the "ceate and initialise new database " option is selected by default..Does this happen all the time or am i missing something.What if i have already initialised the destination database.
Thanks
Madhukar Gole
Log Shipping
Aug 18, 2006Hi all,
I am testing my log - shipping strategy. I have tried with northwind database and it was successfully created and is operating. However in order to test I have created a new Test Table in the primary database to see whether it is working. From now on database shows that it has been loading and I cannot see any tables it is grayed and it says loading. What would be the problem? When I checked the logs it has been copying to the secondary database and it doesn't show any error in the log-shipping monitor. It seem everything is cool accept this loading part. If some one help me I really appreciate it.
Thanks
LS
:confused:
Log Shipping
Mar 27, 2002Hi all,
I am using SQL 2k EP Editions with SP2 on Win 2k Advance servers. Since more than week or so I am trying to establish log shipping between two servers. But its not working.
I am using database maintainence plan wizard to set up log shipping. Every thing works fine as far as wizard is concern, it creats plan for log shipping. But my log shipping is not working. The plan to back up log on source database is working fine. I can see the job history and the log files in the backup folder. But I have found that the job on the standby server to copy log file on network folder is failing and so the job to restore log on stand by server. I get the following message
"sqlmaint.exe failed with error state....."
Little reaserch on the standby server found that sql server is using maintainence plan to copy and restore log files, but i do not see any database maintainence plans on standby server as well as I have checked that there is no plan id in sysjobs table on either server.
I have sa rights. The account used by sql service and sql agent have admin rights and they do have rights to access the network folder for both the servers. So there is no rights problem.
I have followed all steps published in white paper for setting up log shipping on microsoft web site.
I have searched microsoft KB but it is of no use for sqlmaint.exe.
Any help is highly appreciated.
thanks in advance.
Minesh.
Log Shipping
Apr 3, 2002Hi All,
I failed to configure the Logshipping in
SQLServer2000, through database maintenance plan wizard,
at last step I got the following error.
Microsoft SQL-DMO(ODBC SQLState:42000)
Error 50007:xp_repl_encrypt:Error executing
srv_paramsetoutput
I am running SQLServer2000 Enterprise Edition with
Servicepack2, and Windows2000AdvancedServer with
Servicepack2 on both machines and bothe machines are in
the domain, both SQLServerservices are running under
Domain account.
thanks in advance,
Rob.
Log Shipping
Jun 19, 2002HI,
Is there any effect on taking full backups on Primary Server while log Shipping is in place? If we take a full backup is that applied to the secondary server?
thanks
Log Shipping
Jul 18, 2001We would like to use log shipping for a Near Real Time (5 min delay)reporting server. SQL 2000 requires exclusive use of the database when the logs are applied. This presents a problem since there may be a couple of hundred users on the NRT server. Even if they only make connections when running the reports there could be some contention issues. Has anybody used log shipping in this type of scenario?
View 2 Replies View RelatedLog Shipping
Oct 17, 2001Is it possible to log ship a database from server A (source) to server B (read only) when the database on server A is partly replicated (transactional repl) to several other servers ? All hints and advices are highly appreciated.
View 2 Replies View RelatedLog Shipping
Jan 26, 2001Does anyone know why some databases in SQL 2K Enterprise are not available for log shipping? In other words when you select a database Log Shipping this option is not available (grayed out). Other DB's on the same server log ship without problem.
Any ideas?
I think Microsoft has an answer to this but their site is not working so I can't get to the Q270006 which supposed to discuss that. If you do a search in KB for "Log Shipping is disabled" you will see the WebCast link which references Q270006 but then if you click on the link to go to the Q link does not work and search Q270006 is not found in KB..
Log Shipping
Feb 26, 2004Hello,
to use Log Shipping solution, Entreprise Edition is required for both Primary and Secondry Server (you agree ?).
Do you have an idea about licence regarding the secondry Server (suppose that you have to switch 2 or 3 per year. The second server will be used 2 or three times per year).
In some plateform (xeon CPU), the logical number of CPU is 2 times the physical number (SQL Server See 2 CPUs. You have 1 physical CPU).
Do you have an idea about licence in this case ?
Thanks a lot ...
Log Shipping Help
Apr 8, 2008Hi,
I have following question ( same was asked to me by my collegue)
I have Log shipping between two servers ( say Server A - Primary & Server B - Secondary).
One user is logged in to B Server and fired a long query on the same database / object. it is taking a very long time to execute.
Q : How this will impact the log shipping process ?
Ans : Log shipping will fail as the secondary db will show as " database is in use" for log shipping process.
Q : How to recover the log shipping process?
And : Notify the user to disconnect the session / or kill the user session. But i don't know how to recover the log shipping.
Thanks in advance.
-- Chetan
Log Shipping
Jan 25, 2008All, I am new to log shipping and I am troubleshooting an issue with it. Basically somebody restored a backup from a day ago on the primary server, now the load is failing on the destination (obviously). I read that I should disable the jobs and backup and restore to the destination server to get it going again. I have some questions on this though, since the destination is a standby I read that I need to use the WITH STANDBY clause and specify a standby file name to use. Where do I find out the standby file name to use here and also is this the only way to get it back in synch?
Thanks in advance.
Future guru in the making.
Need Help In Log Shipping
Jul 23, 2005I am attempting to set up log shipping from my primary dell clustered 2node sql server to a backup quad xeon server in case of a failure. When Ifollow the information given from Microsoft regarding this and I get tothe point where I am at "Add Destination Database" I am being promptedfor my server name, which shows the primary one ONLY. I dont seem to findany documentation on where I add in the secondary server name...Searching online has yielded no responses for me and MS only says toregister the servers... but where? and do I need to restart? I am onlytrying to be extra cautious as this is a live database.Thank you in advance for your help...
View 5 Replies View Related