November 28, 2006 at 4:12 am
This looks like a promising bit of code, but I don't understand how to action it, I've run it in QA against my database (that's run out of space, which Sergiy here was trying to help with), but I need this to start actually freeing up some space now, but I don't know what parameters I need to supply, how to supply them etc.
In other words, i just need to "point and shoot", so I suppose I need to start with, "Exec....."
Thanks,
Jaybee.
if exists ( select [name] from tempdb.dbo.sysobjects (nolock) where [name] like '#sp_shrinkdata%' and type = 'P' )
drop proc #sp_shrinkdata
go
create proc #sp_shrinkdata
@updateusage bit = 0,
@freed_space int = null, -- is the portion of free space (MB) to reclaim, eg. 90% of 1 gb is 900 mb
@Msg_rtn_intv int = null -- is the size of reclaimed space (MB) displayed between each message echo
as
/*
** Author: King James ** Created on: 28/11/2006
** Purpose: Shrinks data file (not log).
** Features:
** 1. Can shrink data size down to a specified value (optional). If desired size not provided, it
** will shrink all the way down until there's 200 MB free space left;
** 2. Can report time, data size and cycle number after each run. If echo interval not supplied,
** it will report at every 20 MB interval.
** Requirements:
** 1. Must be in the current database.
** 2. Must first compile the stored procedure.
*/
declare @initial_size int,
@end_size int,
@message varchar(150),
@count int,
@data_used int,
@cmd varchar(500),
@datafile sysname,
@dbname sysname,
@data_free_start int
set @dbname = db_name()
set @count = 1
set @datafile = (select rtrim(name) from sysfiles (nolock) where fileid = 1)-- Trims away trailing blanks or will NOT work!!
set @initial_size = (select sum(size) from sysfiles (nolock) where (status & 64 = 0)) * 8192.0 / 1048576.0
set @data_used = (select sum(convert(dec(15),reserved)) from sysindexes (nolock) where indid in (0, 1, 255)) * 8192.0 / 1048576.0
if @Msg_rtn_intv is null
set @Msg_rtn_intv = 20 --default to 20 mb at each interval
if @freed_space is null
set @freed_space = @initial_size - @data_used - 200 -- default to 200 MB free space left after shrinking
set @end_size = @initial_size - @freed_space
if @updateusage = 1
exec ('dbcc updateusage (0) WITH NO_INFOMSGS')
print '...... DBCC UPDATEUSAGE complete.'
print''
print '*** Shrinking data file for ' + db_name() + ' ***'
print ''
set @data_free_start = (@initial_size-@data_used)
print 'Total size of data: ' + convert(char(5),@initial_size)
+ ' MB Data used: ' + convert(char(5),@data_used)
+ ' MB Data free: ' + convert(char(5),@data_free_start) + ' MB'
print ''
while @initial_size > @end_size
begin
set @initial_size = @initial_size - @Msg_rtn_intv
set @cmd = N' dbcc shrinkfile ('+@datafile+', '+convert(varchar(5), @initial_size)+') WITH NO_INFOMSGS'
exec (@cmd)
set @message = convert(varchar(30), getdate(), 20) + ' - at the ' + convert (varchar(6), @count) + 'th run. ' +
' - Free space left: ' + convert(char(5),(@initial_size - @data_used)) + ' MB'
print @message
set @count = @count + 1
end
print ''
print 'Total reclaimed space: ' + cast((@data_free_start - (@initial_size - @data_used)) as varchar(10)) + ' MB'
print ''
print '*** Shrinking data file for ' + db_name() + ' is complete. ***'
go
-- #sp_shrinkdata
-- #sp_shrinkdata @updateusage = 1, @freed_space = 100 , @Msg_rtn_intv = 10
November 28, 2006 at 6:28 am
he's helpfully commented out an example proc call at the end
-- #sp_shrinkdata @updateusage = 1, @freed_space = 100 , @Msg_rtn_intv = 10
just remove the --
MVDBA
November 28, 2006 at 6:29 am
when you say you've run out of space - what do you mean ?? the drive is full?
MVDBA
November 28, 2006 at 8:07 am
Chock full!!
Not my/our fault...we took over a company who's adherance to best practice was questionable, at best.
But it IS our problem - or rather, MY problem.
So the end of the code looks like,
go
-- #sp_shrinkdata
#sp_shrinkdata @updateusage = 1, @freed_space = 100 , @Msg_rtn_intv = 10
???
Cheers,
Jaybee.
November 28, 2006 at 8:14 am
i'm not sure why you're not just running dbcc shrinkfile or the shrink commands from enterprise manager.
have you checked the size of your log files - ???
if you can run the command
dbcc sqlperf(logspace) and post back the results we might be able to point you in a better direction.
MVDBA
November 28, 2006 at 8:32 am
master 0.4921875 46.825397 0
tempdb 999.99219 5.7368126 0
model 0.4921875 71.031746 0
msdb 2.2421875 50.631535 0
isalog 14198.805 0.2937431 0
November 28, 2006 at 9:51 am
ok - so just running the following should free ip 14Gb of space
use isalog
go
dbcc shrinkfile (2,1,truncateonly)
assuming that the log file is file number 2
MVDBA
November 28, 2006 at 9:53 am
and to find the log file number do the following
use isalog
go
select * from sysfiles
look for the log file and replace the first number of the above command with teh file number
MVDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply