October 14, 2015 at 2:33 pm
I have this script from https://gallery.technet.microsoft.com/scriptcenter/SQL-Script-to-list-VLF-e6315249 and this is what I have
DROP TABLE [dbo].[INVENTORY]
CREATE TABLE [dbo].[INVENTORY](
[ID] [int] NULL,
[ServerName] [varchar](200) NULL,
[Database_Name] [varchar](200) NULL,
[Recovery_Model] [varchar](30) NULL,
[Creation_Date] [datetime] NULL,
[Owner] [varchar](50) NULL,
[IS_Mirrored] [int] NULL,
[VLFcount] int
)
--variables to hold each 'iteration'
declare @query varchar(100)
declare @dbname sysname
declare @vlfs int
--table variable used to 'loop' over databases
declare @databases table (dbname sysname)
insert into @databases
--only choose online databases
select name from sys.databases where state = 0
--table variable to hold results
declare @vlfcounts table
(dbname sysname,
vlfcount int)
--table variable to capture DBCC loginfo output
--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version
declare @MajorVersion tinyint
set @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))-1)
if @MajorVersion < 11 -- pre-SQL2012
begin
declare @dbccloginfo table
(
fileid tinyint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '
insert into @dbccloginfo
exec (@query)
set @vlfs = @@rowcount
insert @vlfcounts
values(@dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
else
begin
declare @dbccloginfo2012 table
(
RecoveryUnitId int,
fileid tinyint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '
insert into @dbccloginfo2012
exec (@query)
set @vlfs = @@rowcount
insert @vlfcounts
values(@dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
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)
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
FROM sys.databases D
Join sys.database_mirroring M on D.database_id = M.database_id
Join sys.database_files F on D.state = F.state
select * from INVENTORY
SO now what I am trying to do is this. I need to combine both script together so I can get
ID, ServerName, DBName, Recovery_Model, Creation_DAte, Owner, IS_Mirrored, VLFCount
I just add the vlf script to the script I have and ran a select statement which gave me all the good information but not the VLF count. So basically I am not able to join these 2 scripts. Can I get any help from anyone since my sql skills are at the beginning stage, if you know what I mean :-D:-D:-D:-D:-D:-D
October 15, 2015 at 1:32 am
Can't you just join on the tables like this?
Insert into dbo.Inventory (ID, ServerName, Database_Name, Recovery_Model, Creation_Date, Owner, IS_Mirrored,VLFcount)
SELECT DISTINCT @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 ,vlf.vlfcount
FROM sys.databases D
Join sys.database_mirroring M on D.database_id = M.database_id
Join sys.database_files F on D.state = F.state
Join @vlfcounts vlf ON D.name =vlf.dbname
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply