RESOLVING SPACE ISSUE

  • Comments posted to this topic are about the item RESOLVING SPACE ISSUE

  • 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.

  • Hi guys.

    any comments would be appreciated.

  • 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

  • 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.

  • 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

  • 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

  • its a good script and i should format it better so that everyone can understand.

    will do it shortly.

  • 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

  • Thanks for the script.

  • Ur welcome

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply