RESOLVING SPACE ISSUE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Azad Sale » RESOLVING SPACE ISSUE

RESOLVING SPACE ISSUE

Rate Topic

Display Mode

Topic Options

Author

Message

azad maratha

Posted Saturday, October 23, 2010 1:03 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:54 AM
Points: 283, Visits: 363

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

Post #1009635

azad maratha

Posted Sunday, October 24, 2010 8:10 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:54 AM
Points: 283, Visits: 363

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.

Post #1009767

azad maratha

Posted Wednesday, October 27, 2010 9:56 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:54 AM
Points: 283, Visits: 363

Hi guys.

any comments would be appreciated.

Post #1012041

S. Kusen

Posted Thursday, November 04, 2010 5:39 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:25 PM
Points: 956, Visits: 873

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

Post #1016307

azad maratha

Posted Friday, November 05, 2010 2:45 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:54 AM
Points: 283, Visits: 363

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.

Post #1016413

Phil Streiff

Posted Tuesday, December 14, 2010 2:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 29, 2013 6:01 AM
Points: 2, Visits: 90

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

Post #1034784

azad maratha

Posted Tuesday, December 14, 2010 9:07 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:54 AM
Points: 283, Visits: 363

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

Post #1034873

azad maratha

Posted Tuesday, December 14, 2010 9:15 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:54 AM
Points: 283, Visits: 363

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

Post #1034874

manu0417

Posted Monday, August 13, 2012 8:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 4:01 AM
Points: 1, Visits: 42

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

Post #1344511

« Prev Topic | Next Topic »

Permissions