Friday, March 22, 2019

Kill all connections to SQL Server database -- fast

What's the fastest way to kill all database connections?  Properly, I should say... without pulling the cord on the server.

I'm glad you asked! 😉  The easiest/fastest way that I am aware of is to set the database into SINGLE_USER.  This will sever all active connections to the database aside from your own.  Like this: 

-- kill all connections to a database
USE master;
ALTER DATABASE SSISDB -- change to your dbname
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE; 

When you've done your thing and you're ready to let them back in, you'll set it back to MULTI_USER, like this:

-- set it back into multi user
USE master;
ALTER DATABASE SSISDB -- change to your dbname
SET MULTI_USER;


Easy peasy.

You could also use a cursor to go through all active processes in the database, killing them sequentially... but, I don't often recommend the cursor method, if it can be avoided.

How long is that SQL Server backup going to take?

Good question.  I was backing up a customer database today and let them know about how long to expect it to take.  They said 'No... it should only take a couple hours!'  I had no experience w/this particular database before, so I thought I'd query the system to see how long the last one ran for.  This post is the query I used.  Just a super quick way to get the duration of the last backup for the given database.  


DECLARE @dbname sysname
SET @dbname = 'JDE_PRODUCTION' -- your dbname here
SELECT
  bup.user_name ByWhom,
  bup.database_name DatabaseName,
  bup.server_name ServerName,
  bup.backup_start_date StartTime,
  bup.backup_finish_date EndTime,
  CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS INT))/3600 AS VARCHAR) + ' hours, '
       + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS INT))/60 AS VARCHAR)+ ' minutes, '
       + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS INT))%60 AS VARCHAR)+ ' seconds' Duration
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN (
   SELECT MAX(backup_set_id)
   FROM msdb.dbo.backupset
   WHERE database_name = @dbname
   AND type = 'D' -- change for the type you need;  I = DIFF, L = LOG, D = FULL
   GROUP BY database_name
   );

And there you have it.  Short and sweet.  Just the kind of tSQL I favor.  :)   






It's not a guaranteed duration going forward, but it will certainly give you very close expectations.  Unless of course the thing increased or decreased in size dramatically... but that's another story.

Thursday, January 24, 2019

Who changed SQL server's max memory?

I found the max server memory (mb) was misconfigured today on one of my customer's servers.  The server had 32GB physical ram, but SQL Server's max server memory (mb) was set too high at 48GB.  The customer insisted they hadn't changed it, and asked me to find out who had... but how?  Easy peasy.  Using sp_readerrorlog we can search all of the available ERROR LOGs for the 'configuration option' string.  But, if it's not in the available ERROR LOGs, you'll see this:


In that case, we can got to go to the Windows Application Event log and search for the same thing.  Open Event Viewer, Windows Logs, right click Application and choose 'Find'.  Input your string and lookie there!  We found the configuration change string, and can see it was made TWO YEARS ago.  Have to wonder how that misconfiguration has impacted the server memory all this time!  



So, I changed it to 29GB, which is the recommended setting for a dedicated server with 32GB physical, and now we can see it in the current ERROR LOG.









Need to be careful about who is authorized to make changes like this.  And, we have to recognize that people make mistakes.  May be a good idea may be to trigger notifications when configuration changes are made.  Good idea!  ... for a later post.


Sunday, January 20, 2019

Who shrunk that database file?

Much like 'Who dropped that table?', you hope you're the only one shrinking files in your SQL Server database... but if you believe that, then I've got a bridge to sell you.  😏  Again, I really love SQL Server's default trace!  Incredibly helpful, with so many different uses!!  Here's an example of how to use it to find out who shrank the SQL Server database files.

First run this to find your trace file:

    -- find your default trace file
    SELECT * FROM fn_trace_getinfo(default);

Now input your filepath and name from the above query into the FROM in this statement:

    -- query it for any shrinkfiles
    SELECT
      TextData,
      HostName,
      ApplicationName,
      LoginName,
      StartTime 
   FROM
      [fn_trace_gettable]('C:\Program Files\Microsoft ...\log_175.trc', DEFAULT)
   WHERE 
      TextData LIKE 'DBCC SHRINKFILE%'

And here you go:


Now you know which files Mr/Ms Lenovo shrank, and when.  

Shrinking SQL Server's database files is something you'll do only in worst case scenarios.  ie., Your log is blowing up and you are going to run out of space in the middle of the production day.  Ok.  Shrink it.  But you should really try not to make this a repeat activity.  For more evidence of why, take a look at the Hamster Wheel of Death explanation from Brent Ozar.

Of course, as a reminder, if your default trace isn't enabled/running, it really should be.  Run this to confirm default trace status:

       SELECT * FROM sys.configurations WHERE configuration_id = 1568



Use REPLACE multiple times on a single column

Using REPLACE in a statement allows us to update, or 'replace' something from a string with something else, like this:

       DECLARE @sample VARCHAR(15);
    SET @sample = 'Mister Jones'
    SELECT @sample [Old], REPLACE(@sample,'Mister','Mr.') [New];

Will give this back to you:



But what if there is more than one string in a single data value that you want to replace?  Maybe you need to replace 'Mister' and 'Misses' from the same column -- or even Miz!  You can run REPLACE more than once on a single column, in a single statement, simply by nesting them.  Like this:
DROP TABLE #temp;
CREATE TABLE #temp (DataValue VARCHAR(55))
INSERT #temp (DataValue)
VALUES ('Mister Jones'),
          ('missus Smith'),
          ('Miz Wilson');

SELECT
       DataValue [Old],
       REPLACE(
              REPLACE(
                     REPLACE(
                     DataValue,'Mister','Mr.'),
              'missus','Mrs.'),
       'Miz','Ms.') [New]
FROM #temp;

Gives you back a little something like this:




Important to note that this is not without overhead.  Putting the data in clean, or correcting the data values with an UPDATE would be more effective, but I understand we don't have a choice sometimes.  The nested REPLACE will let you run more than one REPLACE on a single data column.

See here for more details on REPLACE.   



Wednesday, January 2, 2019

Query SQL Server transactions - per day, hour and minute

This post will help you query your SQL Server for transaction count at both the server level and for each of the databases.  The query returns transaction statistics per day, hour and minute since the time of the last service restart.


/* SQL Server transactions per day/hour/minute using sys.dm_os_performance_counters */

       -- declarations
       DECLARE      
              @Days SMALLINT,
              @Hours INT,
              @Minutes BIGINT,
              @LastRestart DATETIME;

       -- get last restart date
       SELECT 
              @Days = DATEDIFF(D, create_date, GETDATE()),@LastRestart = create_date
       FROM   
              sys.databases
       WHERE  
              database_id = 2;

       -- collect days/hours since last restart
       SELECT @Days = CASE WHEN @Days = 0 THEN 1 ELSE @Days END;
       SELECT @Hours = @Days * 24;
       SELECT @Minutes = @Hours * 60;


       -- trans since last restart
       SELECT 
              @LastRestart [LastRestart],
              @@servername [Instance],
              cntr_value [TotalTransSinceLastRestart],
              cntr_value / @Days   [AvgTransPerDay],
              cntr_value / @Hours  [AvgTransPerHour],
              cntr_value / @Minutes [AvgTransPerMinute]
       FROM
              sys.dm_os_performance_counters
       WHERE  
              counter_name = 'Transactions/sec'
              AND instance_name = '_Total';


       -- trans since last restart per database
       SELECT 
              @LastRestart [LastRestart],
              @@servername [Instance],
              instance_name [Database_Name],
              cntr_value [TotalTransSinceLastRestart],
              cntr_value / @Days [AvgTransPerDay],
              cntr_value / @Hours  [AvgTransPerHour],
              cntr_value / @Minutes [AvgTransPerMinute]
       FROM  
              sys.dm_os_performance_counters
       WHERE  
              counter_name = 'Transactions/sec'
              AND instance_name <> '_Total'
       ORDER BY
              cntr_value DESC;


Your output will be similar to this:

















Take a look at this for more details on the dm_os_performance_counters DMV:     sys.dm_os_performance_counters