[dba-SQLServer] SQL Server 2005 unresponsive

Asger Blond ab-mi at post3.tele.dk
Fri Sep 4 18:38:42 CDT 2009


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





More information about the dba-SQLServer mailing list