Hi There
When i created a snapshot replication publication on one of our servers , i noticed that it now keeps track of the last distributed transaction.
I only plan on doing the snapshot every month or so.
I am concerned that becasue it keeps track of the last LSN that the transaction log will keep growing, is this accurate
Secondly why does snapshot replication keep track of the last distributed transaction, since it does not replicate transaction and will do a complete refresh of data every time it is run
Thanx
SNAPSHOT Replciation - Last distributed transaction LSN ?
Hot Topic
- Limit Report Server Access
- Creating New DB using SMO
- how to set up database diagram in SQL Server Management Studio Express
- Stored proc & transaction log size problems!!!!!
- How to access report parameters in custom code
- Full text thesuarus
- limited number of connections to SQLMobile?
- Error while importing Text file using Import Export Wizard
- FTP No Files Error
- SSMSE Cannot show requested dialog
SQL Server
- How to open a SSIS solution with broken Data Sources in less than 20 minutes?
- Newbie: sql 2005 encryption --- or where do I put that key?
- Stored Procedure being saved in System Stored Procedures
- Reports with OLAP Cubes
- How to use a checkbox for Boolean Report Parameter?
- Task Expression Evalutation Time
- Reports using Dynamic Columns
- Questions about data mining programmality
- Problem dropping replication support
- Creating Reports on SQL Server 2005 Mobile Edition
SNAPSHOT Replciation - Last distributed transaction LSN ?
BasL
Hi Sean, dbcc opentran is not very reliable, I"m trying to figure out which DMVs will get you the equivalent information. What dbcc opentran is doing is grabbing the LSN from data stored internal tables, not the log.
Regarding your question on whether your log will grow, once you drop your transactional publication, your log should truncate just fine. To determine if your log is truncating properly:
1. do an insert/update/delete
2. select count(*) from fn_dblog(null, null) /* this returns all the records in your log for your database. make sure this runs in the context of your published database*/
3. checkpoint /* if you're db recovery is full, then do database backup/log backup instead to clear log */
4. wait a few seconds, then rerun the query against fn_dblog.
If the # of rows returned in #4 is less than #2, then your log truncated fine.
As soon as we figure out the DMV, we'll post that here.
GijsVos
sorry, not sure why I was under the impression you were running SQL 2005.
For SQL 2000 syntax, add the double colon in front - ::fn_dblog.
Regardless, as mentioned above, dbcc opentran is grabbing the LSN from stored internal tables, when you remove a transactional publication, we're probably not resetting that table value. The logreader agent is the only agent that marks transactions for replication, not the snapshot agent.
n_tamil
I see it when i run DBCC OPENTRAN
Yasir Alvi
Should have known that one :: , just did nto even try when i got no results for fn_dblog on BOL.
Seems to be truncating fine, thanx for the help.
FergusLogic
MikeBzz
Thank You very much for the response, one problem
select count(*) from fn_dblog(null, null)
gives an error :
Msg 208, Level 16, State 1, Line 1
Invalid object name 'fn_dblog'
I am in the correct database.
I have searched BOL for fn_dblog with no results
This is SS2000, dont know if that makes any difference
Thanx
Rada
I think i have been in dream land if you are speaking of DMV's then you must be reffereing to 2005, this is on 2000, and similar function i can use for log records
Thanx