April 20, 2007 at 9:33 am
Hi All,
I have a SQL 2005 installation with Sp2, I have noticed some few things that might be worth noting.
When you are creating an index on a table in SQL 2005 Sp2, you cant view database properties at thesame time, the dialogue box times out due to a deadlock (index creation blocks is)
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Also, if you are creating an index, it gets blocked by a process called SQL_DMO1.
Anyone has any ideas on the solution to these issues.
How many of us also have SQL 2005 Sp2 installed ?
April 20, 2007 at 12:43 pm
When creating an index it's an offline operation, SQL never allowed access to tables while creating or rebuilding indexes by default. You can specify with online option, but it will take longer to generate an index. That's probably why you're getting a timeout.
April 20, 2007 at 9:16 pm
Here is the list of bugs fixed in sp2...
http://support.microsoft.com/kb/921896
I am not sure it is a bug and as pervious poster mentioned your create index is not online operation unless it is specify and sql server should EE.
May database properties is checking the indexes and sql server default isolation is read committed so that it is blocking...
MohammedU
Microsoft SQL Server MVP
April 23, 2007 at 3:57 am
Something I found in SP2... You can ALTER a database to use the new vardecimal format, except that you cannot ALTER any of the system databases to accept vardecimal.
It does worry me that model and more importantly tempdb cannot be set to use vardecimal. Has anybody noticed any problems with this?
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply