SSIS Error: System.IO.IOException: The process cannot access the file "xxxxx\project.ispac" because it is being used by another process.

The .ispac file is locked by the debug process, which is a normal function of debugging. However, if the debugger crashes (or if Visual Studio itself dies/crashes) then it doesn't let you execute your SSIS package in Visual Studio.

You will likely get the error:
System.IO.IOException: The process cannot access the file "xxxxx:\Project.ispac" because it is being used by another process.

So! To fix this apparent insanity, you have to kill the processes (SSIS Debug Host and/or SSIS Debug Host (32 bit)) and restart Visual Studio.

Kerberos Error

Of all the things, unexpected authentication errors from Kerberos are my least favorite. Mostly because, once I get the authentication set up, it's one of the things I expect to change the least.

I got the following error while trying to log in to the my lab:

Cannot connect to SQL Server.

The target principal name is incorrect. Cannot generate SSPI context.

Turns out, it was cached settings to blame, and running the following commands flushed the old settings and allowed a successful connection.

• klist purge
• ipconfig /flushdns

Joining a domain

When I wrote the how-to a few months ago, I forgot to jot down how to join the domain in both Windows 10 and from the command line.

To join an existing domain from Windows 10:

Right click on the Start button
Choose System from the pop up menu
Choose Advanced System Settings from the menu on the left
Choose the Computer Name tab
Click Change
Click Domain, and follow the prompts to enter your username and password


From the command line, for example, from a new test VM:

Open an elevated command prompt, and then type
netdom join machinenamegoeshere /domain:domainnamegoeshere

On Vacation!

Happy New Year to you! I'm on vacation this week, and will see you after the new year!

Merry Christmas!

Merry Christmas to you ! Here's hoping you enjoy your holidays with family, no matter where you are.

Scripts from Index Defrag DBA Minute

Here's the scripts from last week's DBA Minute on Index Fragmentation.

set nocount on;
use WideWorldImporters;
go

if OBJECT_id('dbo.TblHeap') is not null drop table dbo.TblHeap;
if OBJECT_id('dbo.TblHeapWithPK') is not null drop table dbo.TblHeapWithPK;
go

select sysdatetime() as [StartAt];
go

CREATE TABLE dbo.TblHeap (
TblHeapid BIGINT IDENTITY NOT NULL,
col1 VARCHAR(1024) NOT NULL,
col2 uniqueidentifier DEFAULT NEWSEQUENTIALID() -- because it's the ugliest thing I could think of, that's why
);

CREATE TABLE dbo.TblHeapWithPK(
TblHeapid BIGINT IDENTITY primary key,
col1 VARCHAR(1024) NOT NULL,
col2 uniqueidentifier DEFAULT NEWSEQUENTIALID() -- because it's the ugliest thing I could think of, that's why
);
GO

select sysdatetime() as [TablesBuilt];
go

--Insert 10,000 rows.
DECLARE @i INT = 1;

WHILE @i <= 10000
BEGIN
 INSERT dbo.TblHeap (col1) SELECT REPLICATE('ASDF',200) ;
 INSERT dbo.TblHeapWithPK (col1) SELECT REPLICATE('ASDF',200) ;
 SET @i = @i + 1;
END
GO

select sysdatetime() as [TblHeap built];
go

-- what's in the tables?
select top 10 * from dbo.TblHeap;
select top 10 * from dbo.TblHeapWithPK;
go

-- frag level?
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go

-- force page splits
update dbo.TblHeap set col1 = REPLICATE('x',1024);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',1024);
update dbo.TblHeap set col1 = REPLICATE('x',512) where ((TblHeapid%3)=0);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',512) where ((TblHeapid%3)=0);
update dbo.TblHeap set col1 = REPLICATE('x',256) where ((TblHeapid%5)=0);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',256) where ((TblHeapid%5)=0);
go

delete from dbo.TblHeap where (TblHeapid % 5 ) <> 0;
delete from dbo.TblHeapWithPK where (TblHeapid % 5 ) <> 0;
go

select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go

ALTER INDEX ALL ON dbo.TblHeap REORGANIZE --WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
ALTER INDEX ALL ON dbo.TblHeapwithPK REORGANIZE --WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
GO

select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go

DBA Minute - Index Fragmentation