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 …
--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
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]
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 😉
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%]
-- 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.
-- use current (snapshot?)
-- create table of filegroup names and sizes
select fg.name, sum(df.size/128) mb
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'
--check each filegroup starting with smallest
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
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.
Here are the cdc commands I use …
--is cdc enabled on my current database?
select is_cdc_enabled, * from master.sys.databases
--is cdc enabled on this table?
select is_tracked_by_cdc, * from sys.tables order by 1 desc
exec sys.sp_cdc_enable_db -- enable database
exec sys.sp_cdc_enable_table -- enable table
@source_schema = N'dbo',
@source_name = N'SomeTableName' -- table to be monitored
@role_name = NULL, -- login used to restrict access
@filegroup_name = N'cdc_fg' -- used to set path for change-table to its own drive
-- MISC QUERIES
select * from cdc.dbo_SomeTableName_CT -- changes plus 5x cdc columns
where __$start_lsn = 0xSomeLsnNumber
$operation ... 1=delete
3=value before update
4=value after update
$update_mask ... bit-map of column-names updated (IE: 3 & 4 only)
select * from cdc.captured_columns -- list of logged column-names
select * from cdc.change_tables -- table(s) monitored by cdc
select * from cdc.ddl_history -- ddl chages
select * from cdc.index_columns -- indexes on monitored tables
select * from lsn_time_mapping -- lsn numbers with time-stamps
select min(tran_begin_time), max(tran_begin_time)
from cdc.lsn_time_mapping -- check its running
exec sys.sp_cdc_help_change_date_capture -- get values for @capture_instance etc
-- exec sys.sp_cdc_disable_table -- disable table
@source_schema = N'dbo',
@source_name = N'SomeTableName', -- table name
@capture_instance = N'dbo_SomeTableName'
--exec sys.sp_cdc_disable_db -- disable database ... and delete all data and objects
--show latency in seconds & hours
select latency, latency/3600 latency_hrs, *
--show current sqljob settings
select * from msdb.dbo.cdc_jobs -- note: 4320 mins = 3 days
--chanage 'capture' job settings
exec sys.sp_cdc_change_job @job_type = 'capture',
@maxtrans = 600, --500 default
@maxscans = 200, --10
@continuous = 1, --1
@pollinginterval = 2 --5
--dont forget to stop/start job to use these new settings
-- see if cdc (aka replication) is stopping the log-file shrinking
select name, log_reuse_wait_desc from sys.databases
-- see percentage log-file used
-- show cdc error-log
select * from master.sys.dm_cdc_errors
-- emergency dump all pending transactions, allowing shrink
--sp_repldone @xactid = null, @xact_segno = null, @numtrans = 0, @reset = 1
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.
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