Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Delete Datafiles Expand / Collapse
Posted Wednesday, December 5, 2007 4:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 26, 2016 10:43 AM
Points: 53, Visits: 539
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


Group: General Forum Members
Last Login: Wednesday, April 27, 2016 3:18 AM
Points: 2,687, 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
Post #430089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse