Defragmenting a SQL Database

Issue

Generally, it is not necessary to defragment a SQL database. SQL generally does a good job of managing file resources. However, there are times when it may be necessary. One example is a database that is stored on a drive fairly full ( > 80% utilization). In this case, DB expansions are likely to result in excessive fragmentation, especially if you have not changed the default 1Mb DB expansion size.

Resolution

There are two major ways to defrag a SQL database.

1. Take the database offline and detach it. Move the files to a second drive. Defragment the source volume and move the files back, then re-attach the database.
2. The second option lets you defrag the files where they sit, while users are using the database. Download the Contig.exe utility below. This is a defragmentation utility produced by SysInternals that allows you to defrag a single file. Run contig /? from the command line to retrieve a list of available commands. Notably, contig -a <filename> will return an analysis of the file and list the number of fragments. Contig also supports wildcards, so the command contig -a -s *.mdf will return a list fragmentation in all .MDF files in the current folder and all sub-folders.

Lastly - Please, for gosh sakes, backup any files before you make such a low-level change.

Resources

http://technet.microsoft.com/en-us/sysinternals/bb897428

Syntax

Usage:

contig [-a] [-s] [-q] [-v] [existing file]

or contig [-f] [-q] [-v] [drive:]

or contig [-v] -n [new file] [new file length]

-a: Analyze fragmentation

-f: Analyze free space fragmentation

-q: Quiet mode

-s: Recurse subdirectories

-v: Verbose

Contig can also analyze and defragment the following NTFS metadata files:

$Mft

$LogFile

$Volume

$AttrDef

$Bitmap

$Boot

$BadClus

$Secure

$UpCase

$Extend

Moving System Databases with SQL 2008 R2

Issue

Sometimes after installation, the SQL System databases need to be moved to a different location. Use the steps below to accomplish this task. The steps in this article apply to SQL 2008 R2. See the link under the sources column for information on other versions of SQL.

Resolution

Step 1 - alter the database location in SQL (see sample move script below)

Step 2 - stop the SQL service

Step 3 -move the physical files to their new location

MAKE SURE THE FOLDER AT THE NEW LOCATION HAS THE SAME PERMISSIONS

Step 4 -update the location of the MASTER database MDF and LDF files in the Registry (See Registry File below)

Step 4 - restart the SQL service

Step 5 - verify the file change by running the Check File Location script below

Step 6 - If DBMail is already setup and MSDB database is moved, verify that Service Broker is enabled by running the Check Service Broker script below

 

Sample Move Script

alter database MASTER MODIFY FILE ( NAME = master, FILENAME = 'F:\SQLData\SystemDB\master.mdf' );
alter database MASTER MODIFY FILE ( NAME = mastlog, FILENAME = 'F:\SQLData\SystemDB\mastlog.ldf' );
alter database TEMPDB MODIFY FILE ( NAME = tempdev, FILENAME = 'F:\SQLData\SystemDB\tempdb.mdf' );
alter database TEMPDB MODIFY FILE ( NAME = templog, FILENAME = 'F:\SQLData\SystemDB\templog.ldf' );
alter database MODEL MODIFY FILE ( NAME = modeldev, FILENAME = 'F:\SQLData\SystemDB\model.mdf' );
alter database MODEL MODIFY FILE ( NAME = modellog, FILENAME = 'F:\SQLData\SystemDB\modellog.ldf' );
alter database MSDB MODIFY FILE ( NAME = MSDBData, FILENAME = 'F:\SQLData\SystemDB\MSDBData.mdf' );
alter database MSDB MODIFY FILE ( NAME = MSDBLog, FILENAME = 'F:\SQLData\SystemDB\MSDBLog.ldf' );

Check File Location Script

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

 

Check Service Broker Status Script

SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb';

 

Before and After Registry Settings for SQL 2008 R2

Before Registry File

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters]

"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\master.mdf"

"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\Log\\ERRORLOG"

"SQLArg2"="-lC:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\mastlog.ldf"

After Registry File

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters]

"SQLArg0"="-dF:\SQLData\SystemDB\master.mdf"

"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\Log\\ERRORLOG"

"SQLArg2"="-lF:\SQLData\SystemDB\mastlog.ldf"

 

Sources

This article applies to SQL 2008 R2. For more details or other versions and for troubleshooting information, visit

http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx

Setting up SQL Notifications and Alerts

When running a SQL server, it’s handy to let it tell you how things are going, rather than having to go look at the logs every so often and see if anything’s broken.

Here are the steps to do this. In this case, I have a large number of clients. To standardize, I setup a single GMail account to use for the outgoing email notifications for all the servers I manage. If you have an internal mail server or are using something other than GMail, you’re a big kid now - plug your settings in.

Tasks:

• Setup your mail account
• Setup Database mail
• Setup Operators
• Setup Alerts
• Maintenance Plan Failure notification
• Testing / Debugging
• Resources

Note: These settings are Instance specific, so you'll need to do this for EACH instance.

 

Setup your Mail Account

Setup your GMail account. Verify that you can send / receive emails.

Setup Database Mail

Setup Mail Profile

• SQL Mgt Studio
• Management Folder / Database Mail / Right Click / Configure
• Next / Setup up DB Mail / Next (If prompted to enable Database Mail, choose Yes)
• Profile Name: Alert Notifications
• Add new SMTP Account
• Account name: {Descriptive name of your email user} 
• Email address: {yourmail@server.com}
• Display Name: SQL Alert – {Server \ Instance name} IE: JumboCorp \ CounterPoint
• Reply email: {yourmail@server.com}
• Email Server: smtp.gmail.com
• Port: 587
• Require secure connections: checked
• Enter the basic authentication username / password. Note that for GMail you’ll use your full email address
• Click Next
• Check Public Profile / Set Default Profile to Yes
• Click Next
• Accept defaults for system parameters / Next
• Review / Finish / Close

To test, right click Database Mail, choose Send Test E-Mail.

Setup Operators

• Right Click Operators under SQL Server Agent / New Operator
• Name: {Descriptive name for destination email address} 
• E-mail name: {destinationemail@server.com}
• Save

Setup additional operators if other users wish to be notified of SQL Alerts

 

Setup Alerts

Enable Alerts in the SQL Agent

• Right click SQL Server Agent / Properties / Alert System

