I have been asked to take over the maintenance on dB servers that I have not originally 'set up'. The request came as a result of the database log files growing at such a rapid pace its taking up hard drive space. I ran sp_dboption and noticed that the log files are NOT set up to truncate on checkpoint. As in SQL 2000 I wanted to determine if the dB was set up in 'simple' or 'full' recovery mode. It appears they are set up in 'full' recovery mode (which we can modify, since this is not a transactional dB and we rarely if ever have to restore from the transaction log). Hence my question,
Is there anything I need to worry about (regarding replication)if I change the dboption to truncate on checkpoint and set dB in simple recovery mode?
And after I backup the transaction log and try to shrink it will that have any adverse affects? I feel like this is all I need to do but replication is new ground for me, any information regarding it would be greatly appreciated. Also, any advice on how to maintain the transaction logs would be helpful.
Hi, Every one I have added new column in my Publication - Article Table , Same column I have added in my Subscriber Table , I am doing Transaction replication , but now its giving me error Insert Error - Column Name or Supplied Value doesn't Match, If any one has any idea please suggest me Thanks Nirmal
Hi! i need to clean the transaction log of a local database. I used to do this in SQL server 6.5 with the sentence 'dump transaction {DB} with no_log'. After this ran 'sp_spaceused @updateusage = 'true'' and the problem was corrected. How can i do this in version 7' ? Are the same sentences?
For some reason the transaction log backup which occurs every few hours has failed. This means my transaction log in filling up. Is it possible to increase the space allocated for the log whilst the server is in use.
I would like to capture number of the total transactions made on the server during a day. ie begin tran - commit tran How can I collect this information? Is it possible?
Hi, I have just migrated the 6.5 SQL Server to 7 and the intial isze of Db growth was unlimited for 7.0 and so was the transaction log , now the problem is that the trasaction log file has grown to 8GB, i truncated the transaction log and took a complete DB backup now the log size is 90MB but i was to put restrictions on log growth to max of 1500MB but when i go to properties of Db and try to change the log size to 1500 it says cannot change it as the size is less than max size, the max size it shows currently is 8GB i was to re set it to 1.5GB how can i do that i have tried shrinking the file and relasing the space to OS but still the size it show to me is 8GB.
I have a database with around 2 GB space for the data and 5.8 GB for the transaction log . Now the problem is i do not have any more space on the system and data files requires more space than 2 G.B to execute some stored procedures .
Is it possible to decrease some space of the transaction log , say from 5.8 GB to 2 GB and allocate it to the data files . My data and log files are on different drives . I did not find anything related to this topic in the BOL .
Can somebody help me with this problem ? Anthing related to this issue will be of great help to me since i have no expertise in this field .
i have two remote servers connected to each other through the linked servers.
i can run querries or even perform inserts as far as its not inside a transaction but when i am trying to run distribted transaction but it keeps on returning the same msg.
Server: Msg 7391, Level 16, State 1, Line 3 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
i have configured the firewall, made sure the DTC service is started on both and enabled. they both have the same Sp packs, same config.
We have an order posting stored procedure that's been executing for 14 hours now. This procedure has simple update statements but all the tables updated have triggers which update other tables with triggers and so on. This procedure hasn't commited yet. I have a full database backup from last night and transaction log backup from this morning while it was running. I want to kill this job and restore database from last backup. Only thing I am afraid of is that it will take too long to roll back which I have no estimate of time. Is there any faster way to get rid of all the uncommited transactions? Has anybody rolled back such a massive transaction? Any idea on how long it can take? Can it affect the overall server performance as this is the main production ERP server. I can see in profiler that this procedure is still running and not hung.
We have a database with 20 gig and with huge transactions. The transaction log backup is scheduled every one hour from 3.00 AM to 9.00 PM.
We take a full backup in the disk at 9.00 PM and again a full backup in the tape at 2.00 AM
It works fine in the day from 6.00 AM and complete within seconds and the size is approx. 50 to 200 MB.
But the very first transaction log backup at 3.00 AM is running like 3 hrs and the size is approx. 11 gig whick is almost equivalent to the Full backup size. There are some dts packages that are running in the night and as usual reindex, intergrity checks are running and there no large user traffic during night. But I have no idea which the very first transaction log backup in the morning takes longer time and has this bug size. Is there any work around to fix this proble.
Hi, I am configuring replication and i started using wizard . i opened publishing and distributor wizard and click on next it is saying that "Sql manager could not complete wizard because @@sername is null and use sp_add server name to set @@ servername. This is productionbox. Any body help me how to fix the problem and what is that meaning osf error. Thanks
Hi Guys, We are using transactional replication, I want to add a filter on the rows. Can Any one explain me how to add it. To do this we should stop replication and then add the filter ???.
Hi Guys, I am having around 1000 tables, in which around 700 tables are not having primary keys on the tables, The tables which are not having primary key, i want to set up an snap shot replication, Tables with primary keys with transactional replication. can i have both transational as well as snap shot set up on the same server????
I have setup the snap shot replication on one table.It says the initial snap shot not yet available.What does it mean?How to activate the replication? Thanks.
Hello there, I need some urgent help on the Replication .....SQL 6.5, sce 5a, windows NT4.0
A Publication has been installed between 2 server (SQL 6.5, sce pack 5a).
The task Repl_Subs_Distribution fails with the following message: "The last distributor job id and the last subscriber job id do not match. No jobs were available with a job id > 80"
1) with a Push Subscription, the error "no subscriber has been defined" came up, so I also did a pull subscritption for the remote server = could it be the cause of the job id not matching?
2) although I found a document on a "sp_Mskill_job" replication stored procedure, it is not present in my DB and I cannot use it. Is there a way to create it?
Thanks for your precious help. Best regards, Rachel
I was advised to Compair max job_id in MSsubscriber_status on distribution and MSlast_job_info on subscription and If not match, to modify the id in MSlast_job_info.
There is no distribution server in this Replication....any other idea or possibility??
I wanted information on Transactional Push replication between SQL Servers . I have one table on SQL Server 6.5 and there are certain text in the table with single quotes . When i replicate this table to SQL Server 7.0 the text appears in double quotes on SQL Server 7.0 .
Can anyone help regarding this problem ? Has it something to do with version problem .? Where can i get more information on this issue ?
Many thanks . An early response will be highly appreciated .
Thank you so much for your prompt response . Is it possible to change the settings of the Quoted Identifiers ? How can we do it in SQL Server 6.5 and SQL Server 7.0 ? Do you have any info. on the same . ?
I have a database replicated to another database in a remote server. We have a trigger in one of the tables in remote Database(replicated), which will fire as soon as there is an insert into this table. This trigger is maintaining the business logic. This trigger will insert the records into another table depending on what kind of information obtained from the new row(which was replicated). Problem is, since we are using replication ( it uses bcp internaly), the trigger is not firing. I some how wanted to add these new records to the other table (automated). Can you guys please advice me on how to implement this. I appreciate your help Thanks, Jeyam
hi, I want to setup transactional replication(PULL) between 2 servers . can anyone guide me with the steps involved while performaing a Pull replication from server1 to server2. Any help appreciated.
I need some urgent help. I'm managing merge replication between 12 servers. Today I had to make some changes in the database design, so I removed replication, altered the tables in question (on all servers)and created a new publication. I choose the option that all subscribers have the data and the schema, but when I start the replication I get almost immediately an error "Invalid column name rowguidcol". But there is no column in my database with such a name. Further investigation brings up another message "xp_execresultset: unable to execute result set".
Can anyone please help me. The database should be ready by tomorrow.
We are using Ms SQLSERVER 6.5 on NT 4.0 service pack 3 I have joined in as a new DBA and don't know much about MS-SQLSERVER & it's replication. What I have observed out here is that one of the databases was being replicated betnn Tokyo and Kawasaki and suddenly after 22nd Feb stop replicatiing without giving any errors. I have checked the max job_id in MSsubscriber_status on distribution database as well as max_job_id in MSlast_job_info on subscription db.They seem to match.I have also tried to drop and recreate the unique clustered index on the subscribing database.But nothing seems to work.
A Publication has been installed between 2 server (SQL 6.5, sce pack 5a).
The task Repl_Subs_Distribution fails with the following message: "The last distributor job id and the last subscriber job id do not match. No jobs were available with a job id > 80"
1) with a Push Subscription, the error "no subscriber has been defined" came up, so I also did a pull subscritption for the remote server = could it be the cause of the job id not matching?
2) although I found a document on a "sp_Mskill_job" replication stored procedure, it is not present in my DB and I cannot use it. Is there a way to create it?
Thanks for your precious help. Best regards, Rachel
Hi, all.. I by mistake Overwrites distribution DB. Server1 and Server2 replicates each other. Applications are using Server1, and Server2 is back up purpose. When server1 fails application redirected SErver2, using same data since there is replication. Server2 had distribution DB.
Now there is no distribution DB.. it's messed up... I want to restore replication between Server1 and Server2. I tried to make replication from Server1, I got errors..
By Accident One table got truncated on the subscriber which is one of the table in Published articles. Now all the Update inserts and delete are failing . and other tables are not being replicated either. what could be done to fix this . Publisher gets a lots of updates every hour how ever because of this problem all the tables are not getting any transactins replicated. Please help ASAP
