Kill all sessions connected to database in SQL server 2012 and above

The following script will kill all the sessions connected to database. It works well for 2012 and above , but i have not tried this on 2008 or 2005 version.I think it will work those versions too

***********************************************************************************
**********************************************************************************

declare @dbname1 varchar (60) ='<pass the database name here>'

declare @kilogin varchar(1000)
declare @killkilogin cursor
set @killkilogin=cursor
for  select 'kill '+convert (varchar(50),spid)+';' from sys.sysprocesses
where dbid=DB_id(@dbname1)
open @killkilogin
fetch next from @killkilogin into @kilogin
while @@fetch_status=0
begin
declare @q1 varchar(1000)=@kilogin
 exec (@kilogin)
 print (@kilogin)

fetch next from @killkilogin into @kilogin
end
close @killkilogin
deallocate @killkilogin;
 print 'all connection to '+@dbname1+' is killed '
go

***********************************************************************************
**********************************************************************************

Sometimes you will get the below error

Msg 6104, Level 16, State 1, Line 1

Cannot use KILL to kill your own process.

That means you are running the above query in the database which you are wished to kill all

sessions connected to it. you the DIFFERENT DATABASE and rerun the above script.

***********************************************************************************
**********************************************************************************

 

Blocking full details in sql server 2008 and above

The blocking details in SQL server 2008 and above.

  In a SQL server there might be multiple blocking. Meaning one session might be blocking another session.Then we need to find the root blocking and kill if necessary. The below script will find the root blocking and one of the blocked session by that root blocking.If the server has multiple 
root blocking also , the script will give the output. I strongly recommend not use the second script.

Script 1: this script will find the root blocking and give the complete details about it.

you can also download the script 1 from the below link

https://gallery.technet.microsoft.com/ROOT-BLOCKING-IN-SQL-SERVER-839cfe81

create table ##temptab(blk int)

insert into ##temptab (blk) select blocked from sys.sysprocesses where blocked not in 

(select spid from sys.sysprocesses where blocked <>0) and  blocked<>0

declare @blc int

declare cur cursor for select distinct blk from ##temptab

OPEN cur   

FETCH NEXT FROM cur INTO @blc

WHILE @@FETCH_STATUS = 0   

BEGIN  

select  x.SESsION_ID AS [ROOT BLOCKING],x.host_name[blocking host],

x.login_name [blocking login],y.*,x.text [blocking text] from (SELECT es.*,

st.text FROM sys.dm_exec_sessions es

inner join sys.dm_exec_connections ec on ec.session_id=es.session_id

cross apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) st

where es.session_id=@blc)

 x ,(select spid [blocked],blocked [blocking],status [blocked status],

loginame[blocked login],hostname [blocked host],waittime/60000 [in min],

text [blocked text] from sys.sysprocesses r 

cross apply sys.dm_exec_sql_text(sql_handle) st

where r.blocked=@blc) y

FETCH NEXT FROM cur INTO @blc

END   

CLOSE cur  

DEALLOCATE cur

drop table ##temptab

******************************************************************************************************************************************************************

Script 2: this will create a procedure to find the root blocking and their details .

               let us consider that you created the procedure in master database, then you need to run the 

               script like 'master..block'

create procedure [dbo].[block]

as

create table ##temptab(blk int)

insert into ##temptab (blk) select blocked from sys.sysprocesses where blocked not in 

(select spid from sys.sysprocesses where blocked <>0) and  blocked<>0

declare @blc int

declare cur cursor for select distinct blk from ##temptab

OPEN cur   

FETCH NEXT FROM cur INTO @blc

WHILE @@FETCH_STATUS = 0   

BEGIN  

select  x.SESsION_ID AS [ROOT BLOCKING],x.host_name[blocking host],

x.login_name [blocking login],y.*,x.text [blocking text] from (SELECT es.*,

st.text FROM sys.dm_exec_sessions es

inner join sys.dm_exec_connections ec on ec.session_id=es.session_id

cross apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) st

where es.session_id=@blc)

 x ,(select spid [blocked],blocked [blocking],status [blocked status],

loginame[blocked login],hostname [blocked host],waittime/60000 [in min],

text [blocked text] from sys.sysprocesses r 

cross apply sys.dm_exec_sql_text(sql_handle) st

where r.blocked=@blc) y

