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.

How to automate Microsoft Office 2007 installation

I was just updating some documentation on our wiki and found some old notes on automating Office 2007 installation.  We upgraded our Microsoft Office software early last year from Office 2003 (and a few Office XP) to Office 2007 and this simple installation script saved us a lot of time. Here are the steps:

Step 1.  Copy the contents of the Office 2007 installation CD (or package) to a network share (eg. \\server\Office12).

Step 2.  Run the Office Customization Tool and create a setup customization file (I got these instructions from a BDD 2007 document on Microsoft’s website).

Read the rest of this entry »

How to change from ACPI Multiprocessor HAL back to ACPI Uniprocessor HAL in Windows 2003

“Warning: Changing the number of virtual processors after the guest OS is installed may make your virtual machine unstable.”

You’ve probably seen this message before while working with VMware, especially if you’ve done physical to virtual migrations.

As a best practice, it is recommended to always start with only 1 vCPU when creating virtual machines and only increase the number of vCPUs if you think it’s necessary and if the virtual machine is actually running applications that can utilize multiple processors to avoid wasting resources.

Increasing the number of processors from 1 to 2 or more is actually not a problem with Windows Server 2003 because it will automatically change the HAL to ACPI Multiprocessor PC.  But setting the number of virtual processors back to 1 won’t automatically change the Windows 2003 HAL back to ACPI Uniprocessor PC.

According to Microsoft, “If you run a multiprocessor HAL with only a single processor installed, the computer typically works as expected, and there is little or no affect on performance.”  But if you’re like me and just want to be absolutely sure that there won’t be issues, switching back to the uniprocessor HAL in Windows Server 2003 is pretty easy:

1. Make sure you have at least Windows Server 2003 Service Pack 2 installed.
2. Shut down the virtual machine.
3. Change number of virtual processors to 1.
4. Power on the virtual machine.
5. In Windows, go to Device Manager -> Computer.
6. Right-click “ACPI Multiprocessor PC” and choose “Update Driver…“.
7. Select “No, not this time” option -> “Install from a list or specific location” -> “Don’t search.  I will choose the driver to install.” -> select “ACPI Uniprocessor PC.”
8. Reboot the virtual machine.

That’s it! You’re all set!

How to send syslog messages to a remote syslog server in Fedora 9

I have a virtual machine set up running Fedora 9 on my home network which I mainly use for SSH tunneling and I just realized that this version of Fedora now uses rsyslog as its default syslog daemon.

I wanted to send a copy of the syslog messages for SSH to my central syslog server so I can easily keep track of login attempts to my server from the outside.

Here are the steps:

1. Open /etc/rsyslog.conf and  add this line:

   • authpriv.* @remote_server_ip_address

2. Restart the rsyslog service: /etc/init.d/rsyslog restart

Change “authpriv.*” to “*.*” if you wish to send a copy of all the syslog messages to the remote server.

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 encrypt HTTP traffic (and bypass most firewalls) using SSH Tunneling with PuTTY in Windows

I’ve been using SSH Tunneling for a while now mainly to encrypt HTTP traffic when using public wireless access points.  I won’t go into detail here, just Google for “SSH Tunneling” if you need more information.  I’m also gonna assume you already have an SSH Server set up.

• Download PuTTY and save it to C:/.
• Open notepad, type in the command below and save it as securetunnel.bat (Note: remove username@ and -pw password if you prefer to get prompted for them for security reasons):
  • c:\putty -D 8080 -P 22 -ssh username@ssh_server_ip -pw password
• Double-click securetunnel.bat to connect to your SSH server.
• Open your internet browser and change its proxy settings to use 127.0.0.1:8080 as the SOCKS host.  To do this in Internet Explorer 7, go to Tools -> Internet Options -> Connections Tab -> LAN Settings -> Check “Use a proxy server for your LAN…” -> Click the “Advanced” button and set the settings just like the picture below:”

• Go to http://www.whatismyip.com and if your public IP shows up the same as your SSH server’s public IP then you’re all set!

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.

How to enable Remote Desktop remotely

I had to do this just a few minutes ago and I figured I should probably post it here.

1. Open Registry Editor by going to Start -> Run and typing in regedit.
2. Go to File -> Connect Network Registry… and type in the name of the computer where you want Remote Desktop enabled.  Click OK.
3. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server of that computer.
4. Double-click the DWORD called fDenyTSConnections and set its value to 0.  Click OK.

That’s it! Remote Desktop is now enabled!