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

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

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

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


5

To check for a specific effective permission on a securable, use HAS_PERMS_BY_NAME. For example, to show if the current security context has SELECT permission on the Production.Product table in the AdventureWorks sample database: SELECT HAS_PERMS_BY_NAME ( N'Production.Product', N'OBJECT', N'SELECT' ); Related: To ...


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

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

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

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


3

Use script instead to check status of currently running job. SELECT job.name, job.job_id, job.originating_server, activity.run_requested_date, DATEDIFF(MINUTE, activity.run_requested_date, GETDATE()) as Elapsed, case when activity.last_executed_step_id is null then 'Step 1 executing' else ...


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

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

You just need to deny EXECUTE permissions on any object that you don't want the app login to see. Denying VIEW DEFINITION will only prevent the code of the object from being seen by that user. That is the point of VIEW DEFINITION: to allow users to run code that they can't see the code for. Denying EXECUTE on the object will removing it completely as far as ...


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


2

The spatial index, as with other objects, have their own script <object> as menus and need to be done separately. This can be done to the clipboard and pasted to the query window generated by the script table as command. I can't confirm at the moment, but I suspect that the primary key and other constraints come out in the script table as options ...


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



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