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


Delete Datafiles


Delete Datafiles

Author
Message
Dreamsz
Dreamsz
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 547
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
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 715
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/
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