I have a SQL Server 2005 instance running on a production server. CPU load is relatively low in part thanks to some recent improvements in the code. I'm trying to work out if I could safely reduce the number of CPUs in the machine and would like to test that out before actually getting them removed.
Is it possible to tell SQL Server 2005 to just use say 4 of the 8 CPUs in the machine to test this out?
Thanks, Robin
I'm connecting from a windows vista desktop to a server running windows server 2003. I'm using Remote Desktop and I'm running some very long running processes on the remote server. My problem is that Remote Desktop logs out my session and terminates any running processes after some amount of time without input from myself. This means I need to sit at my pc wiggling the mouse every now and then rather than head out and enjoy the glorious sunshine for a few hours.
Does anyone know how I disable this behavior? I assume it is configurable somewhere.
Got something strange (to me ) going on with my SQL Server 2005 database. The log file has very gradually grown huge.
Running
dbcc loginfo('dbname')
shows everything in the log is active (Status 2)
Running
dbcc opentran
shows 'no active open transactions'
so.... Not really sure what all the stuf in the transaction log is. The only glimmer of hope I've found is this post at sql server central but turning replication on and off seems a bit of a peculiar fix (sort of a variant of turn it off and on again I guess).
Any ideas what's up or what else to try/investigate?
thanks, Robin
I've been asked to doc what I think should be the breakdown of roles and responsibilities for our production system hosting the companies web application. This is partially so we an start to assign responsibility a bit more formally and so we can avoid things not slipping through the 'I thought you did that' gap. So...
I thought I'd ask you lot what you think they should be.
As evidence of some work on my part this is an overview of my current thinking. Each line is a separate role/responsibility. Although one person may have one or more roles if one role is split across multiple people it'd be reasonable to have one main point of contact for it.
There are various cross cutting responsibilities I guess (ie security) which perhaps are coordinated by someone responsible even if the work is done by several people.
Any thoughts, additions, removals or tweakings of the above. I freely admit to be somewhere outside my sphere of expertise on this stuff.
HI folks,
I've a SQL Server 2005 system, with 4 cpus in it each with 4 cores. It's currently mainly utilising just 4 cores, ie 4 hover around 90% utilisation at peak and the others closer to 30%. I'm assuming the 4 cores are one from each cpu although I'm not certain. Is this the expected behaviour does anyone know? Will increased load be distributed onto the additional cores rather than further loading onto the current 4. Is this indicative of some other issue/s I should investigate?
At peak load I'd expect in excess of 40 connections simultaneously at least, and most if not all of them will be set to allow dirty reads. For this reason I don't think it's anything to do with the work load arriving at SQL Server rather that it's how it chooses to use the cpu resources it has available.
thanks,
Robin
If I identify an anonymous attempt to hack my servers is there an appropriate response? Or is it a case of look at what they were attempting and make sure we're covered against it and similar attacks?
Cheers,
Robin
I've got a SQL 2005 database which has some maintenance plans on it set up by someone else. One runs Update Statistics on all databases early in the morning. It seems this is causing tempdb to fill up and hence breaking some other maintenance tasks which are running at the time. It's not successfully run for at least a week now. I've got Auto Update Statistics set to true though, so do I even need to be running Update Statistics every morning? There are other maintenance plans including a nightly reindex job, I don't know if having that then requires that the statistics are updated.
thanks,
I've a problem I'm investigating whereby my tempdb logfile grows out of all proportion to anything which would seem sensible. This morning it was over 10x the size of the data files. However, the database continues to function fine and the only limit on the size of the templog seems to be the size of the disk it's on.
My question then, would it be safe for me to limit the size of the templog, I have heard you shouldn't limit the size of tempdb as it is critical for work which needs to be done within sql server. But, the fact the log file grows just to the size allowed by disk suggests I could limit it without breaking anything. Is this the case? and If so, is there a lower bound (ie the combined size of the data files) I should not limit it below?
Btw, I realise that this is not a solution to the issue of the log growing, that is being investigated but may not be solved soon.
Cheers,
I've a SQL 2005 installation and my templog.ldf file keeps growing to consume all the free space on the drive it's on. Sometimes it'll stop with a few mb free but sometimes it goes further, this being the c drive I think this behaviour may be implicated in some other issues I've been seeing.
My question is, what should I do, I can move the log to another drive but I've reason to assume it won't just do the same thing there. I'm assuming that this behaviour is likely as a result of something I can change and that 45gb is an unusual size for the tempdb log to get to. We do use a lot of temporary tables and table valued functions in our code so there is plenty of scope to use tempdb, I can understand the tempdb database growing but don't understand the reason for the growth of the templog.
So far, I've ran DBCC OPENTRAN('tempdb') to see if any old transactions are hanging around, they aren't. I've read about how to shrink the tempdb and have done this a few times, but I'm really wondering what if anything I can do to stop this happening in the first place or more details on why it might be growing so much in the first place.
==EDITS==
1) The tempdb is using simple recovery model
2) The growth in templog occurs over a couple of hours in the morning when we have some scheduled queries running, basically a load of reporting which runs out of office hours for the day ahead. The size of the file steadily grows over this time. We control how many concurrent reports are running at the same time, increasing the number of concurrent reports increases the rate at which the log grows.
I've a database running on a windows server I'm monitoring with Hyperic. I see the writes per minute ramping up during the day as we approach our peak load. The peaks are increasing gradually over time as we add more users to the system. With other resources (ie CPU, Memory or Disk Space) it's pretty easy to see what the maximum would be and make sure we get additional resources ready before we run out. Is it possible to get even a rough prediction of what the maximum writes per minute is likely to be? Alternatively, do I just need to puh the number of disk writes up and see when things start failing, I assume my disk queue will be the best indicator that I'm exceeding the capabilities of the drive/array.
==EDIT==
By way of some additional details, I'm running SQL Server 2005 and the reason I'm so interested in the writes is that the writes on the disk currently outstrip the reads by about 30x. I'm assuming that writes then are likely to be a bottleneck before any other disk activity. The application it's supporting is mainly OLTP although we do report from the data in there as well.
I've been looking at an upgrade to my SQL Server 2005 Enterprise edition. I'm specifically looking to upgrade as I'm hitting the limit of my 2 CPUs at the moment. It looks like I could move to 64 bit Standard at my hosting provider for about the same I'm paying now. I was going to try adding another 2 CPUs to the set up but figured I could at least try out the 64bit Standard edition first and then add the additional CPUs if it wasn't already improved enough.
Is there anything I need to look out for with moving to a 64 bit platform? ie are there any gotchas with this migration? Looking at the feature comparison listed here I don't think I'll miss anything from the Enterprise edition. I'm not sure what Parallel Index Operations are but a little google for that string seemed to suggest it was just something to do with rebuilding indexes whilst the db is running, something I don't need to do. Is there anything not listed at that link I should be aware of?
HI Folks,
I work at a company with a handful of windows servers in production running our software as a service offering. Part way through making a bunch of changes such as creating new Databases, moving database files round, setting up logins and enabling/disabling windows services. I'm not the only person who makes changes and it's quite possible that if something goes wrong the person who'll be investigating it won't be aware of what changes have been applied recently. For the most part I don't think it's a problem as we're pretty cautious about changing anything, changes usually occur at defined times (when we're upgrading our own software etc) and problems are usually fairly easily tracked down.
However, it does occur to me that recording what changes people made, when and why might be useful, if not for tracking down issues, then for rebuilding those machines should we need to some time. How do others handle this?
I've got a SQL 2000 installation on which I've enabled AWE to address additional memory. I'm in the process of migrating to SQL 2005 and the majority of the memory will now be needed for the 2005 instance. Can I leave 2000 with AWE set up, or should I disable it?
I've got a recently upgraded SQL Server which I'm getting ready to put live, we've gone from SQL Server 2000 to SQL Server 2005. The collation setting on the 2005 box is different though (I didn't do the upgrade so not sure if something was done wrong during that process). Is is possible/advisable to change the system wide collation setting in SQL Server 2005, or should I just get the installation done again?
I've got a recently installed sql 2005 database server (an upgrade from 2000 on the same machine), I'm trying to get maintenance plans set up to reindex etc but they are constantly failing. This is the message I get, I presume some permission for some user somewhere is wrong but not really sure where I should be looking.
Executed as user: 194709-DB2\SYSTEM. ... 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:45:34 Error: 2009-07-20 12:46:05.42
Code: 0xC00291EC Source: {BDA7B017-98D2-41A4-864E-49A5A9B39855} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2009-07-20 12:46:05.42 Code: 0x80019002 Source: OnPreExecute
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2009-07-20 12:46:05.44 Code: 0xC0024104
Source: Reorganize Index Task
De... The package execution fa... The step failed.
I've been running Hyperic for a few months now and for the most part I'm pretty happy with it. I have recently noticed however that the agent process seems to hog 100% of the cpu every 15 mins for about 30 secs. The servers are windows 2003, the hyperic agent is version 4.0.3. Some googling hasn't turned up much of any use. I'd appreciate any pointers as to how I might further investigate this, or suggestions for a cure if anyone else has seen the same themselves.