January 21, 2015 at 3:15 pm
ALTER TABLE [dbo].[TableNameExample] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)
The table is 110 GB, so will take time to compress. But it is one table in a database with 60 tables. Why would executing this statement, cause lock timeout on Object Explorer in SQL Server Management Studio? Users cannot drill down objects in this database without getting lock timeout. When I cancel the compression script, users of SSMS can access objects in this database again from the GUI. Why does compressing a specific table affect access to all tables metadata? I cannot find anything on the internet but I am sure this has happened to other people.
[font="Courier New"]____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog[/font]
January 21, 2015 at 3:29 pm
Thordog (1/21/2015)
ALTER TABLE [dbo].[TableNameExample] REBUILD PARTITION = ALLWITH (DATA_COMPRESSION = PAGE)
The table is 110 GB, so will take time to compress. But it is one table in a database with 60 tables. Why would executing this statement, cause lock timeout on Object Explorer in SQL Server Management Studio? Users cannot drill down objects in this database without getting lock timeout. When I cancel the compression script, users of SSMS can access objects in this database again from the GUI. Why does compressing a specific table affect access to all tables metadata? I cannot find anything on the internet but I am sure this has happened to other people.
Experienced the same, haven't looked into it really but guess it would be both or either of the hidden sys.sysrowsets or sys.syspalvalues being exclusively locked during the operation causing this. Suggest not trying to use the Object Explorer while the compression is running.
😎
January 21, 2015 at 3:40 pm
Thanks, it doesn't bother me but I don't want to affect other users! Good to hear someone else has seen this. Thought about creating a job to run it on the server and test if it still affects users object explorer. I imagine it would...
[font="Courier New"]____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog[/font]
January 21, 2015 at 3:51 pm
That's probably the best way around the problem, schedule the job for off-hours/maintenance window execution when no users are affected.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply