Francisco Tapia
fhtapia at gmail.com
Fri Oct 5 13:51:20 CDT 2012
John, While performing a shrinkfile can cause OS file level fragmentation, unless you are pre-growing your database files, you've already causing file fragmentation, everytime Windows creates a new file, it's NTFS system will be super efficient and begin writing to all available blocks, even when they are not contiguous... it's the inherent nature of the filesystem...in order to avoid fragmentation at all cost, you will want to perform an OS level defragmentation prior to creating any new large file on the OS in windows. You also need to grow your database files to the optimum size, so if your db is 700gb you want to pre-grow to a suitable level slightly above that (since it's mostly readonly) You can properly defrag your existing db w/o having to move your data into a new db container, but will require some downtime and defraging of your Raid volume, it is up to you to believe what you want when it comes to RAID5/6 and OS level fragmentation, but the general consensus says that the OS still treats this as a single volume and therefore a defrag on this volume will yield positive results. downtime... that is generally whey most people do not defrag, in order to defrag the database files at the OS level, you need to take the Sql Server engine offline, otherwise those files are in use. so to be the most effective, on your Read Only database, you will want to 1) take the system offline, 2) perform an OS level defrag, 3) then follow that up with a database defrag (indexes etc). 4) peform a shrinkfile 5) truncate your transaction log to acceptable levels 6) take the system offline a second time 7) defrag at the OS level once more. * defragging at the end here is simply housekeeping since you've moved, defraged, and shrank files, you will want contiguous space anytime your log or db files need to grow.... if your database is a ReadOnly database you should not experience any growth, if you are adding data to your database infrequently, then you will want to manually pre-grow the database file before you import new records, all other temp selections should be done to ##temp tables OR if you need to keep the data longer, to a separate container that you can return to, such as ResultsDB, that way ResultsDB will grow and shrink as you dump report data to it. It seems that you are unnecessarily introducing a chance to corrupt things the way you are doing things.. -Francisco -------------------------- You should follow me on twitter here <http://twitter.com/seecoolguy> Blogs: SqlThis! <http://bit.ly/sqlthis> | XCodeThis!<http://bit.ly/xcodethis> <http://db.tt/JeXURAx> On Thu, Oct 4, 2012 at 11:49 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > > 1. What exactly do you mean by "migrate"? Do you really mean "move", or > are you actually migrating, as in from one version or instance to another? > There's a large difference between them. > > 1) I think I am not doing either by your standards, it is really just > recreating the database from scratch, moving the data from old to new. > > I am scripting every object in db ABC and recreating that in (new) DB XYZ. > I then append the data from the tables in ABC to the same tables in XYZ. > Not being a guru I don't know a more efficient way to do this. > > I have 900 gbytes of RAID 6 SSD which I hold these things on. I had two > specific databases which had grown to almost 500 gigs all by themselves. > By 'shrinking' them down as described above I got the total file size for > the two back under 200g, a worthwhile task given my limited SSD space. > > > >why bother with the log in either your backups or your migration or move? > > 2) I wasn't aware that you could not have a log file. AFAIK when I create > the db the process automatically creates the db file and the log file > simultaneously. If I delete the log file (after detaching that is possible > with a 'simple' database) it automatically creates a new one. > > I pretty much come along behind and shrink the logs after major update > operations. I have been told to never shrink the data files because to do > so fragments them so I do this 'data migration' process if the data files > ever get outsized with major empty space. That doesn't happen often > because these are 'read-mostly' but once in awhile I have to do something > which balloons them up. > > > John W. Colby > Colby Consulting > > Reality is what refuses to go away > when you do not believe in it > > On 10/4/2012 1:03 PM, Arthur Fuller wrote: > >> John, >> >> A few questions. >> >> 1. What exactly do you mean by "migrate"? Do you really mean "move", or >> are >> you actually migrating, as in from one version or instance to another? >> There's a large difference between them. >> 2. Since your database is, except for the occasional mass-update, for all >> intents and purposes, R/O, why bother with the log in either your backups >> or your migration or move? >> >> Arthur >> >> > ______________________________**_________________ > dba-SQLServer mailing list > dba-SQLServer@**databaseadvisors.com <dba-SQLServer at databaseadvisors.com> > http://databaseadvisors.com/**mailman/listinfo/dba-sqlserver<http://databaseadvisors.com/mailman/listinfo/dba-sqlserver> > http://www.databaseadvisors.**com <http://www.databaseadvisors.com> > >