September 30, 2014 at 6:53 pm
Hello,
Attached text file contains a script I started putting together this evening. The basic premise is to perform DBCC checks on as close to 100gb worth of databases at a time, excluding those that have a good DBCC check date in the past 30 days.
A few points:
I know that sp_MSForEachDBB is undocumented and sometimes unreliable. I just wanted to get something moving on this and the Aaron Bertrand replacement wasn't installed on my dev box.
This is for two old servers that hold databases for some sub-optimal in-house software designed a decade ago by people who I can't seem to call enough names. There are typically 2500 databases on each server, because a new one gets created for every 'export'.
I'm calling the Ola Hallengren procedure for the execution because we're already using other aspects of the maintenance plan and it makes the most sense to me to have everything logging to one table.
Anyway, with that in mind, any feedback/shoe throwing/advice on how to do this better is appreciated.
Thanks
October 1, 2014 at 7:40 am
Added a variable to limit time.
SET NOCOUNT ON
SET XACT_ABORT ON
CREATE TABLE #SizeLoop
(
id INT IDENTITY(1, 1)
NOT NULL ,
name sysname ,
sizeMB INT
)
CREATE TABLE #dbinfo
(
[ParentObject] VARCHAR(30) ,
[Object] VARCHAR(30) ,
[Field] VARCHAR(40) ,
[Value] VARCHAR(255)
)
CREATE TABLE #DBCCResults
(
DBName NVARCHAR(255) ,
LastCleanDBCCDate DATETIME
)
CREATE TABLE #doWork
(
name sysname ,
SizeMB INT
)
INSERT #doWork
( name ,
SizeMB
)
SELECT name ,
size / 128 AS SizeMB
FROM sys.master_files AS mf
WHERE mf.database_id > 6
AND mf.type = 0
DECLARE @sum INT = 1024 ,
@cntr INT = -1 ,
@dbname NVARCHAR(128) = '' ,
@TimeLimit INT = 6 ,
@clock DATETIME ,
@cmd NVARCHAR(1024) = '
EXECUTE dbo.DatabaseIntegrityCheck
@databases = ''|dbname|'',
@CheckCommands = ''CHECKDB'',
@LogToTable = ''Y'',
@PhysicalOnly = ''Y'',
@Execute = ''N''
'
SELECT @clock = DATEADD(HOUR, @TimeLimit, GETDATE())
EXEC sys.sp_MSforeachdb '
USE [?]
INSERT #dbinfo
( ParentObject, Object, Field, Value )
EXEC (''DBCC DBINFO([?]) WITH TABLERESULTS'')
INSERT #DBCCResults
( DBName ,
LastCleanDBCCDate
)
SELECT DISTINCT DB_NAME(), [Value] FROM #dbinfo AS d WHERE d.Field = ''dbi_dbccLastKnownGood''
TRUNCATE TABLE #dbinfo
'
DELETE dw
FROM #doWork AS dw
INNER JOIN #DBCCResults AS dr ON dw.name = dr.DBName
WHERE dr.LastCleanDBCCDate >= DATEADD(DAY, -30, GETDATE())
DELETE FROM #doWork
WHERE name IN (
SELECT sd.name
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
GROUP BY sd.name
HAVING COUNT(status) > 0 )
WHILE ( SELECT COALESCE(SUM(sizeMB), 0)
FROM #SizeLoop
) < @sum
INSERT #SizeLoop
SELECT TOP 1
dw.name ,
dw.SizeMB
FROM #doWork AS dw
WHERE dw.name NOT IN ( SELECT name
FROM #SizeLoop AS sl )
ORDER BY dw.SizeMB
--SELECT dr.DBName ,
-- dr.LastCleanDBCCDate
--FROM #DBCCResults AS dr
--SELECT dw.name ,
-- dw.SizeMB
--FROM #doWork AS dw
--SELECT sl.id,
--sl.name ,
-- sl.sizeMB
--FROM #SizeLoop AS sl
SELECT @cntr = MAX(id)
FROM #SizeLoop AS sl
WHILE ( @cntr > 0
AND GETDATE() < @clock
)
BEGIN
SELECT @dbname = name
FROM #SizeLoop AS sl
WHERE sl.id = @cntr
SELECT @cmd = REPLACE(@cmd, '|dbname|', @dbname)
EXEC sys.sp_executesql @cmd
SET @cntr = @cntr - 1
END
DROP TABLE #SizeLoop;
DROP TABLE #dbinfo;
DROP TABLE #DBCCResults;
DROP TABLE #doWork
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply