Hot answers tagged

15

Check if SQLServer performance counters exist in the Performance Monitor. If not, manually rebuild all performance counters running lodctr /R command with administrative priviledges: cd \windows\system32 lodctr /R


13

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)


10

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 ...


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

Go into Management studio, right click the database, Tasks > Shrink > Files. Under File Type, pick the log file. Make sure Release unused space is selected as the shrink action, click OK. As for the shrunken log file growing quickly again, it is probably because you are set to a Full recovery model. Setting it to simple will commit changes immediately and ...


8

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


7

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 ...


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 ...


6

Isn't that the default instance, for which you DO NOT GIVE A NAME IN THE CONNECTION STRING?


6

It turned out to be very simple. On that fancy download page, http://www.microsoft.com/express/Database/, they have links to SQLEXPR32_x86_ENU.exe and SQLEXPR_x64_ENU.exe, which are for their specific platforms only. However, there is a third file, SQLEXPR_x86_ENU.exe, which can be found here: ...


6

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 ...


5

If you want to prevent further growing you need to set your database logging to simple (but only if you do Full Backups of your database!). Right click on your database Choose Properties Choose Options Set Recovery mode to simple This will work and is best if your backup schedule is Full Backup every day. Because in such scenario your log won't be ...


5

Is there a way to create non-R2 databases in an R2 database server? No. Once a database has been attached to a version of SQL server, it can never be re-attached to a previous version, even if the compatibility level stays the same. Am I only stuck with installing the non-R2 SQL server to run side-by-side with my R2 database server? Yes.


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

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

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 ...


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

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.


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 ...


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

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.


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

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 ...


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

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 ...



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