FETCH NEXT FROM cur INTO @blc

END   

CLOSE cur  

DEALLOCATE cur

drop table ##temptab

GO

******************************************************************************************************************************************************************

Extract all database permissions before refresh

The below script will get all the database permission before restoring the database and after refresh to fix the orphan users.

Step 1: Run the below script to backup all database related                      permission and roles.(be sure it be in correct database                which you need to restore)

Step 2: take the output and paste to notepad before database                  restore and after restore copy everything from the                        notepad  and run the quires you pasted in the                                notepad. The script will take care of orphan users                         too(only for which the account exists)

                 

you can also download the script from this link

https://gallery.technet.microsoft.com/Extract-Database-dfa53d5a

*********************************************************************************************************************************************************************

set nocount off

IF OBJECT_ID(N'tempdb..##temp1') IS NOT NULL
     DROP TABLE ##temp1
  
create table ##temp1(query varchar(1000))

insert into ##temp1 
select 'use '+db_name() +';'

insert into ##temp1 
select 'go'

/*creating database roles*/
insert into ##temp1
                    select 'if DATABASE_PRINCIPAL_ID('''+name+''')  is null 
                    exec sp_addrole '''+name+''''  from sysusers
where issqlrole = 1 and (sid is not null and sid <> 0x0)

/*creating application roles*/
insert into ##temp1
                    select 'if DATABASE_PRINCIPAL_ID('+char(39)+name+char(39)+')
                    is null CREATE APPLICATION ROLE ['+name+'] WITH DEFAULT_SCHEMA = ['+
                    default_schema_name+'], Password='+char(39)+'Pass$w0rd123'+char(39)+' ;'
 from sys.database_principals
where type_desc='APPLICATION_ROLE'

insert into ##temp1 
                     select  
                                case  
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                       then
                                                                substring (state_desc,0,6)+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' WITH GRANT OPTION ;'
                                                                
                                                         else 
                                                                  state_desc+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
                    END
from sys.database_permissions 
where class=0 and USER_NAME(grantee_principal_id) not in ('dbo','guest','sys','information_schema')

insert into ##temp1 
                    select 
                               case 
                                         when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                   then
                                                             substring (state_desc,0,6)+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.'+OBJECT_NAME(major_id)
                                                             +' to '+'['+USER_NAME(grantee_principal_id)+']'+' with grant option ;'
                                                     else 
                                                              state_desc+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.'+OBJECT_NAME(major_id)
                                                              +' to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
                                  end
from sys.database_permissions where class=1 and USER_NAME(grantee_principal_id) not in ('public');

 insert into ##temp1 
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                     then
                                                              substring (state_desc,0,6)+' '+permission_name+' ON schema::['+sa.name+
                                                               '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                       else
                                                               state_desc+' '+permission_name+' ON schema::['+sa.name+
                                                               '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                       COLLATE LATIN1_General_CI_AS  
                                      end
from sys.database_permissions dp inner join sys.schemas sa on
 sa.schema_id = dp.major_id where dp.class=3

 insert into ##temp1 
                     select 
                                 case 
                                            when state_desc='GRANT_WITH_GRANT_OPTION'
                                             then
                                                    substring (state_desc,0,6)+' '+permission_name+' ON APPLICATION  ROLE::['+sa.name+
                                                     '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                      state_desc+' '+permission_name+' ON  APPLICATION ROLE::['+sa.name+
                                                      '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                      COLLATE LATIN1_General_CI_AS  
                         end
from sys.database_permissions dp inner join sys.database_principals  sa on
 sa.principal_id = dp.major_id where dp.class=4 and sa.type='A'

 insert into ##temp1 
                      select 
                                 case 
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                           then
                                                  substring (state_desc,0,6)+' '+permission_name+' ON   ROLE::['+sa.name+
                                                  '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                           else
                                                   state_desc+' '+permission_name+' ON   ROLE::['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                     COLLATE LATIN1_General_CI_AS  
                                           end
 from sys.database_permissions dp inner join
sys.database_principals  sa on sa.principal_id = dp.major_id 
 where dp.class=4 and sa.type='R'

 insert into ##temp1 
                      select 
                                  case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                       then
                                                               substring (state_desc,0,6)+' '+permission_name+' ON ASSEMBLY::['+sa.name+
                                                                '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                        else
                                                                state_desc+' '+permission_name+' ON ASSEMBLY::['+sa.name+
                                                                 '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                                 COLLATE LATIN1_General_CI_AS  
                                       end
 from sys.database_permissions dp inner join sys.assemblies sa on
 sa.assembly_id = dp.major_id 
 where dp.class=5

 insert into ##temp1
                     select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                            then
                                                    substring (state_desc,0,6)+'  '+permission_name+' ON type::['
                                                    +SCHEMA_NAME(schema_id)+'].['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                            else
                                                    state_desc+' '+permission_name+' ON type::['
                                                    +SCHEMA_NAME(schema_id)+'].['+sa.name+
                                                     '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                     COLLATE LATIN1_General_CI_AS  
                                              end
 from sys.database_permissions dp inner join sys.types sa on
 sa.user_type_id = dp.major_id 
 where dp.class=6


 insert into ##temp1
                      select 
                                 case 
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                           then
                                                     substring (state_desc,0,6)+'  '+permission_name+' ON  XML SCHEMA COLLECTION::['+
                                                     SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                            else
                                                     state_desc+' '+permission_name+' ON  XML SCHEMA COLLECTION::['+
                                                     SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'];'
                                                     COLLATE LATIN1_General_CI_AS  
                                   end
 from sys.database_permissions dp inner join sys.xml_schema_collections sa on
 sa.xml_collection_id = dp.major_id 
 where dp.class=10



insert into ##temp1
                    select
                               case 
                                         when state_desc='GRANT_WITH_GRANT_OPTION' 
                                          then
                                                   substring (state_desc,0,6)+'  '+permission_name+' ON message type::['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                           else
                                                    state_desc+' '+permission_name+' ON message type::['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                     COLLATE LATIN1_General_CI_AS  
                                             end
 from sys.database_permissions dp inner join sys.service_message_types sa on
 sa.message_type_id = dp.major_id 
 where dp.class=15


 insert into ##temp1
                      select 
                                  case 
                                            when state_desc='GRANT_WITH_GRANT_OPTION' 
                                              then
                                                       substring (state_desc,0,6)+'  '+permission_name+' ON contract::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                else
                                                         state_desc+' '+permission_name+' ON contract::['+sa.name+
                                                         '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                         COLLATE LATIN1_General_CI_AS  
                                   end
 from sys.database_permissions dp inner join sys.service_contracts sa on
 sa.service_contract_id = dp.major_id 
 where dp.class=16



  insert into ##temp1
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                            then
                                                      substring (state_desc,0,6)+'  '+permission_name+' ON SERVICE::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                              else
                                                       state_desc+'  '+permission_name+' ON SERVICE::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                        COLLATE LATIN1_General_CI_AS  
                                    end
 from sys.database_permissions dp inner join sys.services sa on
 sa.service_id = dp.major_id 
 where dp.class=17


 insert into ##temp1 
                      select 
                                   case 
                                              when state_desc='GRANT_WITH_GRANT_OPTION'
                                               then
                                                          substring (state_desc,0,6)+'  '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
                                                          '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                 else
                                                          state_desc+' '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
                                                           '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                          COLLATE LATIN1_General_CI_AS  
                                      end
 from sys.database_permissions dp inner join sys.remote_service_bindings sa on
 sa.remote_service_binding_id = dp.major_id 
 where dp.class=18

 insert into ##temp1
                      select
                                  case 
                                            when state_desc='GRANT_WITH_GRANT_OPTION'
                                              then
                                                        substring (state_desc,0,6)+'  '+permission_name+' ON route::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                else
                                                          state_desc+' '+permission_name+' ON route::['+sa.name+
                                                          '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                         COLLATE LATIN1_General_CI_AS  
                                      end
 from sys.database_permissions dp inner join sys.routes sa on
 sa.route_id = dp.major_id 
 where dp.class=19

 insert into ##temp1 
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                            then
                                                     substring (state_desc,0,6)+'  '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
                                                      '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                       state_desc+' '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
                                                       '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                        COLLATE LATIN1_General_CI_AS  
                                       end
 from sys.database_permissions dp inner join sys.fulltext_catalogs sa on
 sa.fulltext_catalog_id = dp.major_id 
 where dp.class=23

  insert into ##temp1 
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION'
                                            then
                                                        substring (state_desc,0,6)+'  '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                        state_desc+' '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                        COLLATE LATIN1_General_CI_AS  
                                             end
 from sys.database_permissions dp inner join sys.symmetric_keys sa on
 sa.symmetric_key_id = dp.major_id 
 where dp.class=24

 insert into ##temp1 
                      select 
                                  case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                             then
                                                       substring (state_desc,0,6)+'  '+permission_name+' ON certificate::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                               else
                                                          state_desc+' '+permission_name+' ON certificate::['+sa.name+
                                                          '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                           COLLATE LATIN1_General_CI_AS  
                                   end
 from sys.database_permissions dp inner join sys.certificates sa on
 sa.certificate_id = dp.major_id 
 where dp.class=25


 insert into ##temp1 
                     select 
                                 case 
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                          then
                                                     substring (state_desc,0,6)+'  '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
                                                     '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                      state_desc+' '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
                                                       '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                       COLLATE LATIN1_General_CI_AS  
                        end
 from sys.database_permissions dp inner join sys.asymmetric_keys sa on
 sa.asymmetric_key_id = dp.major_id 
 where dp.class=26

insert into ##temp1 
                     select  'exec sp_addrolemember ''' +p.NAME+''','+'['+m.NAME+']'+' ;'
FROM sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
where m.name not like 'dbo';

insert into ##temp1
                     select 'ALTER AUTHORIZATION ON SCHEMA::['+SCHEMA_NAME+'] to ['+SCHEMA_OWNER+'] ;'
from information_schema.SCHEMATA where SCHEMA_OWNER not in ('dbo','guest','sys','information_schema')



insert into ##temp1
select '
declare @query varchar(1000)
declare @executequery cursor
set @executequery=cursor for
select '' sp_change_users_login  ''+CHAR(39)+''auto_fix''+CHAR(39)
+'',''+CHAR(39)+name+CHAR(39)
from sysusers
where (issqluser = 1 ) and (sid is not null and sid <> 0x0)
AND SUSER_SNAME(sid) IS NULL and name not in(''dbo'',''guest'',''sys'',''information_schema'')
open @executequery
fetch next from @executequery into @query
while @@fetch_status=0
begin 
 exec (@query)
 print (@query)
fetch next from @executequery into @query
end
close @executequery;
deallocate @executequery;'

 select * from ##temp1  

******************************************************************************************************************************************************************************

script to find space usage in sql 2008 and later(Recommendation to bring it down to 85 % , if used more than that.)

The script  will give the necessary information about the space usage and the recommendation tobring the space utilization less than 85%, if the drive is full then , the recommendation is to increase the size of file to (drive space)-100 mb. Note if the auto growth is enabled , the recommendation is it increase the max size to 5% of the file increase(you change it if you want)

******************************************************************************************************************************************************************
/*please make sure you are in the current database.scripts works well for local disk
The script will try to bring the space utilisation less than 85*/
if  OBJECT_ID('#freesapce') is not null
begin
drop table #freesapce
end
create table #freesapce( drive char(10),val int)
insert into #freesapce   exec xp_fixeddrives;
go
declare @dbname varchar(50)=(select DB_NAME())
select DB_NAME(mf.database_id) 'db_name',

fp.drive,

fp.val 'mb on drive',

(size*8/1024) [occupied size],

mf.physical_name 'physical name of the file',

FILEPROPERTY(name,'spaceused')*8*1.0/1024 'free space',

FILEPROPERTY(name,'spaceused')*1.0*100/size [percent free],

mf.type_desc as 'file',FILEGROUP_NAME(mf.data_space_id) 'file groupname',

mf.name,

convert(nvarchar(20),left(round((FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/84,0),
CHARINDEX('.',(FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/84)-1)
-(size*8/1024)) +' of mb space added to '+db_name()+' in '+@@SERVERNAME 'details of the action',

isnull(
               case 
                     when 
                          /*checking condition for space utilised more that 85%*/
                         (FILEPROPERTY(name,'spaceused')*1.0*100)/size >85 then  

                 case
                       /*checking condition for space available on the drive */
                      when (fp.val)-(left(round((FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/85,0),
                      CHARINDEX('.',(FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/85)-1)-
                      FILEPROPERTY(name,'spaceused')*8*1.0/1024)<0
                  then

                  case 
                      when mf.growth=0 and mf.type_desc not like '%LOG%' and mf.type_desc like '%ROW%'
                  then
                      CHAR(13)+'/*contact windows*/ use master ; '+CHAR(13)
                      +'alter database ['+DB_NAME(database_id)+']  modify file
                      ( NAME = N'''+name+''',size='+ convert (nvarchar(20),
                      size*8/1024+fp.val-1000)+' mb )'
                      /*fp.val-1000 above and below indicates we are leaving 1000mb of space on the drive and utilising
                      all the space leaving  1000mb to the drive*/

                  when 
                      max_size<>-1 or growth=0 and type_desc not like '%LOG%' and type_desc like '%ROW%'
                  then

                      CHAR(13)+'/*contact windows*/ use master ; '+CHAR(13)
                      +'alter database ['+DB_NAME(database_id)+']  modify file
                      ( NAME = N'''+name+''',size='+ convert (nvarchar(20),
                      size*8/1024+fp.val-1000)+' mb,
                      MAXSIZE ='+convert (nvarchar(20),
                      size*8/1024+fp.val-1000)+' mb )'
                   end 

            else
                   case 
                       /*if you want to bring it less your desired wish
                       ,for exapmle 90% then replace 84 by 89 i,e one less than the required*/
                       when mf.growth=0 and mf.type_desc not like '%LOG%' and mf.type_desc like '%ROW%'
                        then
                             '/*action required*/ '+CHAR(13)+'use master ; '+CHAR(13)
                              +'alter database ['+DB_NAME(database_id)+']  modify file
                              ( NAME = N'''+name+''',size='+
                              convert(nvarchar(20),left(round((FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/84,0),
                              CHARINDEX('.',(FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/84)-1))+' mb )'
                       
                       when max_size<>-1 or growth=0 and type_desc not like '%LOG%' and type_desc like '%ROW%'
                    then

                       '/*action required*/ '+CHAR(13)+'use master ; '+CHAR(13)
                       +'alter database ['+DB_NAME(database_id)+']  modify file
                        ( NAME = N'''+name+''',size='+
                        convert(nvarchar(20),left(round((FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/84,0),
                        CHARINDEX('.',(FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/84)-1))+' mb,
                        MAXSIZE ='+
                        convert(nvarchar(20),left(round((FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/84,0),
                        CHARINDEX('.',(FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/84)-1)+
                        left(round((FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/84,0),
                        CHARINDEX('.',(FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/84)-1)*5/100)
                        +'mb )'


                       end
                end

          else 

          'no action required' end ,'auto growth unlimited -please check the drive space is full or not')action
from #freesapce fp left join
sys.master_files mf on fp.drive=SUBSTRING(mf.physical_name,1,1)
where database_id=DB_ID(@dbname)
/*mention the file group name instead of primary or if you have 
name of the file mention in the place of abcd.mdf
*/
--and FILEGROUP_NAME(mf.data_space_id)='PRIMARY' 
--mf.physical_name='abcd.mdf'
order by FILEGROUP_NAME(mf.data_space_id)

/*total space left on the drive once the space is added on the drive*/

select fp.drive,fp.val-(sum(left(round((FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/85,0),
CHARINDEX('.',(FILEPROPERTY(name,'spaceused')*8*1.0/1024)*100/85)-1)-
(size*8/1024))) 'space on dive after space addition'
from #freesapce fp left join
sys.master_files mf on fp.drive=SUBSTRING(mf.physical_name,1,1)
where database_id=DB_ID(@dbname) 
and FILEPROPERTY(name,'spaceused')*1.0*100/size>85
group by fp.val,fp.drive
go
drop table #freesapce
go


******************************************************************************************************************************************************************