Calculate disk space occupied by each schema in SQL Server

SELECT SCHEMA_NAME(so.schema_id) AS SchemaName
,SUM(ps.reserved_page_count) * 8.0 / 1024 AS SizeInMBFROM sys.dm_db_partition_stats psJOIN sys.indexes i ON i.object_id = ps.object_id
AND i.index_id = ps.index_idJOIN sys.objects so ON i.object_id = so.object_id
WHERE so.type = 'U'
GROUP BY so.schema_id
ORDER BY OBJECT_SCHEMA_NAME(so.schema_id)
,SizeInMB DESC

How to Change the SQL Server Agent Log File Path

SQL Server Agent Error Log: Log File Viewer displays log information from many different components. When Log File Viewer is open, use the Select logs pane to select the logs you want to display. Each log displays columns appropriate to that kind of log. The logs that are available depend on how Log File Viewer is opened.
Solution


To modify location and name of SQLServer Agent log file. We use following

SP_GET_SQLAGENT_PROPERTIES
SP_SET_SQLAGENT_PROPERTIES

The first stored procedure is used to retrieve the SQL Server Agent properties and the second stored procedure is used to set/change the properties for the SQL Agent service.

I strongly suggest testing any undocumented stored procedures in a lab environment first, before changing your production servers.

Steps to move the SQL Agent log file
Step 1

First check the existing location of the SQL Server Agent log file. Run the below undocumented stored procedure to get the current location. This stored procedure will let us know the SQL Agent properties of a particular server. This stored procedure can be found in the msdb database.

USE MASTER
GO
EXEC msdb..sp_get_sqlagent_properties
GO
We can see below the different settings that are returned when we run this command.  We are interested in the errorlog_file column for the change we need to make.  Here we can see the current location is the C drive.

Find the current location of sql server agent log file
Step 2

Now we will change the location of the file from the C drive to the G drive.

First we need to create the new destination folders where we want to put the SQLAGENT.OUT file. So, I created the folder "Microsoft SQL Server\MSSQL.1\MSSQL\LOG" on the G drive. If you do not create these folders you will have issues when the SQL Server Agent service restarts.

After the new folder has been created, run the below stored procedure to change the location.

USE MASTER
GO
EXEC msdb.dbo.sp_set_sqlagent_properties
@errorlog_file=N'G:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT'
GO

Change the agent logfile location to different path
Step 3

Now we will verify whether the SQL Server Agent log file path has changed or not. We will be run the same command as in step 1 to get the SQL Server agent properties. We can see in the below screenshot that the path for the SQL Server Agent log file has been changed to the G drive.

Verify the change of agent log file location
Note, this change will not go into effect until you restart your the SQL Agent service. You can verify this by checking the new location to see if the file SQLAGENT.OUT exists or not.

Step 4

Now restart your SQL Server Agent service to bring the changes into effect. If you have any issues or the SQL Server Agent service does not start (you might get an error like below) then you should check the path you have set in step 2. The path has to be valid to successfully start this service.




Reference

T SQL query to find when was the database backed up lastly on SQL Server

Select database_name As [Database Name]
, [name] As [Backup Name]
, Case When Type = 'D' Then 'Full Backup'
When Type = 'I' Then 'Differential Backup'
When Type = 'L' Then 'Log Backup'
Else 'File or filegroup or partial'
End As [Backup Type]
, Recovery_Model
, [backup_start_date] As [Time of the SQL Backup Job]
FROM [msdb].[dbo].[backupset]

Find what script is being run by specific SPID number

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 53
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

SQL Script to find all Date/time columns in database

select
    so.name table_name
   ,sc.name column_name
   ,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME', 'DATE', 'TIME')

Query to List all the partitioned tables in SQL Server database

select object_schema_name(i.object_id) as [schema],
    object_name(i.object_id) as [object],
    i.name as [index],
    s.name as [partition_scheme]
    from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id order by [schema]

T-SQL code to get the active Connections for each Database.

--Method 1

 SELECT 

    DB_NAME(dbid) as DBName, 

    COUNT(dbid) as NoOfConnections,

    loginame as LoginName

FROM

    sys.sysprocesses

WHERE 

    dbid > 0

GROUP BY 

    dbid, loginame

--Method 2

EXEC sp_who2

--Method 3

SELECT  spid,

        sp.[status],

        loginame [Login],

        hostname, 

        blocked BlkBy,

        sd.name DBName, 

        cmd Command,

        cpu CPUTime,

        physical_io DiskIO,

        last_batch LastBatch,

        [program_name] ProgramName   

FROM master.dbo.sysprocesses sp 

JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid

ORDER BY DBName,spid

Simplest way to Truncate/Delete all tables in a given database.

Simplest way to Truncate all tables in a given database.

Use databasename

EXEC sp_MSForEachTable 'Truncate TABLE ?' --- For Truncating all tables

Simplest way to Truncate all tables in a given database.

Use databasename

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"  --- For Deleting all tables

How to Stop SQL Server Agent Job while it is actively running?

How to Stop SQL Server Agent Job while it is actively running?

Try running sp_who2 and find the SPID and kill the process using KILL SPID with status only so that it will show the rollbackup completed as well.

Difference between KILL and KILL WITH STATUSONLY – Estimate Rollback completion time

While I was talking to a customer during a replication scenario, customer asked me a question about understanding the estimated completion time for a rollback.

I was explaining that we have KILL WITH STATUSONLY but it will just report the progress only if the SPID was killed already and it will not kill the SPID also KILL WITH STATUSONLY will not help to track the progress of ROLLBACK issued by ROLLBACK TRAN. This was something new to him that KILL WITH STATUSONLY will not kill the SPID instead it only reports the progress. 

I thought of sharing this in a blog so that it will be useful for others who overlooked the KILL command.

Here is what BOL says:

KILL WITH STATUSONLY does not terminate or roll back the session ID or UOW; the command only displays the current progress of the rollback.

Lab:

Step 1: I ran a long running batch from SPID 52 

Step 2: Issued a KILL using the command KILL 52

Step 3: Here is what I observed from SP_WHO2 52:

SPID	Status	Login	HostName	BlkBy	DBName	Command
52	SUSPENDED	abc	def	.	microsoft	KILLED/ROLLBACK

Step 4: Here is what I got when I ran KILL 52 WITH STATUSONLY:

spid 52: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.

Step 5: I tried to run the same statement from a different SPID 53 inside an explicit transaction and issued a ROLLBACK TRAN and this is the status when I ranSP_WHO2 53:

SPID	Status	Login	HostName	BlkBy	DBName	Command
53	ROLLBACK	abc	def	.	microsoft	ROLLBACK TRANSAC

Step 6: Here is the output of KILL 53 WITH STATUSONLY :

Msg 6120, Level 16, State 1, Line 1

Status report cannot be obtained. Rollback operation for Process ID 53 is not in progress.

So KILL WITH STATUS ONLY cannot be used to track the progress of  ROLLBACK issued by ROLLBACK TRAN. Hope this helps!

Reference: Sakthivel Chidambaram

Get SQL Server Physical Cores, Physical and Virtual CPUs, and Processor type information using Transact-SQL (T-SQL) scrip

• Total number of physical CPUs
• Total number of physical cores per CPUs
• Total number of physical cores
• Total number of virtual CPUs
• Processor type (x86 or x64)

As a result of this question, I wrote the following script using sys.dm_os_sys_info and xp_msver, which returns the required information:

exec xp_msver 'ProcessorCount'

Query Starts Here

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

DECLARE @xp_msver TABLE (     [idx] [int] NULL     ,[c_name] [varchar](100) NULL     ,[int_val] [float] NULL     ,[c_val] [varchar](128) NULL     ) INSERT INTO @xp_msver EXEC ('[master]..[xp_msver]');; WITH [ProcessorInfo] AS (     SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]         ,CASE             WHEN hyperthread_ratio = cpu_count                 THEN cpu_count             ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))             END AS [number_of_cores_per_cpu]         ,CASE             WHEN hyperthread_ratio = cpu_count                 THEN cpu_count             ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))             END AS [total_number_of_cores]         ,[cpu_count] AS [number_of_virtual_cpus]         ,(             SELECT [c_val]             FROM @xp_msver             WHERE [c_name] = 'Platform'             ) AS [cpu_category]     FROM [sys].[dm_os_sys_info]     ) SELECT [number_of_physical_cpus]     ,[number_of_cores_per_cpu]     ,[total_number_of_cores]     ,[number_of_virtual_cpus]     ,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category] FROM [ProcessorInfo]

The code of this script is tested on SQL Server 2005 and above versions.

Reference: https://basitaalishan.com/2014/01/22/get-sql-server-physical-cores-physical-and-virtual-cpus-and-processor-type-information-using-t-sql-script/