The following query will get you the database size and log file size for all databases on a SQL Server instance. WITH mf AS ( SELECT database_id, [type], CAST((size * 8.0 / 1024) AS INT) AS size FROM sys.master_files ) SELECT [name] AS DatabaseName, ( SELECT SUM(size) FROM mf WHERE [type] = 0 AND mf.database_id […]
For those reading this in the future, digital video recorders (DVRs) were quite prevalent in 2018. They allow you to record programs that are being broadcast and watch them later. Before DVRs there were other ways to record programs that involved writing them to magnetic tape based on a timer. Before that you just had […]
If you have a blocking query, you probably follow a set of manual steps a bit like this… Run sp_who2. Follow the “blocked by” SPIDs until you find a root-SPID (one that is blocking, but not blocked). Run DBCC INPUTBUFFER with the SPID to see what query is being run After doing this twice in […]
Sometimes you need to filter and sort the records you get from sp_who2. You can’t do this directly, but you can use a temporary table to do it. — Create an intermediate table to put all the results CREATE TABLE #spootoo ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME […]
Photo by CEphoto, Uwe Aranas I have said a thing or two about estimates in the past; and it generally leads to no good. What I do sometimes need to do is clarify my perspective, because people don’t get it. As always, definitions are important – so I’m to clarify what I think of when […]
Here is a quick SQL script that will get back information on SQL availability groups, including the role of each server. This can help you find the primary server in an availability group. IF SERVERPROPERTY (‘IsHadrEnabled’) = 1 BEGIN SELECT AvailabilityGroup.name AS AvailabilityGroupName, ReplicaClusterStates.replica_server_name as ServerName, ReplicaStates.role_desc as ReplicaRole, Listeners.dns_name as DnsName FROM sys.availability_groups_cluster AS […]
I have a script I use to find table sizes in SQL server. Sometimes, though, I need to find the rough table size of a massive table without the need for absolute precision. SQL server has a procedure for finding out the number of rows, space, and index size of a table; and it can […]
When you perform a SQL bulk update, you can just press go and wait. Most humans, though, get an increasing feeling of impending doom when the clock ticks up towards several hours with no visible progress. If you are running a transaction and have other things competing for the table, you’ll start hearing about slow […]
I saw a picture that hinted that DevOps was a substitute or successor for Agile. This made me consider the question “Is DevOps a replacement for Agile?”. It had a chart that represented time from left to right, and suggested that Agile was “waterfall with shorter iterations” and that DevOps was “agile with even shorter […]
SAML is a mechanism authenticating users and authorising them for a service in cases where the identify provider is external to the service. There are three parties involved: User (or User Agent, such as a web browser) – UA Service Provider – SP Identity Provider – IdP The user agent interacts with both the Service […]
