How to check the Log Space of a database in SQL server ?

In SQL Server maintaining the transaction log is a big challenge and as a DBA we always need to monitor the space used by the transaction log file.

Here you can check the log space of the transaction log file using the following command.

DBCC SQLPERF (LOGSPACE)

So the above command will display the size of the log file and percentage of log space used.

How to find the SQL Server Last Restarted date & time ?

One of the easiest way to find when SQL server was restarted is using Dynamic Management view (DMV).

In some of the scenarios we need to know when the SQL server was restarted.

DMV

SELECT * FROM SYS.DM_OS_SYS_INFO

Example

I have taken the Sqlserver_start_time column from the above view.

So now my SQL server instance restarted time is 2017-02-08.

Types of Recovery Models available in SQL Server ?

In SQL server there are 3 different types of recovery models.
Each database mush be selected to any of these 3 recovery models.

Following are the types of recovery models used in SQL server.

Simple
Full
Bulk-Logged

T-SQL to change the recovery model of a database

ALTER DATABASE DATABASE_NAME
SET RECOVERY [Full / Simple / Bulk-Logged]

How to find the last identity value in a table ?

In SQL server you can get the last value of the identity column in table using the Ident_Current function.

Syntax

IDENT_CURRENT('TABLE_NAME')

Example

Select IDENT_CURRENT ( 'StudentDetail')

Output

11

In the studentdetail table the last generated value in the identity column is 11.

Note : The table name should always have the identity column.

How to insert the Value in the Identity column of the table ?

In SQL server each table has identity column to auto increment the value , but there is sometimes we need to insert the value in the identity column.

You can use the following syntax to insert the value manually.

Syntax

To Switch ON 

SET IDENTITY_INSERT TABLE_NAME ON

To Switch OFF

SET IDENTITY_INSERT TABLE_NAME OFF

Example

In the below example i tried to insert the value in the identity column of the identitytable, After executing the below query i got the error message in the messages tab as shown below.

After i switched ON the below property and try to execute the statement still i am getting the error message but the message is different.

If we switched ON the below property we have to use the column list in the insert statement.

After using the columns in the insert statement it works fine and executed successfully.

Do not forget to switch OFF the IDENTITY_INSERT property.

Use of DATALENGTH function in SQL server ?

DATALENGTH function in SQL server is used to return the no of bytes used by the expression.

SYNTAX

DATALENGTH (EXPRESSION)

EXAMPLE

The following example demoanstrates the use of datalength function in SQL server.

In the above example i have passed the EmailAddress column to the DATALENGTH function.

In the Results tab the data_length column is showing the corresponding datalength for the row.

The emailaddress is nvarchar data-type and it takes 2 bytes per character, so it returns the value as double of the length column.

Using the above DATALENGTH function we can easily find the bytes used by the expression.

The bytes occupied per each character will be differ for different data-type.

How to find the data file location of the Database ?

While creating the database the two types of data files such as MDF & LDF will be created in the drive.

If we want to see the data file location of the particular database we can find the location via T-SQL query.

SYNTAX

SP_HELPFILE

EXAMPLE

I just executed the below query to find the location of the adventure works database in my instance.

The above command will return the name of the data files , location and its filegroup etc as shown above.

You can easily identify the location using the above command in your instance.