December 5, 2007 at 4:17 pm
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
December 6, 2007 at 2:27 am
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.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply