Friday, May 30, 2014

When was the last transaction log backup?

I was performing a health check on a customer's inventory recently, and I produced a list of databases where the transaction log was larger than the data file.  Of course you know, this can happen if the transaction log dumps aren't occurring, or aren't occurring often enough.  The Customer was fairly adamant that the logs were being backed up, though, so I needed a quick way to confirm the last backup time, per database.

Run this.  It will give you the RECOVERY MODEL and last transaction log dump time for all of your databases.

   SELECT
       d.name [Database],
       d.recovery_model_desc [RecoveryModel],
       MAX(b.backup_finish_date) [LastTranLogBkup]
   FROM
  master.sys.databases d LEFT OUTER JOIN msdb..backupset b
         ON b.database_name = d.name
         AND b.type = 'L'
   GROUP BY 
       d.name, 
       d.recovery_model_desc
   ORDER BY 
       LastTranLogBkup DESC

Monday, May 12, 2014

When was CHECKDB last run on your databases?

Hopefully, as SQL Server DBAs, we all know the value of CHECKDB.  I started a new contract recently, and found that it had never been run.  When I asked, I was just told that 'we didn't think it was that important'.  Long story short, CHECKDB consistency checks allow us to proactively monitor for database corruption, and maintain the integrity of our databases.  Proactive awareness is far better than reactive.  In my book, I don't think you can run it too frequently.  

Here's a quick way to confirm when CHECKB was last run on your databases.  

-- A couple temp tables 
CREATE TABLE #DBInfo (
  ID INT IDENTITY(1,1), 
  ParentObject VARCHAR(255),
  Object VARCHAR(255),
  Field VARCHAR(255),
  Value VARCHAR(255) 
)

CREATE TABLE #Value(
  DatabaseName VARCHAR(255),
  LastCheckDB VARCHAR(255)
)

-- Populate temp tables
EXECUTE sp_msforeachdb 'INSERT INTO #DBInfo Execute (''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS'');
INSERT INTO #Value (DatabaseName) SELECT Value FROM #DBInfo WHERE Field IN (''dbi_dbname'');
UPDATE #Value 
SET LastCheckDB = (SELECT TOP 1 Value FROM #DBInfo WHERE Field IN (''dbi_dbccLastKnownGood'')) 
WHERE LastCheckDB IS NULL;
TRUNCATE TABLE #DBInfo';

-- Return the output 
SELECT * FROM #Value

-- Drop temp tables 
DROP TABLE #DBInfo
DROP TABLE #Value


These are the results from my own instance:

       Database                        LastCheckDB
       master                             2014-05-12 11:26:20.610
       tempdb                           1900-01-01 00:00:00.000 --- no biggie. no need to check tempdb
       model                              2014-05-12 14:52:32.743
       msdb                               2014-05-12 14:52:34.053
       Mine                               2014-05-12 19:58:46.717
       AdventureWorks2012       2014-05-12 19:58:38.850



If you are not running CHECKDB consistency checks regularly, I strongly encourage you to put something into place -- soon!  You can take a look at my User Database Maintenance posting:   
    http://www.sqlfingers.com/2011/01/user-database-maintenance.html

Or take a look at Ola Hallengren's site for a much larger overview of the approach that I use in my maintenance tasks:    
    http://ola.hallengren.com/sql-server-integrity-check.html

Identify the SQL Server Service Accounts using tSQL.

Every so often you are going to need to check the SQL Server service accounts.  Maybe you've just taken on a new customer, or you've inherited someone else's SQL inventory, and you don't know what accounts the services are running under.  You can remote into the servers one at a time, and run SQL Server Configuration Manager, but --  that is kind of time consuming.  Use this piece for a much quicker way to find your service accounts.  You can even run it across a CMS (Central Management Server) Group, and collect this data from all of your servers with one execution.

    SELECT  
         ds.servicename [ServiceName],
         ds.startup_type_desc [StartupType],
         ds.status_desc [ServiceStatus],
         ds.service_account [ServiceAccount],
         ds.filename [EXEFileName],
         ds.last_startup_time [LastStartup]
    FROM
         sys.dm_server_services ds;


There are some other values from sys.dm_server_services as well, but I've just returned a few quick details on the service accounts.  See this for more details on this DMV:

    http://technet.microsoft.com/en-us/library/hh204542.aspx