John McCormack DBA

SQL Server Databases and AWS Cloud Solutions

An alternative to sp_msforeachdb

By

It’s well known that the sp_msforeachdb stored procedure is undocumented however it can be handy. The trouble with the proc is that it is unreliable. What’s more, it could be dropped from future SQL Server releases causing all your automated scripts to fail.

This little script shows you a more reliable solution and saves you from relying on an undocumented procedure.


USE Master
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT name,0 as completed
INTO #tmp
FROM sys.databases
WHERE name NOT IN('tempdb','Training2012') -- A list of DBs you don't wish to include

DECLARE @dbname sysname
DECLARE @cmd NVARCHAR(4000)

WHILE EXISTS(SELECT 1 FROM #tmp WHERE completed = 0)
BEGIN

SET @dbname = (SELECT TOP 1 NAME FROM #tmp WHERE completed = 0) -- You can ORDER BY name if you care about the order
SET @cmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''D:\Backup\'+@dbname+'.Bak'''
EXEC sp_executesql @cmd
UPDATE #tmp SET completed = 1 WHERE name = @dbname

END

Let’s break up what the script actually does:

  1. DROP temp table if it already exists
    1. IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
  2. Select into a temporary table with two columns. (Name and Completed). We set completed=0 for each row created.
    1. SELECT name,0 as completed INTO #tmp FROM sys.databases
  3. The WHERE clause is optional
    1. WHERE name NOT IN('tempdb','Training2012')
  4. Create a @dbname variable (for each DB name) and a @cmd variable (for building up the T-SQL command). 
    1. DECLARE @dbname sysname
DECLARE @cmd NVARCHAR(4000)
  5. The LOOP (Create and execute a new statement for each DB name and exit the loop once all DBs have been handled). 
    1. WHILE EXISTS (SELECT 1 FROM #tmp WHERE completed = 0)
BEGIN
SET @dbname = (SELECT TOP 1 NAME FROM #tmp WHERE completed = 0) -- You can ORDER BY name if you care about the order
SET @cmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''D:\Backup\'+@dbname+'.Bak'''
EXEC sp_executesql @cmd
UPDATE #tmp SET completed = 1 WHERE name = @dbname
END

This approach is simple and reliable and it avoids the use of undocumented stored procedures and cursors. The example above demonstrates a native backup but this command can be anything you like.

 

Index maintenance using Ola Hallengren’s scripts

By

Having recently set up Index maintenance using Ola Hallengren’s scripts on a range of my company’s production servers, I wanted to write about the steps involved including some custom steps to ensure the overall health of the SQL Server database.

The aim of this blog post is to be not too technical, I don’t cover what Ola has documented on his site however I do include scripts for my custom steps. I will only cover index maintenance, although these scripts give the options for statistics maintenance also. This is because we already had a custom solution for statistics maintenance that was working great, whereas we needed to make improvements to our index maintenance solution.

[Read more…]

Check percentage complete of transaction

By 


/*

This simple script shows what percentage of a transaction has completed
This works for the following types of transaction:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE,
ROLLBACK
TDE ENCRYPTION
Change the 63 in the where clause to match the spid for your transaction.
Hint exec sp_who2 and look at command. This is the spid you want.

*/

SELECT percent_complete,*
FROM sys.dm_exec_requests
WHERE session_id = 63 -- Remember to change session_id = to the relevant spid