impact of polling interval

Hi,
We have a setup with merge replication and polling interval of 10
seconds.
Frequently merge replication agent stops with deadlocks.
On another system we have default polling interval of 60 seconds. this
system does not
have the above issue.
Is it because of low value of polling interval ? what should be the
value of this parameter and waht is the impact of this parameter
change?
rgds,
amit
Basically it means it reads the msmerge_contents, and msmerge_tombstone
tables (among others) every 10 seconds looking for changes. It does sound
like this setting is not optimal for your topology. I would use the default
of 60 seconds.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mitsql" <amit.nimje@.tcs.com> wrote in message
news:1141010206.296301.156060@.j33g2000cwa.googlegr oups.com...
> Hi,
> We have a setup with merge replication and polling interval of 10
> seconds.
> Frequently merge replication agent stops with deadlocks.
> On another system we have default polling interval of 60 seconds. this
> system does not
> have the above issue.
> Is it because of low value of polling interval ? what should be the
> value of this parameter and waht is the impact of this parameter
> change?
> rgds,
> amit
>
|||Thanks a lot for the reply can you tell me if setting expiry as 14 days
can also have similar impact?

Images - Store in a database or file system?

I have recently designed and built my first database using SQL server 2005 express. I have included an image (BLOB) column in one of the database tables. This is a bad idea according to some experts, and some say it is OK!

I am currently carrying out a trial with just 3 pictures via Visual Basic 2005 express forms, and there is no problem so far as the images are displayed for each record. But I anticipate between 300 - 1000 images for the table, and this could pose real problems for SQL server 2005 express and Visual Basic 2005 express, I guess.

I have just been reading that the cost of storing large images in the database is too high! I have also read it's better to store images (BLOB) into the file system because it is cheaper to store them no matter how many there are.

But the question is how I can reference an image in this path: C:\Picture\Product\Grocery\00052745.jpg in the database table, so that when I select a record Visual Basic 2005 forms the image is displayed accordingly, similar as when stored directly in the database table? Your help very much appreciated.

hi Paul,

PaulN wrote:

I have recently designed and built my first database using SQL server 2005 express. I have included an image (BLOB) column in one of the database tables. This is a bad idea according to some experts, and some say it is OK!

yes, this often result in "religious wars" bewteen parts.. I'm with the "all in the db" part , with just one caveat (regarding MSDE and SQLExpress), depending on the "limited" database maximum size of 2gb for MSDE and 4gb for SQLExpress.. this obviously could "limit" the quantity and size of the lobs stored in the database, thus penalizing an architecture of a project..

well.. 4gb are not that "limited" if you store little jpges, but could eventually become a real limit if you have to store a "tipycal" ripped movie of about 700mb..

I am currently carrying out a trial with just 3 pictures via Visual Basic 2005 express forms, and there is no problem so far as the images are displayed for each record. But I anticipate between 300 - 1000 images for the table, and this could pose real problems for SQL server 2005 express and Visual Basic 2005 express, I guess.

actually that's not that bad... it's real a "limited" quantity you should not have problem with..

I have just been reading that the cost of storing large images in the database is too high! I have also read it's better to store images (BLOB) into the file system because it is cheaper to store them no matter how many there are.

this is true.. standard disk space is absolutely cheap. but you incour in other kind of problems.. see later..

But the question is how I can reference an image in this path: C:\Picture\Product\Grocery\00052745.jpg in the database table, so that when I select a record Visual Basic 2005 forms the image is displayed accordingly, similar as when stored directly in the database table? Your help very much appreciated.

thi is the problem with "external" resources... I mean that it's a traditional scenario for this kind of stuff, but this includes an important access tradeoff.. if you have "all your things" within the database, you only need to query it for returning the desired output (including the lob's data).. all is incapsulated within the database... you do not have "particular" security problems as your database permissions schema already includes lobs.. on the contrary, if your "table" just stores the "path" to the actual lob jpeg file, that file should be available among the whole lan (if your scenario involves remote connections), meaning it must reside on a "share" where all potential (remote) users can access it, with read-only and/or read/write permissions on it (depending on the nature) in order to allow both access (read) and modifications (write)...

