Impact Of ALTER DATABASE GPx SET NEW_BROKER WITH ROLLBACK IMMEDIATE In Production Db
Feb 20, 2007
System Configuration :
OS : Windows 2003 latest SP
SQL Server : Standard Edition, SP2
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
DownTime : This is not a 24x7 kind of machine. It can have downtime
Reference : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1198044&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1
I have been discussing this Service Broker issues in this forum for quite sometimes and sorry to bother u all again€¦ To make things more clearer before implementing in production environment I have few doubts and it should be clarified..
As discussed in the first link we can clear sys.conversation_endpoints by just giving ALTER DATABASE GPx SET NEW_BROKER WITH ROLLBACK IMMEDIATE. But my apprehension is that if we run this command on production server and it truncate this table€¦ what will be the impact and overall overhead on the system€¦ Is it recommended to give this statement on Production Server daily at less traffic times, to clear this table ?€¦ will it have adverse effect. I repeat I have downtime , I can even shutdown this server daily€¦
I also, just want to know why Microsoft has not looked into this aspects€¦ why the system itself is clearing the expired messages.. What is the thought behind this architecture
I have the script to run in batch€¦ but in high-level meetings it is always difficult to convince this architecture/process€¦
Thanks in advance
View 5 Replies
ADVERTISEMENT
Oct 3, 2006
I am considering the use of ALTER DATABASE ... SET NEW_BROKER within an existing Service Broker instance. The reason is that I have about 23 million old endpoints in Service Broker that I am unable to clean up using the standard END CONVERSATION loops that have been discussed on these forums previously. It's a busy database, and it just cannot do the job within a reasonable timeframe.
There are no critical conversations to be lost, becuse the majority of the old endpoints deal with ASP.NET SQL caching, so they are disposable.
I have read that using the ALTER DATABASE command will delete all past endpoints, but I haven't seen much in the way of the retention of the existing message framework.
If I execute ALTER DATABASE ... SET NEW_BROKER, will it retain everything I have setup, including security, message types, contracts, services, queues, etc.? Is there anything, other than the old conversations and endpoints, that is deleted?
Thanks very much!
View 5 Replies
View Related
Dec 1, 2005
If I execute the command ALTER DATASE SET MULTI_USER WITH ROLLBACK IMMEDIATE and there are any connections to the database, the command fails with a "Lock request time out period exceeded." message. If I use SET RESTRICTED_USER, the command succeeds with the following message: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." This seems to be a bug.
View 5 Replies
View Related
Feb 26, 2008
Recently I created a DDL Server-scope trigger using the following:
create trigger stop_alter_database on all server
for ALTER_DATABASE
as
rollback;
print 'database change stopped by stop_alter_database';
go
Then I ran the following script:
alter database [test] modify file (name=test', maxsize=2028mb);
The result was:
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
database change stopped by stop_alter_database
The problem is that when I checked the max size of the data file it had changed. So, the statement was never rolled back. Is there something I'm missing because I can't find any documentation or articles that state the inability to rollback alter database statements. Whats going on?
View 2 Replies
View Related
Nov 9, 2015
Primary platofrm: Sql12k, 7.0 Ultimate Pro OS
I'm launching the aforementioned statement from one MASTER session windows and I get this message, I am stuck, I though ROLLBACK INMEDIATE go throught any already session open.
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'GFSYSTEM' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
View 4 Replies
View Related
Nov 7, 2007
Just a quick easy question. If I alter a table (add a column to the table), will it take the table offline during the ALTER process? I am adding the column to the end of the table not in the middle. I know if I add it in the middle it will offline the table.
View 4 Replies
View Related
Jul 20, 2005
We have a SQLSERVER database that is replicated to many users.We are currently in an expansion phase where we need to make changesto the server database. Each time we rollout a new release, we aredeleting the local replicating database and recreating.Is there any way to automatically transfer the changes from the serverto existing local database without deleting?
View 1 Replies
View Related
Aug 5, 2015
Our SQL Server 2008 r2 has collation Latin general.
And my database 'DB1' , 'DB2' has collation set to Japanese unicode.
My sql team has informed they cannot change system collation as it hosts other db's as well.
My Query: What will be the impact of changing collation from JP to Latin.
View 1 Replies
View Related
Jun 21, 2007
Hello All,
When creating my database I have modeled some of the tables after the Adventureworks sample database.
There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).
In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.
Thanks for your help!
View 1 Replies
View Related
Aug 28, 2006
I noticed that a database I am working with has a compatibility level set to SQL Server 2000. The instance is actually SQL Server 2005. I'm guessing that it was created like this because the database originally existed on 2000 and was created via backup/restore.
I'm trying to figure out if this needs to be changed and if so how to go about making the change in a non-disruptive manner. What features of 2005 are turned off as a reult of having a 2000 compatibility level?
View 4 Replies
View Related
Apr 26, 2015
We have 3 maintenance jobs configured in this particular DB instance:
Daily backup of system database - SubPlan1 (Check Database Integrity Task --> Rebuild Index Task-->Backup Database Task)Daily backup of user databases - Five subplans for each task : (Check DB integrity --> Rebuild Index -->Backup User Database, Backup Log -->Cleanup History)Weekly maintenance - SubPlan1 (Check Database integrity job (system+user DB)Â + rebuild index job (system+user DB) )
PROBLEM: I just noticed that the User DB Rebuild Index task has been running since the 03/04 and the Weekly maintenance plan - subplan1 since the 12/04.
Which job is "safe" to stop without impacting the database?
View 14 Replies
View Related
Feb 7, 2007
SQL Version - SQL Server 2005 Standard Edition, SP 1
I have a database which is Broker Enabled. We use Query notification extensively and the application is in testing stage. Both Testing and development database is on the same server. When I create database for testing I used to do Backup-restore method. When i restore a borker enabled database, the restored database and the source database will have same GUID for Broker. So by default the restored database will not be broker enabled. So what I do is , i run
Alter database somedatabase SET NEW_BROKER.
This statement will generate new GUID for this database and then we can enable the Broker by SET ENABLE_BROKER statement. Till last week this process was working fine. When I did the same thing yesterday the Alter database somedatabase SET NEW_BROKER query was runing for whole night and it could not complete the process.
What SET NEW_BROKER internally does as per BOL is
NEW_BROKER
Specifies that the database should receive a new broker identifier. Because the database is considered to be a new service broker, all existing conversations in the database are immediately removed without producing end dialog messages.
When I checked the Conversation in the database there were millions of rows.
My question is , can I do the same process in a better way and how. Is there any way to clear Conversation in a faster method. I googled , but I could not find a better solution.
pse help
Madhu
View 15 Replies
View Related
Sep 4, 2007
Hi I have a couple of questions to understand better Service Broker..
Every time I alter the activation Stored Procedure on the receiving queue I also execute the:
alter database set new_broker rollback
1) can somebody explain me in a detailed way why I have to do that? what happen inside SQL Server when there is a Stored Procedure activated on a queue??
Also link to white papers, resouces on so on are appreciated.
My Activation Stored Procedure calls other stored procedure.
2) If ONLY one of the nested stored procedures changes I have to execute the command:
alter database set new_broker rollback ???
I think that the answer to the first question will answer the second one..
Thank you for any helps!
Marina B.
View 3 Replies
View Related
Sep 10, 2006
I use ASP.NET 2.0 and SQL 2005, the SQLCacheDependency didn't work stable for me, it usually works and after a while, it stops working. Recently, It doesn't work. Today I reset service broker by
Alter DB set new_broker
It seems all work, but too early to be happy, it doesn't work now anymore. I don't see any record in
sys.transmission_queue
And I don't see new entries in
sys.dm_qn_subscriptions neither when I modify a record which bind to SQLCacheDependy.
I see some errors in log like
Message
The query notification dialog on conversation handle '{8F8CC642-6340-DB11-8F09-0014227B7B80}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-f79776f7-9ca5-4c5f-8a66-0d81f7673683' because it does not exist.</Description></Error>'.
Any idea how to find out the problem?
thanks!
View 1 Replies
View Related
May 11, 2007
Can someone provide a step by step tutorial for this? I'd like to safely update a database that is used for a website without much or any downtime.
View 1 Replies
View Related
Aug 24, 2006
hi!
I am able to run the package successfuly in test database. but not in production database. It throughs up error saying
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
Description: Failed to open package file "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" due to error 0x80070015 "The device is not ready.". This happens when loading a package and the file cannot be opened or loaded c
orrectly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error
Could not load package "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" because of error 0xC0011002.
Description: Failed to open package file "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" due to error 0x80070015 "The device is not ready.". This happens when loading a package and the file cannot be opened or loaded corr
ectly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
what is the problem here....
JAs
View 3 Replies
View Related
Aug 20, 2015
Testing team is performing some activities on MSSQL - 2008 R2 database ..and after completion of the test, again team wants to restore the original database ( in the same state as before starting of the testing). Currently team is taking the back-up before starting testing and restoring the backup after the testing .. Since the database size is huge, this restoration is taking more time Is there any better way of taking the database to the previous state in lesser time.
View 3 Replies
View Related
Feb 26, 2001
Someone please explain what is a Production Database. Is it a database to which data is keyed in from many data-entry operator terminals?
TIA
View 1 Replies
View Related
Dec 8, 2000
Hi,
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.
View 1 Replies
View Related
Oct 16, 2014
I have a wrapper stored procedure ProcMain_wrapper that executes in one main dataabase and it calls child stored procedure in multiple databases. Does the rollback occur in all the databases if the stored procedure fails in any one of the multiple databases?
create procedure dbo.db_Main.ProcMain_wrapper (
declare @curClientCode as cursor ,@db varchar(10),@dbName varchar(20)
BEGIN TRANSACTION TRAN1
BEGIN TRY
SET @curClientCode = CURSOR FOR
SELECT [name] from sys.databases where name like 'dbclient_%'
[code]....
View 2 Replies
View Related
Jul 23, 2005
Hello faculties,We've a production database that is being used by one of our clientsas a backend for his website. The database size is around 1GB.Recently we added some tables to our local database which is areplica of the production database. Now we need to apply the samechanges at the production one aslo.I've no clue about what steps should i implement.Please guide me!Thanks in advanceDebian*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies
View Related
Aug 31, 2007
We often have to migrate changes to sql server 2000 databases fromdevelopment to production. Normally we dump the sql from Enterprise Managerfor production and development and do a diff (using CSDiff - downloadable forfree).From the diff information we create some scripts to add new tables or alterexisting tables as required. This is time consuming and error-prone.I am now trying the following:1. Backup up the Development database2. Delete the Development database3. Restore the Development database as Development_Backup3. Use the the sql script to create the new Development databasestructure from scratch4. Use Data Transformation Services to migrate the data fromDevelopment_BackupThe problem is that many of the 'sys' tables are empty. For example table'sysforeignkeys' is empty.Is there an easy way to rebuild the data in the 'sys' tables?Thanks
View 3 Replies
View Related
Jul 20, 2005
We are installing an application with MSSQL database at our users place.Now we have two instances of the same database:- developing database, on which we are working at- production database, which is at our user's place.That 2 databases would have to be syncronised.Users will of course enter some data into production database. How can wechange the production database do reflect changes we have made on thedeveloping database?D.--
View 3 Replies
View Related
Sep 5, 2006
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
View 1 Replies
View Related
Apr 24, 2007
I developed an asp.net application in visual web developer 2005 express edition and SQL sever 2005 express with Advanced services. The application has been deployed and iam wondering what tools are availabel to for backing up my data. Are there any tools i can use to back-up my database. Iam not talking of third party tools but tools a vailable in sql sever 2005 express with advanced services or visual web developer express.
OR can write a vb.net Sub procedure that i run and have my database backed up. If so where can i start or what other options may i explorer.
View 4 Replies
View Related
Mar 2, 2004
Can anyone help me in this issue:
How do we replicate database changes (like tables) from QA to production without losing Production data.
We already tried using the DTS export but it is dropping the destination tables before export which will result in data loss in the destination database.
View 3 Replies
View Related
May 15, 2008
Hi,
We have a database in production which has free space about 200 GB in Data files and Index files, I want to shrink Data files and Index files.If I do incremental shrink in daytime does it hurt the performance of the database or please advise what is the best practice.
thanks
View 5 Replies
View Related
Jul 23, 2005
I have a production database with a backup job that creates files with thenaming convention dbname_db_200503291800.bak. I want to schedule a restorejob that will retire yesterdays backup. How can I write my restore statementso that it will specify the backup file with yesterdays date.Thanks
View 1 Replies
View Related
Nov 2, 2006
What's the best way to move database from development to production environment?
View 1 Replies
View Related
Jul 31, 2007
we are having 2 prodction servers in our environment.
and we are planning to perform database mirroring between them.i want to mirror server 1 databases to server2 and server 2 databases to server 1.
is this possible??
if possible , can any one give me a link to document where i can find more info on this, or send me the steps what i need to done for this...
thanks
View 4 Replies
View Related
Sep 29, 2007
Here is the scenario:
- I have one production database and one test database.
- In the test database i have done a few changes on the structure - mostly adding new fields in the tables.
- Now i want to apply the table changes to my production database.
Does anyone have any suggestions on how i should do that?
I mean, of course i could just manually check every field and update the changes - but that could take a while -
i guess there are some better ways this could be done, like:
- backup data
- delete tables
- recreate tables using structure of test database
- reinsert data
Is this the correct way? Are there any easy ways to do this?
The changes i made should not make problems regarding data that is already stored (like changing a string field to int)
View 5 Replies
View Related
Jul 20, 2015
I have a script that automates some db drop/restore operations and bringing the database to single user mode is part of it: ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE...I want this to executes under a login, that has restricted privileges, so I've created a login and granted it a dbcreator role + ALTER ANY DATABASE privileges.
Problem: When I run the script against a database with an active/sleeping connection:It fails when using the restricted login: "Msg 5061...ALTER DATA BASE failed because a lock could not be placed on database ..."It completes successfully when using a sysadmin login According to stackoverflow.com the solution is to kill the active/sleeping connections to the database, before ALTER-ing it, which works fine, but the question is....
Questions: Why the "ALTER DATABASE..." statement works under the sysadmin login, but not under a dbcreator one?Does this mean the sysadmin login kills the connections to the target database in the background?Is it possible to grant additional privileges to the restricted login, so the "ALTER DATABASE..." statement won't need preventive killing of the connections?
View 5 Replies
View Related
