Categories
How To

How to save some Emergency Drive Space for SQL Server (or other Servers)

Running out of disk space on a SQL Server server is something that you should never allow to happen, but it can happen.

If you need to be the emergency Fireman putting out the fire, here is a way to “reserve” some drive space ahead of time.

It is literally a dummy file that you can delete at any time without worry – nothing is using the file.

fsutil.exe file createnew "Emergency Disk Space (safe to delete).$$$" 8589934592

This will create an 8 GB temporary file in the current folder. Adjust the size to suit your needs.

When you need to reclaim the disk space, simply select the file and shift+delete (no need to send it to the Recycle Bin).

Here’s the gist if you don’t file like copy&pasting.

Categories
Beings Protiguous Quote

Faith in God and Science

I’ve tried to say this before, so please be patient with me: Faith in God and Science are not mutually exclusive concepts. Science, as of yet, cannot prove or disprove the existence of any godlike creature.

Personally, I don’t believe there is an all-knowing white guy sitting on a throne demanding to be worshiped.

What I do feel is, is there are Beings of immense knowledge, wisdom, and compassion that have evolved from their lower lifeforms into what they’ve become.

Who knows, maybe we are our own “gods”? Maybe, if we can survive long enough and learn how the Universe operates, then we can become our own godlike immortal beings with souls.

I firmly believe in the existence of our souls. I believe they exist in an extremely higher dimension than our limited 3D+time dimensions.

I believe our souls interact with and are affected by the quantum realm through the mind. A two-way communication as it were..

What do you think? What are your thoughts on this idea?

Categories
Anything Quotes

Bits o’ Good

May be an image of flower and text that says 'Do your little bit bit of good where you are it's those little bits of good put together that overwhelm the world DESMOND TUTU SIMPLICITY ├ŤAT HABIT'
Categories
SQL Script Uncategorized

How to create unsigned data types in SQL Server

Script to create unsigned data types in SQL Server.

USE [Numbers]
GO

DROP TYPE if exists [dbo].[UInt16];
DROP TYPE if exists [dbo].[UInt32];
DROP TYPE if exists [dbo].[UInt64];
DROP TYPE if exists [dbo].[UFloat];
DROP TYPE if exists [dbo].[UReal];
DROP TYPE if exists [dbo].[ZeroToOne];
GO

DROP RULE if exists [dbo].[UInt16];
DROP RULE if exists [dbo].[UInt32];
DROP RULE if exists [dbo].[UInt64];
DROP RULE if exists [dbo].[UFloat];
DROP RULE if exists [dbo].[UReal];
DROP RULE if exists [dbo].[ZeroToOne];
GO

CREATE TYPE dbo.[UInt16] FROM smallint NULL;
CREATE TYPE dbo.[UInt32] FROM int NULL;
CREATE TYPE dbo.[UInt64] FROM bigint NULL;
CREATE TYPE dbo.[UFloat] FROM float NULL;
CREATE TYPE dbo.[UReal] FROM real NULL;
CREATE TYPE dbo.[ZeroToOne] FROM float NOT NULL;
GO

CREATE RULE dbo.UInt16 AS @value >= 0;
GO
CREATE RULE dbo.UInt32 AS @value >= 0;
GO
CREATE RULE dbo.UInt64 AS @value >= 0;
GO
CREATE RULE dbo.UFloat AS @value >= 0;
GO
CREATE RULE dbo.UReal AS @value >= 0;
GO
CREATE RULE dbo.ZeroToOne AS @value between 0.0 and 1.0;
GO
Categories
SQL Script

List Cached Data Per Object in Memory

select
	(count(*) * 8) / 1024 as [MB Used],
	object_schema_name( obj.[object_id] ) [Schema Name],
    obj.[name] [tablename],
	obj.[index_id]
from sys.dm_os_buffer_descriptors bd
join (
	select
		object_name( p.[object_id] ) [name],
		p.[index_id],
		au.[allocation_unit_id],
		p.[object_id]
	from sys.allocation_units au
	join sys.partitions p on ( au.[container_id] = p.[hobt_id] and ( au.[type] = 1 or au.[type] = 3 ) )
		or ( au.[container_id] = p.[partition_id] and au.[type] = 2 )
    ) obj on bd.[allocation_unit_id] = obj.[allocation_unit_id]
where bd.[database_id] = db_id()
group by object_schema_name( obj.[object_id] ), [name], [index_id]
order by [MB Used] desc
Categories
Protiguous Quote

Personal Growth: Believe it.. or not?

Don’t just accept your beliefs.. Challenge them!

Protiguous, 2021
Categories
concurrency

How to update and query an SQL Queue table with multiple consumers

First, create the test table and populate it with some data. The data itself isn’t too important; we just want a table that will be updated while also concurrently be queried and then deleted.

drop table if exists [dbo].[Queue];

create table [dbo].[Queue](
	QueueID int identity( 1, 1 ) primary key,
	SomeAction varchar(100) not null
)

go

insert into [dbo].[Queue]( [SomeAction] )
select top 1000
	ac.[name]
from [master].[sys].[all_columns] ac
where ac.[column_id] between 1 and 100
group by ac.[name]
order by newid();

Now open a second tab and paste this next query. This one will make random changes to the queue table every tenth of a second, while holding a transaction on the modified rows.

again:
	begin tran;
		with numbers as (
			select top 10
				ac.[column_id]
			from [master].[sys].[all_columns] ac
			where ac.[column_id] between 1 and 100
			group by ac.[column_id]
			order by newid()
		)
		update q with(updlock) set
			q.[SomeAction] = convert( varchar(100), newid() )
		from [dbo].[Queue] q
		join numbers n on q.[QueueID] = n.[column_id]

	   waitfor delay '00:00:00.1';
	commit tran;
goto again;

Now, open two more tabs and paste this SQL into each.
Try more tabs if you want to test the behavior.

set nocount on;
declare @queueid int;

waitfor delay '00:00:01';

while exists(select * from [dbo].[Queue] with (updlock, readpast) ) begin;

   begin tran;

	   select top 1 @queueid = q.[QueueID]
	   from [dbo].[Queue] q with(updlock, readpast) 

	   print 'processing queueid # ' + cast(@queueid as varchar) 

	   -- Simulate some slow processing..
	   waitfor delay '00:00:00.1';

	   delete q
	   from [dbo].[Queue] q
	   where q.[QueueID] = @queueid

   commit tran;
end;

Execute all 3+ tabs at once to simulate the load an application might place on the Queue table, along with 2+ queue consumers.

If everything works well, each unique QueueID should only show up once across all running consumer tabs in the results.


How does this work? Here’s my simplified English explanation.

The with(updlock) query hint tells the SQL engine that, “Hey, place some locks on these rows that I’m going to modify inside this transaction! Don’t let anyone else update them!”

And the with(readpast) query hint tells the SQL engine that, “Hey, go ahead and just skip past any rows that are locked. Just show me the ones that are not locked.”

Someone with better wording and the proper terminology (of what the locks are called) can give you a better understanding of what’s going on in each query.

I’m just hoping that these queries (my simplified re-telling) helps you to understand how to modify a queue while also consuming rows from the very same table, but without blocking and without deadlocking.

Any feedback? Questions?
Better practices I’ve glanced over? (Such as setting the isolation level..)

Categories
Uncategorized

My Main Rules For Life

  • Don’t Lie.
  • Be Kind.
  • Not Yours? Don’t Touch.
  • Always Learn.
  • Leave things better than you found them.

These pretty much cover every action I take (or don’t take).

I know they’re not “Earth Shattering” or “Complex“. But I believe in their simplicity and earnestness, and I hope that by sharing them I can bring some more light to the world.


Don’t Lie – Pretty much self explanatory. I won’t lie unless it is a certain special occasion that has no better course of action. If I determine you don’t deserve access to the truth, then I just won’t tell you anything. If you are one of those people that continually spout lies, I will expend energy exposing you in every feasible way.


Be Kind – Never be cruel or cowardly. If there are two possible courses of action to take, then do the kinder option. There’s no need to spread more hatred or be mean to anyone. You are not better than someone else, so stop acting “Holier Than Thou”.


Not Yours? Don’t Touch – This pretty much means “Don’t Steal”, “Hands off my loved one”, or even “Is that mine or yours?”.

Don’t take an item that does not belong to you.

Found a wallet or a purse? Return it to the owner without stealing any items.

Is this woman married? Leave her alone. (For that matter, if any woman says to leave her alone or stop.. then full STOP. Women, this rule applies to you too. :/ )

Are you married? Don’t be touching other women.


Always Learn – If you make a mistake, admit, learn from it and move on.

Don’t dwell on past mistakes, but do learn from them.


Leave things better than you found them

Categories
MSSQL SQL SQL Script SQL Server

A Visual Explanation of Joins

https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Categories
Protiguous Quote

100% Confidence

Oh, to have the confidence of a moron!

Protiguous 2021