jwcolby
jwcolby at colbyconsulting.com
Wed Jun 10 10:54:56 CDT 2009
Is there a generic setting to tell SQL Server to place log files on a specific drive? Is it by the database? How is this done? I am going to set up a RAID 1 pair of 512 gig drives just to put the log files on, but I do not know how to specify that logs should go there. My business has me setting up a new database for each order. In fact what I actually do is copy an existing "template" database, renaming it to the order name. The log files can get pretty large (tens of gigs) depending on the order and processing involved. it would be nice not to have to remember to tell SQL Server to place the logs in a specific place each time I create a database. John W. Colby www.ColbyConsulting.com Francisco Tapia wrote: > excellent, I generally never put a page file on my db drive because I don't > ever want any bandwidth stolen from that partition, I also do not place one > on my transaction log drive. > > my experience with ssd has been limited, but I have found that those drives > perform extremely fast but only on smaller files (smaller than 3-4gb). if > most of your work files are less than 3gb i have found it to be super fast > :) almost like loaded in ram fast :). > > > -Francisco > http://sqlthis.blogspot.com | Tsql and More... > > > On Wed, Jun 10, 2009 at 5:58 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > >> Francisco (and everyone trying to help), >> >> Thanks for pointing me back to the pagefile. I had just rebuilt this >> server and the first thing I >> did was go in and set the page file size... to... 1200 mbytes. Uhh.... >> ooops. That should be 12 >> gbytes (12000 mbytes). I had inadvertently left off a zero. >> >> This will very likely explain the whole problem. >> >> I upped that page file to 12000 mbytes and created a second page file on >> the same volume as the >> database is on. If I shouldn't do that (same volume as the db) let me know >> but I have read >> somewhere to create multiple page files and let Windows take it's pick. >> >> BTW, I have a very fast 32 gig SSD which I am using only 8 gigs of (working >> disk for virtual >> machines). I have read about issues using SSDs but have no direct >> experience using them for writing >> to - my VMs only read from the drive. So I could even place a pagefile out >> on the ssd. >> >> BTW, I just did the same update hash fields operation on 50 million records >> in 21 minutes that it >> took 3.5 HOURS to do yesterday on about 28 million records. The page file >> size error was definitely >> crippling the system. >> >> John W. Colby >> www.ColbyConsulting.com >> >> >> Francisco Tapia wrote: >>> But the issue that John is describing is that his entire system is locked >> up >>> for up to a minute. I am thinking that the problem lies within the i/o >>> setup. On my servers here (the massive systems) they are all setup where >> I >>> have my mirror drive on one card, and the page file on a 2nd channel on a >>> different set of drives. >>> my transaction log is an fiber channel on one volume, as is the database >> and >>> the tempdb. >>> >>> I am curious if you could move the pagefile over from the c drive to >> another >>> set of drives(if you have them) to test out this theory. >>> >>> >>> >>> >>> >>> -Francisco >>> http://sqlthis.blogspot.com | Tsql and More... >>> >>> >>> On Tue, Jun 9, 2009 at 11:36 AM, David Emerson <newsgrps at dalyn.co.nz> >> wrote: >>>> John, >>>> >>>> One area I have found this to happen is record locking. If I have a >>>> table open and run a query that accesses the table then SQL seems to >>>> wait a long time (often until I close the table). >>>> >>>> David >>>> >>>> At 10/06/2009, you wrote: >>>>> All of them are in the same raid card. The OS is on a raid 1 >>>>> pair. The page file is on the C: drive. >>>>> >>>>> The database is all on a single raid 6, even the same volume. That >>>>> is what the questions I was >>>>> asking were aimed at but I didn't really get much response on how to >>>>> spread things around so I just >>>>> did what I know. >>>>> >>>>> I have a set of 6 terabyte drives, which taken together gives me two >>>>> 2 tbyte volumes, raid 6. I >>>>> find it difficult to imagine that the disk stuff would cause the >>>>> server to lock up tight when... I >>>>> am running a query (yes it is large) and I click on another database >>>>> to just look at the views >>>>> contained in that database, or open a stored procedure to see what >>>>> it looks like. >>>>> >>>>> It just seems like that should be instantaneous. I have told SQL >>>>> Server to leave 3 gigs of ram >>>>> available (only take 5 of the 8 gigs). It appears that SQL Server >>>>> does indeed respect that because >>>>> the size of the committed memory is only 5 gigs, and increases if I >>>>> go in and increase that number >>>>> while the query is running. >>>>> >>>>> So in theory I have 3 gigs available for the OS and other apps. The >>>>> processors are idling. And yet >>>>> it does not matter what I do, from look at parts of the databases to >>>>> trying to open Excel or Access, >>>>> it just takes 60 seconds or more to respond. >>>>> >>>>> I have no idea how to troubleshoot this, but the one constant is >>>>> that I do not have this issue >>>>> unless SQL Server is busy. >>>>> >>>>> John W. Colby >>>>> www.ColbyConsulting.com >>>>> >>>>> >>>>> Francisco Tapia wrote: >>>>>> Are these large queries? What's your disk distribution setup like? >>>>>> Where is the OS disk what channel on your raid card, Where is >>>>> your Page File >>>>>> on what channel / Raid Card, where is your DB, what chan... well >>>>> you get the >>>>>> picture... >>>>>> >>>>>> If it's all processing via the same raid card I'm afraid you may have >>>> to >>>>>> troubleshoot that part. There is absolutely no reason your server >>>> should >>>>>> hang while running a view, unless there are problems with the view, or >>>> the >>>>>> design on your I/O layout. >>>>>> >>>>>> >>>>>> -Francisco >>>>>> http://sqlthis.blogspot.com | Tsql and More... >>>>>> >>>>>> >>>>>> On Tue, Jun 9, 2009 at 4:56 AM, jwcolby >>>>> <jwcolby at colbyconsulting.com> wrote: >>>>>>> Why does SQL Server "lock up" when I am running a query and I try to >>>> do >>>>>>> something such as view the >>>>>>> design of a view on a completely different view? >>>>>>> >>>>>>> BTW SQL Server has been limited to 5 gigs out of 8 gigs total memory. >>>>>>> >>>>>>> It seems that SQL Server should be able to do things like this with >>>>>>> absolutely no hesitation. Yet >>>>>>> when I am running a large query and I try to do anything else, >>>>> such as view >>>>>>> the tables or views, >>>>>>> view the SQL in a stored procedure etc., even in a completely >>>> different >>>>>>> database, the entire system >>>>>>> just locks up for a long time, as in a minute. The odd part is that >>>> the >>>>>>> query isn't even using much >>>>>>> cpu. I have a quad core and the total cpu usage in task manager says >>>> 0%, >>>>>>> yet I can't get anything >>>>>>> else to work. Even something like saving a change to an Access >> module >>>> and >>>>>>> closing Access takes a >>>>>>> full minute. >>>>>>> >>>>>>> It is truly annoying. I often have to do other things on the >>>>> machine while >>>>>>> queries run and it is >>>>>>> just impossible to get anything else done. >>>> _______________________________________________ >>>> dba-SQLServer mailing list >>>> dba-SQLServer at databaseadvisors.com >>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>>> http://www.databaseadvisors.com >>>> >>>> >>> _______________________________________________ >>> dba-SQLServer mailing list >>> dba-SQLServer at databaseadvisors.com >>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>> http://www.databaseadvisors.com >>> >>> >> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >