T-SQL that return data of the SQL Server databases

Select @@SERVERNAME -- Server and instance name

Select @@VERSION -- SQL Server Version

Select @@ServiceName -- SQL Server Instance

Select DB_NAME() -- Current Database

SELECT * FROM sys.servers ORDER BY name --Linked Servers

--Last Databases Backup:

SELECT  @@Servername AS ServerName ,

        d.Name AS DBName ,

        MAX(b.backup_finish_date) AS LastBackupCompleted

FROM    sys.databases d

LEFT OUTER JOIN msdb..backupset b 

        ON b.database_name = d.name AND b.[type] = 'D'

GROUP BY d.Name

ORDER BY d.Name;

And more - in the article "Exploring Your SQL Server Databases with T-SQL":

https://www.simple-talk.com/sql/database-administration/exploring-your-sql-server-databases-with-t-sql/?utm_source=ssc&utm_medium=publink&utm_content=exploringwithtsql

Partition info

select distinct

object_name(p.object_id),

      p.partition_number,

      pf.name,

      ps.name,

      prv.value,

      p.data_compression_desc,

      fg.name,

      p.rows

from  sys.partitions (nolock) p

inner join sys.indexes (nolock)i on p.object_id = i.object_id and p.index_id = i.index_id

inner join sys.partition_schemes ps (nolock) on ps.data_space_id=i.data_space_id

inner join sys.partition_functions (nolock)pf on pf.function_id=ps.function_id

inner join sys.partition_range_values(nolock) prv on p.partition_number = prv.boundary_id and prv.function_id=pf.function_id

inner join sys.allocation_units au  ON au.container_id = p.hobt_id

inner join sys.filegroups fg  ON fg.data_space_id = au.data_space_id

where pf.name='PF_Name'

and object_name(p.object_id) = 'TableName'

--and partition_number<386

--and cast(prv.value as datetime) > '2014-03-04 00:00:00.000'

order by object_name(p.object_id),p.partition_number-- desc

Current running query

select getdate() date_time, t3.session_id, host_name, t3.status,DB_NAME(t2.database_id) DataBase_Name, 

blocking_session_id AS Blocked_by,SUBSTRING(t1.text, (t2.statement_start_offset/2)+1,

((CASE t2.statement_end_offset WHEN -1 THEN DATALENGTH(t1.text) WHEN 0 THEN DATALENGTH(t1.text) ELSE t2.statement_end_offset END - t2.statement_start_offset)/2) + 1) AS command,

t3.cpu_time, t3.reads, t3.writes, wait_time, t2.wait_type, last_request_end_time,program_name, t1.objectid 

from sys.dm_exec_sessions t3 

left join sys.dm_exec_requests t2 on t3.session_id=t2.session_id 

outer APPLY sys.dm_exec_sql_text (t2.sql_handle) t1 

where t3.status not in ('sleeping','dormant') and program_name not like 'sqlagent%'

Incorrect syntax near the keyword 'SCHEMA'

If you try to execute:

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'NewSchemaName')

       CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]

GO

You will get this error message:

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'SCHEMA'.

And it although this command will success:

CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]

So why we’ve got the error?

Because schema creation must be the first command in a batch.

A solution:
Run it as dynamic SQL:

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'NewSchemaName')

       EXEC sp_executesql N'CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]'

GO

Tempdb contention on sysmultiobjrefs (2:1:103) causes performance problems

Tempdb contention on sysmultiobjrefs (2:1:103) causes performance problems

The locks in tempdb were from type page latch on file 1 page 103 (2:1:103).

We can see it in our locks table.

Unfortunately this exact page is knows is problematic and also no complete solution currently exist.

Here are a few interesting links

Paul Randal, one of the leading SQL persons says that there is no solution:

That “the SQL Team knows about this. It’s a known issue. Hopefully something will be done about it in one of the future releases.”

But unlike SGAM or GAM contention, there’s absolutely nothing you can do about this to spread the contention around.

http://www.sqlskills.com/insidercontent/201106/20110612newsletter.pdf  

Microsoft are aware of it:

As you mentioned, this is a known issue. We did do some updates in this area that may address some (but unfortunately not all) of these scenarios. These should be in the latest release of SQL Server 2008 R2 and SQL Server 2012. If possible, can you please try out the latest updates and see if they help? Again, this won't fix every scenario. I am going to close this Connect item as fixed but if you are still seeing issues with specific scenarios, can you please open new Connect issues to track those?

https://connect.microsoft.com/SQLServer/feedback/details/777072/

Though there are ways to try and minimize this problem:

We know that we have trouble when there are queries that:

• are executed frequently
• create temp tables (either explicitly or by declaring table valued parameters).
• are not cached (Microsoft explains when temp tables are not cached. In my case, it was because they were ad-hoc queries)
• require a page latch on 2:1:103.

http://michaeljswart.com/2013/01/pagelatch-contention-on-21103/