September 28, 2009 at 5:47 am
Hi All,
Firstly Thanks for Looking,
i am trying to build a DBA Database which will hold information of all our SQL servers accross our networks.
I have built the following tables to hold the relevant information; its a start and i need to expand on what data to hold......
------------------------------------------------------
-- Database information
------------------------------------------------------
USE [SQLServers]
GO
/****** Object: Table [dbo].[Databases] Script Date: 09/28/2009 10:24:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Databases](
[ServerName] [varchar](max) NULL,
[DatabaseName] [varchar](max) NULL,
[DateCreated] [datetime] NULL,
[CompatibilityLevel] [varchar](max) NULL,
[Size] [varchar](max) NULL,
[DatabaseOwner] [varchar](max) NULL,
[DatabaseState] [varchar](max) NULL,
[Instance] [varchar](max) NULL,
[MDFLocation] [varchar](max) NULL,
[LDFLocation] [varchar](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
------------------------------------------------------
-- Maintenance Plan information
------------------------------------------------------
USE [SQLServers]
GO
/****** Object: Table [dbo].[MaintenancePlan] Script Date: 09/28/2009 10:26:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MaintenancePlan](
[ServerName] [varchar](max) NULL,
[MaintenacePlanName] [varchar](max) NULL,
[Description] [varchar](max) NULL,
[PlanRotation] [varchar](max) NULL,
[BackupDirectory] [varchar](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
------------------------------------------------------
-- Server information
------------------------------------------------------
USE [SQLServers]
GO
/****** Object: Table [dbo].[Servers] Script Date: 09/28/2009 10:26:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Servers](
[Name] [nvarchar](50) NULL,
127.0.0.1 [nvarchar](50) NULL,
[Domain] [nvarchar](50) NULL,
[Version] [nvarchar](50) NULL,
[Build] [nvarchar](50) NULL,
[SP] [nvarchar](50) NULL,
[Edition] [nvarchar](50) NULL,
[Port] [nvarchar](50) NULL,
[OS] [nvarchar](50) NULL,
[Description] [nvarchar](50) NULL,
[CollationID] [nvarchar] (50) NULL,
[InstanceName] [nvarchar](50) NULL,
[IsClustered] [nvarchar] (50) NULL,
[IsFullTextInstalled] [nvarchar](50) NULL
) ON [PRIMARY]
i can populate Serverinformation with..............
SELECT
SERVERPROPERTY('CollationID') AS [Collation ID],
SERVERPROPERTY('ComparisonStyle') AS [Comparison Style],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [Computer Name Physical NetBIOS],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('EngineEdition') AS [Engine Edition],
SERVERPROPERTY('InstanceName') AS [Instance Name],
SERVERPROPERTY('IsClustered') AS [Is Clustered],
SERVERPROPERTY('IsFullTextInstalled') AS [Is FullText Installed],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [Is Integrated Security Only],
SERVERPROPERTY('LCID') AS [LCID],
SERVERPROPERTY('LicenseType') AS [License Type],
SERVERPROPERTY('MachineName') AS [Machine Name],
SERVERPROPERTY('NumLicenses') AS [Num Licenses],
SERVERPROPERTY('ProcessID') AS [Process ID],
SERVERPROPERTY('ProductVersion') AS [Product Version],
SERVERPROPERTY('ProductLevel') AS [Product Level],
SERVERPROPERTY('ResourceLastUpdateDateTime') AS [Resource Last Update DateTime],
SERVERPROPERTY('ResourceVersion') AS [Resource Version],
SERVERPROPERTY('ServerName') AS [Server Name],
SERVERPROPERTY('SqlCharSet') AS [Sql CharSet],
SERVERPROPERTY('SqlCharSetName') AS [Sql CharSet Name],
SERVERPROPERTY('SqlSortOrder') AS [Sql Sort Order],
SERVERPROPERTY('SqlSortOrderName') AS [Sql Sort Order Name];
GO
i need some help on the others.......
I want to build a script that will poulate all three tables at the sametime.
Cheers
Pete
Kind Regards
Pete.
September 28, 2009 at 5:54 am
ok, different ways of populating the rest.
sys.databases will return relevant information for the databases
for the maintenence plans look at
sysdbmaintplans
sysdbmaintplan_databases
sysdbmaintplan_history
sysdbmaintplan_jobs
in the msdb database
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 28, 2009 at 6:12 am
Hi Pete,
In addition, if you're looking to expand on what data to hold, it may be useful to break out the file info from the database table (you may have more than two files for some of your DBs). In the files table, some useful info along with the total file size would be free space within the file and free space on the location of the files. You could go one step further and keep this info historically for trending and spotting out of the ordinary growth events.
With the instances you could maintain some historical wait stats and basic performance counters. It all depends on what other monitoring solutions you are already running in house. (It seems that managers love graphs!)
There are plenty of articles on this very site for this sort of thing.. There are some very clever people here! 😉
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
September 29, 2009 at 4:47 am
refer to SQL Server Tacklebox by Rodney Landrum under the books section.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 29, 2009 at 5:04 am
Start with properly design of your own tables !
DON'T use varchar(max) for everything ! Only use it if needed (>8000 bytes to be stored in a single column)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 30, 2009 at 2:19 pm
I suggest that you write your code in modules and in a way that it can be run later to add new servers and/or databases as they are added to the environment, and to update information about changes later. Also, do scripts that compare you tables to the current servers and databases to see if anything has been added or changed. Together these let your DBA database be "what should be" as well as "what is".
We typically have a few servers with many small databases on each in our shop. We have a DBA database on each server which contains some information about the servers and the databases on that server. One of the primary contents of the databases table is the backup schedule and location for each databases, with a '<default>' row for the server. The database and log backup SQL Agent jobs on the server uses sys.databases to determine the databases to backup, and sends an email to the DBA group if a database is missing from the tables. This way we know about the new databases, and can at least verify that they should be using the default backup schedule.
We also capture some database statistics in the DBA database. Currently we gather usage information. I've been working on capturing information about the physical and active T-Log sizes, and using the history to detect unusual growth spurts and knowing when and how much to shrink the log files.
So think about what else you can use your DBA database for to improve your life.
David Lathrop
DBA
WA Dept of Health
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply