DBCC CheckDB

  • All,

    I am wondering if I can get some help from experts. This is what I am trying to do:

    1) How to find out when the last checkDB was for the database?

    2) How to get the datediff in days between that date and now (getdate())

    3) Lastly, if it's more than 14 days, then 1, else 0

    This is the code I have:

    Declare @serverID int,

    @servername varchar (200)

    set @servername = convert( varchar(200), SERVERPROPERTY ('servername'))

    select @serverID = ID from dba.dba.dbo.serverlist

    where servername = @servername

    delete from dba.dbo.INVENTORY

    where @servername = ServerName

    Insert into dba.dbo.Inventory (ID, ServerName, Database_Name, Recovery_Model, Creation_Date, Owner, IS_Mirrored, Database_Status)

    SELECT @serverID, @servername, D.name, D.recovery_model_desc, D.create_date, suser_sname(D.owner_sid), CASE When M.mirroring_state IS NUll Then 0 Else 1 END, state_desc

    FROM sys.databases D

    Join sys.database_mirroring M on D.database_id = M.database_id

    Please keep in mind, I don't have tons of experience in writing extensive SP.:-):-)

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • Something like this

    CREATE TABLE #Temp (ParentObject VARCHAR(100), Object VARCHAR(100), Field VARCHAR(100), Value VARCHAR(100))

    INSERT INTO #Temp

    EXECUTE ('DBCC DBINFO (''YourDBNameHere'') WITH TABLERESULTS')

    select

    distinct

    'Last Known Good DBCC CHECKDB',

    Value,

    datediff(day,convert(datetime,value),getdate()) AS DayDifferenceFromToday,

    case when datediff(day,convert(datetime,value),getdate()) > 14 THEN 1 ELSE 0 END AS IsMoreThan14DaysAgo

    from #temp where field = 'dbi_dbcclastknowngood'

    drop table #temp

  • anthony.green (10/2/2015)


    Something like this

    CREATE TABLE #Temp (ParentObject VARCHAR(100), Object VARCHAR(100), Field VARCHAR(100), Value VARCHAR(100))

    INSERT INTO #Temp

    EXECUTE ('DBCC DBINFO (''YourDBNameHere'') WITH TABLERESULTS')

    select

    distinct

    'Last Known Good DBCC CHECKDB',

    Value,

    datediff(day,convert(datetime,value),getdate()) AS DayDifferenceFromToday,

    case when datediff(day,convert(datetime,value),getdate()) > 14 THEN 1 ELSE 0 END AS IsMoreThan14DaysAgo

    from #temp where field = 'dbi_dbcclastknowngood'

    drop table #temp

    Is there a way to add the date of the last successful checkDB to this table?

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • Any help on this?

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • The date of the last successful CHECKDB is already there. It's the date in the Value column.

    Cheers!

  • New Born DBA (10/5/2015)


    Any help on this?

    Have a go at writing a loop to run the query I posted for each DB you have on your server, one thing to look at could be sp_msforeachdb

    Then have a go at joining your query to the #temp table to get the dates for all the databases.

    Post back what you get, will be a good test to develop your T-SQL further.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply