MS SQL Server - How to determine the active node on a cluster server

Select ServerProperty('ComputerNamePhysicalNetBIOS')

SQL Server - TSQL check the progress of restore and backup

The script below returns the progress of all backup and resotre sessions running.

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

MS SQL Server - How to determine the active node on a cluster server

How to determine the active node on a cluster server

Select ServerProperty('ComputerNamePhysicalNetBIOS')

MS SQL Server - Configure and grant user access to xp_cmdshell

Configure  and grant user access to xp_cmdshell

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

grant execute on xp_cmdshell to username

EXEC sp_xp_cmdshell_proxy_account 'username', 'password'

MS SQL Server - Shrinking transaction logs

Shrinking transaction logs

Run the script below to shrink the database's transaction log. Please note that logs should not really need to be shrank. If logs are not needed, on the database options set the database recovery model to simple this process (see below).

MS SQL Server 2005 

use database_name

backup log progress with truncate_only
dbcc shrinkfile ('transaction_logical_filename')


MS SQL Server 2008 and above

Microsoft SQL Server 2008 does not support the backup with truncate_only directive. So to truncate a log in 2008, run the following script.

BACKUP LOG database_name TO DISK='NULL'

Please endure that the database recovery model is set to full (see below) and a database full back up has been run.

Database option screen - opened by right clicking on the database, and selecting database options.

 

MS SQL Server - Chaning table's schema

Changing table schema 

ALTER SCHEMA schema_name TRANSFER schema_name.table_name;

MS SQL Server - How to reindex all tables within a database

MS SQL Server -  How to reindex all tables within a database

sp_msforeachtable 'dbcc dbreindex(''?'')'