October 2, 2015 at 8:28 am
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]
October 2, 2015 at 8:41 am
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
October 2, 2015 at 10:29 am
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]
October 5, 2015 at 11:07 am
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]
October 5, 2015 at 1:12 pm
The date of the last successful CHECKDB is already there. It's the date in the Value column.
Cheers!
October 6, 2015 at 1:23 am
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