--AddIdentity.sql /******* REMEMBER TO SCRIPT-OUT THE TABLE FIRST *******/ --1 delete the old column Alter Table [dbo].[sometable] Drop Column [somecolumn] Go --2 if it fails ... drop the primary key (or whatever) ALTER TABLE [dbo].[sometable] DROP CONSTRAINT PK_sometable GO --3 create the new identity column Alter Table [dbo].[sometable] Add [somecolumn] Int Identity(1, 1) Go --4 restore the primary key (if dropped) ALTER TABLE [dbo].[sometable] ADD CONSTRAINT PK_sometable PRIMARY KEY NONCLUSTERED ( [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);
Category: MS SQL 2012
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);
SQLCMD
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
go
— interact over multiple rows
select name
from sys.databases
go
— clear the command cache
select @version
:reset
select @@version
go
— redirect output to a file
:out c:\output.txt
select @@version
go
— reset output back to the screen
:out stdout
select @@version
go
— quit
quit
exit
ctl-c
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.
--CheckTablesInAFilegroup_vldb.sql use TheSnapshotName go -- capture table-names from the named filegroup - once begin try select o.name 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 f.name = 'somefilegroupname' order by o.name end try -- check tables one-by-one begin catch declare @sql varchar(200) while (select count(*) from master.dbo.fgtables) > 0 begin select top 1 @sql = 'dbcc checktable ("dbo.' + name + '")' from master.dbo.fgtables order by name exec(@sql) delete from master.dbo.fgtables where name = (select top 1 name from master.dbo.fgtables order by name) end 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 with(nolock)
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]
ADDITIONAL
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 …
--changeIntToBigint.sql USE [Production] GO --1 remove constraint ALTER TABLE [dbo].[sometablename] DROP CONSTRAINT [someconstraintname] GO --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 (somecolumnname) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Filegroup1] GO
Killing BCP
You cannot kill BCP within SSMS – it wont error – it will say ‘killing/rolling back’ forever (Its a command-line executable remember).
Instead find it in Task-Manager and Right-Click the BCP.EXE process and choose “End Process”.
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 ๐
--datafile_resize.sql 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 fg.name, 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 fg.name 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 select(@sql) exec(@sql) delete from #tables where name = (select top 1 name from #tables order by mb, name) if (select count(*) from #tables) = 0 begin drop table #tables select 'Complete' end end catch
Hide SQL Instance
I found even though I had the ‘hide’ flag set to ‘yes’ on my Laptop, its SQL instance would become visable on the network after every reboot.
The fix seems to be to change the sql-service properties from ‘automatic’ startup-type to ‘manual’.
After that, whenever I started SQL the ‘hide’ flag would be read and applied correctly.
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 ๐
UPDATE
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 (http://dba.stackexchange.com/questions/35936/change-data-capture-and-the-update-mask-binary)
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'
CXPacket
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 begin 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!''' exec(@email) select 'email sent' end
Remove datafiles
You need at least two datafiles in the filegroup for this first command to work, as it has to move the data some where.
dbcc shrinkfile('filename', emptyfile); alter database databasename remove file logicalfilename;
Its best to ran this within a sql-job as shrinkfile can take ages.
Backup Status
Here’s my script that estimates when a running backup will finish …
--backup_status.sql 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 command IN ('RESTORE DATABASE','BACKUP DATABASE', 'KILLED/ROLLBACK') --AND CONVERT(NUMERIC(6,2),r.percent_complete) > 0
How many rows are in each table?
select object_name(id) table_name, rows from sys.sysindexes where xmaxlen in ('89', '92') --and object_name(id) = 'sometablename' order by rows --desc --order by table_name desc
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.
BEGIN TRY
BEGIN TRY
(inner DDL statements)
begin transaction
(inner DML statements)
commit transaction
END TRY
BEGIN CATCH
(inner error capture statements)
rollback transaction
END CATCH
(outer DDL statements)
begin transaction
(outer DML statements)
while @@trancount > 0 commit transaction
END TRY
BEGIN CATCH
(outer error capture statements)
while @@trancount > 0 rollback transaction
END CATCH
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 GO sp_configure 'remote admin connections', 1 GO RECONFIGURE GO
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 … http://www.microsoft.com/en-us/download/details.aspx?id=347 … 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.
SSMS 2012 Colors
The Text color (Teal) and Comment color (Dark Green) are rather similar to my eyes. I much prefer to change the former to black … Tools / Options / Environment / Fonts and Colors / Identifier / Black / Ok.
UPDATE: I changed ‘SQL System Table’ to black also.
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 ๐