more, all the automated tasks as you already pointed out are out of scope, you have to load pictures from a stream loaded with the lob's data from the (remote) folder... this last part is not that bad (streaming data), but all the remaining part is (accessing physical NTFS permissions, share, and disk subsystem), IMVHO...

I do prefer to streams lob's data out of a (say) datareader object anyway from the database not directly relying on wizard's bindings as usually data have to be "purged/checked" before presentation (but this is not mandatory, it depends on your design and the actual prj), but all remains "incapsulated" in the db... just a few stored procedures to read/write data in and out and the job is done...

regards|||

I agree with Andrea.

For small photos, store them in the database. You won't 'lose' photos quite as easily as if they are just 'floating' around the file system somewhere. And backups are much easier.

However, if I were dealing with something like a Contract Photo Agency, with hundreds of thousands of photos, in high resolution (meaning multi-MB photo/file sizes), I would investigate something like 'FileNET', or other of the content management 'systems'.

|||Another good ressource is: To blob or not to blob: http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Image Column Data and Backup

I have a table (SQL Server 2000) that stores icon files in an Image
field. I have no problem storing and retrieving the images on my system.
When I back up the database however the rows in that table do not seem
to get backed up. In other words when I restore the database on another
server there are no rows in that one table.
I have used the sp_tableoption to turn "in row text" on for the table
etc. Anyone have a clue what I might be doing wrong? This table will
eventually need to be recreated at a customer site with the data intact
and I was planning on just backing the database up and then restoring it.
Bob PorterA restored database will be exactly like the original, less uncommitted
transactions. A common cause of the problem you describe is that multiple
backups exist in the same backup file and the first (oldest) is restored by
default. You can list the backup file contents with RESTORE HEADERONLY:
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyDatbase.bak'
You can then specify the desired backup with the FILE specification:
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatbase.bak'
FILE=2
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Porter" <rhysliam@.noemail.nospam> wrote in message
news:ud2nToCbFHA.2128@.TK2MSFTNGP15.phx.gbl...
>I have a table (SQL Server 2000) that stores icon files in an Image field.
>I have no problem storing and retrieving the images on my system. When I
>back up the database however the rows in that table do not seem to get
>backed up. In other words when I restore the database on another server
>there are no rows in that one table.
> I have used the sp_tableoption to turn "in row text" on for the table etc.
> Anyone have a clue what I might be doing wrong? This table will eventually
> need to be recreated at a customer site with the data intact and I was
> planning on just backing the database up and then restoring it.
> Bob Porter|||Dan Guzman wrote:
> A restored database will be exactly like the original, less uncommitted
> transactions. A common cause of the problem you describe is that multiple
> backups exist in the same backup file and the first (oldest) is restored b
y
> default. You can list the backup file contents with RESTORE HEADERONLY:
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyDatbase.bak'
> You can then specify the desired backup with the FILE specification:
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\Backups\MyDatbase.bak'
> FILE=2
>
It did! Thanks, sorry for the newbie question I knew better... sigh...|||No problem. You're certainly not the first to run into this :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Porter" <rhysliam@.noemail.nospam> wrote in message
news:%23J0MYlDbFHA.464@.TK2MSFTNGP15.phx.gbl...
> Dan Guzman wrote:
> It did! Thanks, sorry for the newbie question I knew better... sigh...

ignoring keys while Truncating tables

Hello everyone.

I'm working with a customers business application which is developed in MSSQL. The system has over 170 tables and there is no documentation by those who created it. Now the problem is that I have to write script that deletes all the data in all the tables but since there are foreign keys defined in the tables I can't delete the data. Ofcourse I can figure it out eventually by testing back and forth in which order I have to delete the data in the tables but since there are over 170 tables that could take a very long time.

Does anyone now how I can solve this?? is there for examaple a way to make SQL server to ignore the foreign key lookup? What can I do?? is there any way which I can see in what order I should delete the data in the tables?

appritiate any help or comments.

Thanks.

\Homan1. script out your deletes and run the script 170 times, eventualy you will clear out all the tables.

2. use Enterprise Manager to generate a diagram of all the tables in the database. This would tell you the exact order you would need to follow to get everything deleted.

3. in Enterprise Manager and Query Analyzer you can look up the dpendencies of any object. In EM right click on any object, select all tasks, dependencies, in QA press "F8" to show the object browser, select and object drill down till you see dependencies.

4. use sp_depends on all 170 tables/view to figure out the parents and or child relationships.|||If you are going to have to reload the database you are going to want to know your foreign keys.|||For reference of cascade delete refer to SQL TEam (http://www.sqlteam.com/item.asp?ItemID=8595) link.

Refer to this Code (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=641&lngWId=5) and modify to accomplish the task.

HTH|||disable your fk checking before deleting data, and re-enable them after.

Ignoring Errors

I have some "best effort" jobs that try at various times to walk over
missed records from one system to another about 5 times. After that,
the data becomes stale and is no longer usable, so I have another
process that comes after that to investigate the problem.
The problem I'm having is that when my best effort jobs ran in MS SQL
Server, they would run and then bomb out immediately. I want it instead
to just continue on error.
Meanwhile, I then switched the jobs to osql.exe and Task Scheduler this
weekend. The results were exactly the same -- the job dies on error.
Can you help me figure out a way either for osql.exe to continue on
error, or for me to ignore all stored procedure errors and just keep on
continuing?
I'm used to VB's "on error resume next" and unfortunately I don't see
one in MS SQL Server. This is absurd!
Check out the error handling sections of Erland's web site:
http://www.sommarskog.se/
Andrew J. Kelly SQL MVP
<googlemike@.hotpop.com> wrote in message
news:1113236973.134465.132330@.l41g2000cwc.googlegr oups.com...
>I have some "best effort" jobs that try at various times to walk over
> missed records from one system to another about 5 times. After that,
> the data becomes stale and is no longer usable, so I have another
> process that comes after that to investigate the problem.
> The problem I'm having is that when my best effort jobs ran in MS SQL
> Server, they would run and then bomb out immediately. I want it instead
> to just continue on error.
> Meanwhile, I then switched the jobs to osql.exe and Task Scheduler this
> weekend. The results were exactly the same -- the job dies on error.
> Can you help me figure out a way either for osql.exe to continue on
> error, or for me to ignore all stored procedure errors and just keep on
> continuing?
> I'm used to VB's "on error resume next" and unfortunately I don't see
> one in MS SQL Server. This is absurd!
>
|||OSQL is better than Agent TSQL as OSQL doesn't terminate on errors. However, for some errors, *SQL
Server* terminates the batch. I suggest you check out the articles on error handling at
www.sommarskog.se.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<googlemike@.hotpop.com> wrote in message
news:1113236973.134465.132330@.l41g2000cwc.googlegr oups.com...
>I have some "best effort" jobs that try at various times to walk over
> missed records from one system to another about 5 times. After that,
> the data becomes stale and is no longer usable, so I have another
> process that comes after that to investigate the problem.
> The problem I'm having is that when my best effort jobs ran in MS SQL
> Server, they would run and then bomb out immediately. I want it instead
> to just continue on error.
> Meanwhile, I then switched the jobs to osql.exe and Task Scheduler this
> weekend. The results were exactly the same -- the job dies on error.
> Can you help me figure out a way either for osql.exe to continue on
> error, or for me to ignore all stored procedure errors and just keep on
> continuing?
> I'm used to VB's "on error resume next" and unfortunately I don't see
> one in MS SQL Server. This is absurd!
>