List all database files and spaces

19 de setembro de 2014 Deixe um comentário

——————————Data file size—————————-
if exists (select * from tempdb.sys.all_objects where name like ‘%#dbsize%’)
drop table #dbsize
create table #dbsize
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default (‘NA’), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))
go

insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,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 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 sys.database_files  where type=0 group by type’

go

——————-log size————————————–
if exists (select * from tempdb.sys.all_objects where name like ‘#logsize%’)
drop table #logsize
create table #logsize
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))
go

insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
‘use [?];
select DB_NAME() AS DbName,
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 sys.database_files  where type=1 group by type’

go
——————————–database free size
if exists (select * from tempdb.sys.all_objects where name like ‘%#dbfreesize%’)
drop table #dbfreesize
create table #dbfreesize
(name sysname,
database_size varchar(50),
Freespace varchar(50)default (0.00))

insert into #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
‘use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ”MB”)
,”unallocated space” = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) – convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + ” MB”)
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it
ON p.object_id = it.object_id
) AS partitions’
———————————–

if exists (select * from tempdb.sys.all_objects where name like ‘%#alldbstate%’)
drop table #alldbstate

create table #alldbstate
(dbname sysname,
DBstatus varchar(55),
R_model Varchar(30))

–select * from sys.master_files

insert into #alldbstate (dbname,DBstatus,R_model)
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,’status’)),recovery_model_desc from sys.databases
–select * from #dbsize

insert into #dbsize(Dbname,dbstatus,Recovery_Model)
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> ‘online’

insert into #logsize(Dbname)
select dbname from #alldbstate where DBstatus <> ‘online’

insert into #dbfreesize(name)
select dbname from #alldbstate where DBstatus <> ‘online’

select @@SERVERNAME ServerName,
d.Dbname,d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
convert(decimal(10,0), (d.Free_Space_MB / (file_size_mb + log_file_size_mb)) *100) as Percent_DB_Used,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,
convert(decimal(10,0),  (log_Space_Used_MB / l.Log_File_Size_MB) * 100) as Percent_Log_Used,
fs.Freespace as DB_Freespace
from #dbsize d join #logsize l
on d.Dbname=l.Dbname join #dbfreesize fs
on d.Dbname=fs.name
order by Dbname

CategoriasUncategorized

List all triggers on database

19 de setembro de 2014 Deixe um comentário
--List all triggers on database
SELECT  
       TAB.name as Table_Name 
     , TRIG.name as Trigger_Name
     , TRIG.is_disabled  --or objectproperty(object_id('TriggerName'), 'ExecIsTriggerDisabled')
FROM [sys].[triggers] as TRIG 
inner join sys.tables as TAB 
on TRIG.parent_id = TAB.object_id 

SQL Server: List objects in a schema

4 de junho de 2014 Deixe um comentário

– List all tables in a schema
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
where TABLE_SCHEMA LIKE ‘MY_SCHEMA%’

– List all stored procedures in a schema
SELECT s.name SchemaName, pr.name ProcedureName
FROM sys.procedures pr
INNER JOIN sys.schemas s ON pr.schema_id = s.schema_id
WHERE s.name LIKE ‘MY_SCHEMA%’

– List all views in a schema
SELECT s.name SchemaName, v.name AS view_name
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
WHERE s.name LIKE ‘MY_SCHEMA%’

CategoriasUncategorized

SQL Server: Verify shrink progress

27 de março de 2014 Deixe um comentário

If you are executing a shrink and wants to know the progress, use this select:

SELECT 
	percent_complete, 
	start_time, 
	status, 
	command, 
	estimated_completion_time, 
	cpu_time, 
	total_elapsed_time
FROM 
	sys.dm_exec_requests
WHERE
	command = 'DbccFilesCompact'

 

CategoriasUncategorized

SQL Server: Script to disable/enable SQL jobs

18 de março de 2014 Deixe um comentário

Disable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO
 

Enable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;
GO
 

Disable Jobs By Job Name
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE ‘Admin%';
GO
 

Disable Jobs By Job Category
USE MSDB;
GO
UPDATE J
SET J.Enabled = 0
FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.[Name] = ‘Database Maintenance';
GO

CategoriasUncategorized

PowerShell: Execute especific function in a script file

12 de janeiro de 2014 Deixe um comentário

If you just want to execute the function from your current PowerShell session then do this:

. .\script.ps1
My-Func
CategoriasUncategorized

SQL Server: Datafiles and sizes

10 de dezembro de 2013 Deixe um comentário

Selecting datafiles and their sizes:

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
Seguir

Obtenha todo post novo entregue na sua caixa de entrada.