hi all,
i have a snapshot replication running on multiple publisher singel subscriber running on sql2k. i'm frequently encountering these error.
the subscription has been marked inactive and must be reinitialized.
no synch
i've set the subcription to never expire in the publications property tab.
thanks
Greetings,
We periodically get the following message in our three server peer-to-peer transactional replication environment:
"One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error."
What causes a subscription to be marked inactive? What properties or settings can be adjusted to minimize or eliminate this occurrence?
Everthing seems to be running OK and then we get the message. It afflicts the higest change volume publication on our slowest overseas server.
Thanks,
BCB
I have a snapshot & a transcation publication on Server A, and a pull subscription for each on Server B.
The distribution agent on ServerA has the error:
"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."
Any idea what caused this ..... Everything was fine for a week or so.
Does anyone know of any good books or publications on Replication ? I've got some DBA books that cover the basics, but I'd like something that helps explain the oddball situations.
SQL 2K
We have 350 tables in our database, I want to drop/delete those table(s) that are inactive. Is there a way to check which tables are not active, so I could drop/delete that table(s).
View 1 Replies View RelatedHere are two tables. How do i create an SP for INACTIVE delete and how do i use the UNIQUE constraint to avoid duplication of the NAME columns(logingroup_name and login_name) in these tables?
--Creating Table ref_LoginGroup:-
create table ref_LoginGroup
(
logingroupid tinyint identity(1,1) constraint pklogingroupid primary key clustered,
logingroup_name varchar(25) constraint chkname check(logingroup_name NOT LIKE '% %'),
changed_login_id int,
is_active bit,
lastupdr smalldatetime,
)
--Creating Unique nonclustered Index for ref_LoginGroup:-
create unique nonclustered index idxlgn on ref_LoginGroup(logingroup_name)
--Creating Table ref_Login:-
create table ref_Login
(
login_id int identity(1,1) constraint pkloginid primary key clustered,
login_name varchar(50) constraint chklogname check(login_name NOT LIKE '% %'),
firstname varchar(30),
lastname varchar(30),
logingroupid tinyint constraint fklogingroupid foreign key references ref_LoginGroup(logingroupid),
changed_login_id int,
is_active bit,
lastupdr smalldatetime
)
--Creating Unique nonclustered Index for ref_Login:-
create unique nonclustered index idxl on ref_Login(login_name)
My db is working in simple recovery mode, when I run "dbcc sqlperf(logspace)", I find the log file is very large and the log usage percentage is 98%. In most case, when I run "backup log db_name with no_log", the log usage percentage will be reduced. but in fact it is no change. I make sure there are no active logs in it. Who can tell me the reason ? thx.
View 1 Replies View RelatedHello,
I am having a problem with the SQL Server 2005 database of our ERP system. We have a scheduled batch that gets stuck because of a lock on a table. The lock remains overnight and halts all other processes (it seems most of them are waiting for this lock to get solved)
I know which table the lock is on and if I kill the lock manually, the batch continues and finishes without a problem
my question is: can I change this behaviour somewhere?
The piece of code that causes this lock is protected, but in a debug log I was able to see that it happens during following actions:
first a select is performed to fetch a certain record in the file that gets locked
next an update is performed with following syntax:
update ... set.... where current of 'pointername'
this update is waiting for a lock to be released before the batch continues.
My guess is that somehow the pointer that is set with the select statements, locks the row and that's why the update cannot be performed.
The strange part is that this lock does NOT occur everytime that this table needs to be updated. And that's why I am stuck and asking help of the real database people :)
If someone can give me a hint on a setup,property, or just a pointer to where I can find more info on the 'why'-part of the lock, I would be very grateful
database is SQL Server 2005 SP1 on win2k3
I am trying to find any stored procedures or tables that have not been used in the last 2 months or so. Can anyone recommend me a good way to do this?
SQL 2005 Enterprise Edition SP 2
Thanks in advance.
Kay
Hi ,
I have code in SQL Server 2000 (DTS). what is same code to inactive execution status in SSIS
If returnValue <> "" Then
DTSPackage.Steps("DTSStep_DTSDataPumpTask_2").ExecutionStatus = DTSStepExecStat_Inactive
Else
DTSPackage.Steps("DTSStep_DTSDataPumpTask_3").ExecutionStatus = DTSStepExecStat_Inactive
End If
Thanks,
Raja
Hi
I am working at a site that has no dedicated DBA, and our Sql Server 2000 environment has recently been playing up. There are several databases on the Production server that should be inactive (for applications no longer used).
What is the best way for me to see if there has been any recent activity on these databases (short of taking them offline and waiting for screams). Please keep in mind that I am a Sql Server novice.
Cheers
Does anyone know if there is a 3rd party tool available to disconnect user logins after being inactive for a period of time? Is this something that can be custom written?
Thanks for any assistance, Jim
hi,i've just restored my db and when i try to login with a user (even ifthe user is "sa"), an error occours.The error is "User sa is currently inactive"How can i solve this problem?Thanks,Massimo
View 1 Replies View RelatedI' d like to list all inactive clients.Inactive client is a client who hasn't had invoice for 2 months.I use INNER JOIN to join invoice view (vwDok4FSFZGrid) and clientsaddresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_Nazwa !=''). I select only invoices with date after the interesting date.So now I can list all active clients but i can't negate this resultset to get all inactive clients:SELECT dok_PlatnikId, adr_Nazwa,adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWystFROM vwDok4FSFZGridINNER JOIN adr__EwidON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiek tuWHEREadr__Ewid.adr_Nazwa !=''AND(dok_DataWyst >= convert(datetime,'10/03/2007'))GROUP BY dok_PlatnikId, adr_Nazwa,adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWystIs there any way to negate this set?1. Sorry for my English2. I would appreciate any help:)
View 1 Replies View RelatedIs anyone else having an issue with SSRS menus not being able to be clicked on?
When I roll the mouse over the menu item, it highlights, but when I click, nothing happens.
It affects the top menu items, and any right click menu.
This is an intermittent problem, but it does happen daily. I have to shut down VS to fix.
BobP
It seems to me that if a scheduled SSRS report subscription fails ( Status Message - An error has occurred during report processing) , that I actually need to delete the subscription and reconfigure it from scratch. The scheduled job doesn't try to run again automatically (say the next Monday on a weekly Monday schedule).
Is there a way to "reset" a failed subscription without have to recreate the entire subscription?
Thank you
Devon Kyle
28638
SSCE_M_DUPLICATETABLE
Existing subscription already contains table included in the new subscription.
What are the possible causes of this merge replication error?
Could it be caused by a SQL Server Compact Edition User trying to sync their .sdf file after their subscription has already expired on the SQL Server?
Would you expect to see a different message if a SQL Server Compact Edition user tried to sync a subscriber database (.sdf file) with merge replication if it's been longer than the subscription retention period since their last sync?
A Contractor has a list of Contracting Companies that he has worked for in his history.
For each row in the "Contracting Company" table, there is an Active Date and an Inactive Date. Each Contracting Company cannot have overlapping dates.
The last row in the table for that Contractor may have a null Inactive Date. That just means that he doesn't know when his contract will end.
In Report Builder, I can access the "Last Active Date", because there is a pre-built formula for "Last Active Date". However, whenever I access the prebuilt formula "Last Inactive Date" - I get the inactive date for the second-last row, because the last row has a null value for the Inactive Date! I want to get the Inactive Date for the last row - not the second last row!
How to do this easily? Thanks!
Hi! maybe this question is very common, but i can resolve that. I have a DB marked 'loading', and nothing seems to change this state. Is any way to revert to a normal state, or the only way is destroy and re-create?
Thanks in advance
Hi all,
I want to know if the System.Xml.XmlTextReader is marked with Host Protection Attributes(HPA) in the SQL Server 2005 CLR. I ask this because when I add a custom class that inherits from System.Xml.XmlTextReader, I get a code access security error at runtime. I am able to register the assembly just fine. I have tested it with all the cases and pretty much sure my code breaks in this class.
My code snippet is shown below:
public class CustomXmlTextReader : XmlTextReader
{
......
......
}
Doesn't really matter what is in this class. It gives an expection even when there is no code inside.
Any help is greatly appreciated.
Thanks,
Jai
I have SQL Express SP1 installed on serveral machines, each has one instance and each instance has more than one database.
Recently, I found out some dbs are marked Suspect. MOM tool reported something like:
The database "your-DB" in the instance "Your-Instance" is in a critical state. The state is: Suspect.
I tried to run "DBCC CHECKDB", but it failed.
Can someone tell me what could cause the problem, and how to fix it?
Thanks,
Peter
Hi AllgI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advancehttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPGhttp://img139.a.com/img.php?image=28285_2_122_937lo.JPG
View 1 Replies View RelatedI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advance
the screenshot links:
http://img139.imagevenue.com/img.php?image=28285_2_122_937lo.JPGhttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPG
I canceled a restore job, and now the database is marked (Loading). When I go to propertied it tells me "database can't be opened, it's in a middle of a restore" Yet from a Query window I can connect and run queries against that database. Should I just wait until it finishes "Loading" or should I re-start the server?
Thanks
Hello Gurus,
After the power failure my database was marked suspect. According to my associate we will execute SQL command sp_resetstatus. My question is what are the things we have to do first before we execute the command? We are using NT 4.
thanks
Claudio
Hi all,
I have a big problem with a Database on SQL-Server7.0 running on windows 2000 advanced server. The msdb database is marked suspect. I have tried a few suggestions in the SQL BOL but no luck.
Can anyone help please!
We have a new installation on Friday which was running fine. Unfortunately today, the msdb is marked suspect. Do I uninstall and reload SQL server or change the status. We only have a training data on the server presently. Does anybody has any other suggestions? Please help. Thanks for your assistance.
View 4 Replies View RelatedI have a 3GB DB that I was running an update script on. The sript failed and ran out of transaction log space. I expanded it and stopped and restarted SQL. The user DB is now marked as recovering. How long should I wait for it to recover? I don't want to have to restore again and start all over with my script. Please help.
(By the way this is a restored DB)
Thanks.
LC
Usman Masood writes "we were using our database named db1. when suddenly it stopped working and upon checking it gave an error that db1 and db2 are marked as suspected. while we were only using db1,,,,what is the cause of this error?"
View 1 Replies View RelatedHi all
I tried this command
DBCC CHECKDB (A2PD,REPAIR_ALLOW_DATA_LOSS)
The above command was not executed as there was some long rollback was going from a user...
I stopped and re started the MSSQLSERVR service...
Then i found the follwing error..
how can i work with the database A2PD marked as suspect
Server: Msg 926, Level 14, State 1, Line 1
Database 'A2PD' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Thanks
Simon writes "Hi
using the following syntax in SQL 2005 (win 2003) to restore a log to a marked transaction. Code runs ok but I'm never getting back to the point in time where id 13 still exists , help !
kind regards
Simon
/*backup and overwrite */
BACKUP DATABASE AdventureWorks
TO DISK = N'C:SQL Backupsadw.bak'
WITH FORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup'
GO
/* verify data */
restore verifyonly
from disk = 'C:SQL Backupsadw.bak'
with file = 1
go
-- with MARK to place a marker in the tranny log
BEGIN TRANSACTION CandidateDelete13a
WITH MARK N'Deleting a Job Candidate';
GO
USE AdventureWorks;
GO
DELETE FROM AdventureWorks.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION CandidateDelete13a;
GO
BACKUP LOG AdventureWorks
TO DISK = N'C:SQL Backupsadw_log.bak'
with format, NAME = N'AdventureWorks-Trans Log Backup'
GO
/*
RESTORE DATABASE AdventureWorks FROM DISK = N'C:SQL Backupsadw.bak' WITH FILE = 1, RECOVERY, NOUNLOAD, REPLACE,STATS = 10
GO
*/
use master
RESTORE DATABASE AdventureWorks FROM DISK = N'C:SQL Backupsadw.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE,STATS = 10
GO
use master
RESTORE LOG AdventureWorks FROM DISK = N'C:SQL Backupsadw_log.bak'
WITH recovery, STOPatMARK = 'CandidateDelete13a'
GO"
I've got trouble -- somehow my msdb is corrupt and is marked as "suspect". I cannot open it up or it is apparently devoid of any table, etc.
It's on my SQLExpress instance and I'm willing to blow the entire server away -- since this server has been used as a sandbox for personal use.
Can I simply re-install SQLExpress?
Hi,I restored my Windows XP system and discovered that I cannot access aMSDE DB that was saved in D: drive (I restored my primary partition inC). How can I restore it?Thanks a lot.
View 6 Replies View RelatedWe've got a Sharepoint 3.0 SQL database that was partially corrupted during an ill executed BackupExec restore. Now the database will not start up.
Error log reports:
2008-05-07 13:01:50.85 spid8s Starting up database 'model'.
2008-05-07 13:01:50.89 spid8s The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2008-05-07 13:01:50.89 spid5s The database 'msdb' is marked RESTORING and is in a state that does not allow recovery to be run.
2008-05-07 13:01:50.89 spid8s Error: 927, Severity: 14, State: 2.
2008-05-07 13:01:50.89 spid8s Database 'model' cannot be opened. It is in the middle of a restore.
2008-05-07 13:01:50.89 spid8s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2008-05-07 13:01:50.89 spid8s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
I'm looking for suggestions on how to get the database back up and running with minimal SQL Server knowledge. If anyone has a quick and dirty how-to that would be appreciated of course any detailed documents specific to this configuration would also be greatly appreciated.
Thank you,
~Pete