Changing a Table Column to Identity



--1 delete the old column
Alter Table [dbo].[sometable] 
Drop Column [somecolumn]

--2 if it fails ... drop the primary key (or whatever)
ALTER TABLE [dbo].[sometable]

--3 create the new identity column
Alter Table [dbo].[sometable]
Add [somecolumn] Int Identity(1, 1)

--4 restore the primary key (if dropped)
ALTER TABLE [dbo].[sometable]
	[somecolumn] ASC

--5 lastly reset the identity seed
DECLARE @nextid INT;
SET @nextid = (SELECT isnull(MAX([somecolumn]),0) FROM [sometable]);
DBCC CHECKIDENT ('dbo.sometable', RESEED, @nextid);

Odd violation of Primary Key

This error didn’t seem to make sense as the Primary-Key was an identity column – and therefore the Insert that generated the error didn’t include it.

Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint ‘PK_somecolumn’. Cannot insert duplicate key in object ‘dbo.sometable’.

It transpired that the database had recently been restored in an inconsistent state, and the identity column had lost track of how many rows were in the table, and was trying to insert a Primary-Key value that already existed.

The fix was to refresh the identity column with the biggest value it contained, before the Insert …

DECLARE @nextid INT;
SET @nextid = (SELECT MAX([columnname]) FROM [tablename]);
DBCC CHECKIDENT ([tablename], RESEED, @nextid);


I had my first play with SQLCMD on a SQL 2012 Development Server on which the evaluation period had expired. Incidentally this meant SSMS would not run locally (along with maint-plan backups), however the services and other jobs where still working, and remote SSMS could connect and interact as normal.

SQLCMD remind me of Oracle SQLPlus, with the added benefit that you can use the up & down arrow keys to step through history.

— connect (as current windows user)
sqlcmd -S server\instance
sqlcmd -S .\instance

— interact
select @@version

— interact over multiple rows
select name
from sys.databases

— clear the command cache
select @version
select @@version

— redirect output to a file
:out c:\output.txt
select @@version

— reset output back to the screen
:out stdout
select @@version

— quit

A little slice of my dba day

A little slice of my dba life ๐Ÿ™‚ … during morning-checks I noticed the MSDB database was 2.9GB and growing (in utility explorer). I ran the built-in SSMS report ‘Disk Usage by Table’ and found the table sysjobhistory was by far the largest (at 2.6GB).

I checked the sql-job that manages job history – there wasn’t one :). I created sql-job ‘Prune Backup History’ with the command ‘delete from msdb.dbo.sysjobhistory where run_date < convert(char(8), getdate()-30,112)', and scheduled it to run every 5am.

I ran it manually and was disappointed to find the table was still 2.4GB in size. I fired-up SSDT and created a new IS project. I dragged the 'Data profiling Task' to the design surface and configured it to output to \\server\share\msdbprofile. Within 'quickprofile' I connected to the server/database/table.

I saved the package, ran it (made a coffee), then opened the task again and clicked 'open profile viewer'.

Within 'Column Value Distribution Profiles' I found a disproportionate 74% of the run_date values were the same date (about 2 weeks ago). Double-clicking this result I could NOT see the name of the job (in the raw data) but recognised the step-name as belonging to the cdc job I had set-up … about two weeks ago, lol.

I concluded the sql-job was fine, and would dramatically shrink sysjobhistory – in a few weeks time. Cake time ๐Ÿ™‚

Running Checktable in batches

To check for corruption on a large, busy database I was able to use my filegroup checking script on all but one filegroup.

To polish off the final (monster) filegroup, I resorted to creating a database-snapshot (in the morning) and running this (checktable) script during the day.

btw: Whilst I realise checking a database-snapshot is of limited use, I reasoned it was better than the only alternative (not checking).

Before leaving work I would review the results and drop the snapshot (for application optimization), in rediness to create a fresh one (and continue) the next day.


use TheSnapshotName

-- capture table-names from the named filegroup - once
begin try
     into master.dbo.fgtables
     from sys.indexes i
     join sys.filegroups f on i.data_space_id = f.data_space_id
     join sys.all_objects o on i.object_id = o.object_id
     where = 'somefilegroupname'
     order by
end try

-- check tables one-by-one
begin catch
     declare @sql varchar(200)
          while (select count(*) from master.dbo.fgtables) > 0
               select top 1 @sql = 'dbcc checktable ("dbo.' + name + '")' from master.dbo.fgtables order by name
               delete from master.dbo.fgtables where name = (select top 1 name from master.dbo.fgtables order by name)
end catch

Showing row counts with commas

I wanted to display a changing row-count (in a staging table) including commas (eg: 1,234 or 1,234,567).

I tried using STUFF() in a CASE statement to insert commas, then LEFT(), before settling on this, which is more succinct, and scales from 1 to 1,999,999,999

select replace(convert(varchar(20), convert(money, count(*)), 1), '.00', '')
from dbo.sometable

To explain how it works …

– the ‘1’ is a style of VARCHAR that includes commas when converting from MONEY
– then REPLACE removes the ‘.00’ also added during the conversion from MONEY

Database corruption false alarm

To check for corruption on a very large production database I created a database-snapshot to run CHECKDB against.

This found a corrupt-table (“CHECKDB found 0 allocation errors and 4191 consistance errors in table … repair_allow_data_loss is the minimum repair level for the erors found…”).

I tried running CHECKTABLE against the suspect table within the live database, but couldn’t due to lack of space.

Finally I used DBCC CHECKTABLE(sometablename) WITH TABLOCK so dbcc wouldn’t create a background copy on the database to check – and run out of space!

This confirmed the live table was fine (yay! ooo cake).

Arithmetic overflow error converting expression to data type int.

This error message is a sign of the times [sigh]. With databases getting bigger all the time ‘INT’ often needs to be replaced with ‘BIGINT’.

For example my ‘database_sizes’ script used to work just fine starting like this …

select round(sum(mf.size)*8/1024/1000,0) [SizeGB]

But now with Terabyte sized databases, when mf.size is multiplied by 8 its just too big! The (ugly) solution is to explicitly convert INT to BIGINT …

select round(sum(convert(bigint, mf.size))*8/1024/1000,0) [SizeGB]


To change a table column from INT to BIGINT paste any ‘create constraint’ code into a script, then above that create the update statement, then above that work-out the drop constraint code. Here’s an example on a primary-key …


USE [Production]

--1 remove constraint

ALTER TABLE [dbo].[sometablename] DROP CONSTRAINT [someconstraintname]

--2 change int to bigint

ALTER TABLE  [dbo].[sometablename] ALTER COLUMN [somecolumnname] bigint not null

--3 re-create constraint

ALTER TABLE [dbo].[sometablename] ADD  CONSTRAINT [someconstraintname] PRIMARY KEY CLUSTERED 

Resize Datafile

Here’s a simplified version of my previous datafile resizing helper script. I tend to resize most datafiles to 30%free on Friday afternoons for resilience over the weekend ๐Ÿ˜‰


create table #filestats 
(fileid int, fg int, AllocatedSpace int, UsedSpace int, dfile varchar(200), DataFile varchar(200))

insert into #filestats exec('dbcc showfilestats')
update #filestats set AllocatedSpace = AllocatedSpace * 0.0625, UsedSpace = UsedSpace * 0.0625

select DataFile, AllocatedSpace, UsedSpace,
     cast(cast(AllocatedSpace - UsedSpace as numeric) / AllocatedSpace * 100 as int) [Free%],
     cast(UsedSpace * 1.449 as int) [Plus30%]
from #filestats 
-- where datafile like 'E%' -- e-drive only
order by [free%]

drop table #filestats

dbcc checkfilegroup – semi-automatic

On a very-large database I wanted to check each filegroup for corruption with real-time feedback. That is, I did not want to just set a loop going and wait indefinately for the results. Also I did not want info-messages.

The semi-automatic query I wrote (below) runs CheckFileGroup against the smallest filegroup and stops. Then when manually re-run (in the same SSMS session) checks the next smallest filegroup etc.

Then at the end of the day I would know where it was upto.

-- checkfilegroups_vldb.sql
-- use current (snapshot?)

-- create table of filegroup names and sizes
begin try
     select, sum(df.size/128) mb
     into #tables
     from sys.filegroups fg
     join sys.database_files df on fg.data_space_id = df.data_space_id
     group by
     order by mb, name
     select '[F5] to Start'
end try

--check each filegroup starting with smallest
begin catch
     select * from #tables
     declare @sql varchar(100)
     select top 1 @sql = 'dbcc checkfilegroup (''' + name + ''') with all_errormsgs, no_infomsgs;' from #tables order by mb, name
    delete from #tables where name = (select top 1 name from #tables order by mb, name)
    if (select count(*) from #tables) = 0
        drop table #tables
        select 'Complete'
end catch

CDC Update mask

To decode the update_mask 0x00007C00 I wrote out the last 4 digits (‘7C00’, thats those with values plus those to the right) in binary 0111 1100 0000 0000 which (counting from the right) indicated columns 11, 12, 13, 14, and 15 had been updated.

simples ๐Ÿ˜‰

Ok ok. Until I get round to convert the above logic into a big-dynamic-case-statement … here’s a script I adapted from here (

select (select cc.column_name + ', '
     from cdc.capture_columns cc
     join cdc.change_tables ct on cc.[object_id] = ct.[object_id]
     where ct.capture_instance = 'dbo_MYTABLE'
     for xml path('')) as changedcolumns, *
from cdc.dbo_MYTABLE_ct pd
where __$start_lst = SOME_LSN_NUMBER

Converting Delphi datetime to SQL Server

--1of2 -- convert delphi datetime to sql

-- input a delphi float for conversion
declare @input float = 41492.9039269676 -- <<<<<<<<<<< user input

-- seperate the date-part
declare @date int = @input

-- seperate the time-part
declare @time float = (@input - @date)

-- display results
select @input 'delphi', cast(dateadd(dd, @date, '1899-12-30 00:00:00') + @time as datetime) [sql]

--2of2 -- convert sql datetime to delphi

-- input the sql datetime for conversion
declare @input2 datetime = '2013-08-06 21:41:39.290' -- <<<<<<<<<<<< user input

-- display results
select @input2 [sql], cast(@input2 as float)+2 'delphi'


The wait type CXPACKET is no biggie. Its the time spend re-assembling the results from parallel queries.

You can change the parameter ‘Max Degree of Parallelism’, to however many cores your big-queries should be shared among. Best practice is the number of core’s in a single numa (max 8).

You can also change the ‘Cost Threshold for Parallelism’ to set the trigger level for big-queries. The default (‘5’) is usually a bit low.

How to limit sql-job notifications by time

Rolling my own Notifications – I wanted a sql-job that ran every 5 minutes to Notify me when it failed – but not to “spam” me every 5 minutes until I fixed it!

Here’s the simplified last-job-step that would email me the first time the job failed, but then NOT again for half an hour.

declare @email varchar(500), @operator varchar(50)
set @operator = 'DBA'

if (select datediff(mi, max(send_request_date), getdate()) 
 from [msdb].[dbo].[sysmail_mailitems]
 where subject like 'ALERT%') > 30
 set @email = 'execute msdb.dbo.sp_notify_operator @name=N''' + @operator + ''', @subject=N''ALERT: [XX] failed '',
 @body=N''The [XX] sql-job on server [YY] has failed!'''
 select 'email sent'

Backup Status

Here’s my script that estimates when a running backup will finish …

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle))) [CommandText]
FROM sys.dm_exec_requests r WHERE r.session_id > 50
--AND CONVERT(NUMERIC(6,2),r.percent_complete) > 0

Try/Catch and Transactions

Try/Catch-blocks and Transaction-Blocks really don’t play nice together. Within nests for example an outer @@ROLLBACK TRANSACTION will rollback both inner and outer transaction-blocks regardless of already executed COMMIT TRANSACTION statements.

This is the most robust template I could come up with for nested Try/Catch-blocks that contain Transactions.

(inner DDL statements)
begin transaction
(inner DML statements)
commit transaction

(inner error capture statements)
rollback transaction
(outer DDL statements)
begin transaction
(outer DML statements)
while @@trancount > 0 commit transaction

(outer error capture statements)
while @@trancount > 0 rollback transaction

Configuring DAC

There is another definition of DAC in SQL Server – but this one is about the emergency DBA connection method, used when the server it too busy to get onto any other way.

DAC can be a life saver but has to be configured in advance. Luckily its a two minute job and I do it on all Production servers. and here’s how …

– Right click on server-name (in SSMS Object explorer), and choose facets.
– when the Facets box pops up select Surface Area Configuration in the top box.
– in the lower box set RemoteDacEnabled to True, and save with OK.

** TO USE IT **

– startup SSMS and ensure nothing is connected.
– click on “New Query”
– in the “Connect to Server” box prefix the Server Name with “admin: ”
– complete Authentication and click “Connect”

You should now be connected within a minimal safe-mode type environment. That is, with a blank Object Explorer pane.

** UPDATE 05 Mar 2015 **

To enable DAC on multiple servers (2005 and above) programmatically (via Registered Servers) use this …

Use master
sp_configure 'remote admin connections', 1

SQL Server 2012 Books-On-Line

To setup a local copy of SQL Server 2012 Books-On-Line (BOL) on Windows 2012 I downloaded it from here … … unpacked it to the default location and noted it. Then in SSMS I chose Help / Manage Help Settings / Choose online or local help / I want to use local help / Install contents from disk, and browsed within the unpack location for the HelpContentSetup.msha file. I clicked the three ADD links within the pop-up box. And found BOL by clicking Help / View help within SSMS.

Using OUTPUT to simplify the auditing of data changes.

Here’s an example of using OUTPUT to simplify the recording of updates etc …

-- create the audit log
create table audit_table (fname varchar(50), colour varchar(50), 
old_tasty int, changed smalldatetime, new_tasty int)

-- create and populate the fruit table
create table fruit (fname varchar(50),colour varchar(50),tasty int)
insert into fruit values ('banana','yellow',6),('kiwi','green',3),('gooseberry','green',2)

-- change score to 'yuk' for any green fruits
update fruit set tasty = 1
output deleted.*, getdate(), inserted.tasty into audit_table
where colour = 'green'

-- check audit log
select * from audit_table

… and another nail in the coffin of Triggers ๐Ÿ™‚