Month: July 2014

Notification Test

Notification emails telling a DBA that something failed are all-well-an-good, but if you don’t get any emails, how can you be sure the notification system is working?

The simplest solutions are often the best. I’ve taken to scheduling an 8am job (on every SQL Server that runs jobs) called ‘Notification Heartbeat’ that contains just an empty step. It’s own notification configuration is set to email the ‘DBA’ operator every time it succeeds.

Of course, I then have an Outlook rule that moves them (all with ‘Heartbeat’ in the subject), to the ‘DBA’ folder so I dont have to read them every day.

Now when my morning inbox is suspiciously empty – I have a way to verify that the Notification system was tested end-to-end at 8am.

NOTE: I used to try combining the notification-test with actual working Jobs like Backups, but this is simpler and the message value is clearer.

Remove oci.dll

When trying to uninstall Oracle Client (from a Windows server) the standalone deinstall tool couldn’t remove the file “oci.dll”.

To remove it (without rebooting) I tried to manually delete the file. The error message indicated which Windows service I had to stop. (IE: First it was locked by “SQL Server” then when I’d stopped that “VMWare Tools”, then DCOM, then COM+.

After stopping all the locking services in order – I was able to delete the file.
Lastly I restarted all the services I’d stopped 😉

Removing Oracle Client

Whilst setting up a Linked-Server connection from SQL-2012 to Oracle the installation of “Instant client for Windows x64” failed.

To clean-up the mess left I downloaded the ‘Oracle Deinstall Tool’ from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html by clicking ‘see all’ next to Windows x64 within the Oracle 11g2 section.

To use the tool, I opened a command-prompt on my windows-server and changed directory to the folder containing ‘deinstall.bat’ from the above download. The command that worked for me was “deinstall -home D:\Oracle\Client_64”.

BTW@ The path-part of the deinstall command (above) was the ‘Oracle_Home’ location. There are a bunch of ways to find that. I looked in the Oracle Universal-installer.

Finally, I tidied-up my local (enviroment?) Path, by cutting/ pasting from/ into the ‘path’ command – to remove all traces of Oracle.

Cannot truncate table xxx because it is being referenced by a FOREIGN KEY constraint.

When you want to empty a table, and trancate returns this not-very-helpful error message …

TRUNCATE TABLE dbo.SomeTable

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table ‘dbo.SomeTable’ because it is being referenced by a FOREIGN KEY constraint.

Try using delete instead …

begin tran
DELETE dbo.SomeTable
rollback tran

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint “FK__SomeTable__7BFD7C01”. The conflict occurred in database “SomeDatabase”, table “dbo.SomeTable”, column ‘SomeColumn’.

… for a more helpful message.

Dropping & (re)Creating Foreign-Keys

I noticed that when I script-out the dropping and re-creating of a foreign key constraint it would only work once – because the name-portion would be missing … 

ALTER TABLE [dbo].[SomeTable] DROP CONSTRAINT [FK__SomeTable_SYS__FRSIn__1BFD2C07]
GO
ALTER TABLE [dbo].[SomeTable] WITH CHECK ADD FOREIGN KEY([SomeColumnName])
REFERENCES [dbo].[SomeOtherTable] ([SomeColumnName])
GO

The soution was to cut and paste the name portion from the DROP statement into the ADD statement …

ALTER TABLE [dbo].[SomeTable] WITH CHECK ADD CONSTRAINT [FK__SomeTable_SYS__FRSIn__1BFD2C07] FOREIGN KEY([SomeColumnName])
REFERENCES [dbo].[SomeOtherTable] ([SomeColumnName])
GO

Refreshing data (mid-migration)

After I migrated some database from SQL 2000 to SQL 2012 (via SQL2008r2) I was unable to get the front-end applications switched to point to the new backend straight away.

This resulted in the unused SQL 2012 databases gradually becoming out-of-date.

To update them without the pain of repeating the migration I found that I could use the SQL 2012 Import Wizard.

There were three tricky bits to this …

1) to use [Microsoft OLE DB Provider for SQL Server] to connect to the SQL 2000 server
2) to select all Tables but not Views, and
3) To edit the mappings for each table choosing the radio-buttons to [Delete rows in destination Table] and [Enable identity insert]

After this it was simply a matter of saving the output SSIS packages to jobs.

** Update ** … or so I thought. This failed for one database that contained Foreign-keys. So I had to create a ‘pre update’ step to remove the constraints, then a ‘post update’ step to put them back (see next).

Database Compare

Here’s a neat script I wrote to list every database with the total number of rows in it.

The idea was to do a quick-n-dirty post-migration checksum between the old sql2000 and new sql2012 databases – to see which ones needed updating.

--database_compare.sql

EXECUTE sp_MSforeachdb 'use ?; DBCC UPDATEUSAGE(0) WITH COUNT_ROWS;'

create table #temp (DBName varchar(100), TotalRows int)
exec sp_msforeachdb 'insert into #temp select ''?'', sum(rows) 
from [?].[dbo].sysindexes i join [?].[dbo].sysobjects o on o.id=i.id
where indid < 2 and type=''U'''

select * 
from #temp
where DBName not in ('master','model','msdb','tempdb')
order by 1--2

drop table #temp