vijayjain347

A topnotch WordPress.com site

SQL SERVER 2008 : ” This query provide all necessary information of database”

Leave a comment

/*
SQL Server2000- Daily checklist for database

This query provide all necessary information of database.
*/

create table #logsize
(Dbname varchar(200),dbstatus varchar(50),Recovery_Model varchar(40) default (‘NA’), Log_File_Size_MB decimal(20,2)default (0),
log_Space_Used_MB decimal(20,2)default (0),log_Free_Space_MB decimal(20,2)default (0))
go

insert into #logsize(Dbname,dbstatus,recovery_model,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
‘use [?];
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(”?”,”Status”)) ,
CONVERT(varchar(20),DatabasePropertyEx(”?”,”Recovery”)),
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ”SpaceUsed”) AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 – sum(CAST(FILEPROPERTY(name,”SpaceUsed”) AS INT))/128.0 AS log_Free_Space_MB
from sysfiles where groupid=0 group by groupid

go

create table #dbsize
(Dbname varchar(200), file_Size_MB decimal(20,2)default (0),Space_Used_MB decimal(20,2)default (0),
Free_Space_MB decimal(20,2) default (0))
go

insert into #dbsize(Dbname,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
‘use [?];
select DB_NAME() AS DbName,

sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ”SpaceUsed”) AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 – sum(CAST(FILEPROPERTY(name,”SpaceUsed”) AS INT))/128.0 AS Free_Space_MB
from sysfiles where groupid<>0 group by groupid ‘

go

select
d.Dbname,l.dbstatus,l.recovery_model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB
from #dbsize d join #logsize l
on d.Dbname=l.Dbname

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s