26

Try this on the master database: SELECT name, value, value_in_use, [description] FROM sys.configurations WHERE name like '%server memory%' ORDER BY name OPTION (RECOMPILE); Gives you max server memory (MB) and min server memory (MB)


16

I think you're looking for alter assembly. From BOL: If the FROM clause is specified, ALTER ASSEMBLY updates the assembly with respect to the latest copies of the modules provided. Because there might be CLR functions, stored procedures, triggers, data types, and user-defined aggregate functions in the instance of SQL Server that are already ...


15

It's because I was connecting to the SQL Server locally, from the same server that hosted SQL Server. When I connect from another machine on the network, the authentication mechanism used is Kerberos, as expected. SQL Server will always use NTLM if connecting locally. Kerberos is only used if connecting remotely. This post from the SQL Server Protocols ...


14

Here's a solution I just found to this old issue: Stop all SQL services Start with -m Run MSSQL Management Studio As administrator If not started as administrator (as I wasn't), then you'll receive the generic an admin is already connect. Instead of saying you can't connect because you aren't an administrator. Another confusing error from Microsoft. I ...


9

SQL Server 2008 installation fails when you try to install SQL Server a second time How to uninstall an instance of SQL Server 2008 How to manually uninstall an instance of SQL Server 2005


9

Putting your log file on a separate volume, even if it's on the same raid, should be better performance than having the data and logs in the same volume. Each volume can only be access through one path at a time on most storage systems. This means that your logs and data would share the same controller port, controller cache, queue depth, and FC switch ...


8

Despite Microsoft saying otherwise you shouldn't quote after the -m. So for SQLCMD, you'd do -mSQLCMD. I didn't see this said anywhere, so I'll document it here. If nothing else works, try not quoting it.


8

If the site was attacked via SQL Injection, the only thing that can fix it is fixing the code.


8

As stated, SQL 2008 R2 no longer adds the administrators group as a SQL login by default. You first have to add the Administrators group as a SQL Server principal. CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS (or see this article for more information on how to add a login) You can then add that group to the sysadmin role. sp_addsrvrolemember @...


7

To add to Ben Thul's answer, this can be accomplished remotely fairly easily via SQL Server Management Studio's GUI. Under the Object Explorer for your database -> Programmability, right click on Assemblies and select 'New Assembly...'. Browse to your updated DLL. Instead of clicking 'OK' (which will fail, as an assembly of the same name already exists) ...


6

If Jason's suggestion doesn't work, restart the SQL Instance in single user mode and everyone in the local admin group should now have sysadmin rights so you can get into the system and fix the permissions.


6

Just start SQL Server with the -f switch, which will start it in a minimal, single-user mode. You should be able to change it back afterwards.


6

The space actually used by a quorum disk amounts at most to a few kilobytes, not even megabytes. There is really no reason at all to go above the suggested size. That said, creating a LUN and a NTFS volume of a few megabytes in size would be impractical for several reasons; the most common size for quorum disks in my experience is 1 GB, and even that is ...


5

IIS and SQL Server have no known incompatibilities, they are just two different services which can happily run on the same machine, provided hardware resources are adequate to support both applications at the same time, and you secure them properly. Actually, Reporting Services used to run on IIS as a web application: it's only with SQL Server 2008 that it ...


5

What are the versions of your SQL server and your remote monitor machine? If your remote monitor machine is 32 bit and your sql server is 64 bit, perhaps you cannot consume the SQL performance counters remotely. This MSDN link explained the reason.


5

Try connection to it remotely with PowerShell. This usually requires less resources then a full fledged RDP-session. Example: invoke-command -computername myServer -scriptblock {Get-Process} This way you can get a look at what's running remotely and later on, kill it.


5

Apparently, your dataset requires no more than 46GB of memory.


5

You can apply a hotfix directly to a production server - it's your choice. I'd strongly suggest testing it first, myself, and taking a full backup of the system prior to applying it. To do this of course you need to have a test environment and the problem you're trying to fix needs to be one you can work around on the live system while you carry out the ...


5

Looks like TFS 2010 isn't even supported on Windows Server 2012: http://msdn.microsoft.com/en-us/library/dd578592(v=vs.100).aspx. Anyway, an in-place OS upgrade on a server hosting SQL Server and TFS is something I'd really prefer to avoid.


5

If SQL Server Web is running as LocalSystem while instead SQL Server Express is running as NetworkService, then it's perfectly correct that SQL Server Express doesn't have write permissions on the database files, because the NetworkService account doesn't have administrative rights (although, based on the ACLs you posted, it shouldn't even have read ...


4

This question is likely to get ripped to shreds...but, do you mean a standard PC with Windows 7 from your local Supermarket, or a actual proper server with Windows 7 installed on it? If you are just buying a PC to run this, forget it. A PC, even a powerful one, is nowhere capable of running with the fault-tolerance that a server would be capable of. A PC ...


4

As Matt has stated the Windows login you are using has not been setup and you will need to add it with a login that is a member of the sysadmin fixed server role. You do not need to be a member of the sysadmin fixed server role to simply connect to your SQL Server 2008 R2 instance. BTW, during the R2 install, one is able to specify login(s) that need ...


4

is it possible MYFILEGROUP has a parition object on it? If this is the case then the compatability views your using will not return any results (sql server 2000 did not have partition objects!). The message your getting about skipping the object to check is expected behaviour for 2008 when checkdb checks a paritioned table/index , see this blog post. Do ...


4

Do i need to specify a port in this setting? Yes there needs to be a port number listed there. The port that is listed is the port that was assigned on startup. Every time the machine restarts this port number will change. If so, does it require a firewall exception or is there a better way to circumvent this issue? Setup the Windows firewall to allow ...


4

SQL Clustering will give you the high availability that you're looking for, as you'll be able to apply updates to one machine, take it down for reboots, bring it back online, and 'rinse and repeat' for the other servers in the cluster (minimum 2).


4

You really shouldn't need a pagefile more than 4-6GB except in certain circumstances. The only major drawback to having a pagefile smaller than your system memory is that in the event of a BSOD, you won't get a full crash dump. I'd test your system with 6GB, 8GB and 10GB pagefiles and see how performance is. If it's not a machine that swaps a lot, you might ...


4

A single CPU (I assume this means an actual solo CPU core) is entirely unsuited to run SQL Server, period. Since it has to perform several tasks simultaneously ( accessing memory, accessing disk, communicating over the network, computing result sets), for any reasonably modern version of SQL server you want at least 2 CPU cores, and ideally more than that. ...


4

From here: http://sqlserverpedia.com/blog/database-design/error-message-ad-hoc-update-to-system-catalogs-is-not-supported/ Either run this first: EXEC sp_configure ‘allow updates’, 0 RECONFIGURE or change your RECONFIGURE statements to RECONFIGURE WITH OVERRIDE: EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE --really reconfigure ...


4

From the documentation of SET XACT_ABORT: XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions." Perhaps you were using a provider that supported nested ...


4

Per database. The instance level collation defines the default collation for the entire instance but you can set it independently at the database level if you want to override the default. EDIT: In response to your comment: You can check the collation by looking at the database properties in SSMS (right-click the database, get properties, it's on the ...


Only top voted, non community-wiki answers of a minimum length are eligible