Query:
If exists (select * from tempdb.sys.all_objects where name like ‘#DB_Frag_Report_Temp%’ )
drop table #DB_Frag_Report_Temp
create table #DB_Frag_Report_Temp
(DatabaseName varchar(100),ObjectName varchar(100),Index_id int, indexName varchar(100),avg_fragmentation_percent float,IndexType varchar(100),Action_Required varchar(100) default ‘NA’)
go
insert into #DB_Frag_Report_Temp (DatabaseName,ObjectName,Index_id, indexName,avg_fragmentation_percent,IndexType)

exec master.sys.sp_MSforeachdb ‘ USE [?]

SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as ObjectName,

a.index_id, b.name as IndexName,

avg_fragmentation_in_percent, index_type_desc

— , record_count, avg_page_space_used_in_percent –(null in limited)

FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id AND a.index_id = b.index_id

WHERE b.index_id <> 0 and avg_fragmentation_in_percent > 15′
go

update #DB_Frag_Report_Temp
set Action_Required =’Rebuild’
where avg_fragmentation_percent >30
go

update #DB_Frag_Report_Temp
set Action_Required =’Rorganize’
where avg_fragmentation_percent <30 and avg_fragmentation_percent >5
go

select * from #DB_Frag_Report_Temp

——————————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 as ‘DB Servername’,
d.Dbname as ‘Database Name’,d.dbstatus as ‘Database Status’,d.Recovery_Model as ‘Database Recovery Model’,
(file_size_mb + log_file_size_mb) as ‘Database Size’,
d.file_Size_MB as ‘Data File Size in MB’,d.Space_Used_MB as ‘Datafile Space Used in MB’,d.Free_Space_MB as ‘Datafile Freespace in MB’,
l.Log_File_Size_MB ‘Log File size in MB’,log_Space_Used_MB as ‘Logfile Space Used in MB’,l.log_Free_Space_MB as ‘Logfile Free Space in MB’,fs.Freespace as ‘Database Freespace in MB’,
case when  sda.[mirroring_state] = NULL then ‘No’
when sda.[mirroring_state] between 0 and 6 then ‘Yes’
else ‘No’ end as ‘Is Mirrored’
from #dbsize d join #logsize l
on d.Dbname=l.Dbname join #dbfreesize fs
on d.Dbname=fs.name join sys.databases sd
on d.Dbname = sd.name join sys.database_mirroring sda
on sd.database_id = sda.database_id

order by d.Dbname

——————–Enabling xp_cmdshell ————————

sp_configure ‘show advanced options’, 1
go
reconfigure with override
go
sp_configure ‘xp_cmdshell’, 1
go

——————–
reconfigure with override
if exists (select 1 from sysobjects where name like ‘files_total_space’)

begin

drop table files_total_space

end

go

if exists (select 1 from sysobjects where name like ‘drives_db_details’)

begin

drop table drives_db_details

end

go

DECLARE     @Drive TINYINT,

@SQL nVARCHAR(100)

SET   @Drive = 97

— Setup Staging Area

create  TABLE drives_db_details

(

Drive CHAR(1),

Info NVARCHAR(80)

)

Create table files_total_space

(

DBName nvarchar(255),

Logical_FileName nvarchar(255),

DB_Size float,

DB_File_Path nvarchar(500),

DB_File_Drive nvarchar(4),

Drive_Total_Space nvarchar(100),

Available_Free_Space nvarchar(100)

)

insert into files_total_space(DBName,Logical_FileName,DB_size, DB_File_Path )

select DB_NAME(dbid), name,(size * 8), [filename]  from master..sysaltfiles

where filename not like ‘%mssqlsystemresource%’

WHILE @Drive <= 122

BEGIN

SET   @SQL = ‘EXEC XP_CMDSHELL ”fsutil volume diskfree ‘ + CHAR(@Drive) + ‘:”’

INSERT      drives_db_details

(

Info

)

EXEC  (@SQL)

UPDATE      drives_db_details

SET   Drive = CHAR(@Drive)

WHERE Drive IS NULL

SET   @Drive = @Drive + 1

END

declare @drive_name nvarchar(3)

declare @total_space nvarchar(100)

declare @available_space nvarchar(100)

declare @exec_stmt nvarchar(1000)

declare drive_details_cur cursor for

SELECT            Drive,

str(SUM(CASE WHEN Info LIKE ‘Total # of bytes             : %’ THEN (((CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), ”) AS float)/1024)/1024)/1024) ELSE CAST(0 AS BIGINT) END), 5, 1) AS [Total Space in GB],

str(SUM(CASE WHEN Info LIKE ‘Total # of free bytes        : %’ THEN (((CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), ”) AS float)/1024)/1024)/1024)  ELSE CAST(0 AS BIGINT) END),5, 1) AS [Available Free Space in GB]

FROM        (

SELECT      Drive,

Info

FROM  drives_db_details

WHERE Info LIKE ‘Total # of %’

) AS d

GROUP BY    Drive

ORDER BY    Drive

open drive_details_cur

fetch drive_details_cur into @drive_name, @total_space, @available_space

while (@@fetch_status = 0  )

begin

update  files_total_space set DB_File_Drive = upper(@drive_name), Drive_Total_Space = @total_space, Available_Free_Space = @available_space

where SUBSTRING(DB_File_Path, 1,1) = @drive_name

fetch drive_details_cur into @drive_name, @total_space, @available_space

end

close drive_details_cur

deallocate drive_details_cur

–Final Output

select @@SERVERNAME as ‘DB Servername’, DBName as [Database Name], Logical_FileName as [Logical File Name],

cast(str((DB_Size/1024), 10, 2) as nvarchar(20)) + ‘ MB’ as [Database Size], DB_File_Path as [Physical File Path],

DB_File_Drive as [File Drive], Drive_Total_Space as [Total Space on Drive in GB],

Available_Free_Space as [Space Available in GB] from files_total_space

drop table files_total_space

drop table drives_db_details

go
——————Disabling xp_cmdshell—————–

sp_configure ‘xp_cmdshell’, 0
go
reconfigure with override
go
sp_configure ‘show advanced options’, 0
go
reconfigure with override
go

GAM and SGAM (Global Allocation Map & Shared GAM):

GAM: Extents have been allocated: 1 – Free space 0 – No space

SGAM: Mixed Extents have been allocated: 1 – Free Space + Mixed Extent and 0 – No space

Each GAM / SGAM covers 64000 extents – 4 GB

PFS (Page Free Space): Percentage of free space available in each page in an extent.

DCM (Differential Changed Map):  This tracks the extents that have changed since the last BACKUP DATABASE statement. 1 – Modified, 0 – Not modified

BCM (Bulk Changed Map): This tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement. 1 – Modified, 0 – Not modified (Used only in bulk logged Recovery model)

In each data file pages are arranged like below

Along with that we have three different data pages

Data

Index

Text/ Image (LOB, ROW_OVERFLOE, XML)

Its very hard for many experienced professionals to answer this question. So below is simplified one liner’s for each of these normal forms.
  • First normal form is all about breaking data in to smaller logical pieces.
  • In Second normal form all column data should depend fully on the key and not partially.
  • In Third normal form no column should depend on other columns.

s6.jpg

1. Memory Support: The earlier versions of Sql Server (i.e. 2008 R2 and 2012 only support 64 GB. On 2014 the memory support is increased to 128 GB of memory.

2. Indexes: In 2012 columnstore indexes was introduced which was a great help to datawarehouse workloads. But they were limited to non clustered indexes, supported very few number of datatypes. But in 2014 this feature was expanded to support clustered columnstore indexes and many of the datatypes restrictions was removed.

3. Buffer Pool Extension: Use of fast SSD drives that can really prove as a perfect substitutes for memory. This proves to be very grateful when the server does not support more addition to RAM memory and database size exceeding the amount of RAM.

4. The New Cardinality Estimator: The introduction to this estimator which is great at fixing a lot of queries and providing better plans without re-writing the queries themselves.

5. Always on Availability Groups: SQL Server 2014’s AlwaysOn Availability Groups has been enhanced with support for additional secondary replicas and Windows Azure integration.

SQL Server 2014 New Features:

Posted: December 15, 2015 in General

SQL Server 2014 New Features:

  1. In Memory OLTP Engine, 2014 OLTP Engine Supports upto
  • 640 Logical Processors & 4 TB RAM
  • 64 Virtual Processors & 1 TB
  1. Always ON Groups
  • Additional Secondary Replicas
  1. Backup Encryption: AES 192, AES 256 KEYS, Triple DES
  2. Updateable Columnstore Indexes
  3. Power BI for O365 Integration

Run this query against your SQL Server Instance  to find the Fully Qualified Domain Name of that  SQL Server Instance.

Query:

DECLARE @Domain NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT
SELECT Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @Domain AS FQDN
Create a Cursor with providing user_name(username)as the input parameter)

DECLARE @name_holder VARCHAR(100)
DECLARE UserCursor CURSOR
FOR
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','%snap%') 
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @name_holder
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC sp_addrolemember 'db_datareader', 'user_name'
FETCH NEXT FROM UserCursor INTO @name_holder
END 
CLOSE UserCursor
DEALLOCATE UserCursor

There are hard times, when we need to script all the SQL Server Jobs which are running in one server and when we need to move it to some other server. Its been a very challenging task for a DBA that to deploy same set of jobs in multiple servers. Here, is a way to script all the SQL Jobs from SQL Server Management studio and port it to some other servers in one shot.

So, How to do this?

Step 1: Login to the SQL Server instance through SQL Server Management Studio.
Step 2: Expand SQL Server Agent >> Jobs
Step 3: Hit F7 (or) Click on menu ->View >> Summary. This will list out all the options under SQL Server Agent. Click on Jobs on the Summary Page view.
Step 4: Now ALL jobs would be listed out there.
Step 5: Press Control-‘A’ key and choose all the Jobs which needs to be scripted (OR) Hold Control Key to select specific/Set of Jobs which you want to script out.
Step 6: Right click >> “Script Job as” >> “Create To” >> File (provide the file name and save it on the desired script)

Please refer to the screenshots for reference:

Image 1:

All_Jobs_scripts_1

 

Image 2:All_Jobs_scripts_2