How to Find Free Log Space in SQL Server?

This post will show you how to monitor free log space in SQL Server.

Method 1: Using SQLPERF

DBCC SQLPERF ('LOGSPACE')
GO

Method 2: sys.dm_db_log_space_usage
  
SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage;

How to Validate Email Address in SQL Server?

How to Validate Email Address in SQL Server-Let us create a column called Email Address in the table named Contacts. Once we create the table, we will insert few valid and invalid email address in it.

Different Methods to Know COMPATIBILITY LEVEL of a Database

There are many methods to know the compatibility levels of a database.

1) Use system stored procedure sp_helpdb
   
EXEC sp_helpdb TEST

How to Change Compatibility of Database

There are two different ways how we can change the compatibility of database to SQL Server.

1) Using Management Studio

For this method first to go database and right click over it. Now select properties.

2) Using T-SQL Script.

You can execute following script and change the compatibility settings to 120.

USE [master] GO
ALTER DATABASE [TEST] SET COMPATIBILITY_LEVEL = 120
GO

Ms SQL Useful Stored Procedures

DBCC CHECKDB ('DB_Name')
DBCC CHECKALLOC ('DB_Name')
DBCC updateusage ('DB_Name')
DBCC CHECKIDENT ('dbo.tablename',reseed,0) 
- '0' by default,Seed Value Last Incremented value can be added

EXEC SP_help

EXEC SP_helptext

EXEC sp_renamedb 'oldname' , 'newname';

xp_fixeddrives  - To get HD available in the Server / Disk Space

To Get List of Jobs running in SQL Agent Job

How to find the SQL Agent Jobs  running in your DB Instance and its Steps, query used for the results

 SELECT J.NAME AS 'JOB NAME',

S.STEP_ID AS 'STEP',

S.STEP_NAME AS 'STEP NAME',

S.COMMAND AS 'QUERY',

DATABASE_NAME AS 'DATABASE'

FROM MSDB.DBO.SYSJOBS J

INNER JOIN MSDB.DBO.SYSJOBSTEPS S ON S.JOB_ID = J.JOB_ID

WHERE J.ENABLED = 1

ORDER BY J.NAME,S.STEP_ID

 

How to Enable Automatic Statistic Update on Database

After putting good indexes and optimizing the query, I could not achieve an optimized performance and I still received an unoptimized response from the SQL Server. On examination, I figured out that the culprit was statistics. The database that I was trying to optimize had auto update of the statistics was disabled.

Once I enabled the auto update of statistics, the database started to respond as expected. If you ever face situation like this, please do the following:

1) Enable Auto Creation of Statistics

2) Enable Auto Update of Statistics

3) Update Statistics for whole database