Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


RESOLVING SPACE ISSUE


RESOLVING SPACE ISSUE

Author
Message
azad maratha
azad maratha
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 370
Comments posted to this topic are about the item RESOLVING SPACE ISSUE
azad maratha
azad maratha
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 370
There are actually four parameters that you can change.The fourth one is filesize.This parameter value is in MB.First you have to set the filesize { set @filesize=100} then the search result will show results having files whose minimum size is 100 MB.
azad maratha
azad maratha
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 370
Hi guys.


any comments would be appreciated.
S. Kusen
S. Kusen
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1190 Visits: 1111
This is a good script script. I think it would be useful for checking for old backups as you suggest. Typically, we try to place backups we retain for multiple days in a specified folder we put on each system and then check the age there. Naturally, we may take backups and place them on drives and never go back to them, so this would be good for a one-off look for those "forgotten" backups that are no longer needed.

Some recommendations:
1. Change it to a stored procedure with the configurable values as input. When I first went to run it, I hadn't realized the configurable values section and had to look at the data types first to get it working.
2. A minor recommendation, but have the default output changed to say "size_mb" instead of just size, output the number of days the file has been there (get the datediff comparing the file date versus today's date)
3. Modify it to loop through all drives (maybe by combining its' use with xp_fixeddrives). This would be useful as an overall review of the system to check all drives to proactively look at the largest files of a certain type.

Some people may also point out that xp_cmdshell and Ole Automation Procedures need to be enabled in order for the script to run, and there are some associated security risks with enabling those options.

A generalized comment:
With monitoring/cleanup scripts, I always try to take the mindset of "How can I run this on all of my SQL Servers/databases/disks/etc from a centralized location?" to help make my job easier.

Hope that feedback helps you out for future submissions.
Steve
azad maratha
azad maratha
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 370
thanks for your feedback steve.

this script is a modified version of another script which i wrote.that script was embedded in vbscript and it was supposed to be run on all servers.but by doing this we dont have enough control on it.and my TL suggested to replace cursor.

i was thinking of set-based solution.but as it is DIWALI festival i will complete it after its over.
and as for xp_cmdshell and ole i assumed that everyone knows about it.

and stored procedure is useful if its permanent and then we can just pass values but as this is a one time script which doesnt create any permanent objects i never thought of creating a procedure for that.

last but not least

thanks for appreciating my work and taking efforts to see it works and needs improvements and
it can be useful to DBA's.
Phil Streiff
Phil Streiff
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 139
I ran the script in a SQL 2005 instance and got the following error:

Msg 245, Level 16, State 1, Line 19
Conversion failed when converting the varchar value 'how old the file is in days' to data type int.

Thanks,
Phil
azad maratha
azad maratha
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 370
Hi,

How old the file is in days......you should put a number...if its week old...then put 7.


Thanks & Regards

Azad R Sale
azad maratha
azad maratha
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 370
its a good script and i should format it better so that everyone can understand.
will do it shortly.
manu0417
manu0417
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 83
Good script Azad. I would also suggest to make it more flixible by adding default values like

1. looping all drives if drive not specified.
2. Providing all files if not filtered by size or extension.
3. If this can be run remotely that would be even beneficial.

And keep posting. All the best.

Regards,
Manu
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9908 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search