I have a log shipping configuration where Server A is primary, B is secondary and C is the monitoring Server. When I run a query on both A and B I get similar results. Which means log shipping is working fine.
However when I run 'Transaction Log Shipping Status' Report on the monitor server I am unable to tell which files were recently copy and which files were last applied. or even when the last backup occurred or a copy was made. The columns are empty. I would like to find out what would cause this to heppen?
Also is there a way to find out the lsn of the databases on both primary and secondary servers.
I'm trying to get reports and report server site aveable through user certificate.
Web site is under default web site. Under default web site is certificate server web page, it is aveable and configured the same way and it works if I use certificate.
If I type https://FQDN/Reports , i get error The request failed with HTTP status 403: Forbidden. But if I type https://FQDN/reportserver i can access.
Both sites are aveable if I use only https. But if I but if i use Require client certificate, than I get 403 error.
In rsreportserver.config, there is <UrlRoot>https://NETBIOSServerName/reportserver</UrlRoot>, does not help if I use FQDN.
If I enable anonymous access, I can not access Reports site, an I get error 403, but on Reportserver site I get following error :
I have implemented log shipping on one my databases. Logs are being shipped just fine and are even being applied. Change something on primary and you will see it on the secondary.
However when I generate Transaction Log Shipping Status Report from the Primary Server, I see that Stauts is GOOD. I see primary and secondary servers names. I see when the Last backup occured. But there is no data on COPY and RESTORE. Those columns are grayed out or should I rather say colored gray with no values.
When I go onto secondary node and generate Transaction Log Shipping Status Report, columns under Restore header are popluated. But columns under BACKUP and RESTORE are grayed out.
On monitor node Transaction Log Shipping Status Report doesnt show when the last backup, or restore or copy occured. It only shows the threshold value for backup and restore.
So I have two questions: 1) why am I not seeing all the info in one place? how can I find out how far behind is secondary? 2) shouldnt Transaction Log Shipping Status Report from any node show me same information? 3) even though changes are making it to secondary once the threshold value is hit, status would not be GOOD anymore. so no matter what I do it would be out sync when it is not.
Hello, I have a report that is made out of many subreports. It takes a long time to produce this report. Is there a away of knowing and displaying to the user which sub report is produced now? Is there a way of making status info sent to the UI? Somthing like OnStartPage/OnStartReport (now send status to UI somehow)?
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?
I have log shipping every 15 minutes working between 2 servers, and while the secondary server reports everything ok (time since last copy, restore are all under 15 minutes), the primary server reports that the time since last backup is 7400+ minutes. I have verified that the LS backup job has no failures. How can I correct this false error state and get the primary server to start reporting actual statuses?
When I go into Report Manager, http://localhost/reports, and try running a report it is giving me this error message:
The request failed with HTTP status 401: Access Denied
But if I go directly to the Report Server, http://localhost/reportserver, and try running a report there then everything is fine.
Does anyone know why it is doing this all of a sudden? I did change the Reports app in ISS to allow anonymous access but have since changed it back, and it still gives this error.
hi, i am facing a problem in creating labels usin Report Builder. i need to create a 4X6 label containing 4 rows. But in Report Builder , when i try to create a table format, it only allowing one row. How to insert another row and place data in it?
i was successfully developed labels using report server project in VS 2005.
but it's not possible in Report Builder.
please help me out.if not understand quesion well, please feel free to ask.
I need to create a basic report that displays the userid, username, email, status and need to update the status column to enabled when we select any particular username. And the username parameter comes from the available users from the database. How to do this?
Do I need to mention the where clause where username=@username in the query or we don't need any
Also I need to get the available distinct usernames from the database while entering the parameter username
/*DECLARE variables to use IN queries*/ DECLARE @intErrorCode INT DECLARE @ErrorMessage VARCHAR(MAX) DECLARE @UserID INT SET @ErrorMessage = 'Completed.' SELECT [userid] ,
[code].....
Basically first I need to find the userid, username, accountstatus, email for a given particular user. Then I need to update the accountstatus to enabled for that selected user.
Also I need to create auditing that from before update and after update the accountstatus.
Below query tells us if guest user is enabled or disabled in a particular database
SELECT dp.name, CASE perms.class WHEN 0 THEN 'Yes' ELSE 'No' END AS 'Enabled' FROM sys.database_principals dp LEFT JOIN (SELECT grantee_principal_id, class FROM sys.database_permissions WHERE class = 0 AND type = 'CO' AND state = 'G') AS perms ON dp.principal_id = perms.grantee_principal_id WHERE dp.name = 'guest';
Do we have a query which can also add the database name to above query output? The output must have columns with data against Name,Enabled,Database name
Created Prod order status report, in status, we have different status
created =0 start =4 released =3 reported as finished =5 ended =7
I have the report, in report don't want to show the Prod order for ended status, how can I add the filter for this so it can show for all the other status not ended status. when I did on filter <7 , it did not work
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.
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 am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
Unless I grant a developer "system admin" role, they are unable to see whether or not any job *not* owned by themselves is running?
EX: Developer A can't see whether Developer B's job is executing? Why? I've granted them both db_owner in msdb, and all permissions possible in msdb. Is there an extended sp I can look for?
I have a table with columns c1, c2, c3, c4.if all nulls or blanks. Status = 0if c1 assigned but no c2, c3, and c4, then status = 1if c2 assigned but no c3 and c4, then status = 2if c3 .. then ..if c4 .. then ..I want to have one SQL to get the status like (ignored checking forblanks here for demo)SELECT Status = (if not c4 is null then 4else not c3 is null then 3else not c2 is null then 2else not c1 is null then 1else 0)FROM mytable.Thought of using CASE ... WHEN ... but it is only on one colum.Any better idea.ThanksJohn
In SQL 2000, go to Management, than Jobs, you will see a list of all jobs and their running status. I would like to programmatically display the same on my web page. I can call sysjobs table to get the jobs and use sysjobschedules to determine whether it's been scheduled or not. Then use sysjobhistory to obtain their running history. But how and where do I get the Next Run Date information? Is this calculated or stored somewhere?Can anyone give me some help on how to work with sysjobs, sysjobschedules, and sysjobhistory tables? All I am trying to do is to build a web-based app that does the same thing as the Enterprise Manager is doing - create and schedule a job, and monitor the job status.Thanks a lot!Penn
Hi all - am having trouble working out what would be the best way to do this (most efficient)I have written an order, despatch and accounts system. To get a list of orders ready for despatch, I have written a query which calculates the amount of items ordered against the amount of items despatched from that order. If there are any outstanding items, then this order is in the list of orders which are outstanding for despatch. I am having trouble working out how to go about creating a list of orders ready do be invoiced... Orders that are ready to be invoiced can be completely despatched orders or part despatched orders. That is to say that if an order is only half despatched, my client still wants to be able to raise an invoice against that part of the order. I envisage creating a query which checks my orderline table to see if the item has been invoiced and creating the list from there. But as the application is used more and more, this query will take longer to run? ANy ideas? Thanks. Steve.
I need to know the meaning of the status bit in sysdatabases because BOL only reports some of them. For SQL 7.0 SP1 I have some database with a status bit equal to 0 (zero) and equal to 12. For SQL 2000 SP2 I have some database with a status bit equal to 0 (zero),24,20 and 1073741840. So I would really appreciate if someone can explain the meaning of these status bits. Thank you very much. Franco
I just started working with MS SQL. I'm attempting to find the equivalent of the MySQL commands STATUS and SHOW STATUS.
Essentially, I want to connect to the database from a .net app, check the status, such as how many connections, table locks, errors, etc... and display that information on my application.
I have a question I hope someone can help me with.
My situation: I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.
The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.
My problem: On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).
Is anyone able to shed some light on this?
I assume that the job will only return a completed status after: (a) both A and B have completed execution, and (b) the BACKUP operation has completed
Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?
My boss and I have recently started designing a new database, and we've got into a bit of a debate about creating status tables (e.g. order statuses).
It was my intial instinct to create a separate table for each kind of status (e.g. an OrderStatuses table, a JobStatuses table, etc), however my boss wanted to create one table containing all the statuses, with an extra field for defining what kind of status it is.
I was just wondering what others thought was the best approach.
While iam connecting to servers through management studio iam facing iam seeing like few servers are connected with Green colour (like>)and few are connected with circle as white.Frm these how can i decide the server status.
