May 14, 2012 at 9:09 am
Hi everyone,
We have a product called Websense installed, and the application creates its own databases on a Monthly basis, the scary part is, when it creates its database, it creates them with unrestricted file growth that increments by 500mb's... We have a purge strategy to remove these database after 6 months, but I'm noticing that in the mean time, after the month is complete, about 50% of datafile is unused... it must be purging some data in the data file on a monthly basis... And they grow to a size of 80gb's, leaving approximately 40gb's of free space behind every month, for 6 months worth I'm estimating close to 240gb's of wasted space that can be reclaimed...
What I'm thinking of creating an SSIS package to run on maybe the 5th of every month, scan the list of databases (its a comon server so other applications use this database as well) and find the WSLOGDB_* databases names that has 40% or more of free space in the datafile. I don't want the shrink file operation happening on any other database on the server and also want to ignore doing the shrink on the current active websense datafile. The websense datafiles automatically increment so a wild card will need to be used (WSLOGDB70_1,WSLOGDB70_2,WSLOGDB70_3 etc)
Sounds simple in logic, but I'm still pretty new at SSIS scripting and would appreciate any hints or tips to come to this end point.
Thanks,
May 14, 2012 at 9:52 am
i dont think you need SSIS to do this
you should be able to modify the script below to do what you need
create table ##res(db varchar(100), fileid bigint,filesize float,spaceused float,freespace float,name varchar(255) ,fname varchar(max))
exec sp_msforeachdb 'insert into ##res select ''?'',* from (
select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
?.dbo.sysfiles a
) x
where [free_space_mb]/[file_size_mb]*100>50'
select * from ##res
declare @db sysname
declare @strsql varchar(max)
declare @fname varchar(255)
declare curs1 cursor for select db,fname from ##res
open curs1
fetch next from curs1 into @db,@fname
while @@fetch_status=0
begin
set @strsql='use '+@db+'
dbcc shrinkfile(1,1)
dbcc shrinkfile(2,1)'
exec (@strsql)
fetch next from curs1 into @db,@fname
end
close curs1
deallocate curs1
drop table ##res
all you have to do is modify it to match your naming criteria
MVDBA
May 14, 2012 at 9:54 am
ooops - slight tweak to my code
create table ##res(db varchar(100), fileid bigint,filesize float,spaceused float,freespace float,name varchar(255) ,fname varchar(max))
exec sp_msforeachdb 'use ? insert into ##res select ''?'',* from (
select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
?.dbo.sysfiles a
) x
where [free_space_mb]/[file_size_mb]*100>50'
select * from ##res
declare @db sysname
declare @strsql varchar(max)
declare @fname varchar(255)
declare curs1 cursor for select db,fname from ##res
open curs1
fetch next from curs1 into @db,@fname
while @@fetch_status=0
begin
set @strsql='use '+@db+'
dbcc shrinkfile(1,1)
dbcc shrinkfile(2,1)'
exec (@strsql)
fetch next from curs1 into @db,@fname
end
close curs1
deallocate curs1
drop table ##res
MVDBA
May 14, 2012 at 1:55 pm
ok, thanks,...
replicating 2 databases into our sandbox and I'll give that script a try...
So I think all I need to do is add an AND to where clause such as
...
from
?.dbo.sysfiles a
) x
where [free_space_mb]/[file_size_mb]*100>50
and NAME like ''wslogdb70_%'''
...
Am I right?
May 14, 2012 at 2:22 pm
Wrong!
Ok I know now...
I just extracted that inner query and played with it..
select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,60)
from
wslogdb70_2.dbo.sysfiles a
where filename like '%wslogdb70_%.mdf'
I think this will pick out only the datafiles that has wslogdb70_*.mdf... which is what I want...
May 14, 2012 at 2:30 pm
Norm-677551 (5/14/2012)
from?.dbo.sysfiles a
) x
where [free_space_mb]/[file_size_mb]*100>50
and NAME like ''wslogdb70_%'''
...
Am I right?
if you're going to use system catalogs at least use the right one. For file info at database level use sys.database_files
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 14, 2012 at 2:36 pm
yes, that would be handy, I hard coded the database name when I was playing with that inner query, but I imagine the for each loop grabs the database names and uses that system view...
I'll play around with this one too, although I'm not suppose to be spending too much time on this. So the quicker I can drive out a solution the better...
The disk admins will thank me for freeing up all that disk space 🙂
May 15, 2012 at 1:21 am
its an old script from sql 2000 days - sorry
MVDBA
May 15, 2012 at 9:59 am
Norm-677551 (5/14/2012)
Hi everyone,We have a product called Websense installed, and the application creates its own databases on a Monthly basis ...
Wow. This is really, really bad data-design. :-O
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 15, 2012 at 10:06 am
Your telling me... 🙂
Its one of those vendor apps that like to come in and have sysadmin access to everything... this is not the only bad part of this product... we couldn't use it in our regular database environment with incremental backups, cause since we don't know when a new database was created if it fell in the middle of the week when incremental backups happen the backups would fail cause their was no full backup taken previously... it gets worse... they had no purge strategy to begin with, we had to enforce one cause it was consuming so much data very quickly...
So much can happen when a DBA is not in the room when they choose vendor applications to come into the environment 🙂
Anyhow, its all fun stuff of driving solutions to compensate for this application design at this point...
May 15, 2012 at 1:23 pm
Yeah, I haven't seen this particular anti-pattern since the 80's. My sympathy Norm. 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 16, 2012 at 2:03 am
it's not been implemented by quantix has it ?
MVDBA
May 16, 2012 at 11:40 am
We have Websense also. you should be able to configure the number of Spin-off websense to keep and configure the sizes of the new databases in the WebSense. The vendor should be able to guide you on the configuration. Good luck!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply