Analyze MSSQL database table sizes

On a previous article (http://raunomagi.blogspot.com/2013/01/shrink-all-databases-on-microsoft-sql.html) I showed, how to reduce the sizes of database and log files. But, what if your database size is still too large. Then you need to inspect your Microsoft SQL Server database(s) using the following SQL query (having selected the database to be inspected as default):

SELECT t.name, SUM(a.total_pages) * 8192 AS TotalSpace
FROM sys.tables t, sys.partitions p, sys.allocation_units a
WHERE t.OBJECT_ID = p.OBJECT_ID AND p.partition_id = a.container_id
GROUP BY t.name
ORDER BY TotalSpace desc

This query was inspired from this page (http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database), but I modified the script and removed some unnecessary references and reverse ordered the output using the TotalSpace column. So in the output you see the most heavily used tables first:

How to change/assign/update certificates in Exchange Server 2010

First you need to obtain a certificate from CA. This varies from provider to provider. I manage certificates using Server Certificates section in Internet Information Services (IIS) Manager:

On the right hand pane of Server Certificates you will see commands for importing or requesting the certificate. You can use them for internal or external CA’s.

If you need to create multi-name certificate (SAN or Subject Alternative Name) please use Exchange Management Shell:

New-ExchangeCertificate -GenerateRequest -KeySize 2048 -SubjectName "c=ee, s=Harjumaa, l=Tallinn, o=YourCompany, ou=IT, cn=mail.yourdomainname.com" -DomainName mail.yourdomainname.com, autodiscover.yourdomainname.com -PrivateKeyExportable $True -FriendlyName "mail.yourdomainname.com"

After equal signs (=) you should have: your country code, state, city, organization, OU and common name. Common name is the default name for the certificate (for example mail.yourdomainname.com). The domainname switch is for SAN names (alternative names, like autodiscover.yourdomain.com etc) separated by comma. SAN should include always the common name. FriendlyName is for identifying certificates (for example two certificates with same common name but different expiration date).

This command gives you the certificate request. You should copy and paste it into CA's web form to request a certificate. When you get back the certificate from CA, then you should complete the request by using Internet Information Services (IIS) Manager.

On Windows 2008 based CA you might get the error:

The request contains no certificate template information. 0x80094801 (-2146875391)
Denied by Policy Module  0x80094801,….

Then you will need to use command line to submit the request to CA:

certreq -submit -attrib "CertificateTemplate: WebServer" WebServerCertReq.txt

where the file name in the end of command line contains the output of New-ExchangeCertificate command.

---

OK. Let’s assume, you managed to create a new certificate.

To list all installed certificates, use Exchange Management Shell:

get-exchangecertificate | ft thumbprint,servicesstringform,friendlyname -a

Remember the thumbprint of your new certificate and enter following 4 commands to assign those certificates to 4 protocols (IMAP, POP, SMTP and HTTP).

Enable-ExchangeCertificate thumbprint -Services imap
Enable-ExchangeCertificate thumbprint -Services pop
Enable-ExchangeCertificate thumbprint -Services smtp
Enable-ExchangeCertificate thumbprint -Services iis

Replace the string “thumbprint” with the actual thumbprint from previous get-exchangecertificate command.

Restart POP and IMAP services and do a IISRESET using following commands in powershell as administrator:

get-service *pop3,*imap4 | restart-service
iisreset

If you have CAS array, you should do almost the same procedure on other CAS array members, with the exception, that you don’t need to request new certificate. You must export the existing certificate from first CAS member and import it to other servers.

DPM error “Failed to backup as another backup using the same CSV is in progress.”

DPM 2010/2012 is quite resilient backup software. It tries failed backups after 1 hour again. But it does it only once. Sometimes I still have error messages like:

Failed to prepare a Cluster Shared Volume (CSV) for backup as another backup using the same CSV is in progress. (ID 32612 Details: Back up is in progress. Please wait for backup completion before trying this operation again (0x8007173D))

This happens on hyper-v clusters with shared storage. Of course you can run into other type of error messages. To increase the retry count change AutoRerunNumberOfAttempts registry key to a bigger value using following command:

reg add "HKLM\Software\Microsoft\Microsoft Data Protection Manager\Configuration" /v AutoRerunNumberOfAttempts /t REG_DWORD /d 2

The “/d 2” part specifies the rerun attempts count. I will try with 2 retries and see, whether it gives me less errors to fix on daytime.

To see other registry options see this URL http://blogs.technet.com/b/dpm/archive/2011/06/06/how-to-use-and-troubleshoot-the-auto-heal-features-in-dpm-2010.aspx

How to get rid of multiple Messenger contacts with name “Q” in Skype desktop application

When you are an enthusiastic updater of Skype, then you might stuck in a situation, where you have a lot of contacts with the name Q in Skype:

When I log in from an another Messenger client (for example ebuddy.com), then contact are with normal names.

To get rid of this use following steps:

1. Close Skype from GUI or hit Win+R and type “tskill skype”.
2. Delete Skype user specific profile by hitting Win+R (Start, Run…), type %appdata% and press Enter. From the C:\Users\XXXX\AppData\Roaming\ folder remove or rename the folder with name Skype.
3. Start Skype again and log on with live account.

Of course you will lose all settings (font size, status notifications, automatic save location for received files, privacy settings etc).

How to send e-mail manually using Telnet.exe or PowerShell

Let’s say, you want to send an e-mail to someone@hotmail.com. Then you need to act like a e-mail server.

First of all you need to know which servers serve the e-mail domain (the part after the @ sign). It’s done by using MX records on DNS servers. In our case the domain is hotmail.com. To get the MX records use this command:

nslookup -q=mx hotmail.com 8.8.8.8

The host 8.8.8.8 is good address for external DNS lookups, “hotmail.com” is the domain name and “-q=mx” says nslookup to request only MX records.

The answer section contains several MX records. If you get 0 results, then you cannot proceed (maybe there is a typo in domain name or you don’t have UDP 53 port open to DNS server or something else). Smaller MX preferences mean higher priority. E-mail servers try lower value servers first and if unsuccessful, then use higher value MX records to send e-mails.

Next you need to start “telnet mx1.hotmail.com 25” (you can use any server of those 4 MX records because they all are same priority). When the connection succeeds, type following commands:

helo
mail from:senders@address.com
rcpt to:someone@hotmail.com
data
subject: test

whatever
.
quit

After each line press Enter. Don’t use backspace, because Windows’ telnet.exe doesn’t transmit data at the end of a line, but each character individually. If you fail, press Enter and type the line again (use putty.exe if you need to use backspace).

HELO command needs sometimes the “hostname” after the HELO word separated by space. You can type anything in this place, but some anti-spam measures might want you to use some real name (you can use http://myip.eu to figure out the right hostname to use on your computer).

MAIL and RCPT commands require sometimes e-mail addresses to be inside angle brackets <>. DATA command starts actual e-mail content. You can use just Subject part in the header. Empty line is needed between the header and message body section. Message body will end with a line with a dot (.) as the only character on the line.

If you need to test same stuff more often, then you probably use PowerShell to send the message (instead of Telnet). The command line is pretty simple:

Send-MailMessage -SmtpServer mx1.hotmail.com -To someone@hotmail.com -From senders@address.com -Subject "test" -Body "whatever"

Cloned computer missing from WSUS

When you count your computers in Active Directory and compare it to computers in Windows Server Update Services, you probably see a difference. In my case I saw several computers missing in WSUS. After investigating, I realized that those computers were cloned.

In administrative PowerShell window on WSUS server computer I checked, that the computer really doesn’t exist:

Get-WsusComputer –NameIncludes PartOfYourComputerName | ft -a

Actually you don’t need to sysprep those working systems. All you need is following four lines (in web browser they will wrap to a little more lines) running in administrative command prompt on WSUS client computer:

net stop wuauserv
REG DELETE "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v SusClientId /f
wuauclt /resetauthorization /detectnow
wuauclt /reportnow

And almost instantly you can see this computer appear on WSUS management:

Outlook 2013 one year limit for item searching

If you are used to Outlook 2010 searching experience, then the first thing you notice in Outlook 2013 is, that the search gives you only up to 1 year old results with the text “Showing recent results…”. When you click “More”, then older stuff will be searched from Exchange Server. Actually full timeframe search will be carried out and local cache is not used.

So we used local computer power to search something and then again Exchange Server resources to search again. It seems a wasteful way to search things.

This search limit is there because Outlook 2013 stores only 1 year old items in local copy of the mailbox (.OST file). Luckily this behavior can be customized in mail profile. Following procedures are with keyboard shortcuts. If you find some of them useful, you can leave a comment on this blog entry.

1. Open Control Panel (Press Win+R, type “control”, press Enter)
   
2. In the search box type “mail” (in Windows 8 the cursor is automatically in search box), wait for search results to appear, then hit TAB so that the Mail icon text is highlighted and press Enter.
   
3. Mail Setup windows appears. Press Enter or Space to press the selected “E-mail Accounts…” button.
   
4. In Account Settings window press ALT+A to press the “Change…” button
   
5. Move the slider from 12 months to the rightmost option “All”. Hit 3 times TAB and press END.
   
6. Close all opened windows. Press 2 times Enter (Next and Finish) and then 3 times ALT+F4 (Closes active window).

Close and open Outlook. Wait for entire mailbox to be synchronized and enjoy fast local search. And you can search everything offline too.