Tag Info

Hot answers tagged

8

If xp_cmdshell is an option, here is a script making use of whoami: DECLARE @LockPagesInMemory VARCHAR(255); SET @LockPagesInMemory = 'UNKNOWN'; DECLARE @Res TABLE ( [output] NVARCHAR(255) NULL ); IF (SELECT value_in_use FROM sys.configurations c WHERE c.name = 'xp_cmdshell' ) = 1 BEGIN INSERT INTO @Res EXEC xp_cmdshell 'WHOAMI ...


5

Is this good practice? In this circumstance I would say yes. I'd probably also add an OPTION (RECOMPILE) to let it "sniff" the variable values. The optimal plan will likely vary dependant on the proportion of rows in the larger table that match this range. It provides a potentially useful extra path to the optimiser and it is not something that the ...


5

Join Employees table's unique ID with inserted in trigger. --- Employees table CREATE TABLE Employees ( EmpID INT, --- unique id for employee Salary INT ) --- Dummy data insertion INSERT INTO Employees SELECT 11001,5000 UNION ALL SELECT 10003,6500 UNION ALL SELECT 10004,6900 UNION SELECT 10006,300 -- Salary Trigger CREATE TRIGGER ...


5

I think the script in question is sp_BlitzIndex. The script lists tables that have more than 3 columns, and the number of non-nullable columns is 1 or 0. It doesn't mean that those tables are bad-- but if you see a lot of rows for this, it's just there to raise the question: Is it valid for those columns to all really contain nulls? When tables are ...


4

From View or Change the Default Locations for Data and Log Files: In Object Explorer, right-click a server and click Properties. In the left panel, click the Database settings page. In Database default locations, view the current default locations for new data files and new log files. To change a default location, enter a new default pathname ...


4

Unfortunately no, you can't exclude anything from a backup. If you are talking about Windows Logins then it probably isn't that big a deal. The SIDs won't match anything on their network (very unlikely anyway) so all they will have is the usernames without any of the permissions associated. SQL Logins of course will work fine as long as they create a ...


4

Yes, as ypercube already said. The only thing to look out is a wild discrepancy in the column actual data size. For instance if NC_Index1 is some 1Mb, and NC_Index2 is 200GB then you can introduce some potential scan perf problems. But, frankly, the chances of this being the case are, basically, 0. Or NULL, depending on your prefs.


4

NO, With just Execute permission doesn't allow a user to execute a stored procedure successfully if it contains some DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME) statements.


3

Use module signing. Sign your procedures with a certificate and then grant the required permissions to a certificate-derived user. The link contains a full example.


3

Your values being inserted into the Movies table do not match the foreign key values in Actors. For example, you have ActorID values from 1 - 9 but are trying to insert a value of 10 for your first row in Movies: (1,'The Dark Knight','2008','PG-13','4', 4,'Christopher Nolan', 4 + 6,'Christian Bale' + 'Heath Ledger'), 4 + 6 will result in an ActorID of 10 ...


3

Anyone ever see Cost Threshold for Parallelism being ignored? It is not being ignored. During the compilation process, the optimizer first considers a serial plan. If the estimated cost of that plan exceeds the Threshold, the optimizer goes on to look for a parallel plan. If the resulting parallel plan is costed below the best serial one, it will be ...


3

First, just a little terminology thing: you do want your trigger to fire, since you are updating Employees; you just don't want the IF statement's condition to be satisfied. Now on to the problem. When you list tables in a FROM clause like this: FROM inserted, deleted you are actually creating a CROSS JOIN between the tables. So this SELECT subquery ...


2

Backup from PROD with COPY_ONLY, compression and restore it on DEV server is the best option. Make sure to enable Instant File initialization to cut down the restore time. Edit: I don't have a permissions to restore database. is it safe to use copy database ? This might imply that you are not authorized to do such operation. You can ask your DBA to ...


2

How can it automatically change host of one if the other one was dropped ? From the client application side, your application connection string should use FailoverPartner pointing to secondary or mirrored server. If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the ...


