New answers tagged

0

My suggestion is to review the ignore list in the publish options. Things like case sensitive/insensitive or columns order in tables can generate this kind of thiing. Verify with visual studio compare (if it is possible) with the same publishing options what is happening to highlight codes differences. What happens if you deploy your dacpac a second time? ...


1

With groups it should be work in the same way as single user. You must have in place proper spn to grant kerberos delegation between the two instances. Maybe you have tried from ssms so you haven't done the two hops for authentication. In that way NTLM is granting you permission but when you are using an application you will need to grant authentication on ...


0

Seeing a Worktable in your statistics means the execution of this query resulted in TempDB being leveraged (as you've noticed). Deriving why there's one there (even with no IO use) is a difficult thing to do especially without seeing your query and actual execution plan. Paul White actually even mentions it's not very fruitful to look at IO statistics for ...


0

On mobile so short for now. Look at memory grant. Instead of selecting and sorting all columns, select only needed primary keys into temp table. Then do a join again to get all relevand columns.


7

Create an "alias" database with the name you want to use. Create synonyms for the objects by querying the system tables to create a simple script, here for example for user tables. select 'Create Synonym ' +quotename(object_schema_name(id, db_id()))+'.' +quotename(o.name)+ ' For [SourceDB].' +quotename(object_schema_name(id, db_id()))...


2

Now, I know that the query came form DB server 1 with ProgramName Microsoft SQL Server. Now I am confused whether the program Microsoft SQL Server is indicating the link server query or application query using ORM. The host name "DB server 1" along with the "Microsoft SQL Server" program name indicates a linked server query, not a query ...


1

I believe your CTE already gives you the list of all distances between any two Cities, so you just need to join the CTE to itself on reversed City fields to get the reverse results like so: WITH CityDistances AS ( SELECT CITYA_id, CITYA, CITYB_id, CityB, DIS_IN_KM FROM DB.dbo.DISTANCE d INNER JOIN cities ...


1

Try using the TempDBInfo procedure, it shows what currently is inside TempDB's files and who is using it most https://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/TempDB%20-%20TempDBInfo.sql Procedure is not fully finished, I am going to rework the way it shows summary information, and also I am planning to add logging capabilities But ...


0

If corruption is on clustered index rebuilding index is not going to fix corruption. Because clustered index is table itself. Yes I agree it MIGHT work for non clustered index. Imzyrk, As suggested you can use TABLOCK hint to run check db but please also note this can cause grave concurrency issues so please run it at time when load is relatively very less. ...


5

Another idea using Extended Events. Create an extended events session to catch errors CREATE EVENT SESSION [Track_Errors] ON SERVER ADD EVENT sqlserver.error_reported( ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text)) ...


5

When my stored procedures run other stored procedures, how can I find out which stored procedure's code generated the error? In your CATCH block, you can use ERROR_PROCEDURE() to get the name of the procedure or trigger where the error occurred. If you simply use THROW in your CATCH block in the inner procedures, then your outer procedure can access these ...


5

Use @@procid to get the currently executing routine and object_name() to convert the id to a name Hth


2

What you are you seeing is by design for tempdb Transaction Log file. I wish Microsoft had better documentation on this. How is the transaction log growth increasing upto 80% and then lowering down back to 0 and then increasing and growing and so on , with reaching almost max 90-95%. From the First reference article: Tempdb is not recovered in the event ...


0

You can use the system stored procedure sp_spaceused to get the size of the Table which is going to be the size of your clustered index since the clustered index is the logical storage of the Table itself. Example syntax: EXEC sp_spaceused 'UserActions'; Specifically you'd want to look at the data column of the result set since that will tell you the total ...


0

Interesting that you don't see any running transactions but it continues to grow, as there has to be an underlying cause. Have you explored the last piece of advice Aaron mentions in that answer you linked? You may also consider that your tempdb log usage may be caused by internal processes that you have little or no control over - for example database mail,...


0

Right-click on the instance Click on properties Click on Security Click "Sql Server and Windows Authentication mode" Now click on the Security Tab Under the Database and expand on logins right-click on the sa and go to properties provide the password Endable Enforce Password Policy Click on status and click on login enabled MOST IMPORTANTLY RESTART ...


Top 50 recent answers are included