Collecting SQL Server 2000/2005/2008 baseline

Question

Collecting SQL Server 2000/2005/2008 baseline

akhamoshioke

Ten Centuries

Points: 1110
More actions
April 29, 2014 at 5:44 pm

#286330

Comments posted to this topic are about the item Collecting SQL Server 2000/2005/2008 baseline

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jorge Serres Old Hand Points: 352 More actions · Answer 1

Just change column physical_memory_in_bytes with [physical_memory_kb/1048.576]!! otherwise it'll generate some blood!:-D

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 2

akhamoshioke

Ten Centuries

Points: 1110

May 18, 2014 at 11:17 pm

#1714513

What version of sql server id you run it against?

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 3

What we are actually doing with that line of code is converting bytes to MB.

1024bytes = 1Kb

1024Kb = 1MB

therefore; 1MB = (1024 * 1024) = 1048576

Hence the "physical_memory_in_bytes/1048576" Column being displayed as (physical_memory_in_bytes/1048576) as 'PhysicalMemoryMB'

You can tweak it to show you in GB or KB or bytes. It's totally up to you.

No Bloods yet:::

:-D:-):-D:-):-D;-)

Robert_sqlapprenticeDOTnet SSC-Addicted Points: 433 More actions · Answer 4

Hi,

unfortunately neither sys.sysdatabases nor sys.databases (or even DATABASEPROPERTYX) can provide any usefull information regarding if the database is subscribed or not. (See Book Online).

If you want to check this part you should use something like this:

USE DB

DECLARE @replication_role NVARCHAR(15) = ''''

IF OBJECT_ID(''dbo.MSreplication_objects'') IS NOT NULL

SET @replication_role = ''Subscriber''

You can wrap up the code into sp_MSforeachdb.

Jorge Serres Old Hand Points: 352 More actions · Answer 5

Hi, I'm using SQL Server 2014!

The column name is [[highlight=#ffff11]physical_memory_kb[/highlight]]! There's no column [physical_memory_in_bytes] in sys.dm_os_sys_info;

Here are all the columns in the DMV sys.dm_os_sys_info

cpu_ticks

ms_ticks

cpu_count

hyperthread_ratio

physical_memory_kb

virtual_memory_kb

committed_kb

committed_target_kb

visible_target_kb

stack_size_in_bytes

os_quantum

os_error_mode

os_priority_class

max_workers_count

scheduler_count

scheduler_total_count

deadlock_monitor_serial_number

sqlserver_start_time_ms_ticks

sqlserver_start_time

affinity_type

affinity_type_desc

process_kernel_time_ms

process_user_time_ms

time_source

time_source_desc

virtual_machine_type

virtual_machine_type_desc

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 6

Summer90

SSC-Dedicated

Points: 32891

May 19, 2014 at 8:58 am

#1714670

Great script, thanks!

zuma-1122451 SSC Enthusiast Points: 146 More actions · Answer 7

Jorge Serres (5/18/2014)
Just change column physical_memory_in_bytes with [physical_memory_kb/1048.576]!! otherwise it'll generate some blood!:-D

I am running SQL Server Enterprise 2012 SP1. I just changed the line in the quote above and it was good to go.

Thanks for the great script Patrick!

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 8

akljfhnlaflkj

SSC Guru

Points: 76202

May 11, 2016 at 6:29 am

#1878132

Thanks for the script.