Siusic Dot Com

Today is the first weekend that I feel relax after l was looking for a new job and started working for another company. I brought my family to Laguna beach. It is not far from my apartment. It is Marvin’s first time to touch the ocean. He was very exciting. I took some photos. Laguna beach is one of the famous California beaches.

Marvin at Laguna beach

Multi-million dollar houses over seeing Laguna beach

In my previous post “What is Fragmentation and How to Defragment a Table Being Used Consistently?” I told you that DBCC INDEXDEFRAG command was the solution to defragment in a 24/7 environment. This is only true for SQL 2000.

In SQL server 2005. There is a new way of dealing with it. When you set up a maintenance plan using the index rebuild task you will find there is an option to Keep index online while re-indexing. See the following screen shot.

When this option checked SQL server uses the following statement to rebuild indexes behind the scene

ALTER INDEX IndexName ON TableName REBUILD WITH ( ONLINE = ON ).

This is a new feature in SQL 2005. However If you run this statement you may experience error saying this feature is only available in SQL 2005 enterprise edition. Microsoft wants money from the advance feature that it provides on SQL 2005. So for standard SQL 2005 you will have to rely on DBCC INDEXDEFRAG. There is a new command to replace DBCC INDEXDEFRAG in SQL 2005 though. It is ALTER INDEX statement with the REORGANIZE clause

Example: ALTER INDEX IndexName ON TableName REORGANIZE .

In a recent problem that I was trouble shooting with, I was told that a store procedure which had been running well for a long time was taking exceptionally long time to run and experienced “lock timeout“. When I used the sp_who or sp_who2 system store procedure to look at the running processes one told me that the store procedure was blocked by the AutoShrink process and the other one told me the store procedure was blocking the AutoShrink process. Looks like the process of the store procedure and the AutoShrink process was in dead lock.

It hung the database which the store procedure was running against. When I kill the process that ran the store procedure I saw the same message, “Estimated Rollback Completion: 0%. Estimated Time Remaining: 0 Seconds“, which I talked about in my previous post. However no matter how long I wait this killed process was not able to finish the rollback process. So I wanted to killed the AutoShrink process. When I did that it said that I can not kill a system process. Looks like restarting SQL Server was the only way to solve the problem. In fact I don’t think there is way to kill a AutoShrink process. If you find one please let me know.

However when I look into the code of the store procedure to try to find out what the store procedure was trying to do I found some statements like the following.

OPENDATASOURCE (’SQLOLEDB’,'Data Source=ServerName;User ID=CheckUser;Password=Password’).databasename.ownername.tablename

It uses the OPENDATASOUCE function to open an other connection to the server itself and try to read a big table in another database using a different login. When I ran sp_who again I found there were process running under the login CheckUser and the host that initiated the process was the server itself. All of a sudden I understood what happened. Even though the process of running the store procedure was killed it was still waiting for the process initiated by OPENDATASOURCE function to finish its job. And killing a parent process will not automatically kill the child process initiated by the parent. I went ahead and kill the process running under CheckUser and the parent process that ran the store procedure disappear in the sp_who result. That means the process was finally stopped and the database was back to normal.

Maybe I should question why to use OPENDATASOURCE to connect to the server itself. There might be all kinds of historical reason for a specific environment.

A coworker came to me for help today when he tried to add two columns on to a very big table that contained 165 million records. He added these two columns to the table using SQL server management studio and after waiting for almost two hours he got query timeout message. So I looked at SQL server using activity monitor. You can see where Activity monitor resides in the following console tree

It shows that the process adding the two columns was still running so I looked at the statements that was executed against the database. It was like

ALTER TABLE table_name ADD column_name VARCHAR(20) NOT NULL DEFAULT ‘DefaultValue’

I issued a kill statement to kill that process. The statement was like Kill 55. The process turned into Killed/Rollback status in activity monitor. And when I issue the same Kill statement again it showed the following information.

“Estimated Rollback Completion: 0%. Estimated Time Remaining: 0 Seconds.”

I issued the same kill statement again after two hours it still showed me the same message so I was wondering what was going on and I thought the rollback process must be hung. I search online for a solution and seems to me restart SQL service was the only way to clear it but that was a production server and I could not do that. I finally discover the best solution and that is to sit back and wait. Because the information report from the kill statement or “Kill spid statusonly” statement doesn’t report the correct information sometime. You simply can not trust what it said. If the process shows Killed/RollBack it must be in the Rollback process and you should feel safe to just wait on it. A lot of forum will tell you to restart SQL service but that doesn’t do any good. SQL server still have to continue the RollBack process after it is restarted.

The best approach is to make sure the Rollback process is not blocked by other process. Activity monitor will tell you that. When that is made sure then you basically need to sit back and wait for the Rollback to finish.

Here are the information of blog income in May 2008. It was down from April. There was a problem on Google Analytics and the traffic statistic in April and May may not be correct. Compare to April, May has 25% more unique visitors but traffic is about the same. And the traffic down turn observed in April looks like a normal fluctuation now.