How to check the progress of the ‘Shrink Database’ task in SQL Server 2005

My first blog post in a long time!

So I archived some data from our database yesterday and I needed to shrink the database file so I ran the “Shrink Database” task in Management Studio.  The database is over 500GB in size and I checked 7 hours later and the database shrink operation is still going.  I got a little worried because someone needs to use this database today and I’m not sure if the task would be done by then because the GUI is not showing the progress.  I needed to know where the DBCC Shrink operation is currently at or if it’s even doing anything so I can cancel if it doesn’t look like it will finish in time.

The next step I took of course is open up Firefox and used my favorite search engine to look for a way to check the progress.  I found this website that explained it and all I needed to do is open Query Analyzer and query sys.dm_exec_requests and look for DbccFilesCompact in the command column.

Here’s an example:

SELECT percent_complete, start_time, status, command, estimated_completion_time,
cpu_time, total_elapsed_time
FROM sys.dm_exec_requests

In my case, the percent_complete was already at 94.xxxx and slowly moving so that made me happy.  Going from 94 to the end took about 5 hours, by the way.  The entire operation took around 12 hours.

Can’t connect to SQL Server 2005 using PHP

I was updating our web application last Friday and I needed it to connect to an SQL Server 2005 Express Edition database to query some tables but my application couldn’t connect to the MSSQL server.

After a few hours of searching the internet and trying different things I finally got it to work by downloading a newer version of ntwdblib.dll (version 2000.80.194.0) and replacing the one that came with PHP 5.2 (we’re using WAMP (Windows, Apache, MySQL, PHP) btw).

In our case, I had to put it in wamp\php, wamp\Apache2\bin, and C:\Wndows\System32.

Also, in your application, don’t forget to add the instance name of the SQL Server in your connection string (eg. server1\instanceName).

Here’s an example connection string for ADOdb (it worked for me without specifying the port number):

‘mssql://admin:password111@webserver01\\SQLEXPRESS/Database1′

I don’t remember the URL where I found this solution, I’ll post it here if I find it again.