What’s up SQL? – Number 7 – Log size

One of the most common problems DBA’s have is the Transaction Log size. For some reason your transaction log grows, and ends up using all of your disk free space.

Of course you can limit the growth of the transaction log file, but, then you’ll end up with the users complaining about lack of free space in T-Log.

There are, no doubt about it, many ways to check the file size, and the amount of used space, for me, the easiest one is using:

DBCC sqlperf(logspace)

It will return a list with the Database name, Log Size, Log Space used and Status.

What’s up SQL? – Number 6 – Last backup

One of the great things about SQL Server is the amount of information it stores about itself, so, let’s say you need to find out when your databases where last backed up. Luckily, SQL Server keeps that information in th msdb database.

And here’s a little query, to help you get that information.

SELECT database_name, user_name as ExecutedBy, –physical_device_name,
backup_start_date , backup_finish_date , backup_size
FROM msdb.. backupset bckset
INNER JOIN msdb ..backupmediafamily bckfmly
ON bckset. media_set_id = bckfmly . media_set_id order by backup_start_date DESC

What’s up SQL? – Number 5 – Query Percentage

We’ve seen how to find out what users are doing in our SQL Server boxes, but, how about knowing where a query i in it’s execution?

Well, I found a query that help’s with that.

First you need to know the session id you want to monitor. Then open a query windows and type:

select percent_complete,
       dateadd (second ,( datediff( second ,start_time , getdate())* 100 )/percent_complete , start_time)
       as EstimatedEndTime, command
from sys . dm_exec_requests
where session_id = ID_OF_SESSION

This will show you the percentage of completion, an estimate for the completion and the command being executed.



What’s up SQL? – Number 4 – SQL Queries by Session

Sometimes it’s good to know what the users are up to.

In a SQL Server, you can get a picture of what is happening with a bit of T-SQL coding.

Here’s what i use to know what the users queries are.

First I start by finding

      SELECT sqltext.TEXT,
      INTO TMPTable
      FROM sys.dm_exec_requests req
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
      WHERE 1 = 2


      INSERT TMPTable
      SELECT sqltext.TEXT,
      FROM sys.dm_exec_requests req
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
After you just have to execute a select query to see the results.
SELECT * from TMPTable ORDER BY cpu_time DESC

What’s up SQL? – Number 3 – Top 10 Heaviest Instructions

Hello readers,

Here’s a T-SQL script to identify the 10 heaviest instructions executed in a SQL Server.

I must say, this was a script found by a college of mine and passed along, so I can’t give the credit to it’s original programmer, but whoever you are. Thank you very much.


SELECT TOP 10 — You can change number of lines
       SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset
        WHEN 1 THEN DATALENGTH(st.text)
       ELSE qs.statement_end_offset END
        qs.statement_start_offset)/2) + 1) as statement_text,
       total_worker_time/execution_count AS [Avg CPU Time],
        when execution_count = 0 then null
        else total_logical_reads/execution_count
       end as avg_logical_reads,
       when execution_count = 0 then null
       else total_logical_writes/execution_count
       end as avg_logical_writes,
      FROM sys.dm_exec_query_stats as qs
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
      WHERE qs.last_execution_time >= dateadd(day, 1, getdate()) — You can change the datepart and number
      –ORDER BY max_logical_reads DESC — You can change to max_logical_writes
      –ORDER BY max_logical_writes DESC
      order by [Avg CPU Time] DESC
You can change the number of instructions and the order of presentation.
So just use it. It’s very handy.

How to Back Up and Restore SQL Server Logins

Backing Up and Restoring Logins

You can back up and restore logins using a script as described in the following procedures.

To back up a login using a script (SQL Server 2005 or SQL Server 2008)
  1. Start SQL Server Management Studio.
  2. Expand the Security folder, and then expand the list of Logins.
  3. Right-click the login you want to create a backup script for, and then select Script Login as.
  4. Click CREATE To, and then click one of New Query Editor WindowFile, or Clipboard to select a destination for the script. Typically, the destination is a file with a .sql extension.
  5. Repeat this procedure from Step 3 for each login you want to script.
To restore a login from a script (SQL Server 2005 or SQL Server 2008)
  1. Start SQL Server Management Studio.
  2. Click File, click Open, and then click File.
  3. Locate and open the file containing the scripted login.
  4. Execute the script to create the login.

What’s up SQL – Number 2 – SQL Server Version

Hello reader.


Did you ever had to look up for the SQL Server version you are using?

Me too. Here’s how I do it.


Open a new query in SQL Server Management Studio and type:

The result will be something like this:
“Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) “
If you need more information you can also use the Stored Procedure sp_server_info, to get information like system collation.

What’s up SQL? – Number 1 – Anyone there?

Greetings fellow reader.

I’ve decided to start a series of posts about SQL Server, to tell, you about my experiences with SQL Server.

So, just to get started, let’s find out who is using my SQL Server, and what are they doing?

There are two ways to go about this. (Actually thee are more, but let’s start with these two!)


Open a query window in SQL Server Management Studio and type:

sp_who or sp_who2 the just press F5 or hit the Execute button.

The result will be a list of sessions with information about them, and you ca copy and manipulate in Excel.


Opening Activity Monitor

And checking the list of processes.

There we have a list of logins in use and there Session ID’s.

And there it is. Now we know who’s using our SQL Server box.

NOTE: The first 50 sessions are usually system sessions, but after SQL Server 2005, system sessions may pass that limit.