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

Delete Datafiles Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2007 4:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 1:48 PM
Points: 49, Visits: 398
Hi Champs,

I have a task may be it sounds funny but as am new to sql so its hard for me.
Anyways the task is

I have a database on sql server 2005 where there are around 120 datafiles in which 40 are for indexes

so i want to delete the data files which is not in use or dosent have any data in it so is there any way where i can figure out which data file is empty and delete it

and ya also the log file for this database is growing unexpectedly so is there anyway where i can figure out the cause

and today i did shrink log file through management studio and from 128gb it came to 100mb and i didnt took any backup for log file, so did i screw up anything am not sure

and ya if you guys can tell me when we are using dbcc shirnk database of files what percent we should keep usually


Thanks in advance
Post #429961
Posted Thursday, December 6, 2007 2:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
You'd just do so much better going on a training course rather than trying to change a database structure that you might not understand, especially if it's a production database.
Whilst on the face of it I guess the number of files seem to be overkill you should try to establish exactly why this was done this way - generally for a high performance database you may wish to partition using filegroups and you will probably want to use multiple files per filegroup, so on an 8 way box you might have 8 files per filegroup. so .. before you start making changes understand the differences and the reasons. You can can query the dmvs to find out the location of each object, I suggest you obtain a dmv system map - you can download a pdf from microsoft I think. I'm not tooo sure which books to recommend - I usually figure the "inside ...." are a good place to start.


The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #430089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse