SQL Query to Determine Current Space Utilization

SQL Query to Determine Current Space Utilization

Here is the script to determine space used by each database. This includes path of data and log file, database names, used space and free space.  This will also provide total database count in the server and total space available in each and every drive. I found this script very useful as a quick look of the space utilization.

The following query I use every day to monitor the space on my servers. If I found any issues, I can dig more and get the root cause. Following could be reason of log file growth though:

1.    Incorrect recovery models.

      2.    TempDB full
      3.    Not a good capacity planning
      4.    Too many indexes

Set NoCount On
--Check to see the temp table exists

IF EXISTS ( SELECT Name FROM tempdb..sysobjects Where name like '%HoldforEachDB%' )

--If So Drop it

DROP TABLE #HoldforEachDB_size

--Recreate it

CREATE TABLE #HoldforEachDB_size

(
[DatabaseName] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Size] [decimal] NOT NULL,
[Name] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Filename] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
)
ON [PRIMARY]

IF EXISTS ( SELECT name FROM tempdb..sysobjects Where name like '%fixed_drives%' )

--If So Drop it

DROP TABLE #fixed_drives

--Recreate it

CREATE TABLE #fixed_drives

(
[Drive] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MBFree] [decimal] NOT NULL
)
ON [PRIMARY]

--Insert rows from sp_MSForEachDB into temp table

INSERT INTO #HoldforEachDB_size

EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName, Case
When [?]..sysfiles.size * 8 / 1024 = 0 Then 1 Else
[?]..sysfiles.size * 8 / 1024 End
AS size,[?]..sysfiles.name,
[?]..sysfiles.filename From [?]..sysfiles'

--Select all rows from temp table (the temp table will auto delete when the connection is gone.

INSERT INTO #fixed_drives

EXEC xp_fixeddrives
Select @@Servername
print '' ;

Select

rtrim(Cast(DatabaseName as varchar(75))) as DatabaseName,

Drive,
Filename,
Cast(Size as int) AS Size,
Cast(MBFree as varchar(10)) as MB_Free
from #HoldforEachDB_size
INNER JOIN #fixed_drives ON
LEFT(#HoldforEachDB_size.Filename, 1) = #fixed_drives.Drive
GROUP BY DatabaseName,Drive,MBFree,Filename,Cast(Size as int)
ORDER BY Drive,Size Desc

print '' ;

Select

Drive as [Total Data Space Used |],

Cast(Sum(Size) as varchar(10)) as [Total Size],
Cast(MBFree as varchar(10)) as MB_Free
from #HoldforEachDB_size
INNER JOIN #fixed_drives ON
LEFT(#HoldforEachDB_size.Filename, 1) = #fixed_drives.Drive
Group by Drive,MBFree

print '' ;

Select

count(Distinct rtrim(Cast(DatabaseName as varchar(75)))) as Database_Count

from #HoldforEachDB_size

FInd how much we have allocated in each FIlegroup

Here is the script that will provide the available/consumed space in each file group. By using this script we can get the total size of each file group and how much we have allocated in each file group. Test this script in DWV/QA environment and run in production (If required).

-- Find the total size of each Filegroup

select data_space_id, (sum(size)*8)/1000 as total_size_MB

into #filegroups
from sys.database_files
group by data_space_id
order by data_space_id

-- FInd how much we have allocated in each FIlegroup

select ds.name, au.data_space_id

, (sum(au.total_pages) * 8)/1000 as Allocated_MB
, (sum(au.used_pages) * 8)/1000 as used_MB
, (sum(au.data_pages) * 8)/1000 as Data_MB
, ((sum(au.total_pages) - sum(au.used_pages) ) * 8 )/1000 as Free_MB
into #Allocations
from sys.allocation_units as au inner join sys.data_spaces as ds
on au.data_space_id = ds.data_space_id
group by ds.name, au.data_space_id
order by au.data_space_id

-- Get the Required output

select f.data_space_id

, a.name
, f.total_size_MB
, a.allocated_MB
, f.total_size_MB - a.allocated_MB as free_in_fg_MB
, a.used_MB
, a.data_MB
, a.Free_MB
from #filegroups as f inner join #allocations as a
on f.data_space_id = a.data_space_id
order by f.data_space_id

drop table #allocations

drop table #filegroups

Recovering a SQL Server Database From SUSPECT Mode

How to Repair Database which is in SUSPECT mode in SQL Server 2005/2008

Morning you come to your office and you see your one of the critical database in SUSPECT mode and you are not able to do any action against that database. You need to repair the database at the earliest.

Why Database Is In SUSPECT Mode

There could be so many reasons for this but here are the main reasons that I can think of.

1.    Data files or log files are corrupt.

      2.    Database server was shut down improperly.
      3.    SQL cannot complete a rollback or rollforward operation.

If you want to find the exact reason why your database is in SUSPECT mode, you can use below query. This should give you the exact error message.

DBCC CHECKDB ('<Database Name>') WITH NO_INFOMSGS, ALL_ERRORMSGS

How to Repair the database and bring it online

Follow below steps to bring the database ONLINE and accessible.

1.    Change the status of your database by running following query

          EXEC sp_resetstatus '<Database Name>';

2.    Set the database in EMERGENCY mode by running following query

ALTER DATABASE <Database Name> SET EMERGENCY;

3.    Check the database for any inconsistency by executing below query

DBCC CHECKDB('<Database Name>');

4.    If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you are done.

          ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

5.    Take the backup of the database just to be in safe side.

6.    Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('<Database Name>', REPAIR_ALLOW_DATA_LOSS);

7.    Finally bring the database in MULTI USER mode by running following query.

ALTER DATABASE <Database Name> SET MULTI_USER;

8.    At last step you verify the connectivity from your database. Now users should be able to connect to the database properly. If any data loss, you may need to get from the backup taken in step 5.

Now your database is ONLINE and accessible. Now Rock and have the coffee to RELAX.

Find Currently Running Queries

How to find out currently running queries in SQL Server 2005/2008


We can find out currently running queries by using dynamic management views in SQL Server 2005 and 2008. Here is the script by using we can find out currently running queries. We can order our result by CPU cost. We will get complete query text in the output.

select r.session_id

      ,status
      ,substring(qt.text,r.statement_start_offset/2,
      (case when r.statement_end_offset = -1
      then len(convert(nvarchar(max), qt.text)) * 2
      else r.statement_end_offset end - r.statement_start_offset)/2)
      as query_text
      ,qt.dbid
      ,qt.objectid
      ,r.cpu_time
      ,r.total_elapsed_time
      ,r.reads
      ,r.writes
      ,r.logical_reads
      ,r.scheduler_id
from sys.dm_exec_requests r
      cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.cpu_time desc

Query to find Blocking

We can find out the blocking by running following query. If this query results something then definitely there is blocking in the server.

select

spid,
blocked,
waittype ,
waittime,
lastwaittype,
dbid ,
uid,
cpu,
physical_io,
memusage ,
login_time,
last_batch, hostname ,
program_name,
nt_domain,
nt_username,
loginame
from master..sysprocesses
where blocked <> 0
or spid in (select blocked from master..sysprocesses)