March 31, 2010 at 9:49 am
Wow, and I'm blaming all of it on the power failure...
I may not have communicated it correctly, #2 is setup by manufacturer's DB creation utility, so #5 idealy should match it..
March 31, 2010 at 9:55 am
Alex V (3/31/2010)
Gail, there are no objects on either new or production servers under "replication" besides empty folders "Local Subscriprions" (on both) and "Local Publications" (only on a new 2008 server).
One more question, is this running on SQL Express, or a higher edition of SQL Server? I know you mentioned express....
p.s. I see this problem about once a fortnight on average on various forums.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2010 at 9:59 am
SQL 2005 Express 32 bit currently on production.
SQL 2008 Standard x64 on a new "replacement" production server, where I'm trying to migrate the databases from 2005 express. Only #5 is a problem one.
Thanks,
Alex
March 31, 2010 at 10:33 am
Ok, good.
This probably looks a little strange, but does generally fix the problem.
Create a transactional replication publication on that database and publish a single table. Doesn't matter which table. Wizard is started by right-clicking on 'Local publications' and selecting 'New publication'. Make sure that you select transactional when the option comes up.
Scroll further down object explorer and expand out SQL Agent. Find the log reader job (check the properties of each job until you find the one with a category of REPL-LogReader) and stop it. (right click on the job-> Stop job)
Open a query window and change context to the database with the problem (database drop down at the top of management studio)
Run EXEC sp_repldone
Go back to object explorer and drop the publication that you just created.
Run DBCC OPENTRAN and post the output. The reference to distributed and non-distributed LSNs should be gone.
For anyone following along and curious about the roundabout route, it's possible (somehow) to get a DB that's 'partially' replicated. There are remnants of replication in the log, but SQL thinks that the DB is not replicated, so running any replication stored proc results in an error, typically like 'Database X is not replicated' Hence creating a publication so that the DB is fully replicated and then drop the publication so that the database is 'unreplicated' correctly and the remnants in the log are removed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2010 at 11:04 am
Gail,
On a production server, got a message: "There are no publications to which you can subscribe, either because this server has no publications or because you do not have sufficient privileges to access it"
I'm logged in as a member of Administrators group to windows, Windows authentication to SQL management and sysadmin and serveradmin are on for my user in SQL.
On a new server, replication was not installed. Trying to install it failed, MS now wants me to install a SQL2008 SP1. Downloading.
March 31, 2010 at 11:59 am
GilaMonster (3/31/2010)
Ok, good.Go back to object explorer and drop the publication that you just created.
On a new server, after adding replication, could not stop the agent job, since "the job was suspended".
Continued on, got up to that point of dropping the replication. Got an error:
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Changed database context to 'DirectorySQL'. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=15517&LinkId=20476
------------------------------
Server Name: test\test
Error Number: 15517
Severity: 16
State: 1
Procedure: sp_replcmds
Line Number: 1
Run DBCC OPENTRAN with this result:
No active open transactions.
Good news, I take it.
Now, if I could only get rid of that replication job...
March 31, 2010 at 1:08 pm
OK. Figured out the DBO error, changed the ownership of the database and deleted replication publication.
Do I need to delete all the agent jobs related to replication manually?
Run query on sys.databases, log_resuse is now set to NOTHING on all databases.
Deleted second log file.
Shrunk the main log to 1mb.
Set it to increase by 1 mb with a 500mb limit.
Anything else I'm forgetting?
Thanks again,
Alex
March 31, 2010 at 1:54 pm
Alex V (3/31/2010)
OK. Figured out the DBO error, changed the ownership of the database and deleted replication publication.Do I need to delete all the agent jobs related to replication manually?
They should have been deleted when you dropped the replication. If not, should be ok to delete them manually.
You can also disable distribution, right click the replication folder, configure distribution. I think the options are there to disable. If not I'll go research. I don't actually have an instance that's got replication configured at the moment.
Run query on sys.databases, log_resuse is now set to NOTHING on all databases.
Deleted second log file.
Shrunk the main log to 1mb.
Set it to increase by 1 mb with a 500mb limit.
Anything else I'm forgetting?
Grow the log a bit, to what it should be for regular usage. 1 MB is way too small for the log for an active database. Reasonable rule-of-thumb is 1.5* the size of the largest table (to facilitate index rebuilds in full recovery). Set the autogrow to a reasonable figure. Remove the max size unless you are absolutely, totally, 100% certain that the log will never need to be that big.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2010 at 2:13 pm
GilaMonster (3/31/2010)
You can also disable distribution, right click the replication folder, configure distribution. I think the options are there to disable. If not I'll go research. I don't actually have an instance that's got replication configured at the moment.
Looked and deleted the database from the list of publishers.
Grow the log a bit, to what it should be for regular usage. 1 MB is way too small for the log for an active database. Reasonable rule-of-thumb is 1.5* the size of the largest table (to facilitate index rebuilds in full recovery). Set the autogrow to a reasonable figure. Remove the max size unless you are absolutely, totally, 100% certain that the log will never need to be that big.
This database is set for simple recovery. Currently, it is under 90mb in size. What would be a good size to set the log for?
I'm also planning to do a daily local backup.
And a last question - where can I send you flowers?
March 31, 2010 at 2:26 pm
Alex V (3/31/2010)
Looked and deleted the database from the list of publishers.
Should be possible to disable distribution completely. I'll investigate for you tomorrow.
This database is set for simple recovery. Currently, it is under 90mb in size. What would be a good size to set the log for?
Thumb-suck (ie near-complete guess), log at 10MB, autogrow at 5MB for now. See if it grows. I assume that the ability to restore to point of failure 9in case of a failure) is not required?
I'm also planning to do a daily local backup.
Good. Backups are nice things to have
And a last question - where can I send you flowers?
😀 No need for anything like that.
I'm in South Africa if it matters
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2010 at 2:54 pm
GilaMonster (3/31/2010)
Should be possible to disable distribution completely. I'll investigate for you tomorrow.
Thank you. I really do appreciate it. Tomorrow I'll try to go through the same steps with a live DB backup... hope I can repeat the sucess )
Thumb-suck (ie near-complete guess), log at 10MB, autogrow at 5MB for now. See if it grows. I assume that the ability to restore to point of failure 9in case of a failure) is not required?
Soft. vendor had an initial log size set for 1mb with 10% autogrowth to 2gb.
Since this database is 10 times bigger then a newly created one, I followed your thumb and did 10 with growth by 5mb.
Restoring to a point of falure is nice. But in our case we'll be OK if we are thrown back a day in the worst case scenario (provided we have a daily backup)
😀 No need for anything like that.
I'm in South Africa if it matters
Gosh.. so far. No chance for a personal visit till retirement ))
I heard it is beautiful over there.
But I still would like to thank you somehow besides the words. Just PM me with some kind of address I can use to reach you, and I'll test SA mail service )))
I any case, here is a big THANK YOU!
Alex
Viewing 11 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply