2025-01-05

The obscure star-database

To play around with some obscrure naming possibilities in SQL Server I create the star-database. The name has nothing to do with the data warehouse star-schema pattern. The name is because the database is named with the star character (*).

CREATE DATABASE [*]
    ON PRIMARY ( NAME = N'star_data', FILENAME = N'L:\SSDB2019\Data\star_data.mdf', SIZE = 8MB, FILEGROWTH = 8MB )
    LOG ON ( NAME = N'star_log', FILENAME = N'L:\SSDB2019\TransLog\star_log.ldf', SIZE = 8MB, FILEGROWTH = 8MB );

The database is created on specific directory as inplicit default path using only the database name does not work. This was expected. Using the star-character as name requires me to wrap the name in [].

In the star-database I then create a star-schema.

USE [*];
GO
CREATE SCHEMA [*] AUTHORIZATION [dbo];

... and then a star-table.

CREATE TABLE [*].[*]
(
    [*] bigint NOT NULL IDENTITY (0, 2),
    [%] nvarchar(42) NULL,
    [<] int null,
    [>] int null
);
GO
ALTER TABLE [*].[*] ADD CONSTRAINT
    [PK_star] PRIMARY KEY CLUSTERED 
    ([*]) ON [PRIMARY];

The column names are are almost as obscure as the other names. And then I insert a few rows.

INSERT INTO [*].[*] ([%],[<],[>])
VALUES
    (N'række0', 1000, -1000),
    (N'rÆkke1', 10, -100),
    (N'räkke2', 237, 7);

Then I can select the table in different ways, where the syntax is quite important to the output.

SELECT [*] FROM [*].[*];
SELECT * FROM [*].[*];
SELECT COUNT(*) AS [**] FROM [*].[*];

The first two statements visually looks similar, but the [] makes quite a difference. Adding some WHERE-clauses also adds to the fun.

SELECT * FROM [*].[*] WHERE [%] LIKE '%ä%';
SELECT * FROM [*].[*] WHERE [<]<500;
SELECT * FROM [*].[*] WHERE [>]<0;

The script is in SQLAdmin Github (star_database.sql). Next round could be adding more fun with unicode...

The obscure master-database

The master database is well known. And creating a database with a similar name can make things obscure or unexpected. Playing with adding space before or after "master" is only partly possible. Adding a space after the name as [master ] does not work and gives the error "Msg 1801, Level 16, State 3, Line 8 Database 'master ' already exists. Choose a different database name.". But adding a space after like [ master] does work.

CREATE DATABASE [ master] ON  PRIMARY 
( NAME = N'master0_data', FILENAME = N'L:\SSDB2019\Data\master0_data.mdf', SIZE = 8MB, FILEGROWTH = 8MB )
LOG ON 
( NAME = N'master0_log', FILENAME = N'L:\SSDB2019\TransLog\master0_log.ldf', SIZE = 8MB, FILEGROWTH = 8MB );

Adding other characters known from other parts of a database can add to the obscure situation.

CREATE DATABASE [master.] ON  PRIMARY 
( NAME = N'master_dot_data', FILENAME = N'L:\SSDB2019\Data\master_dot_data.mdf', SIZE = 8MB, FILEGROWTH = 8MB )
 LOG ON 
( NAME = N'master_dot_log', FILENAME = N'L:\SSDB2019\TransLog\master_dot_log.ldf', SIZE = 8MB, FILEGROWTH = 8MB );
GO

CREATE DATABASE [master.dbo] ON  PRIMARY 
( NAME = N'master_dbo_data', FILENAME = N'L:\SSDB2019\Data\master_dbo_data.mdf', SIZE = 8MB, FILEGROWTH = 8MB )
 LOG ON 
( NAME = N'master_dbo_log', FILENAME = N'L:\SSDB2019\TransLog\master_dbo_log.ldf', SIZE = 8MB, FILEGROWTH = 8MB );

The script file with the obscure master databases is also in SQLAdmin Github in obscure_master_database.sql.

2025-01-01

Top-ten posts 2024

2024 was a year with fewer posts than planned - again. As each post is like a personal refined note on a specific situation this signals that I am working less with specific technical incidents. My day is more filled with long-term considerations and actions.

This is the top-ten list of my posts on the 2024 statistics.

RankMoveRank 2023TitleViewsCreated
101 SqlBulkCopy with PowerShell18502017-07-30
2+13 ISO 8601 date formatting using PowerShell3342008-08-14
3+710 Audit Log for Analysis Services2842014-01-08
4+48 Start shared HTA with PowerShell2582012-09-13
5> 6> 10 Add SQL Agent job step with tokens in output file name2382023-02-14
6+17 DBCC CHECKDB with PowerShell2102014-10-09
7> 4> 10 MSDTC Operations1172022-11-29
8> 3> 10 xp_instance_regwrite syntax1752010-09-15
9> 2> 10 Database version1242009-09-26
10-55 SQL Server Agent schedule owner982010-02-08

With a total of 39500 views in 2024 the top-ten posts is with 3748 views about 9 % of the views this year. The all time number of views is 402741.

This year there are both repeats and new posts on the top-ten. The number one post on SqlBulkCopy with PowerShell is sovereign again this year. There must be a link somewhere… I am surprised to see the post Database version as it is both rather old and on a very specific and rare subject.

Former Top-ten

My first post on this blog was published 2008-08-13, and many posts are irrelevant today. But I still think it is a good exercise to write each post as it gives me a option to review, refine and improve a description or solution.

Pages

TitleViews
DBA SQL Server Tools184
DBA Security64
DBA Litterature52
Microsoft Resources45

I think that the blog pages should be migrated to the SQLAdmin DK site. I am rewriting the entire site, and might take this migration into the rewrite.

2024-11-19

List orphan database users

Some days ago I was asked - politely - to move some databases between two SQL Server installations. Unfortunately the service had failed on the source, so I have no access to the logins definitions.
This give me a lot of orphan database users.
To make thing more entertaining some of the databases has compability level 80, and this can not be changed.
To get a list of orphan database users, I have to combine two methods. This is because the compability level 80 databases does not have the system view database_principals.

DECLARE @db_name AS nvarchar(128); -- Database name
DECLARE @_msg AS nvarchar(2047); -- Execution message
DECLARE @_cmd AS nvarchar(MAX); -- Dynamic command
CREATE TABLE ##orphan_user (
[db_name] nvarchar(128) NOT NULL,
[user_name] nvarchar(128) NOT NULL
);

DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT [name]
FROM [master].[sys].[databases]
WHERE [database_id] > 4;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT [compatibility_level] FROM [master].[sys].[databases] WHERE [name] = @db_name) < 90 BEGIN SET @_msg = N'The database [' + @db_name + N'] is SQL Server 2000 or older.'; SET @_cmd = 'INSERT INTO ##orphan_user ' + N'SELECT ''' + @db_name + N''' AS [db_name], [sysusers].[name] AS [user_name] ' + N'FROM [' + @db_name + N'].[dbo].[sysusers] ' + N'LEFT OUTER JOIN [master].[dbo].[syslogins] ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid] ' + N'WHERE [sysusers].[uid] > 4 AND [sysusers].[issqlrole]=0 AND [sysusers].[isapprole]=0 AND [master].[dbo].[syslogins].[sid] IS NULL;';
END
ELSE
BEGIN
SET @_msg = N'The database [' + @db_name + N'] is SQL Server 2005 or newer.';
SET @_cmd = N'INSERT INTO ##orphan_user ' +
N'SELECT ''' + @db_name + N''' AS [db_name], [database_principals].[name] AS [user_name] ' +
N'FROM [' + @db_name + N'].[sys].[database_principals] ' +
N'WHERE [database_principals].[type] IN (''S'',''U'',''G'') ' +
N'AND [database_principals].[is_fixed_role] = 0 ' +
N'AND [database_principals].[name] NOT IN (N''dbo'', N''guest'', N''sys'', N''INFORMATION_SCHEMA'');';
END
RAISERROR(@_msg,0,0) WITH NOWAIT;
EXECUTE (@_cmd);

FETCH NEXT FROM db_cursor INTO @db_name;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;


SELECT * FROM [##orphan_user];

SELECT DISTINCT [user_name] FROM [##orphan_user]
ORDER BY [user_name] DESC

DROP TABLE [##orphan_user];


I execute only a part of the script where I fill the temporary table. Then I can filter and sort the data to handle the logins.

2024-11-16

DBA Career Levels

Starting from the first day the career levels could be something like this.

Trainee

Assist on tasks and issues. Can be given trivial tasks with some training. Mentor assigned to introduce to profession.

Junior (Level I)

The Junior DBA resolves issues while developing professional skills. Handle assigned tasks. Check test results. Will mostly work individual, but will have a close colleage as mentor (Sensei).

Usually with up to two years of experience. But the period length is not defining - it is the capabilities.

Specialist (Level II)

Works on troubleshooting, review and test. Does now have solid fundamental skills.

Between two to five years of experience. Unusally depending on the organisation, capabilities and the mentor (Sensei).

Senior (Level III)

Now works as individual contributor with deep understandings. Ownership of technical components like SQL Server components. Key resource in problem analysis. Give process recommendations. Comprehensive knowledge of practices and organization structures, both formal and informal (goat paths).

Develop and present proposals, also to customers. Negotiate project and product technical terms and requirements with vendors and customers. Participate in pre-sales. Professional mentor (Sensei). Key business knowledge. Recognized for technical skills on international level with influence on wide organizational level.

Typically more than five years of experience. Often more than ten years.

Principal

Designing and presenting technical stratigies. Setting department goals and creating implementation plans. Setting and manging engineering budgets.  Develop and implement proces improvements.

Provide expert advise. People management in research and design. Professional and career mentor (Sensei) but not as primary priority. Deep business knowledge.

Now it is no longer a matter of years but more of capabilities and ambitions. Usually has at least a bachelor degree.

Master

Guiding and training professional teams and individuals. This is the primary focus. Deep knowledge and experience in both tech and processes. Can also be called Sensei in a Lean context.

Executive

Part of senior management. Set budgets. Define products and projects.

Driven by career ambitions.

Very rare title. Most people has left database administration many years before this level is relevant to pursue a more focused business career.


Inspiration

History

2024-11-16: Post created.

2024-12-23: Master level added.

2024-11-07

Reduce OS memory with BCDEdit

This is a quick description on how to reduce OS memory on virtual Windows Server 2016 from 128 GB to 48 GB without changing VM configuration. This can be done with the command BCDEdit.exe which is somewhat described by Microsoft in the documentation on bcdedit and the documents "BCDEdit Command-Line Options" and "BCDEdit Options Reference". The tool is a default tool in Microsoft Windows and is placed in the directory "C:\Windows\System32\".

The name of the tool referes to Boot Configuration Data, which is described somewhat in an old MSDN Vista document BCD.docx. Boot Configuration Data is a firmware independant data store with boot data. The store is placed in the system partition.

Reducing memory available to the OS is sometimes done, when you have an application that is not using all available memory, but is licensed by the memory amount available to the OS. And this is sometimes made worse by a computer with way too much memory that can't be reduced by ridiculous agreements.

Before

System Information (msinfo32.exe) on the guest VM. All installed memory is available to the OS:

Reduce OS Memory

Calculation: The number needed for the command is the amount of removed memory. In this case 128 GB - 48 GB = 80 GB. As the number must be in MB the corection is 80 GB * 1024 MB/GB = 81920 MB.

Start console (cmd.exe) as Administrator. PowerShell does not work. Enter the command line:

bcdedit /set {current} removememory 81920

  • bcdedit is shorthand on BCDEdit.edit.
  • /set is a option to BCDEdit described in BCDEdit /set.
  • {current} is a reference to the current boot entry.
  • removememory removes memory available to the Windows operating system.
  • 81920 is the amount of memory to remove from the OS in MB. See calculation above.

Reboot VM.

After

System Information on guest VM:

Task Manager on guest VM:

It shows both 128 GB and 48 GB even only 48 GB is available to the OS. This will confuse most operators and administrators.

Roll-Back

Start console as Administrator. And enter the command line:

bcdedit /deletevalue {current} removememory

Reboot VM.

And all the memory is available to the VM as shown by System Information.

Notes on System Configuration

Windows has the standard tool System Configuration where it looks like you also can reduce OS memory. Let me point out that this does not work as bcdedit /set removememory but like bcdedit /set truncatememory which is not as effective.

Disclaimer

This is not my own findings but given to me by the good colleague Ib Thornøe (LinkedIn)