• Enable mail profile
• Mail system: Database Mail
• Mail Profile: Accelerando Notification
• Ok
• Restart the SQL Server Agent service
  • (If notifications don't go out after a SQL Agent restart, you may also need to restart the SQL Service)

Setup Alerts - General Alerts

Run the this script against the Master database.

 

Enabling Alert Notification

After the alerts are created, you'll need to activate notification for each of them (one at a time)

• Right click the alert / Properties
• Response Tab / Notify operators: checked
• Check email for Accelerando SQL Alerts
• Click Options Tab / Include alert error in: Email (checked)
• Delay between responses: 180 (3 hours)
• Ok

 

Large Log File Alert

Additional alerts - these cannot be scripted because they depend on specific databases. You'll need to create an alert for each of the databases you wish to monitor.

• Right click Alerts under SQL Server Agent / New Alert
• Alert name: {Servername / Instance Name - DBNAME} DB - Large Log File Alert
• Type: SQL Server performance condition alert
• Object: SQL Server Databases
• Counter: Log File(s) Size (KB)
• Instance: Choose the DB to monitor
• Alert if counter: rises above
• Value: {Insert reasonable number here. For large databases 50000000 would be 50GB)
• Enable the email alert notifications as in the section above

Maintenance Plan Success / Failure notification

Open the maintenance plan to which you'd like to add notifications. You can add notifications to just the backup task or to multiple tasks. To add a notification, use the following steps:

• Grab the Notify Operator Task and drag it into the maintenance plan.
• From the step on which you'd like to receive the notification, grab an available component output and connect it to the Notify Operator Task. Right click on the component output and choose whether you'd like the notification to happen on success or failure.
• Edit the Notify Operator Task and check any operators that should be alerted in this notification
• In the Subject line, enter {Server\Instance} - {Action} Failed / Succeeded
  • For example: JumboCorp\DynamicsGP - Transaction Log backup failed
• In the notification message, repeat the above line.
• Repeat the steps above for any other points in the plan that you'd like notification for.
• NOTE: If you want to receive both a success and failure notification, you'll need to add two separate Notify Operator Tasks

Testing / Debugging

Test Alert Level Notifications

Run the following script against the Master database. It should trigger an alert notification and email response.

RAISERROR('This is a test', 17, 1) WITH LOG

Checking Sent Mail status

Run the following script against the Master database. It will return a list of the emails that have been sent and show the status of each of the emails

SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_event_log

If sent emails fail, test by installing an email client on that machine with the same configuration as above.

Incoming mail server: pop.gmail.com
Port: 995
Require SSL: Checked

You can download Windows Live Mail here -
http://explore.live.com/windows-live-essentials-other-programs?T1=t2

Only install the email client (live mail)

Resources

SQL Server 2008 : Database Mail - Using SQL Server Agent Mail
http://mscerts.programming4.us/sql_server/sql%20server%202008%20%20%20database%20mail%20-%20using%20sql%20server%20agent%20mail.aspx

Setup SQL Server 2008 Maintenance Plan Email Notifications
http://808techblog.com/2009/07/setup-sql-server-2008-maintena.html

SQL Server Alerts: Soup to Nut
http://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/

SQL Script - General Alerts

Requirements

DBMail activated
Run against Master DB

 

Script

USE [msdb]
GO

/****** Object:  Alert [Alert Level 17 - Insufficient Resources]    Script Date: 11/16/2011 12:20:18 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 17 - Insufficient Resources',
        @message_id=0,
        @severity=17,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 18 - Nonfatal Internal Error]    Script Date: 11/16/2011 12:20:18 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 18 - Nonfatal Internal Error',
        @message_id=0,
        @severity=18,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 19 - Fatal Error in Resource]    Script Date: 11/16/2011 12:20:18 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 19 - Fatal Error in Resource',
        @message_id=0,
        @severity=19,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 20 - Fatal Error in Current Process]    Script Date: 11/16/2011 12:20:18 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 20 - Fatal Error in Current Process',
        @message_id=0,
        @severity=20,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 21 - Fatal Error in Database Processes]    Script Date: 11/16/2011 12:20:18 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 21 - Fatal Error in Database Processes',
        @message_id=0,
        @severity=21,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 22 - Fatal Error: Table Integrity Suspect]    Script Date: 11/16/2011 12:20:19 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 22 - Fatal Error: Table Integrity Suspect',
        @message_id=0,
        @severity=22,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 23 - Fatal Error: Database Integrity Suspect]    Script Date: 11/16/2011 12:20:19 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 23 - Fatal Error: Database Integrity Suspect',
        @message_id=0,
        @severity=23,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 24 - Fatal Error: Hardware Error]    Script Date: 11/16/2011 12:20:19 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 24 - Fatal Error: Hardware Error',
        @message_id=0,
        @severity=24,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object:  Alert [Alert Level 25 - Fatal Error]    Script Date: 11/16/2011 12:20:19 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert Level 25 - Fatal Error',
        @message_id=0,
        @severity=25,
        @enabled=1,
        @delay_between_responses=10800,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

My Top 5 SpyWare Utilities

It’s handy to have them all in one place when you have to clean a PC (most likely belonging to a family member or colleague)

Download these 5 and go to town…

• RKill by BleepingComputer (Kills known spyware processes)
• TDSKiller from Kaspersky (RootKit detector)
• Ad-Aware from LavaSoft (Spyware cleaner)
• The free version of AVG Antivirus from AVG (Free Anti-Virus)
• MalwareBytes (Another Spyware cleaner)

Use another computer and download these 5 utilities. Save them on a thumb drive. Boot the computer that’s having problems. I would NOT connect it to a network or the internet yet.

Run the RKILL utility first. Then install and run each of the other utilities as they are. If you have to reboot for some reason, run RKILL again before you start running the others after it boots up.

Connect the computer to the internet and update and rerun each of the applications again.

Changing Unit of Measure Schedules in Dynamics CRM when integrated with Dynamics GP using the Connector

Environment: Dynamics CRM 2011 / Dynamics GP 2010 / Connector for Microsoft Dynamics

Recently a client changed the base unit of measure from Box to Each for one of their unit of measure schedules. The connector, of course, wouldn’t properly integrate the new mapping because it had already been assigned to product lists. The sequence to get it changed is as follows -

GP is the master for the Unit of Measure schedule, so start there.

• In GP, find any products that are using the Unit of Measure schedule you want to change and switch them to another schedule. Note that you’ll be warned that you will have to recreate the price lists and purchasing options for this product.
• Run the Price Level to Price List map in the Connector. After it runs, verify that your products in CRM are switched to the new Unit Group
• Delete the Unit of Measure schedule in GP
• In CRM, deactivate and then delete the old Unit Group
• In GP, create the new Unit of Measure schedule with the correct Base Unit
• Run the Unit of Measure Schedule to Unit Group map and check the log to verify that the change was brought over successfully.
• In CRM, verify that the new unit group is available.
• In GP, change the products to the correct Unit of Measure schedule
• Recreate the Price Lists and Purchasing options for those products
• Run the Price Level to Price List map in the Connector. After it runs, verify that your products in CRM are switched to the new Unit Groups.

Unit of Measure Schedule Troubleshooting with CRM Connector for GP

When running the CRM Connector for GP, one of the maps that integrates the two systems is a UofM Schedule to Unit Group map that transfers the GP Unit of Measure schedules into CRM.

One of the requirements for the map to run successfully is that the UoM schedules in GP can’t have repeating values in the schedule. Take for example the situation below.

Schedule for Gallon
Unit Name	Quantity	Equivalent
Gallon	1.000	Gallon
Gallon	0.018	Drum
Drum	55	Gallon
Schedule for Drum
Unit Name	Quantity	Equivalent
Drum	1	Drum
Drum	55	Gallon
Gallon	0.018	Drum

A limitation of CRM is that a Unit Group cannot contain the unit name more than once. During the map’s integration, only the first 2 lines of each schedule would be integrated, the connector stops processing with the first duplicate is detected.

Finding those duplicates can be a little tricky though. You’ll be able to see them in the log for the map in the Connector for Dynamics mapping client. However, this isn’t very descriptive. Here’s an example of an error message.

[UofM Schedule to Unit Group] has encountered an error while processing key [GAL]. A Unit named GAL already exists in the specified Unit Group which may or may not have the same Base Unit.  Microsoft Dynamics CRM does not currently permit Units to have the same name when they belong to the same Unit Group.

Once you do find the U of M schedule that’s causing problems, it’s sometimes challenging to find the duplicates. It would also be handy to know if there’s going to be any problem rows before you Activate the map.

I’ve written a script to help you find (and mark) the problem rows before you even start the integration.

Search for Duplicate UofM Schedule Rows

SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)

Return all the rows from IV40202 that are duplicates

SELECT *
FROM IV40202 WHERE (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
IN (
SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)
)

To make the records easier to find in GP, we can use the Long Description field for the row as an indicate.
NOTE: THIS WILL OVERWRITE THE LONG DESCRIPTION FIELD FOR ANY RECORD THAT’S A DUPLICATE!!

Update Long Description to indicate problem rows

UPDATE IV40202
SET UOFMLONGDESC = 'Duplicate'
WHERE (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
IN (
SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)
)

Now, if you open the Unit of Measure Schedule in the GP client and pull up one of the problem schedules, you should be able to expand the rows and see the lines that are causing or will cause problems.

Update:

In the above example tables, you’ll encounter errors when you try to delete the duplicate rows. GP doesn’t want you to have the base unit of measure in the schedule more than once, even though it allows you to enter it. In this case, you’d have to remove the extra row from the SQL table. Beware, modifying data directly in SQL is NOT supported by Microsoft.

CounterPoint v7 Gift / Credit Card Expiration

One of the applications I support is Radiant’s CounterPoint Point of Sale software. In the v7 version of the product, we had an incident where a customer had a gift card that had an available balance and an expiration date in 2020.

When swiped, the card would read properly, then come back with a ‘Card Expired’ error.

The answer is a little obscure, but it’s a work-around that was done to make the product Y2K compliant. Since CounterPoint v7 is written in COBOL, it maintains only a 2 digit year record. To get around this, it also maintains a ‘current century’ setting that defines to the system what years fall within the current century. By default, that is set to 1920 – 2020. So with the card expiring after 2020, the system thought it was reading 1920 and errored out.

To resolve, click through to Setup / System / Company / Option #7 is the current century.

Update to some time before company opened (eg: 1980 - 2080)

Done!