Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

RESOLVING SPACE ISSUE Expand / Collapse
Author
Message
Posted Saturday, October 23, 2010 1:03 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 4:27 AM
Points: 283, Visits: 367
Comments posted to this topic are about the item RESOLVING SPACE ISSUE
Post #1009635
Posted Sunday, October 24, 2010 8:10 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 4:27 AM
Points: 283, Visits: 367
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
Posted Wednesday, October 27, 2010 9:56 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 4:27 AM
Points: 283, Visits: 367
Hi guys.


any comments would be appreciated.
Post #1012041
Posted Thursday, November 4, 2010 5:39 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 968, Visits: 964
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
Posted Friday, November 5, 2010 2:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 4:27 AM
Points: 283, Visits: 367
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
Posted Tuesday, December 14, 2010 2:41 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 1:18 PM
Points: 2, Visits: 96
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
Posted Tuesday, December 14, 2010 9:07 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 4:27 AM
Points: 283, Visits: 367
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
Posted Tuesday, December 14, 2010 9:15 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 4:27 AM
Points: 283, Visits: 367
its a good script and i should format it better so that everyone can understand.
will do it shortly.
Post #1034874
Posted Monday, August 13, 2012 8:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 9, 2014 9:40 PM
Points: 1, Visits: 63
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 »

Add to briefcase

Permissions Expand / Collapse