#Kill multiple sessions T-SQL

[thedbahub]

A T-SQL script for killing multiple SQL Server sessions

A T-SQL script to kill multiple SQL Server sessions efficiently involves querying the sys.dm_exec_sessions view to identify active sessions based on criteria like user name or database name. This script demonstrates how to dynamically construct and execute KILL statements for those sessions, offering a practical solution for database administrators to manage server resources by terminating…

[deplloyer]

DEA 2.0: Analysis FAQ  

 What security permissions do I need to generate and view an analysis report on my server?

The current logged in user in DEA should have sysadmin privilege to the analysis server.  If the user is part of a group permission, make sure the group has sysadmin privileges as well.  

Possible Errors: Explanation / Solution: “Unable to connect to the database. Please ensure you have sysadmin privileges for analyzing and viewing the reports.”  You may not have access or not have sysadmin permissions to the server and/or database.  Confirm your login permissions and privileges and try again. “Unable to generate Report Name on server Server Name. For details, check the Report Name report.”  You may not have sysadmin privileges needed to generate a new report.  Detailed errors can be found by clicking on the errored out report (see #4) and by checking the logs found at %temp%\DEA. “The current user doesn’t have the required permissions to perform the operation. Please ensure you have sysadmin privileges for performing trace and analyzing the reports.”  You do not have sysadmin privileges needed to generate a new report. 

I am unable to connect to the SQL Server.

(A)   Confirm the SQL Server name is valid.  To confirm, try connecting to the server using SSMS. 

(B)   Confirm your firewall configuration is not blocking connections to SQL Server. 

(C)   Confirm user has the permissions listed in #1.  

Further details can be found in the logs at %temp%\DEA.  If the problem persists, contact the product team.

Possible Errors:  Explanation / Solution:  “Unable to connect to the SQL Server instance, please ensure the server name is correct and have required access for the current logged in user. | Sql Error Code (53).”  You may not have access/privileges to server or the server name may be incorrect.  

  Can I create a new analysis report while another report is being generated?

No.  Currently, only one report can be generated at a time to prevent conflicts. You can, however, run multiple captures and replays at once. 

I am hitting an error when generating an analysis report.

(A)   First time generation of an analysis report after installation of DEA requires internet access to download packages needed for statistical analysis.  

(B)   If an error occurs during creation of the report, the progress page will show the specific step at which analysis generation failed.  Further details can be found in the logs at %temp%\DEA.  You should check if you have a valid connection to the server with the appropriate permissions and retry.  If problem persists, please contact the product team.

Possible Errors:  Explanation / Solution:  “RInterop hit an error on startup. Please check RInterop logs and try again.“  DEA requires internet access to download dependent R packages. Check RInterop logs at %temp%\RInterop and DEA logs at %temp%\DEA.  If RInterop was initialized incorrectly and/or without the correct R packages, you may see an exception “Failed to generate new analysis report.” after the InitializeRInterop step in the DEA logs. 

The RInterop logs may also show an error similar to “there is no jsonlite package available”. If your machine does not have internet access, you can manually download the needed jsonlite R package with the following steps:

A) Navigate to the location where R.exe was installed (usually in C:\Program Files, or similar).

B) Open a command prompt here and type >R or >R.exe to start an R session.

C) Run >.libPaths() to get a list of library paths. Use the command ‘rownames(installed.packages())’ to list all available packages from all libPaths.

E) If jsonlite package is missing, run >install.packages(“jsonlite”,repos=”http://ift.tt/1KlpBY1;, dependencies=TRUE)

If RInterop was initialized and set up correctly, you should see “Installing dependent R package “jsonlite”” in the RInterop logs.

“Unable to connect to the SQL Server instance, please ensure the server name is correct and have required access for the current logged in user.“  You may not have access/privileges to server or the server name may be incorrect.   “RInterop process timed out. Please check DEA and RInterop logs, kill RInterop process from task manager and try again.”  

OR 

 “RInterop is in faulted state. Please kill RInterop from task manager and try again.” 

Check logs at %temp%\RInterop to confirm the error and remove RInterop process from the Task Manager before trying again.  If problem persists, contact the product team.

The report is generated, but data appears to be missing.

Check the database on the analysis SQL Server to confirm that data exists.  Check analysis database exists and check its tables (eg. TblBatchesA, TblBatchesB, and TblSummaryStats).  If data does not exist, the data may not have copied over correctly and/or database may be corrupt.  If only some data is missing, the trace files created in capture/replay may not have captured your workload accurately. If data is there, then further check log files at %temp%\DEA to see if any errors were logged. Then try regenerating the analysis report.  

What does my analysis report tell me?

Using statistical tests, DEA analyzes your workload and determines how each query performed from Target 1 to Target 2, with performance details for each query.  Learn more about DEA here.

I upgraded DEA to version 2.0, can I still view and use my old reports?

Yes.  To view previously generated reports, you will need to update the schema of the report.  Learn more here. 

Can I generate an analysis report using command line?

Yes.  You can generate an analysis report on the command line.  You can then view it using the UI.  Learn more here.

Further questions or feedback?  Submit feedback through the DEA tool by clicking on the ‘smiley’ in the lower left corner.    

from Microsoft Data Migration Blog http://ift.tt/2mZssBA via IFTTT