2

Automatic failover requires a witness. See Database Mirroring Witness: To support automatic failover, a database mirroring session must be configured in high-safety mode and also possess a third server instance, known as the witness. The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to ...


2

Some details to clear things up Based on that msdn page : server memory settings it's a good practice to keep the default values for SQL-Servers min and max memory to keep it dynamic. No, its not written its a good practice its said its recommended and I am sure MS books online cannot write it as good this because of fact that its always good to have ...


2

The MIN value is useful when you have a server with multiple instances. You can use it to prioritise memory usage per instance. But mainly it is to guarantee that SQL with have at least that much memory to work with. This stops people running something on the server and stealing all the memory. The default setting basically leaves memory allocation to SQL ...


2

I'm pretty sure that sp_BlitzCache will show you the cost of the actual plan used for your queries, whereas the server will considers a parallel plan when the initially estimated cost exceeds the threshold value. A noticable difference between estimated and actual query plan costs could happen if you have stale/bad statistics on your tables. If you've ...


2

Assuming these are not contained databases, you need to worry about the logins (server level), not the users (database level). Contained users will be transferred automatically without difficulty. Manually creating a login with the same name on the other server is not good enough - the SID will not match. Robert Davis has written a post that describes how ...


2

Based on your comment about the "unwieldy" SP I'd almost guarantee that is at least part of your problem. Try running the following query replacing DBName and SPName as appropriate. USE DBName GO SELECT SUBSTRING(text, statement_start_offset/2, CASE WHEN statement_end_offset = -1 THEN 999999 ELSE ...


2

There are other methods as well. Perhaps you can use two DMV's. Please note that both will only work for SQL Server 2008 and above A non zero value for locked pages allocation KB would tell that SQL Server account has Locked pages in memory privilege select osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb from ...


2

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive. Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my ...


2

No. Full recovery model is a prerequisite of AlwaysOn Availability groups as per the check list here, and minimally logged operations are only available under Simple or Bulk Logged recovery. Quote from the Data Loading Performance Guide: Minimally logged operations are available only if your database is in bulk-logged or simple recovery mode. Re ...


2

Converting my comment as answer : You have to use undocumented fn_dblog Begin Time, SUSER_SNAME([Transaction SID]) or fn_dump_dblog -- when reading from T-log backups. Be careful and try it on a NON PROD system as every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be ...


2

Start SQL Server with the -m parameter, specifying the application name allowed to take the only available connection. For instance, to allow sqlcmd to take the connection: sqlservr -m"sqlcmd" If you want to work with SQL Server Management Studio, use this: sqlservr -m"Microsoft SQL Server Management Studio - Query" Remember that the "Connect..." ...


2

While I, like @Thomas, completely agree with @Aaron in the comments on the question regarding the concerns of "per-database CPU usage" being either accurate or useful, I can at least answer the question as to why those two queries are so different. And the reason why they are different will indicate which one is more accurate, though that higher level of ...


1

Converting my comment to answer : You can use SQLCMD.exe -S <AG listener> -D <AG database> -E. You may want to specify AGlistener,port_no to see if you can connect to the listener.


1

I found this article here. There it states that: To resolve that problem you have 3 options: Run SQL queries from data server (you need to be remotely connected to the database server) Enable use of Kerberos on the database server Set proxy account for linked server, so that MDX queries are executed in its context instead of in context of the user that ...


1

You can create a new schema and expose the objects you need with a synonym: USE master; GO IF DB_ID('testDatabase') IS NOT NULL BEGIN ALTER DATABASE testDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE testDatabase END GO CREATE DATABASE testDatabase GO USE testDatabase; GO -- test table in schema dbo CREATE TABLE dbo.test ( ...


1

I would suggest moving all clients to the domain and removing the SQL logins as the AD route is much more secure. If this is not an option then I would probably remove the AD logins and have RDP users use SQL logins as having two logins per user doesn't make much sense. If for some reason you decide you have to go the two login route I would write a stored ...



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