Log Shipping Monitor Script For SQL2000 By Ron Craig
Apr 3, 2003
The above mentioned script by Ron Craig was put on the SWYNK site on 22 February 2002. When all the SQL Server scripts were moved from SWYNK to Jupiter, this article and scripts were lost. I have contacted Jupiter and they admit that it has been lost. If anyone has downloaded this article(prior to the move from SWYNK), please can you forward it to me. If Ron Craig should read this, please can you forward your article to me and ask Jupiter to put it back.
View 2 Replies
ADVERTISEMENT
Mar 21, 2007
I have SQL Server 2005 log shipping setup with primary/secondary configuration. I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message:
Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'. The statement has been terminated.
There is nothing special about the configuration. Any ideas?
View 9 Replies
View Related
Aug 19, 2003
I have 4 sql 2000 servers. One is a the primary, one is a local stand-by, one a remote standby and one is a training server. WHen configuring which server should monitor log shipping I was torn between the local standby and the training. The training server which only has a few users if ever, might be used for some DTS tasks in the future for some B to B stuff. Based upon this I am leaning on the local standby to monitor. Anyone have an experience or input?
Thanks!
(No I can't buy any more servers)
View 2 Replies
View Related
Oct 27, 2006
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 Related
Jan 30, 2015
At one of our client side a wired log shipping issues has come up.while monitoring those two server i noticed that although log-shipping report says both server are in sync, report displays information related to both backup and restore , it doesn't shows information related to copy that is when was last file copied and last file copy column is showed blank. Same is when i execute proc "sp_help_log_shipping_monitor" . I get same result ...
When i expand copy job history to analyse its what i found is although job has executed successfully , but in depth reading each steps says that no .trn file was copied .
My copy directory is at secondary server itself where .trn files are placed.And from this location itself files are begin restored.
SQL server and agent on both servers are run by same domain account ....
View 5 Replies
View Related
Oct 9, 2007
We have set-up log shipping in both our development and production environments. The difference between the two is that development is using SQL 2005 Developer Edition SP2 and production is using SQL 2005 Enterprise Edition SP2. As well, the production environment runs using 64-bit 3-node failover cluster set-up for the source, whereas the development source server environment is 32-bit and not clustered. Also, our development environment destination/monitor instance is located within the same geographic location mapped to the same domain controller. The production environment destination/monitor instance is located off-site, and although is part of the same domain, uses a different domain controller which is synched-up with the primary domain controller used for the source server and entire development environment. Other than that, both environments run using Windows 2003 Server Enterprise Edition SP1.
Originally, both environments were configured to use Monitor connections "By impersonating the proxy account of the job (usually the SQL Server Agent service account of the server instance where the job runs)". This presented no problems in the development environment, but in the production environment, this results in the following error whenever the source server tries to update the monitor instance with the backup alert status:
Error: 18456, Severity: 14, State: 11.
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. [CLIENT: XXX.XX.XX.XX]
This also results in the log-ship alert job falsely reporting that backup jobs are "out-of-synch", since the source server cannot write log information to the log ship tables on the destination/monitor instance.
Now, according to BOL, when choosing to impersonate the proxy account, this is supposed to default to the SQL Server Agent Service account, which in our systems (both development and production), is a Windows domain account with full administrator priviledges and a SQL system adminstrator on both source and destination/monitor instances.
Upon trying to open a case with Microsoft originally when we were running on SQL 2005 SP1, and spending several hours ensuring there were no duplicate SPNs and linked servers were properly configured, they had come to the conclusion that this was the result of a known SP1 issue (http://support.microsoft.com/kb/925843), and would be solved by applying SP2. We are now running SP2 + hotfix (9.0.3152), but are still receiving this error.
The only way I have currently of fixing this issue is by changing the Monitor connection from authenticating via proxy account to using a SQL Server login account which has system admin priviledges.
I have limited knowledge of how security is applied across different domain controllers within the same domain. Any help would be greatly appreciated.
View 4 Replies
View Related
May 11, 2007
After try and error for several months, finally my SQL2000 LS works.
i gave my self a little celebration for that.
but after running well for a day, just one day !
this morning it give me Out of Sync.
error messages:
failure /4305
activity : Load
[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
activity : Copy
failure / 32
The process cannot access the file because it is being used by another process.
i just dont understand, how come the load & copy process failed, after it works for a day.
View 6 Replies
View Related
Oct 10, 2007
I am testing log shipping on SQL2000 and am having an issue with the log shipping copy job on the destination. Using the log_shipping_monitor_probe, no trn log files are ever copied to the destination share. I even granted full access to everyone on the primary and destination share folders and using NT authentication uses the ANONYMOUS USER account (not sure why). My error returned from xp_sqlmaint is:NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
Logged on to SQL Server 'BOCAVSQLD2LOGSHIPSQLD2'
as 'NT AUTHORITYSYSTEM' (trusted)
NULL
NULL
Starting copy for plan BOCAVSQLD1.pubs_logshipping
NULL
Source database - pubs
Copied 0 files
NULL
Finished copy for plan BOCAVSQLD1.pubs_logshipping
(14 row(s) affected)
Any help would be appreciated!
Todd P.
View 8 Replies
View Related
Oct 12, 1999
Dear Craig,
I didn't understand where you were going with the response you gave me. Could you possible expand on it? Thanks, Daimon
View 2 Replies
View Related
Aug 19, 2015
If I'm on a remote machine, meaning a computer not in the WSFC cluster, and I open SSMS 2014, point it to a SQL Instance, and open activity monitor:
1. I get all the panes and charts except % Processor Time.
2. Then, if I authenticate to the cluster's domain by mapping a drive with valid domain credentials, I'm free to put performance counters in the Perfmon - - - but SQL Activity Monitor shuts down with“The Activity Monitor is unable to execute queries against server SQL-V01INSTANCE1..Activity monitor for this instance will be placed into a paused state.Use the context menu in the overview pane to resume the activity monitor.
Additional information: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))(Mscorlib)”
3. Of course, the Activity monitor can't be resumed via the context menu. Removing counters and closing the perfmon do not work. I dropped the mapped drive and rebooted the machine. That brought back 95% of the information in the Activity monitor.
4. Further experimentation showed that any mapping of drive shares present on the SQL Server to the computer running SSMS cut off functionality of the 'overview' pane in the remote machine's SQL Activity monitor -- the monitor that had been trying to watch the server offering the shares.
View 4 Replies
View Related
May 13, 2007
Hi
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
View 1 Replies
View Related
Jan 30, 2007
I have setup transactional replication everything on one box. later(two or three weeks later), Replication monitor is show red X Under my publishers (publications is disconnected). this is SQL2005.
Everyone known how to fix this problem?
Thanks,
Frank
View 1 Replies
View Related
Jun 8, 2006
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
View 5 Replies
View Related
Sep 17, 2006
i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?
View 1 Replies
View Related
May 13, 2008
i am in the process of Migrating SQL 2000 to my new SQL2000 server i want to know the what would the best way for me to migrate one SQL server to another SQL server on the same network and rename the new server to the old server and bring it up for use in our ecommerce website.
View 10 Replies
View Related
Oct 23, 2007
Hi, Today, I heard about Sql Profiler, and I seems to be a tool to monitor, analyze, and fine tune sql server. Is there a tool like this for SQL Server Express 2005? thanks,John
View 1 Replies
View Related
Apr 29, 2004
Hullo. I have a SQL SCHEDULER Job that keeps hanging.
1) How do I monitor this? I would like to have another Sql Job monitor the first one hourly, and send me an email if it is hanging. Isn't there a system table that tells me a job status?
1a) Once I find the job, how can I "Stop it" automatically?
2) Is there a way to have the Job stop itself it it runs for more than 20 minutes?
Thanks.
~Le
View 4 Replies
View Related
Apr 3, 2008
Hi ..
i have a task to monitor the sql jobs..
i wish to do with this with C# or asp.net application.
please help me to finish this..
View 7 Replies
View Related
Jan 20, 2004
Hi all,
Can anyone please tell me whether there are any ways we can check whether a connection that was opened to a SQL Server database is closed. Is there a tool we can use?.
Thx
Hakl
View 2 Replies
View Related
Jun 4, 2001
Would the Performance Monitor be the best tool
to use if you wanted to find out how many transactions
are being process a day?
View 1 Replies
View Related
Aug 1, 2003
How do I access performance monitor (system monitor).I am using Windows 2000 professional, SQL server 2000. Do I need to set up analysis service or something? I am not seeing system monitor on my box? am I missing something?
View 5 Replies
View Related
Dec 18, 2001
Hello all,
I want to know whether any performance monitoring tools/procedure available in market for SQL Server other than Performance monitor, Profiler, Query Ananlzer of SQL Server.
thank you in advance.
regards,
Nana
View 1 Replies
View Related
May 11, 1999
Hi guys!
Please tell me where is mistake?
I have win.nt workst. 4.0 and SQL Server 6.5 Client Configuration Utility installed.
I open Performace monitor and try to add to chart sql server which already have been registered in SQL Enterprise Manager.
And I have error message: Computer name not found.
Why??????
Thank you.Sev
View 2 Replies
View Related
Oct 14, 1998
Until this week, I had full functionality of SQL Monitor`s capabilities to monitor SQL Server. Now, when I select our Production Server and attempt to `Add to Chart` I am unable to view/select any SQL Server... entries in the `Object` dropdown list box. When I click the down arrow with the Development server selected I DO see all of the SQL Server selections. This inability to `see` the SQL Server functions in the Performance Monitor for the Production server exists regardless of whether I attempt to view them from a Win NT 4.0 Workstation or at the Server`s console. Again, I had full functionality from both locations last week. The logins have not changed. What has changed?
ANY help would be GREATLY appreciated. I need to monitor this system and I`m running blind at the monent.
TIA
Mike Gaudet
Visages, Inc.
View 1 Replies
View Related
Jul 9, 2002
Hello,
I do all my monitoring locally for disk space, locks, blocking, I've 10 production servers, We need to centralised the monitoring server so from one server all the monitors can be done. Does anyone has any ideas how memory, cpus consumption, disk space, all alerts, locks, blocking, log space and job completition monitoring can be handled.
Any idea or guidance is appreciated
Thanks
Roma
View 3 Replies
View Related
Jan 4, 2001
Help ????????
I have a winnt and sql server 6.5 when i open up sql performace monitor it doesn't show any thing it comes blank when i go to reintialize the sqlserver object is not there i need to know how can i fix it
its urgent
girimanjesh
View 1 Replies
View Related
Feb 29, 2008
I'm trying to record all the queries running on a specific database to a file.
Does anybody know of free or commercial utility that can do that?
View 2 Replies
View Related
Feb 22, 2007
Hi,
How do most of you monitor your SQL servers (i.e. performance, cpu utilization, job failures...)? Can you recommend any tools that are not too costly?
Thanks.
Mary
View 4 Replies
View Related
Jan 20, 2004
I have discovered that there is an extended procedure called xp_fixeddrives, which can check how much space you have on your SQL Server's drives, it works fine. Can some one tell me what can I use to monitor the above (space used on drive) on servers that do not have SQL at all. Remember I want to call that from my SQL NOT from VB
View 1 Replies
View Related
Feb 24, 2004
Is there anyway that you can automate performance monitor from the cmd line. I.e feed it some parameters like server and counters and receive a log file at the end.
Cheers
View 5 Replies
View Related
Apr 10, 2008
Is there a way to allow a user to run activity monitor without making that user system admin
View 9 Replies
View Related
Apr 23, 2008
Hi experts,
I just want to know how can i kill all the processes of a database
if the database have more than 100 connections.This is for the purpose of restoring a database.
RKNAIR
View 3 Replies
View Related
May 16, 2008
Hi all,
I'm very new with SQL Server. My company just purchased a dedicated server and we installed SQL Server 2005 Express Edition. The operating system is Windows Server 2003.
We have only 8 databases in that machine and there is this 1 particular database that keeps getting error. I know that the database is in error if I go to the Management Studio Express and I expand the database, that particular database has no [+] expand symbol and if I try to view the properties it will say "out of memory error" or something like that.
How can I find out what is causing this error? That database has is 18MB and is not used frequently. The error usually appears once every month.
How to debug this issue?
thanks in advance
View 3 Replies
View Related
