Friday, August 7, 2020

Can you query SQL Server's Error Log file location or size?

Why yes.  You can!  Working a new server today that I have limited access to... and I needed a way to get around that limitation.  I just needed to find the location of the SQL Server error log files -- and you know what else?  The size, too, because it was taking an ungodly amount of time to open the error log within SSMS.  The server hasn't been cycled in months, so I was sure the thing was pretty big.  

What does the DBA without sysadmin privileges do?  😏 


DECLARE @logfiles TABLE (

       [FilArchive#] TINYINT,

       [Date] DATETIME,

       [LogFileSizeB] BIGINT



INSERT @logfiles

EXEC xp_enumerrorlogs





       CONVERT(VARCHAR(50),CAST(SUM(CAST([LogFileSizeB] AS FLOAT)) / 1024 / 1024 AS DECIMAL(10,4))) + ' MB' SizeMB








-- to identify error log file location

SELECT SERVERPROPERTY('ErrorLogFileName') [Error_Log_Location];

The current one is not quite as large as I expected, but here you go -- the location and size of the current and all archived SQL Server Error logs.


DBCC SQLPERF(logspace) -- Only one database, or with conditions.

Many SQL Server DBAs often use 'DBCC SQLPERF(logspace)' to analyze transaction log utilization for their databases.  It is very helpful.  When run exactly as I've quoted (but without the quotes), you will return log usage details for all databases on the server:

But - what if you have a ton of databases on the server and you don't want to be scrolling through everything to find what you care about?  Or, maybe you only wish to see those databases where the log usage is greater than X%. 

Here's a quick little trick to use DBCC SQLPERF(logspace) with conditions, only returning details for the databases you care about.  

-- optional parameters


       @dbname VARCHAR(155) = NULL,

       @SpaceUsed FLOAT = NULL



       dbName VARCHAR(155),

       LogSizeMB FLOAT,

       [LogSpaceUsed%] FLOAT,

       [Status] INT



EXEC ('DBCC SQLPERF(''logspace'')')


-- Now pull it back for review

-- if your optional parms are null, you return log usage for all databases

SELECT dbName, LogSizeMB, [LogSpaceUsed%][Status]


WHERE (dbName = @dbName OR @dbName IS NULL)

AND ([LogSpaceUsed%] >= @SpaceUsed OR @SpaceUsed IS NULL);

Here I have populated my optional @SpaceUsed parameter, to only return details where the log is 30% used or greater.  Most likely you'll want to use a higher @SpaceUsed, but this is just a visual so you can see what I mean.

Or in this case, I've input the @dbName:                            

Hopefully you find it useful!

More details here:

Friday, July 31, 2020

Auto-generate ALTER DATABASE statements to move SQL Server database files

This is for the DBA who needs to move a LOT of SQL Server database files, but has very little patience to sit and type each one up one by one.  Like me.  Someone I know had to move log files and adjust filegrowths for nearly 50 databases yesterday, and she tells me she began pulling her hair out about 15 minutes into it. 😣

This query is just a fast way to generate the ALTER DATABASE statements to modify file location and filegrowth settings for the given @dbName.  And, it generates the rollback as well, just in case.  Remember, always cya.  Check the variables, edit to suit your needs.

USE master;  -- run it in the master db 

       @dbname SYSNAME = 'tempdb',  -- change to db you are moving
       @oldpath VARCHAR(255) =  'C:\MSSQL\2017\TempDB',  -- input existing path
       @newpath VARCHAR(255) = 'C:\MSSQL\2017\Data',  -- input new path
       @dFileGrowth CHAR(5) = '256MB',  -- change, if needed
       @lFileGrowth CHAR(5) = '128MB'  -- change, if needed

-- Generate ALTER statement to move files
SELECT + CHAR(13) + CHAR(10) +
'ALTER DATABASE ' + @dbname '
       Name = ' + name + ',
       FileName = ''' + REPLACE(physical_name,@oldpath,@newpath) +''',
       FileGrowth = ' + CASE WHEN mf.type = 1 THEN @lFileGrowth ELSE @dFileGrowth END + ');'
FROM sys.master_files mf
WHERE database_id = DB_ID(@dbname);

/* Generate ROLLBACK statement for safety measures.  */
SELECT + CHAR(13) + CHAR(10) +
'ALTER DATABASE ' + @dbname + '
       Name = ' + name + ',
       FileName = ''' + physical_name + ''');'
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);


Standard SSMS query to grid results will give you something like this, where you can just cut/paste into another query window to execute.

Or, if you change the SSMS query results to output to text, you can better see the structure of the ALTER statement.  Like this:

Happy SQL.  Hope to have helped!!

Thursday, July 23, 2020

Query CHECKDB history for all databases on a SQL Server

Do you know what Avamar backups are?  No?  Well, neither did I until very recently.  One of my customers uses Dell EMC Avamar for backing up their SQL Server databases.  I have worked with a lot of 3rd party backups, but this was my introduction to Avamar.  Not a big deal.. much like many 3rd party backup solutions, we just need to be sure the configuration and scheduling is correct, and that's that.

But it wasn't.  We've been having frequent unexplained failures, so the customer forwarded me details from their Avamar setup, and this is the first thing that caught my eye:

Consistency checks?!  The backup software is also performing CHECKBs?  Probably fine for some people, but I like my backup software to only do backups.  The CHECKDBs were already running in the daily maintenance, so I feared they may be running twice daily.  This is a quick query of your CHECKDB history, and if you look at the screenshot, you can see each database is being hit twice daily.  Even more interesting -- the 1st CHECKDB on 'ClassPrd' began at 3:10AM and ran for 0:11:57.  The next one began at 3:12AM and ran for 0:12:16.  I'll admit, I did not even know you could run CHECKDB from two different sessions on the same database in parallel.  You can.  I just tested it.  Probably not ideal.  Maybe this is why I was unaware.  😇

Cut/paste into your own SSMS query window and run as-is.  No changes needed.  Of course, you need to run it against a SQL Server where CHECKBs are being performed.

        @default_trace_path VARCHAR(500),
        @tracefilename VARCHAR(500),
        @indx INT;

SET @default_trace_path = (SELECT path FROM sys.traces WHERE is_default = 1);
SET @default_trace_path = REVERSE(@default_trace_path);
SELECT @indx  = PATINDEX('%\%', @default_trace_path);
SET @default_trace_path = REVERSE(@default_trace_path);
SET @tracefilename = LEFT( @default_trace_path,LEN(@default_trace_path) - @indx) + '\log.trc';
  SUBSTRING(CONVERT(NVARCHAR(MAX),TEXTData),36, PATINDEX('%executed%',TEXTData)-36) [Command],
(MAX),TEXTData),PATINDEX('%minutes%',TEXTData)+8,PATINDEX('%seconds.%',TEXTData)-PATINDEX('%minutes%',TEXTData)-8) [Duration]

FROM::fn_trace_gettable( @tracefilename, DEFAULT)
WHERE EventClass = 22 


Results from the instance where CHECKDBs were being done twice daily:

Side note;  Do we need to run CHECKDB twice daily?  No.  In my book, you run the integrity checks (CHECKDB) as often as you can, where best case would be daily.  It's a matter of managing the databases proactively, and becoming aware of potential problems in advance, rather than reactively (is that a word?) -- where we suddenly find corruption and have to figure out how bad it is, and how to resolve it -- fast.  

Proactive DBA = Happy DBA.

A couple good reads in this regard:

A little more about Dell Avamar: