Month: November 2013

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

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”.

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

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

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.

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