Hello Everyone,
I am running SQL2K with sp3, continuous merge replication, only one
subscriber.
My current setting for Subscription expiration is the default of 7, I
have just recently changed this from the default of 14 days, as our
Subscriber is basically always available.
I am still trying to figure out how to cleanup my merge metadata. From
what I have read and from what I understand ,
sp_mergemetadataretentioncleanup is automatically run, if the agent
running Merge has the Parameter for MetadataRententionCleanup set to 1
I am under the assumption that if I have has the Parameter for
MetadataRententionCleanup set to 1 it should automatically be cleaning
up my metadata. Even though I have it set to 1 it does not appear
like it is actually cleaning out MSmerge_contents or MSmerge_tombstone.
These tables just keep growing.
Now if I had this parameter set to 0 then I would need to use
sp_mergecleanupmetadata to clean up MSmerge_contents or
MSmerge_tombstone.
Would there ever be a reason for running both?
I did go to BOL but I read the following about using
sp_mergecleanupmetadata:
If you want to run sp_mergecleanupmetadata without the subscriptions
being marked for reinitialization:
Stop all updates to the publication and subscription databases.
Unfortunately, I cannot stop all updates as our servers need to be
available 24/7.
Does anyone run this without stopping replication first? And if you do
run it do you have MetadataRententionCleanup set to 0?
If anyone can help I truly would appreciate it.
Thanks,
Barbara
*** Sent via Developersdex http://www.codecomments.com ***
are you using anonymous subscribers? Merge replication does not clean
up the metadata as efficiently with anonymous subscrptions as opposed
to named subscriptions.
|||Thanks Hilary.
Unfortunately no, we do not have any anonymous subscriptions only one
named subscriptions.
Any other ideas?
Barbara
*** Sent via Developersdex http://www.codecomments.com ***
|||I believe I have my answer:
1) sp_mergecleanupmetadata
2) sp_mergemetadataretentioncleanup
The First should be used for topologies with SQL Server 2000 SP1 or less
(which is not my case)
The Second one is the one to control the process manually for SQL Server
> SP1 which is your case and when the MetadataretentionCleanup is set to
0 (not automatic)
There is also a catch though the second sp is executed automatically by
merge Agent at at startup! when MetadataretentionCleanup is set to 1
therefore when in continuous mode it will be called only once.
So I'll need to manually run sp_mergemetadataretentioncleanup or setup a
job that will do it for me on a scheduled basis.
Thanks for your help Hilary.
Barbara
*** Sent via Developersdex http://www.codecomments.com ***
Showing posts with label current. Show all posts
Showing posts with label current. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Mentoring
I graduated several years ago with a CS bachelor degree. My first (and current) job hired me mainly to replace a consultant they had been using to administer and modify their DB.
Basically I taught myself Access to accomplish the above. That was three years ago and looking back the DB is designed and implemented poorly, although it works fine. But I believe our data is going to out grow Access in the next couple years so I thought I'd redo the whole thing, again, using Access as the front end and SQL Server 2005 Express (we are a small company) as the backend. My other motivation is I want to learn SQL Server to expand my future job opportunities. Basically I have no room to grow in my current job. I really have no time table or deadline so I can take my time learining SQL Server.
But I always have doubts as to whether or not I'm doing things right. In an ideal world I would have been hired by a software company and learned the ropes. In reality I was hired by a small, non-technical, company and I learned enough to get the job done.
Basically I want to know if anyone with "real world" experience developing client/server solutions would be interested in guiding me in my effort to redo this DB utilizing SQL Server 2005 Express and Access as the front end. The goal is that I will learn how to use these two technologies in a "real world" environment. I would guess most communication would be done via email. I don't need someone to hold my hand just someone to offer advice as to what techniques to use. An example might be should I use bound controls or ADO? Not how do I use bound controls or ADO.
I feel a bit silly posting this, but I've always wanted someone to learn from and this forum seems to be full of helpful people who have the knowledge I'm seeking.
If you are interested please email me (gwgeller@.yahoo.com). I can provide much more detail if needed.
Thanks,
GGHeck, why settle for one abuser, when you can have dozens of us? I'd suggest that you just plain forget about the "mentor" idea, and simply participate in the forums here.
A mentor is a wonderful thing, and having a mentor is a lot better than not having one. Participating in the forum ratchets that whole experience up several levels, in that you then have a number of people with more experience, scattered around the world so time becomes irrelevant (yes, at least some of us are online almost any hour of almost any day). You can also get more than one opinion, which is almost always a good thing... That way you can consider a few viewpoints and then form your own, rather than simply inheriting the biases and methods of one mentor.
More importantly, you'll learn much more by teaching (helping others) than you possibly can by studying (being helped by a mentor). When people ask questions that you're comfortable answering (even if you need to create a test scenario to validate your initial response), go ahead and answer them. Every time you do this, you'll learn something, and more often than not you'll learn something valuable. If you are wrong (and often even when you're right), someone else will chime in, and you can learn from that too.
There are plenty of opinions around here, and people aren't usually shy about offering them. There are a few moderators that help to police the spam and the really obnoxious folks (we don't mind someone telling you that you're wrong, but they need to be polite about it and had better offer you an explanation instead of just a post saying "you're wrong"), and those moderators are what really set a good forum apart from the free-for-all of Usenet... We actively try to add value to the process by pruning out the garbage and sometimes giving folks a nudge when they've got good ideas but they're hesitant to express them.
-PatP|||Pat,
Thanks for the reply. I debated between just posting my questions and this mentoring idea. It is true what you say about getting multiple perspectives and I considered that. I guess what swayed me towards the mentoring idea was the amount of questions I'd have and the specifics of my own situation. I didn't want to bog the forum down with a lot of posts. I figured a conversation with one person would be more efficient and I wouldn't worry about "stupid" or small questions that I don't feel worthy of a post. So for now I'll see if anyone is willing to do the one on one thing, but if it doesn't work out I'll post away :)|||You have the right attitude.
My advice: Maintain the existing Access system but whenever you have slack time, use it to learn SQL Server and build a new system on that platform. You will grow your skill set, satisfy your growth urges, and in the long run build better systems for the companies that you work for.
You want formal training or a proper mentor, but those are rare luxuries in this industry. If those opportunities arise, take them, but don't expect them. Don't wait on anything else. Take initiative yourself. Ask for help when you need it and learn the technologies that you are interested in.
Good luck!|||Heck, I've long since cornered the market on stupid around here... I'm sure that I'm the undisputed king!
Jump in, nobody bites (well, I guess I do bite, but only when someone asks me to, but that's a whole different story). Dive in, paddle around a bit, have fun. That's pretty much what we're here for!
-PatP|||Mentor tip #1 - go unbound. It will force you to learn ADO and disconnected client server techniques which will stand you in good stead when you move on to .NET (or whatever else).
There are a few moderators that help to police the spam blah blah blah and those moderators are what really set a good forum apart from the free-for-all of Usenet... Aw shucks - thanks Pat :D|||Yeah, but if he's going to go unbound he might as well develop in .Net rather than Access. You lose half the convenience of MSAccess as a front end if you use unbound forms and controls.
Besides, he's trying to learn SQL Server, not front-end development. Learning both at the same time may be a bit much. I'd suggest he stick with his idea of converting the Access database to SQL Server back-end.
But, you should definitely convert the Access front-end to a Microsoft Access Data Project, rather than a classic mdb file.
Going unbound would increase your application performance, but probably not noticably unless you have scores of uses, and since you are currently using straight MS Access I'd guess you have no more than five or six simultaneous users, right?|||That bound control/ADO question was only an example of what questions I might ask. I didn't want ppl thinking I was a total newbie. I think I have the tools I just need to know the best way to use them. After I posted it I knew I should have come up with something different.
My goal wasn't to get into specifics in this post, however Blindman's response relates to some other questions of mine. I want to keep this post limited to the "mentoring" aspect so I'll start another post using Blindman's response. (http://www.dbforums.com/showthread.php?t=1608587)
Thanks to all who have responded so far.|||It could be said that some response boarder on Mental-ing
Like this one
Did you download Express yet?
First thing to do would be to create a data migration plan|||Brett - Do you mean SQL Server 2005 Express? If so yes, I have had it for awhile along with VB 2005 Express. I have completed a couple tutorials on both and actually have start converting some of the Access DB to SQL Server, but only for learning purposes. You are right about a data migration plan but that is beyond the scope of this post.sql
Basically I taught myself Access to accomplish the above. That was three years ago and looking back the DB is designed and implemented poorly, although it works fine. But I believe our data is going to out grow Access in the next couple years so I thought I'd redo the whole thing, again, using Access as the front end and SQL Server 2005 Express (we are a small company) as the backend. My other motivation is I want to learn SQL Server to expand my future job opportunities. Basically I have no room to grow in my current job. I really have no time table or deadline so I can take my time learining SQL Server.
But I always have doubts as to whether or not I'm doing things right. In an ideal world I would have been hired by a software company and learned the ropes. In reality I was hired by a small, non-technical, company and I learned enough to get the job done.
Basically I want to know if anyone with "real world" experience developing client/server solutions would be interested in guiding me in my effort to redo this DB utilizing SQL Server 2005 Express and Access as the front end. The goal is that I will learn how to use these two technologies in a "real world" environment. I would guess most communication would be done via email. I don't need someone to hold my hand just someone to offer advice as to what techniques to use. An example might be should I use bound controls or ADO? Not how do I use bound controls or ADO.
I feel a bit silly posting this, but I've always wanted someone to learn from and this forum seems to be full of helpful people who have the knowledge I'm seeking.
If you are interested please email me (gwgeller@.yahoo.com). I can provide much more detail if needed.
Thanks,
GGHeck, why settle for one abuser, when you can have dozens of us? I'd suggest that you just plain forget about the "mentor" idea, and simply participate in the forums here.
A mentor is a wonderful thing, and having a mentor is a lot better than not having one. Participating in the forum ratchets that whole experience up several levels, in that you then have a number of people with more experience, scattered around the world so time becomes irrelevant (yes, at least some of us are online almost any hour of almost any day). You can also get more than one opinion, which is almost always a good thing... That way you can consider a few viewpoints and then form your own, rather than simply inheriting the biases and methods of one mentor.
More importantly, you'll learn much more by teaching (helping others) than you possibly can by studying (being helped by a mentor). When people ask questions that you're comfortable answering (even if you need to create a test scenario to validate your initial response), go ahead and answer them. Every time you do this, you'll learn something, and more often than not you'll learn something valuable. If you are wrong (and often even when you're right), someone else will chime in, and you can learn from that too.
There are plenty of opinions around here, and people aren't usually shy about offering them. There are a few moderators that help to police the spam and the really obnoxious folks (we don't mind someone telling you that you're wrong, but they need to be polite about it and had better offer you an explanation instead of just a post saying "you're wrong"), and those moderators are what really set a good forum apart from the free-for-all of Usenet... We actively try to add value to the process by pruning out the garbage and sometimes giving folks a nudge when they've got good ideas but they're hesitant to express them.
-PatP|||Pat,
Thanks for the reply. I debated between just posting my questions and this mentoring idea. It is true what you say about getting multiple perspectives and I considered that. I guess what swayed me towards the mentoring idea was the amount of questions I'd have and the specifics of my own situation. I didn't want to bog the forum down with a lot of posts. I figured a conversation with one person would be more efficient and I wouldn't worry about "stupid" or small questions that I don't feel worthy of a post. So for now I'll see if anyone is willing to do the one on one thing, but if it doesn't work out I'll post away :)|||You have the right attitude.
My advice: Maintain the existing Access system but whenever you have slack time, use it to learn SQL Server and build a new system on that platform. You will grow your skill set, satisfy your growth urges, and in the long run build better systems for the companies that you work for.
You want formal training or a proper mentor, but those are rare luxuries in this industry. If those opportunities arise, take them, but don't expect them. Don't wait on anything else. Take initiative yourself. Ask for help when you need it and learn the technologies that you are interested in.
Good luck!|||Heck, I've long since cornered the market on stupid around here... I'm sure that I'm the undisputed king!
Jump in, nobody bites (well, I guess I do bite, but only when someone asks me to, but that's a whole different story). Dive in, paddle around a bit, have fun. That's pretty much what we're here for!
-PatP|||Mentor tip #1 - go unbound. It will force you to learn ADO and disconnected client server techniques which will stand you in good stead when you move on to .NET (or whatever else).
There are a few moderators that help to police the spam blah blah blah and those moderators are what really set a good forum apart from the free-for-all of Usenet... Aw shucks - thanks Pat :D|||Yeah, but if he's going to go unbound he might as well develop in .Net rather than Access. You lose half the convenience of MSAccess as a front end if you use unbound forms and controls.
Besides, he's trying to learn SQL Server, not front-end development. Learning both at the same time may be a bit much. I'd suggest he stick with his idea of converting the Access database to SQL Server back-end.
But, you should definitely convert the Access front-end to a Microsoft Access Data Project, rather than a classic mdb file.
Going unbound would increase your application performance, but probably not noticably unless you have scores of uses, and since you are currently using straight MS Access I'd guess you have no more than five or six simultaneous users, right?|||That bound control/ADO question was only an example of what questions I might ask. I didn't want ppl thinking I was a total newbie. I think I have the tools I just need to know the best way to use them. After I posted it I knew I should have come up with something different.
My goal wasn't to get into specifics in this post, however Blindman's response relates to some other questions of mine. I want to keep this post limited to the "mentoring" aspect so I'll start another post using Blindman's response. (http://www.dbforums.com/showthread.php?t=1608587)
Thanks to all who have responded so far.|||It could be said that some response boarder on Mental-ing
Like this one
Did you download Express yet?
First thing to do would be to create a data migration plan|||Brett - Do you mean SQL Server 2005 Express? If so yes, I have had it for awhile along with VB 2005 Express. I have completed a couple tutorials on both and actually have start converting some of the Access DB to SQL Server, but only for learning purposes. You are right about a data migration plan but that is beyond the scope of this post.sql
Monday, March 12, 2012
memory usage - pages or KB?
I have read conflicting reports that the number in the memory usage column under Current Processes is the number of pages and that it is the number of KB of allocated memory. Which one is correct? Thanks
It's (8 KB) pages. See the documentation for sysprocesses (from which the
information in Enterprise Manager is derived) in BOL.
Where did you read the information that it was KB instead of pages btw?
Jacco Schalkwijk
SQL Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:37D533A5-479A-48E6-A12A-7CF2E83883FB@.microsoft.com...
>I have read conflicting reports that the number in the memory usage column
>under Current Processes is the number of pages and that it is the number of
>KB of allocated memory. Which one is correct? Thanks
|||A couple of people had told me that, and one of them referenced this page:
http://www.microsoft.com/technet/pro.../c03ppcsq.mspx
but more sources say it is the number of pages so I will stick with that. Thanks.
"Jacco Schalkwijk" wrote:
> It's (8 KB) pages. See the documentation for sysprocesses (from which the
> information in Enterprise Manager is derived) in BOL.
> Where did you read the information that it was KB instead of pages btw?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:37D533A5-479A-48E6-A12A-7CF2E83883FB@.microsoft.com...
>
>
It's (8 KB) pages. See the documentation for sysprocesses (from which the
information in Enterprise Manager is derived) in BOL.
Where did you read the information that it was KB instead of pages btw?
Jacco Schalkwijk
SQL Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:37D533A5-479A-48E6-A12A-7CF2E83883FB@.microsoft.com...
>I have read conflicting reports that the number in the memory usage column
>under Current Processes is the number of pages and that it is the number of
>KB of allocated memory. Which one is correct? Thanks
|||A couple of people had told me that, and one of them referenced this page:
http://www.microsoft.com/technet/pro.../c03ppcsq.mspx
but more sources say it is the number of pages so I will stick with that. Thanks.
"Jacco Schalkwijk" wrote:
> It's (8 KB) pages. See the documentation for sysprocesses (from which the
> information in Enterprise Manager is derived) in BOL.
> Where did you read the information that it was KB instead of pages btw?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:37D533A5-479A-48E6-A12A-7CF2E83883FB@.microsoft.com...
>
>
memory usage - pages or KB?
I have read conflicting reports that the number in the memory usage column u
nder Current Processes is the number of pages and that it is the number of K
B of allocated memory. Which one is correct? ThanksIt's (8 KB) pages. See the documentation for sysprocesses (from which the
information in Enterprise Manager is derived) in BOL.
Where did you read the information that it was KB instead of pages btw?
Jacco Schalkwijk
SQL Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:37D533A5-479A-48E6-A12A-7CF2E83883FB@.microsoft.com...
>I have read conflicting reports that the number in the memory usage column
>under Current Processes is the number of pages and that it is the number of
>KB of allocated memory. Which one is correct? Thanks|||A couple of people had told me that, and one of them referenced this page:
http://www.microsoft.com/technet/pr...s/c03ppcsq.mspx
but more sources say it is the number of pages so I will stick with that. Th
anks.
"Jacco Schalkwijk" wrote:
> It's (8 KB) pages. See the documentation for sysprocesses (from which the
> information in Enterprise Manager is derived) in BOL.
> Where did you read the information that it was KB instead of pages btw?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:37D533A5-479A-48E6-A12A-7CF2E83883FB@.microsoft.com...
>
>
nder Current Processes is the number of pages and that it is the number of K
B of allocated memory. Which one is correct? ThanksIt's (8 KB) pages. See the documentation for sysprocesses (from which the
information in Enterprise Manager is derived) in BOL.
Where did you read the information that it was KB instead of pages btw?
Jacco Schalkwijk
SQL Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:37D533A5-479A-48E6-A12A-7CF2E83883FB@.microsoft.com...
>I have read conflicting reports that the number in the memory usage column
>under Current Processes is the number of pages and that it is the number of
>KB of allocated memory. Which one is correct? Thanks|||A couple of people had told me that, and one of them referenced this page:
http://www.microsoft.com/technet/pr...s/c03ppcsq.mspx
but more sources say it is the number of pages so I will stick with that. Th
anks.
"Jacco Schalkwijk" wrote:
> It's (8 KB) pages. See the documentation for sysprocesses (from which the
> information in Enterprise Manager is derived) in BOL.
> Where did you read the information that it was KB instead of pages btw?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:37D533A5-479A-48E6-A12A-7CF2E83883FB@.microsoft.com...
>
>
Wednesday, March 7, 2012
Memory Problem, please help
My SQL server has 3.68 G memory in total. It has no other application on it, the server is not budy.
The current memory usage of SQL server stays at 1.8, in sys.dm_os_memory_clerks, I see
MEMORYCLERK_SQLBUFFERPOOL Default 0 0 400 1636152 1636152
MEMORYCLERK_SQLCLR Default 0 720 1088 101888 22512
MEMORYCLERK_SQLSTORENG Default 0 1480 104 4480 4480
MEMORYCLERK_SQLCLRASSEMBLY Default 0 0 0 4208 4208
OBJECTSTORE_LOCK_MANAGER Lock Manager : Node 0 0 1912 0 4096 4096
The problem is I can not run a CLR function which read a xml file from harddisk, it gives error
.NET Framework execution was aborted by escalation policy because of out of memory.
I tried before, if I restart my SQL server, the function works, but what is the problem? it doesn't look like it is wrong that SQL server allocate 1.8 G memory, and the SQLCLR is only using 24M, it said if there is not enough memory, SQLCLR will upload appdmain to release memory, so even SQLCLR can reused the 24M , it should be able to load my file, which is only 1M/
Any idea?
thanks
It looks like you are running 32-bit SQL Server 2005. If so, you need to make sure that your boot.ini file has the /3GB switch in the boot.ini file (at the root of your C: drive). Then you need to enable AWE in SQL Server 2005. I would also suggest that you give the SQL Server Service Account the "Lock Pages in Memory" right.
This will allow SQL Server to use more than 2GB of memory and will allow SQL to control paging itself.
|||Nice, really helpful, I will try it
thanks!
|||Hi David,
Not trying to be contentious here but AWE is only useful if your server has more than 4GB of RAM which is not the case here.
Do you see any errors in the SQL Server error log?
regards
Jag
|||Hi
Not trying to be contentious here but AWE is only useful if your server has more than 4GB of RAM which is not the case here.
Is there any errors in the SQL Server error log.
regards
Jag
|||The problem you describe is a known bug in SQL Server 2005 SP1 with a hot fix available:
http://support.microsoft.com/kb/928083
However the fix list for SQL Server 2005 SP2 indicates that the fix is included in the new service pack.
-
The KB article suggests that a workaround is to not use the context connection in your CLR code but rather create a standard connection object.
|||thanks for your suggestions.
My server doesn't have 4G memory, I did think about it when I applied the changes.
My SQL server looks ok now, after reboot that day after applied the changes. It consume less than 150M now.
I do see a lot of message in SQL log, like
Date 09/03/2007 4:03:41 PM
Log SQL Server (Current - 09/03/2007 8:44:00 PM)
Source spid23s
Message
The query notification dialog on conversation handle '{C7311ACC-9ACE-DB11-9441-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service 'SqlQueryNotificationService-a1e3d318-87b4-4ae6-84e7-8ef2f04241c1'.</Description></Error>'.
not sure what it affects, but it is not good since it logs a dozen entried every minute, any suggestion what causes that?
thanks
Subscribe to:
Comments (Atom)