Problem With Snapshots On Mirror
Jun 2, 2007
Hi All
We are running a Mirrored instance of SQL Server 2005 SP2 (in High
Performance mode) and using snapshots on the Mirror to provide a reporting
solution.
The problem is that quite frequently the Database Snapshot starts going
through a Recovery Process. This is causing large delays in the reporting
process.
The sequence of events in the SQL logs is
1. An Event for "Starting up Database Snapshotname"
2 A series of events "Analysis of Database Snapshotname is X% complete"
This can take up to 2 minutes
3. A series of events "Recovery of Database Snapshotname is X% complete"
This recovery process can take up to 1hr 45 minutes.
The database is running on new servers and there are no apparent disk
problems.
Can anybody advise on why a snapshot should start a recovery process
and how to prevent this
Regards
Patrick Flynn
View 1 Replies
ADVERTISEMENT
Jun 14, 2015
I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
View 4 Replies
View Related
Jan 31, 2008
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
Thanks,
View 9 Replies
View Related
Dec 27, 2006
I was trying to test mirroring and now would like to delete the mirror database but it says I need to remove database mirroring first. I deleted the endpoint and cannot figure out how to remove the mirroring. Can someone please help.
View 1 Replies
View Related
Mar 26, 2007
Have 3 reports that runs using a shared schedule to generate historic snapshots. This has be working for months and months but out of nowhere it changed.
So now when ever the schedule runs it will generate the historic snapshot for 1 report but NOT the 2 remaining. As a strange twist the report that is successful changes from time to time...
SQL Server 2000, RS+SP2.
Any ideas?
Thanks
Regards
Jonas
View 2 Replies
View Related
Jul 13, 2005
We will soon be going to a new ERP system using MS SQL 2000. I'm looking into possible backup strategies. The database size will be about 50 Gigs.
We have a SAN, which the new ERP system will be backed up on. I know a little about a SAN, but I need to make decisions about the types of backups to make. We can have 10 active snapshots, so I could use snapshots during the day as a point in time backup. I could also use the SAN snapshot instead of using a full backup. Would it be safe to use the SAN snapshots instead of the normal SQL Server backups? I’m not sure how long it will take to do a full SQL Server backup because the server is still in testing mode. I’m not getting the big picture about when to use snapshots and when to use regular SQL Server backups.
Is it possible in Windows 2000 Server to stop the transactions and flush the buffers for a few seconds to do a snapshot? From what I understand, the database could be left in a suspect state if you use snapshots to restore a database.
Thanks in advance for any help!
View 5 Replies
View Related
May 16, 2008
I think I've read some conflicting advice in BOL. Maybe someone can clarify it for me.
Under "Database Mirroring and Database Snapshots" it says:
"You can take advantage of a mirror database that you are maintaining for availability purposes to offload reporting. To use a mirror database for reporting, you can create a database snapshots on the mirror database and direct client connection requests to the most recent snapshot"
To my mind, to enjoy a noteworthy performance gain, that mirror database would need to be on another server.
But then you read under "Database Snapshots":
"Multiple snapshots can exist on a source database and always reside on the same server instance as the database."
So how do you get the snapshot to the mirror?
View 2 Replies
View Related
Nov 9, 2007
HiWhat would be the quickest way to create a backup and revert programon an sql (2000) database?- Can you create a transaction on a database, regardless of theconnections and thenrollback it all via an external program- Could you monitor the changes with profiler and then reverse those?- If desperate, could you backup the db from a tool and then restoreit? (too slow to be practical?)Not sure how to do this so any offers would be appreciatedta
View 3 Replies
View Related
May 2, 2006
If multiple snapshots for the same report attempt to generate at the same second, is there something in place to prevent them from conflicting with each other?
Our SSRS 2005 application will use a console app that we are writing to change the default parameters on each report and call the CreateReportHistorySnapshot method. This application will be multi-threaded, so there is a possibility that multiple versions of the same report with different parameters will attempt to run at the same time. We need to be sure that these snapshots will not conflict and return the appropriate SnapshotHistoryID for that report run.
From looking at the History table in the ReportServer database it looks like there are uniqueIDs and other keys that would prevent duplicates, but what if the requests come in at the same time? Is SSRS 2005 smart enough to delay for a second so that there is not a duplicate? Since the way you reference these snapshots in your URL is with the Snapshot Time in UTC format, that only goes out to full seconds (2006-05-02T00:00:00).
Sorry for the length, but I could not think of how to condense this.
Thanks,
Steve
View 4 Replies
View Related
May 13, 2008
Hopefully not too much of a stupid question.
At the moment, we are using SSRS to report off an Oracle database, if we take a snapshot of the data on the Oracle database and put it within SQL, will we expect an increase in performace/response.
And is it a good idea to keep the snapshot in the same place where SSRS is installed?
Thanks in advance.
View 1 Replies
View Related
Mar 12, 2008
I want to share one of my doubt about SSRS and report server,
if i use snapshots or cache to my reports in report server, is it going to make increase in performance?
if it is going to make increase in performance,then SQLSERVER DATABASE is going to have any burdens,
means is it going to down the performance of Database?
Pls give me Responce to my Questions....
View 3 Replies
View Related
Jun 15, 2007
Hi
I am creating a full Merge replication setup between two servers
Is there any way that I can set it up so that I do not have to create a snapshot.
Ie I would gurantee that the data and structutre is EXACTLY the same on both the subscriber and the publisher.
The problem is that my data base is over 4GB and it is quicker for me to get the data from one server to the other by doing a backup and restore.
I am using SQL 2005 with the latest SP
Cheers
Ants
View 4 Replies
View Related
Aug 28, 2007
I am having the huge db of 80 GB and trying to configure merge replication. The intial snapshot application is failing due to some schema issues. I have made some necessary changes to avoid the schema issues. If I try start the merge agent it is going to re-initialize the snapshot and reapplying the whole snapshot will take atleast 10-15 hrs.
Is there any way to resume the snapshot from the point of failure.. avoiding the reapplying all the data which has already got transfered to subscriber.
View 1 Replies
View Related
Apr 30, 2007
Does someone know if doing a reindex on a clustered or non-clustered index cause the snapshot file to grow? In other words, is the data that makes up the snapshot copied from the source to the snapshot database? If a normal reindex is done on the underlying database, will it block users from acessing the snapshot? Any help would be appreciated.
View 1 Replies
View Related
Aug 7, 2006
Hi
I have setup my web synchronisation with very few problems until now. Initially I set the snapshots to generated by the subscriber when they first synchronise, this worked perfectly every time until a bottleneck occured after I reinitialised all the subscribers. When more than one subscriber then initialises the snapshot agent, the snapshot jobs fail because there is already a job running for user distributor_admin. The agent then retries etc, etc.. until all retries have failed. Ultimately only one of the snapshots is successfully created only after all other jobs have failed and run out of retries, it is then left alone to carry out its job.
Following this I made the decision to run with pre-generated snapshots instead so that I can schedule the server load and avoid these bottlenecks. Using the same publication I unchecked the option "Automatically define a partition and generate..." in the publication properties and proceeded to add each of the partitions specifying the correct "Host_Name()" value etc. After this I selected one partition and clicked "Generate the selected snapshots now". The snapshot was duly created but when synchronised the snapshot is not collected and used. Weirdly the merge porcess begins enumerating changes at the publisher followed by downloading 3100 chunks of data before receiving an error concerning the format of the message from the distributor.
If I check the option to allow auto generation, the synchronisation begins absolutely perfectly but only once it has generate a snapshot (Ignoring the one I created previously).
To add further weirdness, I created a small app which would generate the dynamic partitions using RMO. I used the sample code implicitly from BOL and it duly generated the dynamic snapshot job. I can then create a new Job class passing it the JobServer and JobName, it gives me the correct job but I cannot then call the Start() method because it keeps telling me the Job object is not created even though It is clearly enabled as a job in SSMS and can be right_clicked and started from there.
Even partition jobs created from within the SSMS appear as state "Creating" when I make an instance of it using the Job class.
What am I missing? Are my pre-generated snapshots not available to the subscriber because they seem to still be in the creating state? Is this a SQL Server configuration issue or have I setup the publication incorrectly?
Any help would be much appreciated.
Many thanks
Rab
P.S. Code snippet below
Imports Microsoft.SqlServer.Replication
Imports Microsoft.SqlServer.Management.smo
Imports Microsoft.SqlServer.Management.smo.agent
Imports Microsoft.SqlServer.Management.Common
Public Class frmSnapshot
Private Sub btnGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click
Dim hostName As String = txtSiteID.Text.Trim
' Define the server, database, and publication names
Dim publisherName As String = "WDU340"
Dim publicationName As String = txtOrgID.Text.Trim
Dim publicationDbName As String = publicationName
Dim distributorName As String = publisherName
Dim publication As MergePublication
Dim partition As MergePartition
Dim snapshotAgentJob As MergeDynamicSnapshotJob = Nothing
Dim schedule As ReplicationAgentSchedule
' Create a connection to the Distributor to start the Snapshot Agent.
Dim distributorConn As ServerConnection = New ServerConnection(distributorName)
Try
' Connect to the Publisher.
distributorConn.Connect()
' Set the required properties for the publication.
publication = New MergePublication()
publication.ConnectionContext = distributorConn
publication.Name = publicationName
publication.DatabaseName = publicationDbName
' If we can't get the properties for this merge publication,
' then throw an application exception.
If (publication.LoadProperties() Or publication.SnapshotAvailable) Then
' Set a weekly schedule for the filtered data snapshot.
If RunJob(publication, snapshotAgentJob, distributorConn, hostName) = False Then
schedule = New ReplicationAgentSchedule()
schedule.FrequencyType = ScheduleFrequencyType.OnDemand
'schedule.FrequencyRecurrenceFactor = 1
'schedule.FrequencyInterval = Convert.ToInt32("0x001", 16)
' Set the value of Hostname that defines the data partition.
partition = New MergePartition()
partition.DynamicFilterHostName = hostName
snapshotAgentJob = New MergeDynamicSnapshotJob()
snapshotAgentJob.DynamicFilterHostName = hostName
' Create the partition for the publication with the defined schedule.
publication.AddMergePartition(partition)
publication.AddMergeDynamicSnapshotJobForLateBoundComClients(snapshotAgentJob, schedule)
RunJob(publication, snapshotAgentJob, distributorConn, hostName)
End If
Else
Throw New ApplicationException(String.Format( _
"Settings could not be retrieved for the publication, " + _
" or the initial snapshot has not been generated. " + _
"Ensure that the publication {0} exists on {1} and " + _
"that the Snapshot Agent has run successfully.", _
publicationName, publisherName))
End If
Catch ex As Exception
' Do error handling here.
MessageBox.Show(String.Format( _
"The partition for '{0}' in the {1} publication could not be created.", _
hostName, publicationName) & ": " & ex.Message)
Finally
If distributorConn.IsOpen Then
distributorConn.Disconnect()
End If
End Try
End Sub
Private Function RunJob(ByVal publication As MergePublication, ByVal snapshotAgentJob As MergeDynamicSnapshotJob, ByVal distributorConn As ServerConnection, ByVal hostName As String) As Boolean
Dim jobs As ArrayList
Dim iJob As Integer
Dim bExists As Boolean = False
jobs = publication.EnumMergeDynamicSnapshotJobs()
For iJob = 0 To jobs.Count - 1
snapshotAgentJob = DirectCast(jobs(iJob), MergeDynamicSnapshotJob)
If snapshotAgentJob.DynamicFilterHostName = hostName Then
'Run the Job
bExists = True
Dim server As New Server(distributorConn)
Dim job As New Job = (server.JobServer, snapshotAgentJob.Name)
MessageBox.Show(String.Format("About to run the dynamic snapshot job: {0} with status: {1}", job.Name, job.State.ToString))
Try
job.Start()
Catch ex As Exception
MessageBox.Show(ex.ToString)
Throw
End Try
Exit For
End If
Next
End Function
End Class
View 7 Replies
View Related
Mar 28, 2007
I posted last year about getting and Expand All/Collapse All link working in reports, and it does work perfectly for on-demand reporting. I was able to get these working using the boolean report parameter and then the Jump to Report option going back to itself changing that parameter.
The issue that I have just discovered is that when a report with these Expand All/Collapse All links are in a report snapshot, clicking those links will cause it to re-render the report from the datasource at the time the link is clicked. This is an issue for one of the datasources we are using because the data is always changing, so when it goes back to re-render the counts and data returned are completely different then they were when the snapshot was first created. As time goes on the data will no longer even be in the datasource since it can only maintain 4 months of data.
Is there another way to get all detail groups to expand/collapse at once that does not require the report to be completely re-rendered from the datasource?
View 1 Replies
View Related
Feb 2, 2007
I am using SQL2005 merge replication and have pull subscribers on a low bandwidth link
I am compressing the snapshot into an alternate folder. Files are not put into the default folder
When I start a synchronization, I would expect the cab file to be copied to the subscriber and then the files to be extracted locally at the subscriber in order to apply the snapshot
However, what appears to be happening is that the files are being extracted from the cab file on the publisher (in a UNC specified directory) and then copied in their uncompressed form to the subscriber - resulting in an extremely slow snapshot application.
Any ideas what I am doing wrong? I have read about the options for using FTP to transfer snapshot files, but I am not clear whether I have to use FTP in order to transmit a compressed snapshot. I don't want to use FTP unless I need to.
Any suggestions would be much appreciated
aero1
View 8 Replies
View Related
Sep 27, 2006
Hi everybody, I'm quite new to SQL 2005 and I€™m trying to understand some key concepts regarding replicas. I need to develop an application with characteristics similar to the Sales Order Sample for Merge Replication, on the client side it should run with the express version of sql server and also the synchronization should only work via web. I try to run the sample but I got an exception in the CreateSubscription method on invoking publisherConn.Connect();
TITLE: Microsoft.SqlServer.ConnectionInfo
------------------------------
Failed to connect to server XXX.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
I can€™t understand how this connection could work if it isn€™t aware of the fact that it should use https to connect with the server.
Can someone point me in the right direction?
Thanks
Angelo
View 6 Replies
View Related
Feb 12, 2007
Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:
CREATE DATABASE DB_SNAP ON
( NAME = DB_SNAP_Data, FILENAME =
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDB_SNAP_Data.ss' )
AS SNAPSHOT OF DB;
And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?
Tanks.
F.
View 4 Replies
View Related
Oct 17, 2007
Hi All..
I am trying to create daily automatic snapshots by using the code below...
Code Block
declare @MyDay varchar(20)
declare @query varchar(1000)
declare @DatabaseName varchar(128)
declare @snapshotName varchar(128)
declare @snapDataName varchar(128)
declare @snapFileName varchar(128)
declare @snapFilePath varchar(128)
set @Myday = (Select datename(weekday,getdate()))
print 'It is ' + @MyDay
Set @DatabaseName ='Cerritos_Net'
Set @SnapDataName='Cerritos_Net_Data'
Set @SnapshotName ='Cerritos_Net_Snapshot'+'_'+@MyDay
Set @SnapFilename ='E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data'+'_'+@MyDay+'.ss'
Print 'Snapshot name is ' +@SnapshotName
select * from sys.databases where source_database_id =db_id(@databasename) and name = @SnapshotName
if @@rowcount <>0
begin
set @query = 'Drop database '+ @SnapshotName
print @query
exec(@query)
end
set @query ='Create database '+ @SnapshotName + ' on (Name = '''+@snapDataName +', FileName="'
+@SnapFilename +'") AS SNAPSHOT of '+ @databasename+';'
print @query
exec(@query)
But I keep getting this error
Code Block
It is Tuesday
Snapshot name is Cerritos_Net_Snapshot_Tuesday
(0 row(s) affected)
Create database Cerritos_Net_Snapshot_Tuesday on (Name = 'Cerritos_Net_Data, FileName="E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data_Tuesday.ss") AS SNAPSHOT of Cerritos_Net;
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'Cerritos_Net_Data, FileName="E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data_Tuesday.ss") AS SNAPSHOT of Cerritos_Net;'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Cerritos_Net_Data, FileName="E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data_Tuesday.ss") AS SNAPSHOT of Cerritos_Net;'.
I think I placed my quotations right.....
View 3 Replies
View Related
Feb 20, 2007
I have a database which contains more than a hundred publications and the clients need a snapshot for each one. Is there a way of automating this process of creating a snapshot? Like a job or something.
View 1 Replies
View Related
Jun 19, 2007
Hello,
I would like to know what is the difference between a snapshot and a cached instance in SSRS?
Which one has the best performances and which one is the best for multiple users and reports containing parameters (the parameters are then passed in the where clause of the sql code; ex: WHERE IN(@param1))?
Thanks for your answers.
Zoz
View 4 Replies
View Related
Apr 17, 2008
I am programatically adding and creating snapshots and subscriptions in report services from my application I created.
1) Is there a way to create a snapshot with user defined parameters? If so how would you do this programatically with the Web service?
2) How do you create a subscription with user defined parameters? Do you do this with a data driven subscription? or do you use another webservice method to do this?
Thanks!
View 3 Replies
View Related
Apr 16, 2007
Hi. I'm evaluating performance features between Enterprise Edition and Standard Edition. I've heard that Database Snapshots are only available in Enterprise Edition, but cannot find anything that proves/disproves this.
Does anyone currently use Database Snapshots in Standard Edition SQL Server 2005? (Not talking about the Replication Database Snapshots.)
Quick script to try on a Standard Edition SQL Server:
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
Thanks!
View 4 Replies
View Related
Aug 21, 2006
Hi
Heads up on TEMP files
I've been running web Synchronisation for over a month. Just today some subscribers received a message warning of an "OS Error" could not retrieve file "dynsnapshotvalidation.tok". Sometimes they also got an error regarding low or no virtual memory. After much investigation it seems that the Web Server hosting the web sync IIS had literally no space left on the system c: drive. It also turns out that the \WindowsTEMP folder contained over 9GB of snapshot files.
It seems that when the Web server collects snapshot files from Data Server to deliver to the subscriber it creates a Temp copy on it's own local system. Unfortunately this temp data is not cleaned up and over time has jammed up the system and caused this failure.
Is there an answer to this or does it involve manually checking the server from time to time to clean-up the TEMP folder?
Has anyone else noticed this?
Thanks
Rab
View 9 Replies
View Related
Mar 15, 2007
We have a statutory requirement to keep 5 years of history, so I've checked the option in the Site Settings page in the Report Manager to "Keep an unlimited number of snapshots in report history". The drives are partitioned as follows:
C: -- OS; 20GB
D: -- Apps; 20GB
E: --Data: 160GB
I want to force the report history snapshots onto the data (E:) drive, but I can't figure out what setting will force them there. Can anyone tell me how to set the path to the reports snapshots folder?
View 1 Replies
View Related
Aug 14, 2006
I have merge replication set up between sql mobile and sql 2005 (just recently upgraded from sql 2000). My publication has around 40 published tables, about half of them are filtered. The other day I noticed that the initial replication to get the entire database was downloading the same tables multiple times. The last test was each table was downloaded 10 times. So I did some research which brought me to the following fix, which was to implement dynamic snapshots.
However, when I try to replicate, it generates a dynamic snapshot, but it fails with the following error€¦
An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199428)
I haven€™t changed the snapshot locations, and if I don€™t use dynamic snapshots it works fine. I€™ve tweaked the permissions (in development) to the point where everyone has full permission on the IIS share, and snapshot folder with no luck.
I would appreciate any advice. Thank you!
View 1 Replies
View Related
Mar 8, 2007
Hello, My question is simply: according to BOL, a DB snapshot taken from a Mirror is read-only. Say I wanted a temporary (disposable) read-write DB created from the snapshot. Is there a way to generate a writable temp DB (that's not tempdb) from the snapshot? Thanks,AK
View 1 Replies
View Related
Apr 24, 2006
Hello Everyone,
I am new to SQL Server Administration/
We have a distributed system running on IP-VPN Network. Every site in the network has one SQL Server 2000 standard server database running on Windows 20003 server.
I am setting up Merge replication on every site where the database is updates are consolidated in the main database.
I have no problem in setting up merge replication, and system is working ok as of now.
But some of remote sites are located in different geographical locations and I want to transfer the Initial snapshots using a removable device, which I want to manually restore at those remote sites, and then enable the merge replication.
I have already read thru many manuals, websites etc to find out how to restore the initial snapshots on the subscriber, but can not find any info.
This is PUSH replication where the distributor is pushing the updates to the different remote subscribers.
My deadline for implementation is approaching and I will very much appreciate any help in this matter.
Ravi Shankar
View 3 Replies
View Related
Sep 5, 2015
Our last CheckDB failed on the 02/09. We were having problems with our SAN then and that got sorted out. The databases that it failed still has the snapshot showing in SQL Management Studio, screenshot exemple below (server name blanked out)
I reran CheckDB on the 04/09 on all the databases and the same databases that failed on the 02/09 failed again. I noticed in the log file, it tried to use the same snapshot file created on  02/09 to run CheckBD
I ran CheckDB manually on the databases that failed "DBCC CheckDB (WSS_Content_ce) with all_errormsgs"Â and did not get any errors back.Â
How do I remove the snapshot databases? We had a problem on 02/09 so the snapshot copy obvisouly has problems. I'd like to remove these snapshots and rerun a CheckDB on all the databases to ensure all is ok.
View 9 Replies
View Related
Apr 20, 2007
Hi,
Is there a way to take a snapshot of a table at precisely the same time on multiple servers and write the table snapshot to another 'work' database? I'm asking in this forum hoping that perhaps SSIS has a built in tool to help with this. I'm wanting to audit table(s) accross seven different servers. In order to do this I want to make sure that I have the table at the same moment in time on all the servers. I plan on using the tablediff utility to then compare each table on each server against one another. If somebody knows a better method to do this please let me know.
Thanks,
Phil
View 1 Replies
View Related
May 2, 2008
I have a report that runs monthly and they want to see the previous 2 months of data. This data is stored no place other than the snapshot of the same report for the previous 2 months. Is there any way I can display the last 2 snapshots/history withing my report? Thanks.
View 1 Replies
View Related
Jun 12, 2015
We have a SSRS report that runs on snapshots. The report contains a textbox that displays the report name using the Global!ReportName built-in field.Â
Recently, we changed the RDL name to something else but this seems to have affected the snapshots too. The older snapshots (generated before the name change) are showing the updated name.
Just wondering if there is some way to say that the snapshot will show the report name based on the time it was generated (same as the ExecutionTime built-in field). Why is there a difference?
View 3 Replies
View Related
