Maintenance After Snapshot?

May 16, 2008

We have a training server and I've had a request that after each training session, we have the ability to quickly roll the training server back to its previous state so that the next group of people can be trained with the same examples.

Doing a restore requires that the maintenance (defrag, warm cache, etc..) also runs after that before the server performs fast enough.

So I was thinking of snapshots as an alternative. When you roll back to a snapshot, does that invalidate what's in the cache or have any adverse effect on query plans, stats, or indexes?

View 1 Replies


ADVERTISEMENT

Problem When Applying A Snapshot When Tables Have Been Updated During Snapshot Generation

Jun 20, 2007

Hi

I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.



Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.



The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.



I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.



All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.

View 1 Replies View Related

Maintenance Plan Wizard Vs Tsql Maintenance

Aug 17, 2007

Hello,

I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.

Thanks.

View 1 Replies View Related

Maintenance Commands Affect On Log File / Log File Maintenance Without Log File Backups?

Jun 18, 2015

I am testing some maintenance tasks sql commands such as index rebuild, index reorg, update statistics and db integrity check on a SQL Server 2014 Database. This is a new non-production vendor database (DB Size 500 GBs, Log Size 25 GBs) which eventually will be created in production. Currently, it is in full recovery model and without log backups. The database has a whole lot of indexes. I am just trying to rebuild and reorganize all the indexes (that need it), in addition to trying to get an idea of how long these maintenance task will take and the space needed in the log file to complete these tasks/commands. I would like to execute these tasks manually (the first time) to gather the duration and space required information. Eventually, I would probably schedule a weekly job to perform this maintenance.

I ran the index rebuild task on the database and noticed that the log file grew by over 50 GBs. I killed the process and truncated and shrunk the log file back down.

1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?

2. Does Indexs Reorg have less impact on log file then Index Rebuild?

3. Should a truncate log and shrink log file be performed after these maintenance commands?

4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?

I have read and understand that shrinking is not good for the database (could lead to more fragmentation and more data file growth when data is added) and I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.

Since this is a non-production database maybe I should set the recovery model to simple, run the maintenance commands and leave the database in simple recovery model unless the vendor needs it in full recovery model for some unknown reason.

5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?

View 3 Replies View Related

What Is A Snapshot?

Apr 17, 2007

What is a snapshot from replication point of view.

------------------------
I think, therefore I am - Rene Descartes

View 6 Replies View Related

Snapshot Is Not Available

Aug 28, 2006

Dear friends

I am a new fellow to replication.What is the problem I am getting is I am not able to create snapShot.Whenever I start Snapshot Agent it is going for half an hour and it will stuck in the case of one view saying like that 'failed to process bulk copy of data from dbo.syncobj_03x666*'.If I am publishing tables only then also I am getting same error.This view created by system.In my case publiher only working as a distributor.I created a SQL username which have access in subscriber & Publisher.Preferably I will be happy if someone suggest what and all are the primary criterias we have to keep in mind while doing Replication

Thanks in Advance

Filson














View 5 Replies View Related

Snapshot Error

Apr 9, 2002

I get the following error message in the job history for
synchronization between two SQL 7 machines across the
network.

The process could not read file 'acct.sch' due to OS error
1231.

Is there something to fix this? It has been running fine
for almost 2 years and suddenly quit. There is a firewall
on both sides, if that helps.

TIA,
Cami

View 3 Replies View Related

Snapshot Replication - Help!!

May 7, 2002

The database will grow to 40GB in a short while and I intend to schedule replication every hour. Can this not be done using snapshot replication??

Subject:
From:
Date: Snapshot Replication - Help!! (reply)
MAk (mak_999@yahoo.com)
5/7/2002 12:02:08 PM

Create jobs to copy database and restore database in destination servers


------------
Robert at 5/7/2002 11:00:30 AM

Yes and I would rather not use dts to accomplish this task.

------------
Ray Miao at 5/7/2002 10:02:15 AM

Do you have direct network connection to remote server? Did you try dts?


------------
Robert at 5/7/2002 9:08:06 AM

I've been trying to replicate a database to an off site server using snapshot replication. It is scheduled to run every hour but I've noticed when data is changed at the source it never gets replicated to the destination. Does anyone know why?? I can't use transactional replication beause not all the tables have primary keys and they can't be added due to code. Some tables have id colunms and have been created with the Not for Replication option on the subscriber. Any help will be appreciated.

Thanks


Robert

View 1 Replies View Related

Snapshot Replication

Aug 27, 2001

I can set up snapshot replication for those tables without foreign key constraints. But if there are foreign keys in the table, there will be error
message indicating that this object can not be dropped because it is referenced by ....

Do you have any remedy for that? Thanks

View 1 Replies View Related

Snapshot - Dependency

Dec 29, 2000

Hi,

I've problem in replicating data thru SNAPSHOT as I've the tables with Foreign Keys at subscriber end. The Truncate table is not working because these tables
were referenced by a FK. Even for recreating table during snapshot is also same problem. Any suggestions?

Thanks

TT

View 1 Replies View Related

Snapshot Replication

Dec 28, 2000

2 questions:

1) In snapshot replication, can the subsciber send info back to the publisher (even in a manual process)

2) In snapshot replication, do we need a distributor set up between the publisher and subscriber if there will only be a single subscriber, or can we write directly to it?

Thanks so much for any and all help!

View 1 Replies View Related

PK,FK,IDentity - Snapshot,

Jan 11, 2001

Hi,

I am replicating all tables in the DB. the tables have PKs,FKs and identity columns. While truncating the data from the tables in subscriber getting
the constraint errors. Please suggest how to get rid of this error.

Thanks

Sam

View 1 Replies View Related

PK,FK,IDentity - Snapshot,

Jan 11, 2001

Hi,

I am replicating all tables in the DB. the tables have PKs,FKs and identity columns. While truncating the data from the tables in subscriber getting
the constraint errors. Please suggest how to get rid of this error.

Thanks

Sam

View 1 Replies View Related

Obsolete Snapshot

Oct 17, 2002

Hi everybody,

We have merge replication between two servers with Sql Server 2000 Service Pack 2, and the Merge agent display the follow message :

The snapshot for this publication has become obsolete. The snapshot agent needs to be run again before the subscription can be synchronized.

But i have changes at the suscriber, How can I do to Merge the last changes if the merge agentes is stopped.

I appreciate any help.

thanks

View 1 Replies View Related

Replication Through Wan Without Snapshot

Jan 13, 2003

Hi Guys,

We have a production server in East Coast (SQL Server 2000 SP2 - Database size is around 30 Gig). We have a reporting server is the West Coast. We need to replicate (transactional replication every one hour) from East coast to West coast. Is there any way that I can take a backup and restore upto the last transaction backup and then start replication agent on the production (by saying schema and data already exist). Basically we don't wan't to snapshot using FTP or bcp through WAN because it is going to be very slow.

If this is possible, will there be any validation problem.


Please help.

Thanks,
Anu

View 3 Replies View Related

Snapshot Replication

Apr 13, 2006

Suppose i want to replicate data from server A to server B
I am using snaphot replication.I did the snapshot replication for the first time
and server B got a snapshot of server A.

Next time i run snapshot i want the incremental data to be replicated and not all..Is this possible in snapshot replication? If not which type of replication should i use?

View 2 Replies View Related

Snapshot Problem

Jun 5, 2008

Hello everyone

I am learning about snapshots at the moment and i dont know how to incorporate the filegroup within the sytax


at the moment i use

create snapshot snapshotName
on (
Name='Filename_data',
Filename=@'osFIlePath')

as snapshot of DatabaseName

i get an error for filegroup, can someone tell me what im doing wrong please.

Regards

Rob Dineen

View 13 Replies View Related

Help With Creating A Snapshot Please

Jan 23, 2008

hello everyone

i am going through the self kit book to get my MCTS and i am stuck on
one of the practices. Creating a snapshot

I typed in

create database snapshottest
on
(
Name= 'Adventureworks_data',
Filename = 'C:program filesMicrosoft SQL servermssql.1mssqldatasnapshottest.ds')

as snapshot of adventureworks
i get the error message

msg 5127 level 16 state 1 line 1
all files must be specified for database snapshot creation
missing file "AdventureworksFT_data".


i have not stated AdventureworksFT_data

i stated Adventureworks_data

why is it asking for AdventureworksFT_data

can someone please tell me if the syntax is wrong or is there something missing

Regards

Rob

View 7 Replies View Related

Database Snapshot

Jan 31, 2008

Hi...
Why is it that I encounter an error during execution of database snapshot i SQL Server 2005 Standard Edition. It says that database snapshot is not supported under standard versin. Is this true?

Thanks.

View 2 Replies View Related

Snapshot Replication

Jul 23, 2005

Hi All,I have set up a snapshot replication, and schedule it to run everynight. The snapshot run successfully, and data get replicated to thesubscribed server. However, data do not get transfer as the second dayand there after. I check the job history, the job (distribution) runsuccessfully. I start the snapshot agent again, then data gettransferred. I can schedule the snapshot agent to run every night, butthis is just not the way it's supposed to be. Is there anyone out therecan give me some help. Thanks.

View 1 Replies View Related

Snapshot Query

Sep 3, 2007

HiI am relatively new to databases. I would like to be able to run aquery that returns the t_no, b_no, status and cpu for the latestrecord for each unique combination of (t_no, b_no and cpu) before agiven point in time (say @snap_time). d_no is an autoincrementingprimary key.d_no t_no b_no status cpu update_time1 500 3 0 1 3:012 501 3 1 1 3:023 501 3 0 1 3:034 502 3 1 1 3:045 503 3 1 1 3:056 500 3 1 1 3:10In this example, if @snap_time = '3:15' the query would return the(t_no, b_no, status, cpu) for records 3, 4, 5, 6 because 1 issuperceded by 6 (which has the same t_no, b_no, cpu combination butoccurs later) and 2 is superceded by 3.If @snap_time = '3:03', the query would return records 1 and 3 becauserecords 4, 5 and 6 were created at a later update_time and record 3supercedes record 2.The query:SELECT MAX(update_time), t_no, b_no, cpu, statusFROM my_tableWHERE (time <= @snapTime)GROUP BY t_no, b_no, cpureturns an error:Column 'dbo.my_table.status' is invalid in the select list because itis not contained in either an aggregate function or the GROUP BYclause.And even if this did work, ideally I would like to suppress theupdate_time field from appearing.How could I do such a query and is it even possible without usingsomething more sophisticated than a select query?Many thanksJon

View 1 Replies View Related

Storage Snapshot

Jul 20, 2005

hello group,is it possible to do a storage snapshot of a running ms-sql databasewithout losing transactions?What tasks must be done before such a snapshot.thanks in advance,Bernhard

View 7 Replies View Related

Snapshot Replication

Sep 28, 2006

Hi,

I am using SQL SERVER 2005 snapshot wizard to create snapshot. But as soon as i create a snapshot it takes away all the indexes and constraint for the tabels on the subscription end although i have this indexes and constraint on Publisher side.

Can someone help me in finding if their is some setting to create a snapshot without losing indexes and constraints or if their is some other way to do this.

Thanks,

Prashant

View 5 Replies View Related

Database Snapshot

Dec 18, 2006

Need some help, i have some database snapshots files provided from an external source. Need to be able to understand how i get them back into a database format if possible.

Files for example are table1.bcp, table2.bcp with als a file called scheme.sql which sets up these tables in sql but does not populate them. Nothing else was provided except a .vcd file which i dont know whtat its for.

any clues?

Dan

View 6 Replies View Related

Subreport Snapshot

Aug 23, 2007

I get an error "Error: Subreport could not be shown." when I set subreport rendering from snapshot. When I render subreport with the most recent data everything works fine.

Does anybody know how to use snapshots for subreport? Thanks in advance.

View 1 Replies View Related

Snapshot Location

Apr 10, 2007

Can anyone tell me where the data for a snapshot is kept at?

R

View 1 Replies View Related

When A Snapshot Run Last Time?

Aug 26, 2007

How can I, with TSQL script, know when a snapshot run last time?

Thank You.

View 3 Replies View Related

Snapshot And History

Aug 31, 2007

Hi Everyone

Im a newbie on this but I have created a couple of reports and am running sql server 2005 and have downloaded all the updates. The problem i have is that when i view the report i have no History Tab and it is not listed in properties and cannot create snapshots even in sql man. studio. I have set the snapsots to unlimited in site settings. what am i doing wrong that is so obvious.

cheers
Barry

View 8 Replies View Related

Transactions Or A Snapshot?

Jan 28, 2008

I had it all set up to take a snapshot of my OLAP as the first step in my package and restore from it on error...then I started to think.....isn't that what a transaction is supposed to do?

Does anyone have any experience of the pros and cons of either?

Thanks.

View 7 Replies View Related

History Id And SnapShot Id

Jan 11, 2007

Hi,

Wanted to clarify some doubts about Report History and Snapshot.

1. What is the difference between the snapshot id and history id. Report Snapshots are stored in the report history.

When I call CreateReportHistorySnapshot method programmatically, the method returns a history Id. Is this id different from the snapshot id (report snapshot which is stored in the history). If the id's are different how can I get the snapshot id ?

2. The History id is a date-time stamp of the snapshot. This string is used as a unique identifier for the historical snapshot. It contains the time portion till seconds(not milliseconds). Hence if I generate the next snapshot in the same second it gets overridden.

Is there any way to create more snapshots in the same second ?

Is there any way to change the algorithm used to generate history ids ? eg : Can I use some unique id generator and get history id using this algorithm ?



Thanks in advance,

View 1 Replies View Related

While Creating A Snapshot...

Apr 20, 2007

Hello,





Here's the idea: There is a central SQL Cluster Server and there are branches in different cities. I'll replicate databases from branch SQL Servers to the central SQL Server. Every city will have a database in the central SQL Server.



While i was creating publication for replication, i get the following errors from different (6-7) tables among 289 articles.



----------------------------------------------------

2007-04-20 12:00:45.18 [46%] The replication agent had encountered an exception.
2007-04-20 12:00:45.18 Source: Replication
2007-04-20 12:00:45.18 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-04-20 12:00:45.18 Exception Message: Incorrect syntax near the keyword 'percent'.
2007-04-20 12:00:45.18 Must declare the scalar variable "@metadata_type".
2007-04-20 12:00:45.18 Must declare the scalar variable "@compatlevel".
2007-04-20 12:00:45.18 Must declare the scalar variable "@lineage_old".
2007-04-20 12:00:45.18 Must declare the scalar variable "@lineage_old".
2007-04-20 12:00:45.18 Must declare the scalar variable "@compatlevel".
2007-04-20 12:00:45.18 Must declare the scalar variable "@p8".
2007-04-20 12:00:45.18 Failed to generate merge replication stored procedures for article 'mf_pro_processeditem_financial'.
2007-04-20 12:00:45.18 Message Code: 156
2007-04-20 12:00:45.18

----------------------------------------------------



These articles are belong to a software which is developed by another company.

Any ideas?



SQL Version : Enterprise 2005 with SP2

Replication type: Merge





Thanks,

Eko

View 6 Replies View Related

Replication On Snapshot

Apr 3, 2007

Hi is that possible to configure replication in the following situation.

server A is built by snap of server B. Because i am able to create publisher on server B but i am unable to create same on server A.

could any one explain the situation, why it is happening this way.

any help is appriciated.

if you are curious what kind of error i got.

TITLE: Publisher Properties

------------------------------

An error occurred connecting to Publisher 'SERVER A'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubshrPropertiesErrorSR&EvtID=CantConnectToPublisher&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVER B'. (Replication.Utilities)

------------------------------

BUTTONS:

OK

View 1 Replies View Related

Snapshot Agent,

Sep 24, 2006

Dear Colleagues,

I am using Windows Server 2003 RT, and SQL Server 2005.

When Replication is performed with SQL Server Mobile. All 3 taskes i.e. Publisher creation, Article Publish, Snapshot agen starting. All are ok.

However after replication is done when checking the Status of the Snapshot Agent it is not Running, and I cannot make it Run as well.

Please advice me as to what could possibily be wrong with it, what can I do.

Thankx.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved