« first day (4229 days earlier)      last day (640 days later) » 

Zikato
Zikato
6:14 AM
Morning
 
 
2 hours later…
Vérace - Слава Україні
Vérace - Слава Україні
8:03 AM
A chairde - Morning all!
 
Erik Darling
Erik Darling
8:30 AM
🌝
 
Vérace - Слава Україні
Vérace - Слава Україні
:-)
 
Zikato
Zikato
9:09 AM
Is there a way to find an object name in the hidden resource database? Some are exposed through OBJECT_NAME or OBJECT_DEFINITION but I can't find others
I'll probably post it as a question
 
ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells
Burn of the week -
MicroStrategy co-founder Michael Saylor gave up his chief executive officer title and said he'll focus more on Bitcoin after the enterprise-software maker reported a loss of more than $1 billion related to the second-quarter plunge in the price of the cryptocurrency. Bloomberg reports:
Saylor, who founded the Tysons Corner, Virginia-based company in 1989, will continue to serve as executive chairman as retains its Bitcoin buying strategy. MicroStrategy President Phong Le will take on the chief executive role. The company also filed with the Securities and Exchange Commission to register 450,000 shares. MicroStrategy took a $917.8 million impairment charge related to the decline in the value of the Bitcoin it holds.
Bitcoin tumbled 59% in the quarter, and traded about 45% lower than the price at the end of the year-earlier period.
Revenue dropped to $122.1 million. Analysts polled by Bloomberg expected revenue of $123.25 million in the second quarter. Net quarterly loss of $1.062 billion compared with a loss of $299.3 million in the same quarter of last year. The quarterly loss is almost exactly twice the company's revenue in the last 12 months. As of June 30, the carrying value of the company's 129,699
Bitcoins was $1.988 billion, the company said, reflecting the cumulative impairment loss of $1.989 billion. The cumulative amount is now more than Bitcoin on the company's balance sheet.
"MicroStrategy's original strategy and consulting business needs full-time attention," said Henry Elder, head of decentralized finance at Wave Financial. "Now Michael can focus on what he does best, promoting Bitcoin. And the company can focus on making more money to buy more Bitcoin. They are basically doubling down."
 
ypercubeᵀᴹ
ypercubeᵀᴹ
9:39 AM
@ConcernedOfTunbridgeWells I thought they were going bankrupt
 
Charlieface
Charlieface
@HannahVernon There are two hard things in computer science: cache invalidation and counting things and off-by-one errors
 
Zikato
Zikato
9:58 AM
My question from earlier:
0
Q: How to find names of all objects in Resource database?

ZikatoI'm looking at the sys.dm_exec_function_stats view to find Scalar Function stats. That view also includes rows from a database with an id 32767, which I've found is the Resource Database. Some of these IDs can be passed to OBJECT_NAME() or OBJECT_DEFINITION() Metadata functions but some IDs retur...

sql-server sql-server-2019 dmv
 
ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells
@ypercubeᵀᴹ Their bitcoin investments tanked. I have no idea what their financial position would have been otherwise. Maybe nobody wants to buy them, although I would have thought at least CA might have wanted to pick over the corpse.
 
 
2 hours later…
Hannah Vernon
Hannah Vernon
12:14 PM
@Zikato Paul's answer is the definitive one. Make copies of the files, rename them, and attach them as a new database, then you can poke around all day long.
 
Zikato
Zikato
@HannahVernon, I'm trying it at the moment. The DAC works for OBJECT_NAME() but not for the sys.all_objects.
 
Paul White
Paul White
I don't understand why that question isn't a duplicate
 
Zikato
Zikato
As for the reattach with a different name, it's my first time doing it. But It errors out:

Msg 208, Level 16, State 100, Line 3
Invalid object name 'wpr_bucket_table'.
 
Hannah Vernon
Hannah Vernon
Which version of SQL Server? 2022?
 
Paul White
Paul White
The attach command throws that error? I can't conceive what you're doing there.
 
Hannah Vernon
Hannah Vernon
12:17 PM
@PaulWhite you with the hard questions
 
Paul White
Paul White
CREATE DATABASE [System] ON
( FILENAME = N'D:\Databases\SQL2019\mssqlsystemresource.mdf' ),
( FILENAME = N'D:\Databases\SQL2019\mssqlsystemresource.ldf' )
 FOR ATTACH;
That's the command I used
Also I wouldn't expect the second undocumented parameter to OBJECT_DEFINITION to work.
 
Zikato
Zikato
same, maybe 2022 CTP is the problem. I'll try on older version
Same error on Microsoft SQL Server 2019 (RTM-CU16-GDR)
 
Hannah Vernon
Hannah Vernon
Please provide a repro.
 
Paul White
Paul White
@Zikato You copied the files somewhere else first?
 
Zikato
Zikato
Repro: Create folder D:\Databases\SQL2019
Copy there the mdf and ldf files (from C:\Program Files\Microsoft SQL Server\150\LocalDB\Binn)
Run Paul's script verbatim
 
Paul White
Paul White
12:27 PM
Does your instance have any funky triggers?
 
Hannah Vernon
Hannah Vernon
Closed as too localized
 
Zikato
Zikato
The only funky thing I can think of is localized server collation. I'll try in a container later on today
 
Hannah Vernon
Hannah Vernon
Which collation?
 
Zikato
Zikato
IIRC the collation prevented setting up Data collection, so it wouldn't be too surprising
 
Paul White
Paul White
@Zikato Check server triggers under "Server Objects" > "Triggers" in SSMS
 
Zikato
Zikato
12:33 PM
Czech_CI_AS - that's on the SQL2019
My 2022 CTP has SQL_Latin1_General_CP1_CI_AS
 
Paul White
Paul White
Attaching a database doesn't run any SQL unless you have some sort of server trigger
 
Zikato
Zikato
nothing in SELECT * FROM sys.server_triggers AS st on the 2019 instance. There is one on 2022 CTP that I can delete
 
Paul White
Paul White
Maybe you're running audit or something?
@Zikato Are you using SQL Server for Windows or Linux?
 
Zikato
Zikato
Windows, no audit. I've deleted and recreated the files again, and I'm getting a new error which looks promising

Unable to open the physical file "D:\Databases\SQL2019\mssqlsystemresource.mdf".
 
Paul White
Paul White
Permissions error
 
Zikato
Zikato
12:41 PM
Added Full control on the folder and success!
 
Paul White
Paul White
Great
 
Zikato
Zikato
Thanks all
 
Paul White
Paul White
Still think your question is a duplicate
I answered before I saw the dupe close flag
25 mins ago, by Paul White
Also I wouldn't expect the second undocumented parameter to OBJECT_DEFINITION to work.
 
Hannah Vernon
Hannah Vernon
Interesting - I think I'll try that on Linux
 
Paul White
Paul White
Running SSMS as an administrator also allows permissions errors to be fixed
 
Hannah Vernon
Hannah Vernon
12:46 PM
Assuming you're using Windows Auth on Windows SQL Server
 
Zikato
Zikato
Which duplicate? Charlieface posted two duplicate questions
 
Paul White
Paul White
@HannahVernon Yes
@Zikato The second one
 
Hannah Vernon
Hannah Vernon
@PaulWhite I was like, wait, there's two? But now I see they deleted the first one, which you just undeleted
 
Paul White
Paul White
Yes
 
Hannah Vernon
Hannah Vernon
Almost as rare as seeing a dodo in the wild
 
Paul White
Paul White
12:49 PM
Novelty, I'll prob delete it again because the second dupe is better
 
Hannah Vernon
Hannah Vernon
For sure
I agree with the 2nd dupe
Fwiw
 
Paul White
Paul White
And mostly because I only learnt about a year ago we could undelete self-deleted comments
 
Zikato
Zikato
well, I didn't consider it a duplicate. Because some of the IDs are exposed through the OBJECT_NAME and this wasn't about them
also hard to find when it's asking about a single specific object ID, I was asking about all of them
 
Hannah Vernon
Hannah Vernon
SE search ia terrible
 
Paul White
Paul White
4
Q: Locating the Resource system database

igelrThere is a database called the Resource database, which I know that is read only and contains information about system objects and does not save any user related data or metadata. I am using SSMS to manage my database instance and I can only see four system databases - master, model, msdb, tempdb...

sql-server sql-server-2012 ssms system-databases
@Zikato This one ^^^
My answer is basically a copy
Today is off to a confusing start
 
Zikato
Zikato
12:53 PM
Yeah, that's probably fair
should I delete it?
the DAC trick is neat
 
Hannah Vernon
Hannah Vernon
Don't delete it
Dupes serve as a great way for others to find the answer
 
Paul White
Paul White
Yeah we don't mind good duplicates at all
 
Hannah Vernon
Hannah Vernon
Plus they signal-boost the target q&a
 
Paul White
Paul White
Also you're unable to delete it
So there's that
 
Hannah Vernon
Hannah Vernon
It's nice to upvote the target question, and any good answers
 
Paul White
Paul White
1:00 PM
I only ever half-upvote questions because I disagree they are worth 10 points
 
Hannah Vernon
Hannah Vernon
lol. Upvotes on questions should not award any points.
Its about signal strength really, not unicorn points
Every day SE is telling me to vote on questions, too
 
Paul White
Paul White
They're so naggy
 
Zikato
Zikato
Shame, I thought I might supersede Erik as the last good question asked.
 
Hannah Vernon
Hannah Vernon
I don't think they mean downvote though lol
@Zikato its a high bar
 
Zikato
Zikato
Erik definitely has more bar experience than I
 
Paul White
Paul White
1:04 PM
v true
 
Hannah Vernon
Hannah Vernon
Probably more than any of us. Probably more than he would like.
 
Erik Darling
Erik Darling
Hello
Did someone say bar
 
Hannah Vernon
Hannah Vernon
Hola
Tell us you're at a bar
 
Paul White
Paul White
@ErikDarling Is your idea of a fun holiday visiting every airport in Europe
Better than staying home and catching monkeypox I suppose
 
Zikato
Zikato
Can you catch monkeypox if you're staying home?
 
Paul White
Paul White
1:07 PM
In New York you can
I hear there's an emergency there
 
Erik Darling
Erik Darling
I think you have to engage in very specific activity to get monkey pox
I am in an airplane seat that I’ve turned into a bar
 
Paul White
Paul White
Oh right
 
Erik Darling
Erik Darling
But this wine sucks
 
Zikato
Zikato
Spain-bound?
 
Erik Darling
Erik Darling
Yeah
Bar… thelona!
2
Vueling is a real bullshit airline
 
Zikato
Zikato
1:12 PM
I'm still wondering why some of the resource DB functions are exposed through Object_name, and others aren't.
Probably summer interns
 
Paul White
Paul White
Mar 11 at 8:26, by McNets
Bar-ze-lon-a
 
Zikato
Zikato
How did you find that? That far back
 
Paul White
Paul White
I remembered it
 
ypercubeᵀᴹ
ypercubeᵀᴹ
I guess the z in -ze- is pronounced like the Spaniards pronounce zoom: thoom !
 
Paul White
Paul White
@Zikato Do you have a convenient example of one that does and one that doesn't?
 
ypercubeᵀᴹ
ypercubeᵀᴹ
1:18 PM
(at least some of the Spaniards)
 
Paul White
Paul White
those with a lithp
to bring the conversation full-circle
2
 
Zikato
Zikato
@PaulWhite Well, yes. It was in the question SELECT OBJECT_NAME(-665919249), OBJECT_NAME(-706609674)
 
Paul White
Paul White
@Zikato fn_cdc_jobs is marked ms-shipped, fn_hadr_is_primary_replica isn't
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@PaulWhite and those who have drunk too much wine
 
Erik Darling
Erik Darling
@PaulWhite thircle
3
 
Paul White
Paul White
1:23 PM
tho thilly in this room
2
 
Zikato
Zikato
@PaulWhite True, still no logical reason to disregard those, right? Other non-ResourceDB ms_shipped objects don't have this problem
 
Paul White
Paul White
@Zikato I haven't checked but I imagine some are considered (and marked in metadata) as system objects that shouldn't be visible to users
ms-shipped isn't a very reliable indicator of that, but it is quick
 
Zikato
Zikato
Why expose them in the sys.dm_exec_function_stats if they are not to be seen? Either hide them everywhere or give me tools how to find them.
 
Paul White
Paul White
Ask the intern
In the old days we would use sp_ms_marksystemobject 
---------------------------- sp_MS_marksystemobject -----------------------------
-- FOR INTERNAL USE ONLY ... DO NOT DOCUMENT --
-- This procedure sets a bit in sysobjects.  This bit has no meaning, various
--	groups (starfigther, davinci, replication) use it for different things
-- MSQL makes no warranty, express or implied, on what objects will or will
--	not have this bit set.  Use at your own risk.
@Z As close to an official answer as you'll get I think
You could certainly waste some time writing feedback about the issue
 
Zikato
Zikato
Or a petition
 
Paul White
Paul White
1:37 PM
Microsoft is not a democracy
site offline again
and back
 
Hannah Vernon
Hannah Vernon
1:52 PM
And off again
 
Paul White
Paul White
> Stack Exchange Network Sites has transitioned to Unavailable
Might need more site reliability engineers
 
Hannah Vernon
Hannah Vernon
Seems like that's true
Aaron messing with indexes again
 
Zikato
Zikato
Maybe they're gonna mention it today on 8KB
https://eightkb.online/
17:30 UTC
 
Paul White
Paul White
2:16 PM
> Stack Exchange Network Sites has transitioned to Degraded
 
Erik Darling
Erik Darling
🥳🥳🥳
 
Hannah Vernon
Hannah Vernon
@PaulWhite as expected it works the same way on Linux. Obvs the files are in a different location.
/var/opt/data on my RHEL server.
 
Paul White
Paul White
2:34 PM
thanks
 
Hannah Vernon
Hannah Vernon
np
 
Hannah Vernon
Hannah Vernon
2:47 PM
I wonder how much fun it would be to replace the mssqlsystemresource.mdf and ldf files with blank databases files. Probably not much, I'm guessing.
 
Erik Darling
Erik Darling
Hello I’m Spanish now
2
 
Zikato
Zikato
¡Hola! Soy Tom
 
Zikato
Zikato
3:03 PM
Site is down for maintenance
 
Paul White
Paul White
Briefly, back again for me
 
Hannah Vernon
Hannah Vernon
up and down like a yoyo here
 
Zikato
Zikato
El sitio está fuera de servicio por mantenimiento
 
McNets
McNets
3:16 PM
@ErikDarling where are you now?
 
Zikato
Zikato
2 hours ago, by Erik Darling
Bar… thelona!
 
McNets
McNets
3:51 PM
30º C today, not bad
 
Zikato
Zikato
How does SE pick which posts go on Twitter?
https://twitter.com/StackDBAs/status/1554844556377706498
 
Erik Darling
Erik Darling
select top (1) * from dbo.posts as p join users as u on p.owneruserid = u.id where u.displayname <> N'zikato' order by p.creationdate desc;
 
Zikato
Zikato
4:12 PM
that's not deterministic
 
Erik Darling
Erik Darling
no it isn't
it determines they'll never tweet one of your posts
 
Paul White
Paul White
 
Zikato
Zikato
Spanish Erik is mean
 
Paul White
Paul White
4:27 PM
@Zikato Also, the current view on dba.stackexchange.com/?tab=hot might not be a coincidence
 
Zikato
Zikato
@PaulWhite The current view shows my duplicate question. Twitter shared the original
 
Paul White
Paul White
@Zikato Yes I wouldn't be surprised if it would follow the dupe path
Wouldn't make much sense to tweet the dupe itself in general
 
 
2 hours later…
Hannah Vernon
Hannah Vernon
6:47 PM
@MichaelGreen - I saw that you requested a re-open on this - what do you have in mind for it? Are you wanting to provide an answer?
 
Josh Darnell
Josh Darnell
6:59 PM
> You must enable the following set of trace flags before you can enable Query Store for secondary replicas: 12606, 12607, 12608, 12610, T12624.
That's a lot of trace flags. And an annoying inconsistency in the way they listed them in the docs.
I wonder if you can force plans on a secondary with that enabled.
 
 
2 hours later…
Paul White
Paul White
8:44 PM
12606 enables basic QS secondary support
12607 makes the views visible
12610 is associated with asynchronous messaging
12624 is associated with persistence on replicas
There are additional options available not listed in the docs.
It is kinda annoying to have so many of them, but unavoidable when things are ready at different times.
And where any one of them might need to be turned off in a hurry.
 
Erik Darling
Erik Darling
Sounds like a docs issue
 
Paul White
Paul White
Intern let loose with a keyboard again
btw regarding your comment, notice the limit is KB not MB
I mention that because I used a 512MB string in sql.kiwi/2021/11/careful-lobs-recompile.html
 
Paul White
Paul White
9:02 PM
Latest update on the site status:
> We are still investigating the cause of the instability
 
Josh Darnell
Josh Darnell
@PaulWhite oh wow, thanks Paul!
 
Erik Darling
Erik Darling
@PaulWhite Oh right so my XML variable is immune to common sense
 
Josh Darnell
Josh Darnell
@PaulWhite Top Answers doesn't have these problems 😜
 
Paul White
Paul White
@JoshDarnell ha ha right
 
Michael Green
Michael Green
@HannahVernon It came up in the review queue. I thought it reasonably well-formed and answerable. It is unlikely I shall be answering myself.
 
Hannah Vernon
Hannah Vernon
9:15 PM
ahhh, so it wasn't you that triggered the re-open queue. Cheers for replying either way.
 
Michael Green
Michael Green
np
 
Paul White
Paul White
@HannahVernon You can tell who it was by looking at the timeline
That vote has since aged away
 
Michael Green
Michael Green
'Morning all
 
Hannah Vernon
Hannah Vernon
@PaulWhite that's what I thought
@PaulWhite ahhh
 
Paul White
Paul White
@HannahVernon It would be impossible for it to have been Michael since his vote came after the reopen review started
 
Hannah Vernon
Hannah Vernon
9:19 PM
yah I see that now - I was looking at the review history, which is like 'duh'.
 
Paul White
Paul White
aha
 
Paul White
Paul White
9:36 PM
@ErikDarling Oh I see what you mean now, about the thing going out of scope in the statement when PEO happens.
Unless you mean something else?
@x itself goes out of scope at the end of the batch. With PEO a new lob is created on each loop for the embedded value of the parameter.
 

« first day (4229 days earlier)      last day (640 days later) »