To achieve our requirement we have to execute two stored procedures given below :
1. StoredProcedure [dbo].[Srisp_configure]
2. CREATE procedure [dbo].[usp_freedrivespace]
Find the stored procedures script below :
USE [master]
GO
/****** Object: StoredProcedure [dbo].[Srisp_configure] Script Date: 6/29/2018 10:42:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[Srisp_configure]
@begin bit=0
AS
set nocount on
if (CONVERT(char(1), SERVERPROPERTY('ProductVersion'))='8')
return
set @begin=isnull(@begin,1)
print '/*'
if @begin=1
begin
if object_id('tempdb.dbo.##sp_configure') is not null
drop table ##sp_configure
select convert(int,value) 'value', config, comment
into ##sp_configure
from master..sysconfigures
end
declare @oleap varchar(50),@xpcs varchar(50),@adhocdq varchar(50),@showadv varchar(50)
print convert(varchar,@@servername)+', '+convert(varchar(40),@@version)
set @showadv=isnull((select value from ##sp_configure where lower(comment) like '%show advanced options%'),1)
set @oleap=isnull((select value from ##sp_configure where lower(comment) like '%ole automation procedures%'),1)
set @xpcs=isnull((select value from ##sp_configure where lower(comment) like '%command%shell%'),1)
set @adhocdq=isnull((select value from ##sp_configure where lower(comment) like '%ad hoc distributed queries%'),1)
if @begin=1
begin
if @showadv=0
begin
EXEC sp_configure 'show advanced options', 1
RECONFIGURE with override
end
if @oleap=0 EXEC sp_configure 'Ole Automation Procedures', 1
if @xpcs=0 EXEC sp_configure 'xp_cmdshell', 1
if @adhocdq=0 EXEC sp_configure 'Ad Hoc Distributed Queries', 1
end
else
begin
if @oleap=0 EXEC sp_configure 'Ole Automation Procedures', 0
if @xpcs=0 EXEC sp_configure 'xp_cmdshell', 0
if @adhocdq=0 EXEC sp_configure 'Ad Hoc Distributed Queries', 0
end
RECONFIGURE with override
print '*/'
GO
Use master
GO
CREATE procedure [dbo].[usp_freedrivespace]
(@filedrive varchar(5)='f:\' --e.g. 'c:\' or 'd:\' or 'e:\' etc... '%:\' for all
,@filefreespacemin int =1000 --any db files with > @filefreespacemin freespace(MB)(e.g.,1000 = 1GB)
,@filefreespacemax int=1000000000 -- and < @filefreespacemax freespace(MB)(e.g., 15000 = 15GB)
,@freespaceshrinkpct float=.90 --% to shrink freespace of each file(to .2 and run a few times)
,@bakdaysOld int=30 --delete @bakdaysOld .bak files on @filedrive (>999 to skip delete step)
,@filestodelete varchar(10)='*.bak'--
,@dbfiletype varchar(10)='%' --to 'data' or 'log' or '%'. '%'="shrink both log and data files"
,@dbnamelikethis varchar(100)='%' --used in 'like' "where clause" for dbnames to shrink
,@dbnamenotlikethis varchar(100)=' ' --used in 'not like' "where clause" for dbnames to shrink
,@printonly bit=0 --only print commands..don't execute them
)
as
declare @ssql nvarchar(4000),@reuse_wait varchar(100),@masterdbs varchar(100)
----------------- parms you set end.
-- shrinks all db files on a drive by a percentage of internal db freespace
-- change parms below where appropriate.
-- also deletes old files from drive (if @bakdaysOld set to < 999).
-- select * from sys.databases
-- Run as many times as needed. Does 2 things to free space:
-- 1. Delete all @filestodelete older than @bakdaysOld days on drive @filedrive (if @bakdaysOld set to < 999)
-- 2. On drive @filedrive, shrink every db file that has freespace between @filefreespacemin and @filefreespacemax by: @freespaceshrinkpct
if object_id('tempdb.dbo.#tabledir') is not null
drop table #tabledir
if object_id('tempdb.dbo.#spacetbl') is not null
drop table #spacetbl
if object_id('tempdb.dbo.##alldbfiles') is not null
drop table ##alldbfiles
if object_id('tempdb.dbo.##alldbfilesbefaft') is not null
drop table ##alldbfilesbefaft
if object_id('tempdb.dbo.##TMPFIXEDdriveS_shrink') is not null
DROP TABLE ##TMPFIXEDdriveS_shrink
CREATE TABLE ##TMPFIXEDdriveS_shrink (
drive CHAR(1),
mbfree BIGINT)
INSERT INTO ##TMPFIXEDdriveS_shrink EXEC [master]..xp_fixeddrives
--select * from ##TMPFIXEDdriveS_shrink
create table #tabledir (ID INT IDENTITY(1,1) PRIMARY KEY, x varchar(300) null)
create table #spacetbl (ID INT IDENTITY(1,1) PRIMARY KEY, drivename varchar(10),bytesfree varchar(50),befaft varchar(25) null)
create table ##alldbfilesbefaft (dbname varchar(256),filename varchar(256),filesizemb bigint,freespacemb bigint,befaft varchar(25) null,log_reuse_wait_desc varchar(50) null)
create table ##alldbfiles (dbname varchar(256),name varchar(256),filename varchar(256),sizemb bigint,fileid int, freespacemb bigint, pctfree float,usage varchar(10))
set @filedrive=upper(@filedrive)
if (@freespaceshrinkpct>.8) set @freespaceshrinkpct=.8
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1)<>'8'
begin
set @masterdbs='sys.databases sdb'
set @reuse_wait='sdb.log_reuse_wait_desc'
end
else
begin
set @masterdbs='master..sysdatabases sdb'
set @reuse_wait='''n/a'''
end
declare @msg varchar(4000)
set @msg='/*Warning: Shrinking DB files is only meant as a !Temporary! relief of drive space pressure.
The need to free up drive space in the first place is telling you to add more drive space to SQL Server:
https://connections.cat.com/wikis/home?lang=en_US#/wiki/SQL%20Server%20Support%20WIKI/page/Request%20space%20for%20disk
If you absolutely have no choice and have to run a data file shrink operation, be aware that you
are going to cause index fragmentation and you should take steps to remove it afterwards if it is going to
cause performance problems. The only way to remove index fragmentation without causing data file growth again
is to use DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE. These commands only require a single 8KB page of
extra space, instead of needing to build a whole new index in the case of an index rebuild operation.
Bottom line - try to avoid running data file shrink at all costs!*/'
select @msg 'Warning'
print @msg
if @filedrive<>'%:\' --and exists (select top 1 value from dbo.sysconfigures where (config=16390 and value<>0))
begin
exec catsp_configure @begin=1
set @ssql='dir /d '+@filedrive
insert #tabledir exec master..xp_cmdshell @ssql
insert into #spacetbl
select @filedrive,convert(varchar,Right(x,len(x)-charindex(')',x))),'Before' from
#tabledir where upper(x) like upper('%bytes free%')
truncate table #tabledir
if charindex('.bak',@filestodelete)=0 and charindex('.zip',@filestodelete)=0
and charindex('.ldf',@filestodelete)=0 and charindex('.mdf',@filestodelete)=0
and charindex('.ndf',@filestodelete)=0
set @filestodelete='*.bak'
set @ssql='forfiles /p '+@filedrive+' /s /m '+@filestodelete+' /d -'+convert(varchar(3),@bakdaysOld)+' /c "cmd /c echo @path ; size:@fsize ; date:@fdate"'
-- make sure to fully qualify cmdshell... without master.. it gets forcibly close msg:
insert #tabledir exec master..xp_cmdshell @ssql
if not exists (select * from #tabledir where upper(x) like '%NO FILES FOUND%') and @bakdaysOld < 999
begin
select x 'Deleting these files' from #tabledir where upper(x) like upper('%'+@filedrive+'%'+replace(@filestodelete,'*','%'))+'%'
set @ssql='forfiles /p '+@filedrive+' /s /m '+@filestodelete+' /d -'+convert(varchar(3),@bakdaysOld)+' /c "cmd /c del /q @path"'
if @printonly=1
print @ssql
else
begin
insert #tabledir exec master..xp_cmdshell @ssql
end
end
exec catsp_configure @begin=0
truncate table #tabledir
end
else
begin
--select 'master..xp_cmdshell not allowed. skipping show freespace step...' 'cmdshell'
insert into #spacetbl
select drive,convert(varchar,mbfree)+' MB free','Before' from
##TMPFIXEDdriveS_shrink where upper(drive) like left(@filedrive,1)
-- select @filedrive,convert(varchar,mbfree)+' MB free','Before' from
-- ##TMPFIXEDdriveS_shrink where left(@filedrive,1)=upper(drive)
end
set @ssql= 'if ''?'' not in (''master'') begin
use [?];
insert into ##alldbfiles
select ''?'',name,filename,size/128,fileid,(size/128.0 - (CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0)),
((size/128.0 - (CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0))/(size/128.0)),
''usage'' = convert(varchar(25)
,(case status & 0x40
when 0x40 then ''log''
else ''data'' end))
from [?]..sysfiles where upper(filename) like ('''+upper(@filedrive)+'%'')
--print convert(varchar,@@rowcount)+'' - ''+''?''
end;'
--print @ssql
exec sp_MSforeachdb @ssql
--select * from ##alldbfiles
declare @tsql nvarchar(4000); set @tsql = ''
declare @dbname varchar(256),@iLogFile int,@size bigint,@freespace bigint,@name varchar(133),@filename varchar(300),@pctfree float
declare alldbfiles cursor for
select top 30 dbname,name,filename,sizemb,fileid,freespacemb,pctfree
from ##alldbfiles where freespacemb between @filefreespacemin and @filefreespacemax
and usage like @dbfiletype and lower(dbname) like lower(@dbnamelikethis) order by pctfree desc
--select @@rowcount 'rows'
open alldbfiles
fetch next from alldbfiles into @dbname,@name,@filename,@size,@iLogFile,@freespace,@pctfree
while @@fetch_status = 0
begin
-- select @dbname,@filename,(@freespace*1.0),(@freespace*@freespaceshrinkpct),@filefreespacemin,@filefreespacemax
if ((@freespace*1.0)>(@freespace*@freespaceshrinkpct)) and ((@freespace*1.0) between @filefreespacemin and @filefreespacemax) and lower(@dbname) not like lower(@dbnamenotlikethis)
begin
-- select @dbname,@filename,(@freespace*1.0),(@freespace*@freespaceshrinkpct),@filefreespacemin,@filefreespacemax
set @tsql='DBCC SHRINKFILE('+cast(@iLogFile as varchar(5))+', '+cast(cast(@size-(@freespace*@freespaceshrinkpct) as decimal(18,0)) as varchar)+') WITH NO_INFOMSGS;'
print char(13)+'-- '+@dbname+' file: '+@filename+', '+convert(varchar,@pctfree*100)+'% free,'+char(13)+'-- current size:'+convert(varchar,@size)+'MB, free:'+convert(varchar,@freespace)+'MB. shrinking freespace by ~'+convert(varchar,(@freespace*@freespaceshrinkpct))+'MB'+char(13)+'use ['+@dbname+']'
RAISERROR(@tsql, 0, 1) WITH NOWAIT
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1)<>'8'
set @tsql='begin try'+char(13)+@tsql+'end try'+char(13)+'begin catch'+char(13)+'exec master..usp_geterrorinfo @appinfo='''+'use ['+@dbname+'];'+@tsql+''';end catch;'
set @tsql='use ['+@dbname+']
insert into ##alldbfilesbefaft
SELECT '''+@dbname+''',sf.name,size/128,(size/128.0 - CAST(FILEPROPERTY(sf.name, ''SpaceUsed'') AS int)/128.0) AS AvailableSpaceInMB, ''Before'' befaft, '+@reuse_wait+'
FROM sysfiles sf left join '+@masterdbs+' on sdb.name='''+@dbname+''' where (has_dbaccess(sdb.name)=1) and upper(sf.filename) like ''%''+upper(left('''+@filename+''',len('''+@filename+''')))+''%'';'+@tsql+'
insert into ##alldbfilesbefaft
SELECT '' '','' '' ,size/128,(size/128.0 - CAST(FILEPROPERTY(sf.name, ''SpaceUsed'') AS int)/128.0) AS AvailableSpaceInMB, ''After'' befaft, '+@reuse_wait+'
FROM sysfiles sf left join '+@masterdbs+' on sdb.name='''+@dbname+''' where (has_dbaccess(sdb.name)=1) and upper(sf.filename) like ''%''+upper(left('''+@filename+''',len('''+@filename+''')))+''%'';'
if @printonly=0
exec(@tsql)
end
fetch next from alldbfiles into @dbname,@name,@filename,@size,@iLogFile,@freespace,@pctfree
end
close alldbfiles
DEALLOCATE alldbfiles
truncate table #tabledir
if @filedrive<>'%:\' --and exists (select top 1 value from dbo.sysconfigures where (config=16390 and value<>0))
begin
exec catsp_configure @begin=1
set @ssql='dir /d '+@filedrive
insert #tabledir exec master..xp_cmdshell @ssql
exec catsp_configure @begin=0
insert into #spacetbl
select @filedrive,convert(varchar,Right(x,len(x)-charindex(')',x))),'After' from
#tabledir where upper(x) like upper('%bytes free%')
end
else
begin
-- select 'master..xp_cmdshell not allowed. skipping show freespace step...' 'cmdshell'
truncate table ##TMPFIXEDdriveS_shrink
INSERT INTO ##TMPFIXEDdriveS_shrink EXEC [master]..xp_fixeddrives
insert into #spacetbl
select drive,convert(varchar,mbfree)+' MB free','After' from
##TMPFIXEDdriveS_shrink where upper(drive) like left(@filedrive,1)
end
select drivename,bytesfree,befaft from #spacetbl
order by drivename,befaft desc
if (select count(*) from ##alldbfilesbefaft) >0
begin
if (select count(*) from ##alldbfilesbefaft)/2=20
select 'Limiting shrink to top 10 files by pct of freespace...'
else
select convert(varchar,(select count(*)/2 from ##alldbfilesbefaft))+' Files with freespace between '+
convert(varchar,@filefreespacemin)+'MB and '+convert(varchar,@filefreespacemax)+'MB' 'Files matching criteria'
select dbname,filename,filesizemb,freespacemb,befaft,log_reuse_wait_desc,case when lower(log_reuse_wait_desc) like 'log_backup' and lower(befaft) like 'before' then 'perform LOG BACKUP of '+[filename]+' then reissue DBCC SHRINKFILE' else '' end [info] from ##alldbfilesbefaft
end
else
select @filedrive 'drivename', 'No eligible DB files found' 'msg'
-- in emergency:
--USE dbname
--GO
--ALTER DATABASE dbname
--SET RECOVERY SIMPLE
--GO
--DBCC SHRINKFILE (dbname_Log, 10)
--GO
--ALTER DATABASE dbname
--SET RECOVERY FULL
--GO
/*
Be aware that the LSN chain is immediately broken when the recovery model is set to SIMPLE.
Restarting the Chain
To restart the LSN log chain, immediately perform a full or differential backup after resetting the recovery model to FULL, and then resume normal log backups:
*/
After executing above scripts to perform shrink opeartion on a database mdf,ldf files and also if you want delete old backup files from the drive to clear space we can use this below script :
use [master]
set nocount on
--waitfor delay '00:26:00'
----------------- parms you set:
execute [master].[dbo].[usp_freedrivespace]
@filedrive='%:\' --e.g. 'c:\' or 'd:\' or 'e:\' etc... '%:\' for all
,@filefreespacemin=1000 --any db files with > @filefreespacemin freespace(MB)(e.g.,1000 = 1GB)
,@filefreespacemax=1000000000 -- and < @filefreespacemax freespace(MB)(e.g., 15000 = 15GB)
,@freespaceshrinkpct=.90 --% to shrink freespace of each file(e.g., set to .2 and run a few times)
,@bakdaysOld=1 --delete @bakdaysOld .bak files on @filedrive (set >999 to skip delete step)
,@filestodelete='*.trn'
,@dbfiletype='log' --set to 'data' or 'log' or '%'. '%'="shrink both log and data files"
,@dbnamelikethis='%' --used in 'like' "where clause" for dbnames to shrink
,@dbnamenotlikethis=' ' --used in 'not like' "where clause" for dbnames not to shrink
,@printonly=0 --only print shrink commands..don't execute them
--,@reorgindex='print' --comment or set to NULL or 0 or '' to skip reorgs. values: 'print,execute' - performs a reorg if a db file had a shrink performed on data file
GO
NOTE : I have been using this script without any issues .But still please do some testing of this script before executing it in your Production.
No comments:
Post a Comment