SQL 2012 :: Upgrade Compatibility Mode And Rollback Process?
Mar 24, 2014
We upgraded QA and production to sql server 2012 last year ( in place) leaving the user databases at sql 2005 ( 90 ). A few months ago the QA user databases were set to sql 2012 compatibility mode. Management is worried about upgrading production and wants to know if we can quickly roll back.
I want to confirm that we can roll back using the same command, and if dbcc freeproccache can be used to avoid having to update all statistics.
ALTER DATABASE <mydatabase> SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE <mydatabase> SET COMPATIBILITY_LEVEL = 90
This works fine in QA on my own test user database. No errors.
I have a 2005 database sitting on a 2012 server, we're looking to change its compatibility mode from 90 to 110 so we can avail of what 2012 offers.I did a migration project a couple years ago for SQL Server 2000 databases changing to 2008 R2 and we ran into loads of problems where we left most databases in compatibility mode 80 as these were application databases that the owners couldn't stand over in terms of deprecated code / features no longer in use in 2008 R2.
From what I can see with changing from 90 to 110, there doesn't seem to be as many issues but I just want to double check if there's a way to confirm this. I know upgrade advisor is a handy tool but will it pick up database specific issues as opposed to database server compatibility issues? URLs...I know UA won't cover all the bases but it would look good when attached to the report I'm submitting recommending the change.
We recently upgraded out SQL version from SQL2008R2 to SQL2014. As such, the compatibility mode changed to SQL2104 (120).
We have several queries that used to run fine that now take forever to bring back results. There are no errors (which surprised me). They just take way too long now. PLus they seem to be causing high I/O and CPU.
If I change the compt level back to SQL2008 - these queries run fine.
QUERY with SQL2008 compt level - finished in 2 minutes. QUERY with SQL2014 compt level - finishes in 3 hours 22 minutes.
same exact query - same server - only thing changed was compatibility level.
WHat do I look for in the queries that could be causing this? (they look fine but obviously I'm missing something here)..
We have some users who are nervous about our upgrading to SQL Server 7.0 even though we will use 6.5 compatibility mode initially while we work through 7.0 upgrade issues in the applications.
Has anyone had bad experiences with the 6.5 compatibility mode feature? Just how good is it?
I currently use MSDE and now I need to upgrade because we moved to 2005. The problem for me is teh compatibility mode. I need to change it to 80 (2000).
I also need to do it via command line because we have a base command line install for users.
We use it to attach databases and view information.
Is there a way to install SQL Express with a default mode of 80 (SQL 2000).
I have been migrating Databases from a SQL Server 7.0 Instance to a 2000 Instance. Basically the method I use is as follows:
a)Create a new Database on the Destination Server (same name as the Source) When I create the new Database on my destination server, the compatibility mode is '80' and the source is always '70'
b) do a 'revlogin' on the Source Server and use the output from that query to recreate the logins on the destination server
c) make sure that the default DB for the newly creates logins are correct and change them if necessary
d) Backup the DB on the Source Server and Restore it to the Destination Server.
e) Check login permissions and fix any orphaned users - usually I don't find any that need to be fixed, but I always check.
I've read BOL on Changing the compatibility mode on the Database... but I'm still unsure when I would have to do this and why???????????????? SHOULD I be changing the compatiblity mode from 80 to 70 when migrating Databases from 7.0 to 2000???? Any advice on moving DBs in this manner would be appreciated.
On my current contract we have a SQL 2005 box with a user database and the tempdb database in 6.5 compatibility mode. I would like to remove the user database and change the tempdbs compatibility mode. When you try and use EM just about everything fails with errors about collation or syntax. From what I have found it could be related to the tempdb being in 6.5 compatibility mode but either way I would like to remove this old unused DB.
My question is after I remove the user DB and reset the tempdb compatibility mode what will or could break? I haven't been able to find much on what would change or what to look out for after the change is made. Has anyone else done this? If so what issues did you face.
I've been scouring the forums and the web for anything that would substantiate an argument for database application developers to keep developing in SQL 8.0 when we have migrated to SQL 2005.
I read somewhere that compatibility 8.0 mode is an intern stepping stone for migrating from 8 to 9, but it seems 2005 will handle and run compat 8.0 databases just fine.
So am finding it really diffucult to substantiate my argument against running 2005 with all databases in compatibility 8 mode indefinitly.
Any suggestions or links that could help me with this.
We are planning to upgrage our SS 2000 databases(couple of hundreds) to SS 2005.
To minimise the potential work arounds, it is agreed to have complex databases, continue running on compatibility mode 80. What are the potential impact of having database on mode 80(apart from not being able to use new feature)on SS2005?
Fruther how we can we import SS 2000 DTSs to SS 2005 SSIS in an cluster environment?
I€™m having a problem setting up maintenance plans on one of our SQL 2005 servers but first here€™s a bit of back ground.
The server is a Virtual machine running win2k3R2 SP2 server std and SQL 2k5, A supplier arrived to install and app on the server when discovering there app didn€™t work with 2k5 they seem to have set the 2 dbs on the server into 2k compatibility mode and set the entire SQL install to think its a 2000 install. (I€™m not sure how this is done all I can see is the wrong version number in management studio) The problem this is giving me it that the folder in management studio for Database Maintenance Plans is no longer under the management folder instead it under a sub folder called Legacy (which is how management studio seems to deal with all registered 2000 instances) and as such a can no longer right click and create new database maintenance plans is there any way around this or will I have to setup my backup job manually?
I have this weird situation on one of the production database. Somebody ran a sql script couple days ago and must have hit cancelled. Somehow this script is still rolling back. The script is actually very simple statement..should not take more than couple miliseconds to run. Is there any way to see whether rollback is really in process or this process has simply hung? is there any other way to get rid of this rollback without stopping and restarting sql server services? Can it cause problems in getting sql server up again if I restart sql server services?
Hi All, We were upgraded our databases from 6.5 to 7.0 .After upgrading the Compatibility level was 65 .We changed to 70 for all databases using sp_dbcmptlevel system stored proc.We were having some problems with Ansi nulls if the compatablity level is 70,We changed back to 65 that works fine--OK with ANSI NULLS.While Upgading ANSI NULLS OFF after upgrading ANSI NULLS OFF.But from the front end side ODBC settings ANSI NULLS are ON.Is that some thing to do with compatablity level or with ANSI NULLS . If you guys have any great ideas Please let me know.
This may be very obvious and when I get to the first test server I will find out, but I only have a new installation of SQL2K5 so far to test with. I have several databases that need to be upgraded to run on 2K5, but the application requires them to run in 8.0 compatibility. Does this mean that an in-place upgrade is not an option?
I apologize if this is a silly question, but I have not yet found the answer specifically.
We have upgraded our database from SQL 2000 to SQL 2005 keeping the compatibility level 80. Now the question is - Would microsoft support compatibility level 80 in SQL Server 2005 even after the extended support date for SQL Server 2000?
OR
If Microsoft dropped the support for SQL 2000, then would Microsoft support compatibility level 80 in SQL Server 2005?
I have a test database for the end users to test their select queries for reports. One of my users is writing queries that cause locking in the database. I killed the process last evening and they are in Killed/Rollback status but are still hogging 90% of the CPU resources for the past 12 hrs. I tried killing them several times but no go.
I know that the best way to clear of these processes is by restarting SQL Server. If that is not an option is there is any other way we can clean these processes?
Also the user running these queries has a read only and create view access to the database. From my experience processes that go into Kill/Rollback state after you kill them are processes associated with some update transaction. Since the user as far as i know is running Select commands would an infinite loop cause this ?
I have over 500 transaction records in a single DB process handling within SQL transaction (Begin, Commit, RollBack and End). Is there any limitation for the following rollbacktransaction function to handle more records (eg. over 500 records)? Public Shared Sub RollBackTransaction() Dim transactionObj As Object Try transactionObj = SqlTransaction.GetExistingTransaction If (Not IsNothing(transactionObj)) Then CType(transactionObj, SqlTransaction).RollBack() End If
Catch ex As Exception Throw New Exception(ex.Message) End Try End Sub
I have a query to refer to the data source in as400 with ODBC 'iSeries Access for Windows ODBC data source' and a linked server with 'oledb for ODBC driver' in SQL2005 (9.0.2047)
Now we got 2 problems,
1. When we refer to the view with openquery e.g select * from openquery (abc, 'select * from dates')
This query continue to run without ending. In Activity monitor, we found that it is waiting for resource ole db
2. Since the process continue to run without ending, I kill the process in activity monitor or by command 'kill'. It shows the status 'KILLED/ROLLBACK' and it returns 'SPID 197: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.' in 'kill 197 with STATUSONLY'
In problem 1, I found some ppl encountered the same problem (waiting for resource oledb) with openquery to different data source e.g. vfp, oracle.
In problem 2, we can't restart the sql server for clear the unkillable process
Not sure if this gets fixed on a restart, but the Backward Compatibility upgrade/piece failed on service pack 1 install for SQL Server 2005. Here is the log output. Anyone experience this?
05/19/2006 08:58:27.648 ================================================================================ 05/19/2006 08:58:27.648 Hotfix package launched 05/19/2006 09:00:04.368 Attempting to install instance: SQL Server Native Client 05/19/2006 09:00:04.383 Attempting to install target: CLARITY 05/19/2006 09:00:04.383 Attempting to install file: sqlncli.msi 05/19/2006 09:00:04.399 Attempting to install file: \<server>s$917f50731dade382ab1bHotFixSqlncliFilessqlncli.msi 05/19/2006 09:00:04.399 Creating MSI install log file at: C:WINDOWSHotfixRedist9LogsRedist9_Hotfix_KB913090_sqlncli.msi.log 05/19/2006 09:00:04.415 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller 05/19/2006 09:00:04.415 Failed to read registry key: Debug 05/19/2006 09:00:51.040 MSP returned 3010: A restart is required to complete the install. This message is indicative of a success. This does not include installs where the ForceReboot action is run. This error code is not available on Windows Installer version 1.0. 05/19/2006 09:00:51.118 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller 05/19/2006 09:00:51.118 Failed to read registry key: Debug 05/19/2006 09:00:51.118 Pending reboot, successfully installed file: \<server>s$917f50731dade382ab1bHotFixSqlncliFilessqlncli.msi 05/19/2006 09:00:51.134 Successfully installed target: <SERVER> 05/19/2006 09:00:51.134 Successfully installed instance: SQL Server Native Client 05/19/2006 09:00:51.134 05/19/2006 09:00:51.134 Product Status Summary: 05/19/2006 09:00:51.134 Product: SQL Server Native Client 05/19/2006 09:00:51.134 SQL Server Native Client (RTM ) - Reboot Required 05/19/2006 09:00:51.134 05/19/2006 09:00:51.134 Product: Setup Support Files 05/19/2006 09:00:51.134 Setup Support Files (RTM ) - Not Applied 05/19/2006 09:00:51.150 05/19/2006 09:00:51.150 Product: Database Services 05/19/2006 09:00:51.150 Database Services (RTM 1399 ENU) - Not Applied 05/19/2006 09:00:51.150 Reporting Services (RTM 1399 ENU) - Not Applied 05/19/2006 09:00:51.150 05/19/2006 09:00:51.150 Product: Notification Services 05/19/2006 09:00:51.150 Notification Services (RTM 1399 ENU) - Not Applied 05/19/2006 09:00:51.150 05/19/2006 09:00:51.150 Product: Integration Services 05/19/2006 09:00:51.150 Integration Services (RTM 1399 ENU) - Not Applied 05/19/2006 09:00:51.150 05/19/2006 09:00:51.150 Product: Client Components 05/19/2006 09:00:51.165 Client Components (RTM 1399 ENU) - Not Applied 05/19/2006 09:00:51.165 05/19/2006 09:00:51.165 Product: MSXML 6.0 Parser 05/19/2006 09:00:51.165 MSXML 6.0 Parser (RTM ) - Not Applied 05/19/2006 09:00:51.165 05/19/2006 09:00:51.165 Product: SQLXML4 05/19/2006 09:00:51.165 SQLXML4 (RTM ) - Not Applied 05/19/2006 09:00:51.165 05/19/2006 09:00:51.165 Product: Backward Compatibility 05/19/2006 09:00:51.165 Backward Compatibility (RTM ) - Not Applied 05/19/2006 09:00:51.181 05/19/2006 09:00:51.181 Product: Microsoft SQL Server VSS Writer 05/19/2006 09:00:51.181 Microsoft SQL Server VSS Writer (RTM ) - Not Applied 05/19/2006 09:00:51.181
I am in the process of migrating from Sql Server 2000 to 2005. Part of my plan is to move some database's to 2005, but use the 2000 compatibility mode for the short term. My issue is this, our DR boxes are still on SQL Server 2000, would I still be able to use our log shipping processes? Or would I be better off in starting with migrating the DR boxes to 2005 first?
I upgrade to sql 2005 from sql2000. The problem is that the compatability mode 90 is not available, 8.0 and 7.0 are, when looking at the compatability level in the properties tab. THe GUI, Management studio is loaded and operational.
What do I need to do to make the 90 compatability mode available?
what are the SS rules around forwards compatibility? For example, I have a SS2014 DB instance but I'm able to query it through SSMS2012 Express. Also, I backed up a SS2012 database and restored it into a SS2014 instance which works as expected. Should I also be able to back up a SS2014 instance that was restored from SS2012 and restore it into a separate SS2012 instance?
I have checked my Production databases, both are went to Disconnected state, both are unable to connect to the application, i checked all the services and its working fine. and restarted the end points on Principal and Mirror, still issue not solved, after restart the witness server End point both the databases are working fine.
We have killed a job which is now in KILLEDROLL BACK state. Job activity monitor is not showing any running jobs but I can see the SPID of that job. When tried to kill again its giving the message ‘command completed successfully‘, not able to get the percentage or time for the roll back to complete.
Another DBA tried to create a snapshot and it was stuck and I believe it was because of this ROLLBACK as both were using same Database.
begin try declare @param2 int begin transaction exec proc2 @param2 commit transaction end try begin catch if @@trancount > 0 rollback transaction end catch
i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.
I have a DB, MDF is about 170GB, log 200GB. When I do a restore of it, the backup takes about 20 minutes to get to 100% but then it just stays there in Restoring Mode.
I have specified the WITH RECOVERY option.
What is happening that it doesn't commit the backup for some reason?
I have a bat file that kicks off a master package, which kicks off about 300 child packages. My bat file is working correctly, however there seems to be issues executing packages in 64 bit mode.
So my question is...
Is there a way to specify in the bat file to execute packages in 32 bit mode and not 64 bit?
bat file: "C:Program FilesMicrosoft SQL Server110DTSBinnDTExec.exe" /f "E:MasterPackage.dtsx"
Is it possible to put master database to single mode, if yes then whats the procedure. The reason behind the scenario is to restore master database from backup for which its strictly recommended to put the database in single mode. But I m not able to do so.
