June 12, 2019 at 12:54 am
Hi, how do you guys document your SQL set-ups?
I've seen lists of products but each one I look at seems to document the DDL of the database itself.
I'm looking for some sort of tool (or script(s))that I can run that will generate a report showing the SQL Server config (options and settings) and the same for the DBs attached to it. Filepaths, logical names, recovery options etc.
Does such a thing exist?
June 13, 2019 at 1:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 13, 2019 at 4:46 pm
Something like this?
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 @servername varchar (200)
set @servername = convert( varchar(200), SERVERPROPERTY ('servername'))
Insert into dba.dbo.INVENTORY (ID, ServerName, Database_Name, Recovery_Model, Creation_Date, Owner, IS_Mirrored, VLFcount)
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, v.VLFcount
FROM sys.databases D
join @vlfcounts V on D.name = v.dbname
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
"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]
June 13, 2019 at 4:47 pm
This one is helpful as well
DECLARE @serverID INT
, @Memory INT
, @Edition VARCHAR (200)
, @Version VARCHAR (200)
, @CoreCount INT;
DECLARE @MemoryOutput TABLE ( MemoryGB INT )
select @serverID = server_id from sys.servers
SELECT @CoreCount = cpu_count
FROM master.sys.dm_os_sys_info;
SELECT @Edition = CONVERT (VARCHAR (200), (SELECT SERVERPROPERTY ('Edition')))
, @Version = CONVERT (VARCHAR (200), (SELECT SERVERPROPERTY ('ProductVersion')));
IF @Version LIKE '9%' OR @Version LIKE '10%'
BEGIN
INSERT INTO @MemoryOutput ( MemoryGB )
EXEC('SELECT CONVERT(Numeric(5,0),physical_memory_in_bytes / 1073741824.0 ) /* Convert Bytes to GB */ FROM master.sys.dm_os_sys_info')
END
ELSE
BEGIN
INSERT INTO @MemoryOutput ( MemoryGB )
EXEC('SELECT CONVERT(Numeric(5,0),physical_memory_kb / 1048567.0 ) /* Convert KB to GB */ FROM master.sys.dm_os_sys_info')
END
SELECT @Memory = (SELECT TOP 1 MemoryGB
FROM @MemoryOutput)
INSERT INTO dbo.inventory_server ( ServerID, Memory, Edition, VERSION, CoreCount )
SELECT @serverID
, @Memory
, @Edition
, @Version
, @CoreCount;
"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]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply