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 Get the Running query text using SPID ?

While performing the tuning in SQL server sometimes we found that some of the queries are running for a long time and it would block the other transaction.

To get the running query in SQL server we can use the Database Console Command (DBCC) to get the running query.

SYNTAX

DBCC INPUTBUFFER (SPID)

If you know the SPID of the blocking query then the above command would be helpful to get the blocking query.

EXAMPLE

In the query window 1 i ran the below query and  my server process ID is 55 and it is highlighted below.

For demonstration purpose i used the following query.

In the query window 2 i ran the DBCC command by passing the Server Process ID obtained from the previous query window 1 as shown below.

After executing the above command I found the Running query in the EventInfo column as shown above.

From this you can find the running query in SQL server.
Refer here to find another way of getting the running query text.

How to reseed the identity value in SQL server ?

In SQL server we can reseed the identity value of the table using DBCC Command.

Syntax

DBCC CHECKIDENT ('Table Name',[Reseed],[Reseed Value])

Example

In the following example the current value of identity column (UID) is 3.In the next insert i want to reseed the value to be 11.

Now i reseed the value of UID column to 11.After executing this command the following message will appear.

After executing the above command if we try to insert new record, the UID will be generated as 11 rather than 4.

Now we have the UID value of new row is 11.

In the above scenario the numbers between 4 to 10 will be skipped in this case.

If you want the next value to be 100, then you need to reseed the value as 99.It will always takes the value as reseed value plus one. (i.e) 99 + 1 = 100.

If the identity column is created as primary key, if you try to reseed the value of that column then it will create primary key violation error. 

we can reseed only the identity column in the table.