Find the next information, http://www.cnblogs.com/qanholas/archive/2013/05/15/3080013.html the gods are such a standard answer, the mark should be worthy of the standard answer, write too detailed , considering a variety of situations, but spit groove ah, too much content, find useful information is very difficult, I here to filter the content, the problem is basically server configuration problems, the solution is as follows, to have a picture of
Label:Many times, deadlocks are requested by two or more sessions to hold the other session of the lock while holding other session, but there are some special deadlock only by a single session lock triggered, today saw a related article, transported to share with you! The code that caused the deadlock: Execution Environment SQL
Using SQL Server as a database application system, can not avoid the sometimes deadlock, deadlock, maintenance personnel or developers will only through the sp_who to find the deadlock process, and then kill with Sp_kill. Using Sp_who_lock, this stored procedure makes it eas
Deadlocks and blockages have always been the focus of performance test execution.Here are the monitored SQL Server databases I've compiled, whether there are deadlocks or blocked SQL statements during the performance test, and it's relatively ready to leave.--Number of deadlocks per secondSELECT * from Sys.dm_os_performance_counters WHERE counter_name like ' numb
.
status
nchar (+)
Process ID status. The possible values are: dormant= SQL Server is resetting the session. The running= The session is running one or more batches. When multiple Active Result sets (MARS) is enabled, a session can run multiple batches. For more information, seeusing multiple Active Result sets (MARS). Background= The session is running a background task,
Label:Method One: With Cte_sid (Bsid, SID, sql_handle) As (SELECT blocking_session_id, session_id, Sql_handle From sys.dm_exec_requests WHERE blocking_session_id UNION All SELECT a.blocking_session_id, A.SESSION_ID, A.sql_handle From Sys.dm_exec_requests A JOIN cte_sid B on a.session_id = B.bsid ) SELECT C.bsid, C.sid, S.login_name, S.host_name, S.status, S.cpu_time, S.memory_usage, S.last_request_start_time, S.last_request_end_time, S.logical_reads, S.row_count, Q.text From Cte_sid C JOIN sys.
,net_address,S1=a.spid,s2=0From Master. sysprocesses a Join (Select blocked from master. sysprocesseswhere blocked>0and (@dbid is null or [email protected])Group by blocked) B on a.spid=b.blockedwhere a.blocked=0and (@dbid is null or [email protected])UNION ALLSelect ' |_ victim _> ',Spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,Status,hostname,program_name,hostprocess,nt_domain,net_address,S1=blocked,s2=spidFrom Master. sysprocesses Awhere blockedand (@dbid is null or [email prot
Tags:whileentrycas loops serve Blocking selectobjecttran Use [master] GO/****** Object: storedprocedure [dbo]. [Sp_who_lock] script date:07/30/2015 13:52:38 ******/SET ansi_nulls on Go SET quoted_identifier on Go ALTER procedure [dbo]. [Sp_who_lock] as BEGIN declare @spid int, @bl int, @intTransactionCountOnEntry int, NBS P @intRowcount int, @intCountProperties int, @intCount ER int CREATE TABLE #tmp_lock_who (ID int identity (SMALLINT,BL), spid smallint) IF @ @ERROR
server| Data | database | database connection | problem
For "Unable to connect with TCP/IP protocol and remote SQL Server database" issue
First, it is important to determine your version of SQL Server and what patches it has i
1. Start SQL Server Management Studioand you will see2, there is an authentication. There are two options in this authentication drop-down list:Windows authentication and SQL Server authentication .What is the difference between them:Windows Authentication means: You can log in using the user name and password of your
=89845dcd8080cc91 "or one of its dependencies. The system cannot find the file specified. (MICROSOFT.SQLSERVER.DMF)Workaround: Go online to download a Microsoft.SqlServer.Diagnostics.STrace.dll copy to C:\Program Files\Microsoft SQL Server\100\tools\binn\ Vsshell\common7\ide this directory.Download Microsoft.SqlServer.Diagnostics.STrace.dll's address: http://files.cnblogs.com/files/Hackerman/ Microsoft.SqlS
the result is not NULL but 1, the 3rd row of the SELECT statement root is not assigned to @id, the reason is very simple, because we said the 3rd row of the SELECT statement returns how many records, the @id will be assigned how many times the value, Now that it does not return a row of records, it will not be assigned a value for @id, so the final @id is still the initial value-1Now let's change the code to the following, assigning the aggregate value after the query result sum to the variable
The computer re-installed sqlserver2008 R2 (English), New data table, new field, found that there is a field type set wrong, want to modify the field type, and the table has been saved, that is, after saving to modify the field properties. However, the modified settings cannot be saved, prompting "saving changes is not permitted" (specifically)Workaround:Find the Tools->options in the menu bar, find the designers, and then prevent saving changes that require table re_creation front of the tick,
1. Open "SQL Server Configuration Manager"2. Click "SQL Server Services" on the left pane3. Right-click on your SQL Server instance name in the right pane->default:sql Server (MSSQLSERV
Original: SQL Server 2012 exception Issue (ii)--performance issues caused by installation mediaProblem Description: Production environment A database is upgraded from SQL Server R2 to SQL Serv
functionexec sp_configure ' show advanced options ', 1Reconfigureexec sp_configure ' Ad Hoc distributed Queries ', 1Reconfigure--Allow ace.oledb.12 to be used in the processEXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' allowinprocess ', 1--Allow dynamic parametersEXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' dynamicparameters ', 1--Import temporary tablesEXEC (' INSERT into Jihua (id,[lot number],right (' ' + @filepath + ', charindex (' ' \ ',
fromSys.dm_sql_referencing_entities ('dbo. Mytabletype','TYPE'); OPENRef_cursor; FETCH NEXT fromRef_cursor into @Name; while(@ @FETCH_STATUS = 0)
BEGIN
EXECSys.sp_refreshsqlmodule@name = @Name; FETCH NEXT fromRef_cursor into @Name; END;
CLOSERef_cursor;deallocateRef_cursor;GO
--finally delete the original renamed Tabletype (the one with the same name as the first step)
IF EXISTS(SELECT 1 fromsys.types TJoinSys.schemas s ont.schema_id=s.schema_id andT.name='Obsoleting_mytabletyp
schema (environment in the code above) is used, so the third parameter of the sp_addextendedproperty stored procedure should be schema rather than user. Query related data found that the above SQL statement is based on the corresponding. xdb file generated by PowerDesigner, as the SQL code template for the column comment information, the corresponding XDB template file is stored in the PowerDesigner instal
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.