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.

How to query a Microsoft Office Access database from SQL Server

I was doing this yesterday to query our call logs which are stored in an Access database.  I figured it would be really nice to be able to query our databases from one application.  The software that came with our phone system to generate reports from the call logs is also not that flexible.  It’s just much easier for me to run reports using SQL Server Query Analyzer plus we can join the tables from other databases with it easily if we need to.

Just open Query Analyzer and execute this command:

sp_addlinkedserver ‘LinkedServerName’, ‘ProductName’, ‘Microsoft.Jet.OLEDB.4.0′, ‘path_to_access_database_file’

Example:

sp_addlinkedserver ‘Server1_NBXdb’, ‘MS Access’, ‘Microsoft.Jet.OLEDB.4.0′, ‘\\server1\nbxcdr.mdb’

To query the database, use this format:

select * from  openquery(linked_server_name,’select * from table_name’)

or

select * from linked_server_name…table_name

For more information, please visit this website where I got this information from:

Configuring a Linked Microsoft Access Server on SQL 2005 Server

How to query Active Directory using SQL Server

Here is one of those things that I wish I had known much sooner.  Very useful and a big time saver.  I just found out about this a few months ago when I wanted to run a report against AD to see if the account info are consistent and up to date, to see which accounts have passwords set to never expire, when they were created/last updated, etc.

So I asked myself whether it’s possible to query AD directly from SQL.  The first thing I did was do a search on Google of course and sure enough I found this website with instructions on how to do it!

Here are the steps:

1. Create a linked server to AD using this command in Query Analyzer:

sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’, ‘ADSDSOObject’, ‘adsdatasource’

2. Create views using the example below, I’m calling them vADUsers and vADGroups (replace calazan and com with your domain, add more AD attributes to the view as needed):

CREATE VIEW vADUsers AS
SELECT *
FROM OPENQUERY (ADSI,
‘SELECT co, whenCreated, whenChanged, sAMAccountName, sn, givenName, displayName, mail, telephoneNumber, mobile,
physicalDeliveryOfficeName, facsimileTelephoneNumber, title, department, company, manager, ipPhone, userAccountControl, badPwdCount
FROM ”LDAP://DC=calazan,DC=com”
WHERE objectCategory = ”Person” AND objectClass = ”user”’)

CREATE VIEW vADGroups AS
SELECT *
FROM OPENQUERY (ADSI,
‘SELECT displayName, groupType, mail, name, info, whenChanged, whenCreated
FROM ”LDAP://DC=calazan,DC=com”
WHERE objectClass = ”group”’)

3. Query the views just like you’re querying a normal SQL table:

I also recommend that you download the free ADSI Edit tool (adsiedit.msc) to see all the attributes available in your Active Directory site.  Microsoft Office Communications Server 2007 and Exchange Server 2007 for example extend the AD schema and you might want to query some of those extra attributes as well.