October 29, 2015 at 10:39 am
Hi Team,
I just want to capture the below information through SQL script.
SQL Server Name
Number Of Instances
Number of Databases and size
Service account details
RAM
CPU
ANY HA is configured for databases like (Log shipping,Mirroring,Replication)
Connection mode
Recovery Model of database
October 29, 2015 at 10:55 am
New persopn (10/29/2015)
Hi Team,I just want to capture the below information through SQL script.
SQL Server Name
Number Of Instances
Number of Databases and size
Service account details
RAM
CPU
ANY HA is configured for databases like (Log shipping,Mirroring,Replication)
Connection mode
Recovery Model of database
Cool what have you tried? Hint: some of these will not be possible through t-sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2015 at 11:01 am
I Have a query which will give the information like clustered,Computername,edition,version,hostname,servername,instancename,connedctingmode and service accounts information
--Select @@version,
select SERVERPROPERTY('IsClustered') As IsClustered,
--Serverproperty('BuildClrVersion') AS BuildClrVersion,
Serverproperty('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS,
Serverproperty('Edition') AS Edition,
CASE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
WHEN '9.00' THEN 'SQL 2005'
WHEN '10.0' THEN 'SQL 2008'
WHEN '10.50' THEN 'SQL 2008R2'
WHEN '11.0' THEN 'SQL 2012'
WHEN '12.0' THEN 'SQL 2014'
WHEN '13.0' THEN 'SQL 2016'
ELSE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
END As SQLVersion,
--Serverproperty('productversion') As ProductVersion,
--Serverproperty('EditionID') AS EditionID,
--Serverproperty('EngineEdition') AS EngineEdition,
Serverproperty('MachineName') AS HostName,
--Serverproperty('ProductLevel') AS ProductLevel,
--Serverproperty('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
--Serverproperty('ResourceVersion') AS ResourceVersion,
Serverproperty('ServerName') AS ServerName,
Serverproperty('InstanceName') AS InstanceName,
SERVERPROPERTY('IsIntegratedSecurityOnly')as ConnectingMode
SELECT servicename, service_account
FROM sys.dm_server_services
in another query it will capture the information like
Name
- SQL Version
- Time Zone
- Max Degree of Parallelism (Configured , Running & Optimal)
- Number of Cores
- Memory
- Cluster ot Standalone
- SQL Server service start time
But i want above information in single or multipel query
http://sql-javier-villegas.blogspot.com.au/2011/10/informacion-acerca-de-la-intancia-sql.html
October 29, 2015 at 2:10 pm
OK so you have a query that captures a large part of this information already. What do you actually need help with?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2015 at 2:35 pm
I can help you out with a couple of them, but I think Sean's right - you aren't going to get all of them in T-SQL. In fact, I'm interested to see if someone knows a way to get some of them. 😉
For Version, @@VERSION will give you all the information you need and then some.
SELECT @@VERSION;
For the SQL Server startup time, you can use the date/time that tempdb was created:
SELECT create_date
FROM sys.databases
WHERE name = 'tempdb';
The max degree of parallelism can be obtained from sys.configurations. Using sp_configure will get you both the configured and running values.
SELECT value_in_use
FROM sys.configurations
WHERE name = 'max degree of parallelism';
EXECUTE sp_configure 'max degree of parallelism';
October 29, 2015 at 2:57 pm
Some of these are pretty ambiguous to me.
SQL Server Name - Is this the computer name of the server or the name of the instance?
Number Of Instances - This would always be one from t-sql (if it is even possible). From the windows side this would be really tricky
Number of Databases and size - not sure what you mean by size. Is that data? Data and log files? etc.
Service account details - What service? There are several service accounts that can be used with sql server.
RAM - Is this the total on the machine, the total amount allowed for sql server or the current amount being used?
CPU - I assume you mean processors? Again, is the total on the machine, the number available to sql or the setting for parallelism?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply