Stuart McLachlan
stuart at lexacorp.com.pg
Fri Sep 4 18:53:01 CDT 2009
That's essentially what it said on the page I linked to. -- Stuart On 5 Sep 2009 at 1:38, Asger Blond wrote: > Moving the tempdb is a special case. As the tempdb can't be detached you > have to use a sql like this: > > ALTER DATABASE tempdb MODIFY FILE > (name='tempdev', filename='X:\TempDBData\tempdb.mdf') > ALTER DATABASE tempdb MODIFY FILE > (name='templog', filename='Y:\TempDBLog\templog.ldf') > > Then restart sql-server to make the new placement effective. Eventually > delete the old tempdb-files. > > Asger > > -----Oprindelig meddelelse----- > Fra: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Stuart > McLachlan > Sendt: 5. september 2009 00:38 > Til: Discussion concerning MS SQL Server > Emne: Re: [dba-SQLServer] SQL Server 2005 unresponsive > > Open Management Studio > > To change the defaults: > Right click on the Server and select Properties. > Go to the "Database Settings" tab and you can define the locations for the > data and log files. > > To change the location of data and log files for any normal database. > Detach the database > Move the data and log files to anywhere you want then > Attach the database and point to the new locations for the files. > > To change the location of tempdb: > http://www.tech-recipes.com/rx/2342/sql_server_2005_move_tempdb/ > > -- > Stuart > On 4 Sep 2009 at 18:11, jwcolby wrote: > > > OK so it appears that I have a raid1 500g drive that I set up previously > and I just created a new > > 600 gb Raid 0. > > > > So now, how do I move things around? I do not see a gui way to determine > where the temp db goes, > > nor for that matter where the transaction logs go. I assume that > transaction logs go right in the > > database unless you do something specific to split them out? > > > > John W. Colby > > www.ColbyConsulting.com > > > > > > Francisco Tapia wrote: > > > It's not Sql Server ALLOWING the ODBC call to send it to turtle mode, > it's > > > ODBC, that library is inherently slow, and has problems with larger > > > datasets. > > > > > > If you are seeing slowness on your SQL Server you'll need to fine tune a > few > > > things > > > > > > > > > First check the size of of your transaction logs, whenever they exceed > 50% > > > of the data file size, then you can begin running into some performance > > > problems. (normally i see this if my transaction log file is 2x or more > > > than my data file.) > > > > > > also check the tempdb > > > > > > When you are MOVING a lot of data around, the way you are, you want to > have > > > each data partition on it's own set of spindle disks, this applies > largely > > > to your transaction logs, because you don't want to loose the data but > you > > > want the system to process extremely quick, you should look into raid 10 > if > > > you can afford it. For TempDB it's possible to squeeze a lot of > performance > > > on the disks, first tempdb is always rebuilt when the server comes back > up, > > > and all data is just temporary, so you could go with a pair of raid 0 > > > drives, possibly 3 spindles if you need the extra speed. Striping is > the > > > fastest possible solution to speeding up IO, ideally of course you'll > want > > > to have this on it's own separate i/o card and own channel if possible. > > > > > > benchmark your system by running it through the paces of the SQLIO > > > benchmarking tool from MS > > > > http://www.microsoft.com/DOWNLOADS/details.aspx?familyid=9A8B005B-84E4-4F24- > 8D65-CB53442D9E19&displaylang=en > > > and benchmark as you move towards faster drives to see the new > throughput. > > > In your case you might be easily impressed by moving the tempdb to a set > of > > > raid 0 drives. > > > > > > > > > -Francisco > > > http://sqlthis.blogspot.com | Tsql and More... > > > > > > > > > On Fri, Sep 4, 2009 at 10:12 AM, jwcolby > <jwcolby at colbyconsulting.com>wrote: > > > > > >> Jim, > > >> > > >> Thanks for the response. I have to agree, it does seem to be something > > >> ODBC / Access related in > > >> that pretty much everything I do directly inside of SQL Server works > just > > >> fine. It is annoying that > > >> SQL Server would ALLOW an ODBC call from Access to cause it to go into > > >> turtle mode though. > > >> > > >> > Have you tried .Net? > > >> > > >> Oddly enough I have, but it was some time ago. I was doing this stuff > in > > >> VB.Net / ADO, but I was so > > >> slow at the .Net development that when I needed to "get something done" > I > > >> just went back to Access. > > >> I cannot make any valid comparisons however as back when I was doing > the > > >> .Net work I also was a > > >> complete nube at the SQL Server stuff as well, couldn't even write a > SP. > > >> > > >> I am taking a C# class now and will be moving squarely into C# in the > > >> future. I have pretty much > > >> made a decision to make that my future. But in the here and now... > > >> > > >> John W. Colby > > >> www.ColbyConsulting.com > > >> > > >> > > >> Jim Lawrence wrote: > > >>> I think the issue is Access related and not MS SQL. Had exactly the > same > > >>> issues when connected to an Oracle DB. I ended up writing code that > would > > >>> save my results to a temporary table, in the Oracle DB and then would > > >> have > > >>> Access just retrieve the table results. > > >>> > > >>> The single process step of gathering the data and downloading a > > >> recordset, > > >>> via ADO would sometimes cause a hang up. If I used ODBC, it would > always > > >>> cause a hold up especially on larger data sets. > > >>> > > >>> Have you tried .Net? > > >>> > > >>> Jim > > >> > > >> > > > _______________________________________________ > > > 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 > >