In my introduction to Extended Events blog, I mentioned that the xEvent is my favorite toy for performance troubleshooting. This blog may make you to start playing with it. In this tip, I will be discussing how you can find what caused SQL Server to write error in the Errorlog file.
Problem:
One of the clients reported that SQL Server was kept writing below error into the SQL Server Errorlog file, but they were not able to find the reason what causing SQL Server to write the error.
“Error [36, 17, 145] occurred while attempting to drop allocation unit ID XXXXXXXXXXX belonging to worktable with partition ID XXXXXXXXXXX.”
Solution:
To find the reason for the error, I performed a thorough health check-up of the instance but didn’t see anything abnormal. So, I did quick google and scanned through the following blogs to find out the solution but no lock
- msdn – Error[36, 17, 145] occurred while attempting to drop allocation unit
- sqlservercentral – Error [36, 17, 145] occurred while attempting to drop allocation unit
- sqlservercentral – Error [36, 17, 145] occurred while attempting to drop allocation unit ID xxx belonging to worktable…
- dba.stackexchange.com – Error [36, 17, 145]occurred while attempting to drop allocation unit ID belonging to worktable with partition ID
Since I could not find any clue from above blogs, I used xEvent to create one event session on the server to collect the sqlserver.error_reported and sqlserver.errorlog_written.
- sqlserver.error_reported: This event gets fired every time when an error happens on the SQL Server.
- sqlserver.errorlog_written: This event gets triggered when SQL Server error log is written to.
CREATE EVENT SESSION [Capture_SQLReport_Error]ON SERVER ---- You can change name as you want. ADD EVENT sqlserver.error_reported ------------------------- Any error reported by SQL Server (ACTION (package0.last_error, sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash, sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) ), ADD EVENT sqlserver.errorlog_written------------------------- Any error written by SQL Server in the error log (ACTION (package0.last_error, sqlserver.database_id, sqlserver.nt_username, sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.username ) ) ADD TARGET package0.event_file (SET filename=N'Capture_SQLReport_Error.xel', --------Mention the file name which you want to create r max_file_size=(600) --------at default error log folder to save the output ) WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF) GO
The xEvent helped to capture what was causing to generate the error in the SQL Server Errorlog.
Happy Learning!
The post Capture SQL Server Reported Errors using Extended Events appeared first on .
Microsoft SQL Server Ошибки
Useful links
- System Error Messages
- Database Engine Error Severities
- Integration Services Error and Message Reference
- View and Read SQL Server Setup Log Files
- Troubleshoot the SQL Server Utility
- Common Issues: Licensing Errors
- SQL Server 2016 Distributed Replay Errors
- The Instance ID MSSQLSERVER Is Already In Use
- SQL Server: Detach/Attach Gotchas!
SQL Server All Errors List
SELECT message_id, severity, text
FROM sys.messages
WHERE language_id = 1033; /* assuming US English */
Your language_id
you can find in
sys.syslanguages
system view, column msglangid
:
langid | dateformat | datefirst | upgrade | name | alias | months | shortmonths | days | lcid | msglangid |
---|---|---|---|---|---|---|---|---|---|---|
0 | mdy | 7 | 0 | us_english | English | January,February,March,April,May,June,July,August,September,October,November,December | Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec | Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday | 1033 | 1033 |
1 | dmy | 1 | 0 | Deutsch | German | Januar,Februar,März,April,Mai,Juni,Juli,August,September,Oktober,November,Dezember | Jan,Feb,Mär,Apr,Mai,Jun,Jul,Aug,Sep,Okt,Nov,Dez | Montag,Dienstag,Mittwoch,Donnerstag,Freitag,Samstag,Sonntag | 1031 | 1031 |
2 | dmy | 1 | 0 | Français | French | janvier,février,mars,avril,mai,juin,juillet,août,septembre,octobre,novembre,décembre | janv,févr,mars,avr,mai,juin,juil,août,sept,oct,nov,déc | lundi,mardi,mercredi,jeudi,vendredi,samedi,dimanche | 1036 | 1036 |
3 | ymd | 7 | 0 | 日本語 | Japanese | 01,02,03,04,05,06,07,08,09,10,11,12 | 01,02,03,04,05,06,07,08,09,10,11,12 | 月曜日,火曜日,水曜日,木曜日,金曜日,土曜日,日曜日 | 1041 | 1041 |
4 | dmy | 1 | 0 | Dansk | Danish | januar,februar,marts,april,maj,juni,juli,august,september,oktober,november,december | jan,feb,mar,apr,maj,jun,jul,aug,sep,okt,nov,dec | mandag,tirsdag,onsdag,torsdag,fredag,lørdag,søndag | 1030 | 1030 |
5 | dmy | 1 | 0 | Español | Spanish | Enero,Febrero,Marzo,Abril,Mayo,Junio,Julio,Agosto,Septiembre,Octubre,Noviembre,Diciembre | Ene,Feb,Mar,Abr,May,Jun,Jul,Ago,Sep,Oct,Nov,Dic | Lunes,Martes,Miércoles,Jueves,Viernes,Sábado,Domingo | 3082 | 3082 |
6 | dmy | 1 | 0 | Italiano | Italian | gennaio,febbraio,marzo,aprile,maggio,giugno,luglio,agosto,settembre,ottobre,novembre,dicembre | gen,feb,mar,apr,mag,giu,lug,ago,set,ott,nov,dic | lunedì,martedì,mercoledì,giovedì,venerdì,sabato,domenica | 1040 | 1040 |
7 | dmy | 1 | 0 | Nederlands | Dutch | januari,februari,maart,april,mei,juni,juli,augustus,september,oktober,november,december | jan,feb,mrt,apr,mei,jun,jul,aug,sep,okt,nov,dec | maandag,dinsdag,woensdag,donderdag,vrijdag,zaterdag,zondag | 1043 | 1043 |
8 | dmy | 1 | 0 | Norsk | Norwegian | januar,februar,mars,april,mai,juni,juli,august,september,oktober,november,desember | jan,feb,mar,apr,mai,jun,jul,aug,sep,okt,nov,des | mandag,tirsdag,onsdag,torsdag,fredag,lørdag,søndag | 2068 | 2068 |
9 | dmy | 7 | 0 | Português | Portuguese | janeiro,fevereiro,março,abril,maio,junho,julho,agosto,setembro,outubro,novembro,dezembro | jan,fev,mar,abr,mai,jun,jul,ago,set,out,nov,dez | segunda-feira,terça-feira,quarta-feira,quinta-feira,sexta-feira,sábado,domingo | 2070 | 2070 |
10 | dmy | 1 | 0 | Suomi | Finnish | tammikuuta,helmikuuta,maaliskuuta,huhtikuuta,toukokuuta,kesäkuuta,heinäkuuta,elokuuta,syyskuuta,lokakuuta,marraskuuta,joulukuuta | tammi,helmi,maalis,huhti,touko,kesä,heinä,elo,syys,loka,marras,joulu | maanantai,tiistai,keskiviikko,torstai,perjantai,lauantai,sunnuntai | 1035 | 1035 |
11 | ymd | 1 | 0 | Svenska | Swedish | januari,februari,mars,april,maj,juni,juli,augusti,september,oktober,november,december | jan,feb,mar,apr,maj,jun,jul,aug,sep,okt,nov,dec | måndag,tisdag,onsdag,torsdag,fredag,lördag,söndag | 1053 | 1053 |
12 | dmy | 1 | 0 | čeština | Czech | leden,únor,březen,duben,květen,červen,červenec,srpen,září,říjen,listopad,prosinec | I,II,III,IV,V,VI,VII,VIII,IX,X,XI,XII | pondělí,úterý,středa,čtvrtek,pátek,sobota,neděle | 1029 | 1029 |
13 | ymd | 1 | 0 | magyar | Hungarian | január,február,március,április,május,június,július,augusztus,szeptember,október,november,december | jan,febr,márc,ápr,máj,jún,júl,aug,szept,okt,nov,dec | hétfő,kedd,szerda,csütörtök,péntek,szombat,vasárnap | 1038 | 1038 |
14 | dmy | 1 | 0 | polski | Polish | styczeń,luty,marzec,kwiecień,maj,czerwiec,lipiec,sierpień,wrzesień,październik,listopad,grudzień | I,II,III,IV,V,VI,VII,VIII,IX,X,XI,XII | poniedziałek,wtorek,środa,czwartek,piątek,sobota,niedziela | 1045 | 1045 |
15 | dmy | 1 | 0 | română | Romanian | ianuarie,februarie,martie,aprilie,mai,iunie,iulie,august,septembrie,octombrie,noiembrie,decembrie | Ian,Feb,Mar,Apr,Mai,Iun,Iul,Aug,Sep,Oct,Nov,Dec | luni,marţi,miercuri,joi,vineri,sîmbătă,duminică | 1048 | 1048 |
16 | ymd | 1 | 0 | hrvatski | Croatian | siječanj,veljača,ožujak,travanj,svibanj,lipanj,srpanj,kolovoz,rujan,listopad,studeni,prosinac | sij,vel,ožu,tra,svi,lip,srp,kol,ruj,lis,stu,pro | ponedjeljak,utorak,srijeda,četvrtak,petak,subota,nedjelja | 1050 | 1050 |
17 | dmy | 1 | 0 | slovenčina | Slovak | január,február,marec,apríl,máj,jún,júl,august,september,október,november,december | I,II,III,IV,V,VI,VII,VIII,IX,X,XI,XII | pondelok,utorok,streda,štvrtok,piatok,sobota,nedeľa | 1051 | 1051 |
18 | dmy | 1 | 0 | slovenski | Slovenian | januar,februar,marec,april,maj,junij,julij,avgust,september,oktober,november,december | jan,feb,mar,apr,maj,jun,jul,avg,sept,okt,nov,dec | ponedeljek,torek,sreda,četrtek,petek,sobota,nedelja | 1060 | 1060 |
19 | dmy | 1 | 0 | ελληνικά | Greek | Ιανουαρίου,Φεβρουαρίου,Μαρτίου,Απριλίου,Μα_ου,Ιουνίου,Ιουλίου,Αυγούστου,Σεπτεμβρίου,Οκτωβρίου,Νοεμβρίου,Δεκεμβρίου | Ιαν,Φεβ,Μαρ,Απρ,Μαϊ,Ιουν,Ιουλ,Αυγ,Σεπ,Οκτ,Νοε,Δεκ | Δευτέρα,Τρίτη,Τετάρτη,Πέμπτη,Παρασκευή,Σάββατο,Κυριακή | 1032 | 1032 |
20 | dmy | 1 | 0 | български | Bulgarian | януари,февруари,март,април,май,юни,юли,август,септември,октомври,ноември,декември | януари,февруари,март,април,май,юни,юли,август,септември,октомври,ноември,декември | понеделник,вторник,сряда,четвъртък,петък,събота,неделя | 1026 | 1026 |
21 | dmy | 1 | 0 | русский | Russian | Январь,Февраль,Март,Апрель,Май,Июнь,Июль,Август,Сентябрь,Октябрь,Ноябрь,Декабрь | янв,фев,мар,апр,май,июн,июл,авг,сен,окт,ноя,дек | понедельник,вторник,среда,четверг,пятница,суббота,воскресенье | 1049 | 1049 |
22 | dmy | 1 | 0 | Türkçe | Turkish | Ocak,Şubat,Mart,Nisan,Mayıs,Haziran,Temmuz,Ağustos,Eylül,Ekim,Kasım,Aralık | Oca,Şub,Mar,Nis,May,Haz,Tem,Ağu,Eyl,Eki,Kas,Ara | Pazartesi,Salı,Çarşamba,Perşembe,Cuma,Cumartesi,Pazar | 1055 | 1055 |
23 | dmy | 1 | 0 | British | British English | January,February,March,April,May,June,July,August,September,October,November,December | Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec | Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday | 2057 | 1033 |
24 | dmy | 1 | 0 | eesti | Estonian | jaanuar,veebruar,märts,aprill,mai,juuni,juuli,august,september,oktoober,november,detsember | jaan,veebr,märts,apr,mai,juuni,juuli,aug,sept,okt,nov,dets | esmaspäev,teisipäev,kolmapäev,neljapäev,reede,laupäev,pühapäev | 1061 | 1061 |
25 | ymd | 1 | 0 | latviešu | Latvian | janvāris,februāris,marts,aprīlis,maijs,jūnijs,jūlijs,augusts,septembris,oktobris,novembris,decembris | jan,feb,mar,apr,mai,jūn,jūl,aug,sep,okt,nov,dec | pirmdiena,otrdiena,trešdiena,ceturtdiena,piektdiena,sestdiena,svētdiena | 1062 | 1062 |
26 | ymd | 1 | 0 | lietuvių | Lithuanian | sausis,vasaris,kovas,balandis,gegužė,birželis,liepa,rugpjūtis,rugsėjis,spalis,lapkritis,gruodis | sau,vas,kov,bal,geg,bir,lie,rgp,rgs,spl,lap,grd | pirmadienis,antradienis,trečiadienis,ketvirtadienis,penktadienis,šeštadienis,sekmadienis | 1063 | 1063 |
27 | dmy | 7 | 0 | Português (Brasil) | Brazilian | Janeiro,Fevereiro,Março,Abril,Maio,Junho,Julho,Agosto,Setembro,Outubro,Novembro,Dezembro | Jan,Fev,Mar,Abr,Mai,Jun,Jul,Ago,Set,Out,Nov,Dez | Segunda-Feira,Terça-Feira,Quarta-Feira,Quinta-Feira,Sexta-Feira,Sábado,Domingo | 1046 | 1046 |
28 | ymd | 7 | 0 | 繁體中文 | Traditional Chinese | 一月,二月,三月,四月,五月,六月,七月,八月,九月,十月,十一月,十二月 | 01,02,03,04,05,06,07,08,09,10,11,12 | 星期一,星期二,星期三,星期四,星期五,星期六,星期日 | 1028 | 1028 |
29 | ymd | 7 | 0 | 한국어 | Korean | 01,02,03,04,05,06,07,08,09,10,11,12 | 01,02,03,04,05,06,07,08,09,10,11,12 | 월요일,화요일,수요일,목요일,금요일,토요일,일요일 | 1042 | 1042 |
30 | ymd | 7 | 0 | 简体中文 | Simplified Chinese | 01,02,03,04,05,06,07,08,09,10,11,12 | 01,02,03,04,05,06,07,08,09,10,11,12 | 星期一,星期二,星期三,星期四,星期五,星期六,星期日 | 2052 | 2052 |
31 | dmy | 1 | 0 | Arabic | Arabic | Muharram,Safar,Rabie I,Rabie II,Jumada I,Jumada II,Rajab,Shaaban,Ramadan,Shawwal,Thou Alqadah,Thou Alhajja | Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec | Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday | 1025 | 1025 |
32 | dmy | 7 | 0 | ไทย | Thai | มกราคม,กุมภาพันธ์,มีนาคม,เมษายน,พฤษภาคม,มิถุนายน,กรกฎาคม,สิงหาคม,กันยายน,ตุลาคม,พฤศจิกายน,ธันวาคม | ม.ค.,ก.พ.,มี.ค.,เม.ย.,พ.ค.,มิ.ย.,ก.ค.,ส.ค.,ก.ย.,ต.ค.,พ.ย.,ธ.ค. | จันทร์,อังคาร,พุธ,พฤหัสบดี,ศุกร์,เสาร์,อาทิตย์ | 1054 | 1054 |
33 | dmy | 1 | 0 | norsk (bokmål) | Bokmål | januar,februar,mars,april,mai,juni,juli,august,september,oktober,november,desember | jan,feb,mar,apr,mai,jun,jul,aug,sep,okt,nov,des | mandag,tirsdag,onsdag,torsdag,fredag,lørdag,søndag | 1044 | 1044 |
Levels of Severity
Severity level | Description |
---|---|
0-9 | Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9. |
10 | Informational messages that return status information or report errors that are not severe. For compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application. |
11-16 | Indicate errors that can be corrected by the user. |
11 | Indicates that the given object or entity does not exist. |
12 | A special severity for queries that do not use locking because of special query hints. In some cases, read operations performed by these statements could result in inconsistent data, since locks are not taken to guarantee consistency. |
13 | Indicates transaction deadlock errors. |
14 | Indicates security-related errors, such as permission denied. |
15 | Indicates syntax errors in the Transact-SQL command. |
16 | Indicates general errors that can be corrected by the user. |
17-19 | Indicate software errors that cannot be corrected by the user. Inform your system administrator of the problem. |
17 | Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator. |
18 | Indicates a problem in the Database Engine software, but the statement completes execution, and the connection to the instance of the Database Engine is maintained. The system administrator should be informed every time a message with a severity level of 18 occurs. |
19 | Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch. Severity level 19 errors are rare and must be corrected by the system administrator or your primary support provider. Contact your system administrator when a message with a severity level 19 is raised. Error messages with a severity level from 19 through 25 are written to the error log. |
20-24 | Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect. Error messages in this range can affect all of the processes accessing data in the same database and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 24 are written to the error log. |
20 | Indicates that a statement has encountered a problem. Because the problem has affected only the current task, it is unlikely that the database itself has been damaged. |
21 | Indicates that a problem has been encountered that affects all tasks in the current database, but it is unlikely that the database itself has been damaged. |
22 | Indicates that the table or index specified in the message has been damaged by a software or hardware problem. Severity level 22 errors occur rarely. If one occurs, run DBCC CHECKDB to determine whether other objects in the database are also damaged. The problem might be in the buffer cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database. If restarting the instance of the Database Engine does not correct the problem, then the problem is on the disk. Sometimes destroying the object specified in the error message can solve the problem. For example, if the message reports that the instance of the Database Engine has found a row with a length of 0 in a nonclustered index, delete the index and rebuild it. |
23 | Indicates that the integrity of the entire database is in question because of a hardware or software problem. Severity level 23 errors occur rarely. If one occurs, run DBCC CHECKDB to determine the extent of the damage. The problem might be in the cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database. |
24 | Indicates a media failure. The system administrator may have to restore the database. You may also have to call your hardware vendor. |
SQL Server Errors
message_id | Description | Article |
---|---|---|
0 | You may see “out of user memory quota” message in errorlog when you use In-Memory OLTP feature … | Out of user memory quota |
0 | Logon Failure: The User has not Been Granted. The operating system returned the error ????? while … | Compressed backup errors |
0 | A transport-level error has occurred when receiving results from the server. | link1 |
0 | The MSSQLSERVER service was unable to log on as SQLAuthoritySQLFarmService with the currently c … | The User has not Been Granted |
0 | A server error occurred on current command. The results, if any, should be discarded. | Who owns your availability groups? |
0 | A network-related issue or instance-specific error occured while establishing a connection to SQL Server | Cannot Connect to SQL Server |
0 | Connecting to Azure SQL Database: Requested tenant identifier ‘00000000-0000-0000-0000-000000000000’ is… | Requested tenant identifier |
102 | Incorrect syntax near ‘%.*ls’. | 102_link1,102_link2 |
120 | The select list for the INSERT statement contains fewer items than the insert list. The number of … | 120_link1 |
121 | The select list for the INSERT statement contains more items than the insert list. The number of … | 121_link1 |
131 | The size (%d) given to the %S_MSG ‘%.*ls’ exceeds the maximum allowed for any data type (%d). | 131_link1 |
145 | ORDER BY items must appear in the select list if SELECT DISTINCT is specified. | 145_link1 |
156 | Incorrect syntax near the keyword ‘ORDER’. | 156_link1 |
207 | Invalid column name ‘%.*ls’. | 207_link1 |
213 | Column name or number of supplied values does not match table definition. | 213_link1,213_link2 |
229 | The %ls permission was denied on the object ‘%.ls’, database ‘%.ls’, schema ‘%.*ls’. | 229_link1 |
241 | Conversion failed when converting date and/or time from character string. | 241_link1 |
257 | Implicit conversion from data type %ls to %ls is not allowed. Use the CONVERT function to run this query | 257_link1 |
264 | The column name ‘%.*ls’ is specified more than once in the SET clause or column list of an INSERT … | 264_link1 |
297 | The user does not have permission to perform this action. | 297_link1 |
352 | The table-valued parameter «%.*ls» must be declared with the READONLY option. | 352_link1 |
459 | Collation ‘%.*ls’ is supported on Unicode data types only and cannot be applied to char, varchar or … | 459_link1 |
535 | The datediff function resulted in an overflow. The number of dateparts separating two date/time | 535_link1 |
596 | Cannot continue execution because the session is in the kill state. | 596_link1,596_link2,596_link3 |
650 | You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels. | 650_link1 |
657 | Could not disable support for increased partitions in database … | 657_link1 |
666 | The maximum system-generated unique value for a duplicate group was exceeded for index with … | 666_link1 |
701 | There is insufficient system memory in resource pool ‘%ls’ to run this query. … | 701_link1,701_link2 |
824 | SQL Server detected a logical consistency-based I/O error … | 824_link1,824_link2,KB2152734,824_link3 |
825 | The operating system returned error %ls to SQL Server. It failed creating event for a %S_MSG at … | 825_link1 |
913 | Could Not Find Database %d. Database May Not be Activated Yet or May be in Transition … | 913_link1 |
922 | Database ‘%.*ls’ is being recovered. Waiting until recovery is finished. | 922_link1 |
926 | Database ‘%.*ls’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog … | 926_link1 |
1052 | Conflicting %ls options «%ls» and «%ls». | 1052_link1 |
1065 | The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE … | 1065_link1 |
1105 | Could not allocate space for object ‘%.ls’%.ls in database ‘%.ls’ because the ‘%.ls’ filegroup is … | 1105_link1 |
1204 | The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your … | 1204_link1 |
1205 | Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen … | 1205_link1 |
1222 | Lock request time out period exceeded. | 1222_link1 |
1219 | Your session has been disconnected because of a high priority DDL operation. | 1219_link1 |
1480 | The %S_MSG database «%.*ls» is changing roles from «%ls» to «%ls» because the mirroring session or … | 1480_link1 |
1701 | Creating or altering table %ls failed because the minimum row size would be 8061, including 10 b … | 1701_link1 |
1807 | Could not obtain exclusive lock on database ‘model’. Retry the operation later. … | 1807_link1 |
1904 | The statistics on table has 65 columns in the key list … | 1904_link1 |
1908 | Column ‘%.ls’ is partitioning column of the index ‘%.ls’. Partition columns for a unique index … | 1908_link1 |
2533 | Table error: page %S_PGID allocated to object ID %d, index ID %d, partition ID %I64d, alloc unit ID … | 2533_link1 |
2534 | Table error: page %S_PGID, whose header indicates that it is allocated to object ID %d, index ID %d, … | 2534_link1 |
2812 | Could not find stored procedure ‘%.*ls’. | 2812_link1 |
3101 | Exclusive access could not be obtained because the database is in use. … | 3101_link1 |
3154 | The backup set holds a backup of a database other than the existing … | 3154_link1 |
3241 | The media family on device ‘%ls’ is incorrectly formed. SQL Server cannot process this media fam … | 3241_link1 |
3314 | During undoing of a logged operation in database ‘%.*ls’, an error occurred at log record ID %S … | 3314_link1 |
3634 | The operating system returned the error ‘%ls’ while attempting ‘%ls’ on ‘%ls’. … | 3634_link1 |
3637 | A parallel operation cannot be started from a DAC connection. | 3637_link1 |
3743 | The database ‘%.*ls’ is enabled for database mirroring. Database mirroring must be removed befor … | 3743_link1 |
3906 | Failed to update database «%.*ls» because the database is read-only. | 3906_link1 |
3930 | The current transaction cannot be committed and cannot support operations that write to the log … | 3930_link1 |
3956 | Snapshot isolation transaction failed to start in database ‘%.*ls’ because the ALTER DATABASE command … | 3956_link1 |
3960 | Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to … | 3960_link1 |
4064 | Cannot open user default database. Login failed.Login failed. … | 4064_link1 |
4189 | Cannot convert to text/ntext or collate to ‘%.*ls’ because these legacy LOB types do not support UTF-8 … | 4189_link1 |
4353 | Conflicting file relocations have been specified for file ‘%.*ls’. Only a single WITH MOVE clause … | 4353_link1 |
4629 | Permissions on server scoped catalog views or system stored procedures or extended stored … | 4629_link1 |
4901 | ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition … | 4901_link1 |
4922 | ALTER TABLE ALTER COLUMN Address failed because one or more objects access this column. … | 4922_link1 |
4934 | Computed column ‘%.ls’ in table ‘%.ls’ cannot be persisted because the column does user or … | 4934_link1 |
4947 | ALTER TABLE SWITCH statement failed. There is no identical index in source table ‘%.*ls’ for the … | 4947_link1 |
5004 | To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed. | 5004_link1 |
5011 | User does not have permission to alter database ‘%.*ls’, the database does not exist, or the database … | 5011_link1 |
5061 | ALTER DATABASE failed because a lock could not be placed on database ‘%.*ls’. Try again later. | 5061_link1 |
5084 | Setting database option %ls to %ls for database ‘%.*ls’. | 5084_link1 |
5120 | Unable to open the physical file … Operating system error 5: «5(Access is denied.)» … | SQL SERVER — FIX Error 5120 |
5123 | CREATE FILE encountered operating system error «%ls»(The system cannot find the path specified.) … | 5123_link1, 5123_link2 |
5171 | %.*ls is not a primary database file. | 5171_link1 |
5172 | The header for file ‘%ls’ is not a valid database file header. The %ls property is incorrect. | 5172_link1 |
5235 | %lsDBCC %ls (%ls%ls%ls)%ls executed by %ls terminated abnormally due to error state %d. Elapsed time: … | 5235_link1 |
5846 | Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two … | 5846_link1 |
6335 | XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels. | 6335_link1 |
6348 | Specified collection ‘%.*ls’ cannot be created because it already exists or you do not have permission. | 6348_link1 |
6401 | Cannot roll back %.*ls. No transaction or savepoint of that name was found. | 6401_link1 |
7341 | Cannot get the current row value of column «%ls.%ls» from OLE DB provider «%ls» for linked server «%ls … | 7341_link1 |
7344 | The OLE DB provider «%ls» for linked server «%ls» could not %ls table «%ls» because of column … | 7344_link1 |
7356 | The OLE DB provider «%ls» for linked server «%ls» supplied inconsistent metadata for a column. … | 7356_link1 |
7357 | Cannot process the object «%ls». The OLE DB provider «%ls» for linked server «%ls» indicates that … | 7357_link1, 7357_link2 |
7391 | The operation could not be performed because OLE DB provider «%ls» for linked server «%ls» … … | 7391_link2 |
7719 | CREATE/ALTER partition function failed as only maximum of 1000 partitions can be created. … | 657_link1 |
7926 | Check statement aborted. The database could not be checked as a database snapshot could not be created … | 7926_link1 |
8101 | An explicit value for the identity column in table ‘%.*ls’ can only be specified when a column list is … | 8101_link1 |
8107 | IDENTITY_INSERT is already ON for table ‘%.ls.%.ls.%.*ls’. Cannot perform SET operation for table ‘% … | 8107_link1 |
8115 | Arithmetic overflow error converting %ls to data type %ls. | 8115_link1 |
8116 | Argument data type %ls is invalid for argument %d of %ls function. | 8116_link1 |
8117 | Operand data type %ls is invalid for %ls operator. | 8117_link1 |
8180 | Statement(s) could not be prepared. | 8180_link1 |
8127 | Column «%.ls.%.ls» is invalid in the ORDER BY clause because it is not contained in either an … | 8127_link1 |
8152 | String or binary data would be truncated. | 8152_link1 |
8624 | Internal Query Processor Error: The query processor could not produce a query plan. | 8624_link1 |
8645 | A timeout occurred while waiting for memory resources to execute the query in resource pool ‘%ls’ (%ld … | 8645_link1 |
8651 | Could not perform the operation because the requested memory grant was not available in resource … | 8651_link1 |
8672 | The MERGE statement attempted to UPDATE or DELETE the same row more than once… … | 8672_link1 |
8909 | Table error: Object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls), pa … | 8909_link1 |
8921 | Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or … | 8921_link1 |
8928 | Object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls): Page %S_PGID could not… | 8928_link1 |
8939 | Table error: Object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls), page … | 8939_link1 |
8948 | Database error: Page %S_PGID is marked with the wrong type in PFS page %S_PGID. PFS status 0x%x … | 8948_link1 |
9001 | The log for database ‘%.*ls’ is not available. Check the operating system error log for related … | 9001_link1 |
9002 | The transaction log for database ‘%ls’ is full due to ‘%ls’. … | 9002_link1,9002_link2,9002_link3 |
9105 | The provided statistics stream is corrupt. | 9105_link1 |
9642 | An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: %i, … | 9105_link1 |
10314 | An error occurred in the Microsoft .NET Framework while trying to load assembly id %d. The server may … | 10314_link1,10314_link2 |
10637 | Cannot perform this operation on ‘%.*ls’ with ID %I64d as one or more indexes are currently in … | 10637_link1 |
10794 | The %S_MSG ‘%ls’ is not supported with %S_MSG. | 10794_link1,10794_link2 |
11442 | Columnstore index creation is not supported in tempdb when memory-optimized metadata mode is enabled. … | [11442_link1][51] |
11535 | EXECUTE statement failed because its WITH RESULT SETS clause specified %d result set(s), and the … | 11535_link1 |
12349 | Operation not supported for memory optimized tables having columnstore index. | 12349_link1 |
13609 | JSON text is not properly formatted. Unexpected character ‘%lc’ is found at position %d. | 13609_link1 |
13515 | Setting SYSTEM_VERSIONING to ON failed because history table ‘%.*ls’ has custom unique keys defined. … | 13515_link1 |
13518 | Setting SYSTEM_VERSIONING to ON failed because history table ‘%.*ls’ has IDENTITY column specification … | 13518_link1 |
13523 | Setting SYSTEM_VERSIONING to ON failed because table ‘%.ls’ has %d columns and table ‘%.ls’ has %d … | 13523_link1 |
13543 | Setting SYSTEM_VERSIONING to ON failed because history table ‘%.*ls’ contains invalid records with end … | 13543_link1 |
13570 | The use of replication is not supported with system-versioned temporal table ‘%s’ | 13570_link1 |
13573 | Setting SYSTEM_VERSIONING to ON failed because history table ‘%.*ls’ contains overlapping records. | 13573_link1 |
13575 | ADD PERIOD FOR SYSTEM_TIME failed because table ‘%.*ls’ contains records where end of period is not … | 13575_link1 |
13901 | Identifier ‘%.*ls’ in a MATCH clause is not a node table or an alias for a node table. | 13901_link1 |
13902 | Identifier ‘%.*ls’ in a MATCH clause is not an edge table or an alias for an edge table. | 13902_link1 |
15002 | The procedure ‘sys.sp_dbcmptlevel’ cannot be executed within a transaction. … | 15002_link1 |
15021 | Invalid value given for parameter %s. Specify a valid parameter value. | 15021_link1 |
15136 | The database principal is set as the execution context of one or more procedures, functions, … | 15136_link1 |
15190 | There are still remote logins or linked logins for the server ‘%s’. | 15190_link1 |
15199 | The current security context cannot be reverted. Please switch to the original database where … | 15199_link1 |
15274 | Access to the remote server is denied because the current security context is not trusted. | 15274_link1 |
15404 | Could not obtain information about Windows NT group/user ‘%ls’, error code %#lx. | 15404_link1 |
15406 | Cannot execute as the server principal because the principal «%.*ls» does not exist, this type of … | 15406_link1 |
15457 | Configuration option ‘%ls’ changed from %ld to %ld. Run the RECONFIGURE statement to install. | 5457_link1 |
17051 | SQL Server evaluation edition has expired. | 17051_link1 |
17182 | TDSSNIClient initialization failed with error 0x%lx, status code 0x%lx. Reason: %S_MSG %.*ls | 17182_link1 |
17190 | Initializing the FallBack certificate failed with error code: %d, state: %d, error number: %d. … | 17190_link1 |
17300 | SQL Server was unable to run a new system task, either because there is insufficient memory or the … | 17300_link1 |
17836 | Length specified in network packet payload did not match number of bytes read; the connection has been … | 17836_link1 |
18054 | Error %d, severity %d, state %d was raised, but no message with that error number was found in … | 18054_link1 |
18272 | During restore restart, an I/O error occurred on checkpoint file ‘%s’ (operating system error %s … | 18272_link1 |
18357 | Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated … | 18357_link1 |
18401 | Login failed for user ‘%.*ls’. Reason: Server is in script upgrade mode. Only administrator can connect… | 18401_link1 |
18452 | Login failed. The login is from an untrusted domain and cannot be used with Windows authenticati … | 18452_link1 |
18456 | Login failed for user ‘%.ls’.%.ls%.*ls | 18456_link1 |
20598 | The row was not found at the Subscriber when applying the replicated %S_MSG command for Table ‘%s’ with… | 20598_link1 |
22911 | The capture job cannot be used by Change Data Capture to extract changes from the log when … | 22911_link1 |
25713 | The value specified for %S_MSG, «%.ls», %S_MSG, «%.ls», is invalid. | 25713_link1,25713_link2 |
26023 | Server TCP provider failed to listen on [ %s <%s> %d]. Tcp port is already in use. | 26023_link1 |
33111 | Cannot find server %S_MSG with thumbprint ‘%.*ls’. | 33111_link1 |
33206 | SQL Server Audit failed to create the audit file ‘%s’. Make sure that the disk is not full and … | 33206_link1 |
35217 | The thread pool for Always On Availability Groups was unable to start a new worker thread because … | [35217_link1] |
35250 | The connection to the primary replica is not active. The command cannot be processed. | 35250_link1 |
35264 | Always On Availability Groups data movement for database ‘%.*ls’ has been suspended for the following … | 35264_link1 |
35320 | Column store indexes are not allowed on tables for which the durability option SCHEMA_ONLY is specified. | 35320_link1 |
35337 | UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index. … | 35337_link1 |
35343 | The statement failed. Column ‘%.*ls’ has a data type that cannot participate in a columnstore index. | 35343_link1 |
39004 | A ‘%s’ script error occurred during execution of ‘sp_execute_external_script’ with HRESULT 0x%x. | 39004_link1 |
41121 | The local availability replica of availability group ‘%.*ls’ cannot accept signal ‘%s’ in its current … | 41121_link1 |
41317 | A user transaction that accesses memory optimized tables or natively compiled modules cannot access more… | [41317_link1][51] |
41922 | The backup operation for a database with service-managed transaprent data encryption is not supported on… | 41922_link1 |
[51]:
[35217_link1] :https://www.seangallardy.com/error-35217-and-availability-groups-smh/
List of error messages between 1 and 999 in SQL Server 2017.
These error messages are all available by querying the sys.messages
catalog view on the master
database.
Содержание статьи:
-
- SQL-сервер не найден или недоступен, ошибки соединения с SQL-сервером
- Ошибка SQL-сервера 26
- Ошибка SQL-сервера 18456
- Не удалось запустить SQL-server — код ошибки 3417
- Повреждена база данных
- Код ошибки SQL-сервера 945
- Код ошибки SQL-сервера 5172
- Ошибка SQL-сервера 823
- Ошибка SQL-сервера 8946
- Другие ошибки SQL Server
- Код ошибки SQL-сервера 1814
- Код ошибки SQL-сервера 1067
- SQL-сервер запускается, но работает слишком медленно
- SQL-сервер не найден или недоступен, ошибки соединения с SQL-сервером
SQL-сервер не найден или недоступен, ошибки соединения с SQL-сервером
- Если SQL-сервер не найден, убедитесь, что ваш экземпляр SQL-сервера действительно установлен и запущен. Для этого зайдите на компьютер, где он установлен, запустите диспетчер конфигурации SQL и проверьте, есть ли там тот экземпляр, к которому вы пытаетесь подключиться и запущен ли он. Нелишним будет также получить отчет об обнаружении компонентов SQL-серверов.
- Если вы проделали п1. и не обнаружили источник проблемы, возможно, неверно указан IP-адрес компьютера или номер порта TCP. Перепроверьте их настройки.
- Причиной того, что невозможно подключиться к SQL-серверу, также может быть сеть, убедитесь, что компьютер с SQL-сервером доступен по сети.
- Проверьте, может ли клиентское приложение, установленное на том же компьютере, что и сервер, подключиться к SQL-серверу. Запустите SQL Server Management Studio(SSMS), в диалоговом окне “Подключиться к серверу” выберите тип сервера Database Engine, укажите способ аутентификации “Аутентификация Windows”, введите имя компьютера и экземпляра SQL-сервера. Проверьте подключение.
Обратите внимание, что многие сообщения об ошибках могут быть не показаны или не содержат достаточной информации для устранения проблемы. Это сделано из соображений безопасности, чтобы при попытке взлома злоумышленники не могли получить информацию об SQL-сервере. Полные сведения содержатся в логе ошибок, который обычно хранится по адресу C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLLogERRORLOG, или там, куда его поместил администратор системы.
Ошибка SQL-сервера 26
Одна из наиболее часто встречающихся ошибок подключения к SQL-серверу, обычно связана с тем, что в настройках SQL-сервера не разрешены или ограничены удаленные соединения. Чтобы это исправить, попробуйте:
- в SSMS в настройках SQL-сервера включите аутентификацию Windows
- для брандмауэра Windows создайте новое правило, которое разрешает подключение для всех программ и протоколов с указанного IP-адреса
- убедитесь, что запущена служба SQL Server Browser
Ошибка SQL-сервера 18456
Эта ошибка означает, что попытка подключиться к серверу не успешна из-за проблем с именем пользователя или паролем. По коду ошибки в журнале ошибок можно узнать более точную причину, чтобы устранить ее.
Не удалось запустить SQL-server — код ошибки 3417
Возникает в случае, если были изменены настройки Windows или перемещена папка с файлами MSSQL.
- зайдите в C:Program FilesMicrosoft SQLServerMSSQL.1MSSqLData — БезопасностьНастройки доступа — Учетная запись сетевой службы — добавьте учетную запись сетевой службы
- проверьте, что MDF-файл не сжимается. Если это не так, отключите “Сжимать содержимое для экономии места на диске” в свойствах файла
Иногда ни один из этих способов не помогает, это значит, что файлы БД повреждены и ее придется восстанавливать из резервной копии.
Повреждена база данных
Код ошибки SQL-сервера 945
Ошибка 945 возникает, когда БД SQL-сервера помечена как IsShutdown. Проверьте, достаточно ли места на диске, достаточно ли прав у учетной записи для операций с БД, файлы MDF и LDF не должны быть помечены “Только для чтения”.
Код ошибки SQL-сервера 5172
SQL-сервер хранит свою физическую БД в первичном файле, в котором информация разбита постранично. Первая страница содержит информацию о заголовке mdf-файла и называется страницей заголовка. Она состоит из разнообразной информации о БД, такой как размер файла, подпись и т.д. В процессе прикрепления MDF на SQL-сервере часто возникает ошибка 5172. Это в основном происходит, если MDF-файл поврежден, информация в его заголовке тоже и соответственно сложно добраться до данных. Причиной может быть вирус, аварийное выключение системы, ошибка оборудования.
Ошибка SQL-сервера 823
SQL использует API Windows для операций ввода-вывода, но кроме завершения этих операций SQL проверяет все ошибки обращений к API. Если эти обращения несовместимы с ОС, появляется ошибка 823. Сообщение об ошибке 823 означает, что существует проблема с базовым оборудованием для хранения данных или с драйвером, который находится на пути запроса ввода-вывода. Пользователи могут столкнуться с этой ошибкой, если в файловой системе есть противоречия или поврежден файл базы данных.
Ошибка SQL-сервера 8946
Основной причиной ошибки 8946 так же, как и для 5172, является повреждение заголовков страниц БД SQL вследствие сбоя питания, вирусной атаки, отказа оборудования — SQL-сервер больше не может прочесть эти страницы.
Перечисленные ошибки 945, 5172, 823, 8946 можно устранить двумя методами:
- если у вас есть свежая резервная копия базы — восстановить базу из этой копии
- можно попробовать использовать специализированное ПО, такое как SQL Recovery Tool, чтобы восстановить поврежденные файлы
Желательно определить, что именно привело к возникновению ошибок и принять меры, чтобы это не повторялось — заменить плохо работающее оборудование, повысить информационную безопасность.
Другие ошибки SQL
Код ошибки SQL-сервера 1814
SQL-сервер не может создать базу данных tempdb. Убедитесь, что на выделенном под нее диске достаточно места и что у учетной записи хватает прав для записи в указанную директорию.
Код ошибки SQL-сервера 1067
Эта ошибка может возникать по разным причинам. Наиболее часто оказывается, что повреждены или отсутствуют конфигурационные файлы, SQL-сервер обращается к поврежденным системным файлам, ошибочные данные пользователя, нет информации про лицензию. В самых тяжелых случаях придется переустанавливать SQL-сервер. Но иногда помогает восстановление поврежденных файлов или изменение настроек SQL-сервера — вы можете создать новую учетную запись в домене и использовать ее для службы MSSQL.
SQL-сервер запускается, но работает слишком медленно
Проанализируйте журнал сервера, индексы (фрагментацию), запросы, задания, возможность взаимных блокировок. Причин может быть масса.
Мы работаем с разными версиями SQL-сервера уже много лет, знакомы со всевозможными инструкциями SQL-сервера, видели самые разные варианты его настройки и использования на проектах у своих клиентов. В целом мы можем выделить четыре основных источника неполадок:
- Индексы — причина проблем номер один. Неправильные индексы, отсутствующие индексы, слишком много индексов и подобное. Чаще всего при проблеме с индексами пользователи или администраторы базы данных не получают сообщения об ошибке, они просто видят, что база работает очень медленно и докопаться до причин бывает очень нелегко
- изначально плохая архитектура сервера баз данных — ошибка, которую очень сложно и дорого исправлять на этапе, когда база уже используется
- плохой код, в котором возможны блокировки и тупиковые места
- использование конфигурации по умолчанию,
Если у вас не получается устранить ошибки сервера SQL-server самостоятельно, если они появляются снова и снова, то скорее всего в основе лежит одна из этих причин. В таком случае — если у вас произошла ошибка с SQL сервером, ваше ПО не видит SQL-сервер, либо нужно развернуть кластер SQL-серверов — вы всегда можете обратиться за консультацией и технической поддержкой к специалистам Интегруса, отправив заявку с сайта, написав на e-mail, либо позвонив в колл-центр нашей компании.
Присоединяйтесь к нам,
чтобы получать чек-листы, реальные кейсы, а также
обзоры сервисов раз в 2 недели.
Last week, I had to help some developers to debug a long SQL code. For this, I used the error_reported xEvent. It gives some interesting informations.
Context
The developers executed a SQL code setting some variables, then calling a procedure which itself executes few procedures each of these doing business stuff : selecting, updating, deleting, creating temporary tables and so on …
Until last week, the developers used this SQL code without error. Then they reported an SQL error this week. The error was « Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. »
Which means a subquery used to return one value in a comparison or affectation operation, is, in fact, returning more than two rows. It could be a code like
(...) WHERE id = (SELECT id FROM table WHERE something). -- OR UPDATE table SET value = (SELECT something FROM somewhere WHERE something)
In my case, it was the UPDATE code. So, we all know writing this kind of code is bad (as my developers understood it last week) and it is from « historical » code which nobody had time to fix. OK. But, let’s return to our problem.
First approach
WHERE is this code ? Remember, we had a procedure calling several ones with many statements, something like around hundreds of line of SQL code. And of course, it was an emergency for the business to execute this code in the hour.
At first, I use a naive approach. I just used my classical xEvent session (the *completed_statement, rpc_completed and sql_batch_completed statements) without the duration limits and filtered on the database (luckily for me, at least I got one filter !). I just added the *starting statements (sp_statement_starting and sql_statement_starting). The idea was to find a starting event without the corresponding completed. How naive was I !
I was spammed by hundreds of events. Too much to make the compare quickly. I could import all in a table and try to find it but there were many more *starting statements than *completed.
A better approach
So, how to find where the error was ? Well, with the error_completed xEvent.
The session gives something like this.
CREATE EVENT SESSION [Find Bug] ON SERVER -- error reported event filtered on database name ADD EVENT sqlserver.error_reported( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.sql_text) WHERE ([sqlserver].[database_name]=N'Test')), -- sp_statement_completed filtered on database name ADD EVENT sqlserver.sp_statement_completed( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.sql_text) WHERE ([sqlserver].[database_name]=N'Test')), -- sp_statement_starting filtered on database name ADD EVENT sqlserver.sp_statement_starting( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.sql_text) WHERE ([sqlserver].[database_name]=N'Test')), -- sql_statement_completed filtered on database name ADD EVENT sqlserver.sql_statement_completed( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.sql_text) WHERE ([sqlserver].[database_name]=N'Test')), -- sql_statement_starting filtered on database name ADD EVENT sqlserver.sql_statement_starting( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.sql_text) WHERE ([sqlserver].[database_name]=N'Test')) -- for this example, no TARGET is set. Add at least one target if needed. -- options, note the TRACK_CASUALITY option. WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO
Note the error_reported event. I added also the ACTION event_sequence which could be useful and add the TRACK_CAUSALITY=ON in the options. It allows to see the connection between sp_statement and rpc statements.
I just launched the session, the developers called the code and by watching the Live Data I could quickly find an error_reported event corresponding to the error message. The starting event just before had no completed event. It was it.
Caution
Of course, error_reported will report ALL errors and warnings. But in my case, I had may be 10 error_reported events for 1000 completed events. A quick search was enough to find it.
Also, sql_text of the event gives all the batch. That’s why it is necessary to have the starting events.
This solution was quick and effective. Maybe there are better solutions, but these fits my needs.
error_reported in action
Here is a small example to reproduce the bug.
Let’s create a table with a code and an amount. We’ll create a procedure updating an amount for a specified code using a SET amount = (SELECT …)
Of course, it will fail !
CREATE TABLE dbo.SomeTable (id INT not null IDENTITY(1,1) PRIMARY KEY, code VARCHAR(2) NOT NULL, amount INT) -- check the data INSERT INTO dbo.SomeTable (code, amount) VALUES ('AA', 10), ('BB', 1), ('BB', 2), ('CC', 0);
The procedure
CREATE PROCEDURE PS_UpdateSomeTable(@code1 VARCHAR(2), @code2 VARCHAR(2)) AS BEGIN SELECT 'some statements before' AS what_i_am_doing UPDATE dbo.SomeTable SET Amount = (SELECT Amount FROM dbo.SomeTable WHERE code = @code2) WHERE code = @code1 SELECT 'some statement after' AS what_i_am_doing END GO
Let’s test the code.
-- this works EXEC PS_UpdateSomeTable @code1 = 'CC', @code2 = 'AA' -- this doesn't work EXEC PS_UpdateSomeTable @code1 = 'CC', @code2 = 'BB'
The expected error is here.
I create and start the session (see above code). And look at the Live Data.
I’ve got an error_reported event with a severity of 16, corresponding to my error.
The message field gives : « Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. ». This is it !
Just before, I could find a sp_statement_starting without corresponding sp_statement_completed. It is the UPDATE statement.
Note also the attach_activity_id and attach_activity_guid obtained by the TRACK_CASUALITY=ON. This option is really interesting. The GUID is the same for all statements inside the procedure and the id is a sequential number of the inside statements.
The event_sequence field just returns a row number for events. It is interesting when we need to query against the xEvent results session.
Conclusion
As I see it, the xEvents are not only a replacement for the profiler but gives much more flexibility and monitoring capability. The drawback is it is a little bit more complicated to create and more to query. On the other hand, the GUI is a good start and always useful (it seems impossible to create by heart an event session, at least for me). From a template, it is easy to modify and adapt to your needs or for Azure where the Live Data is not yet available.
Anyway, more I’m using xEvents, more I’m liking it.
The goal of this article is to provide a simple and easy to use error handling mechanism with minimum complexity. This article is completely compatible with MS SQL Server 2012 and above versions.
Table of Contents
- Problem Definition
- Introduction
- Solution
- Is there any structured Error Handling mechanism in SQL Server?
- Will all statements in TRY block try to execute?
- Does the CATCH part automatically handle the errors?
- Is it a good idea to use a general procedure as a modular Error Handler routine?
- What are the benefits of THROW when we have RAISERROR?
- The correct line number of the error!
- Easy to use
- Complete termination
- Independence of sys.messages
- I want to check a condition in the TRY block. How can I control the flow of execution and raise the error?
- Does the CATCH part automatically rollback the statements within the TRY part?
- Can someone use TRANSACTION in the TRY/CATCH block?
- XACT_ABORT
- @@TRANCOUNT
- Conclusion
- See Also
- Related Wiki Articles
- Error Handling in SQL Server 2005 and Later
- Other Languages
Problem Definition
There are many questions in
MSDN forum and other Internet communities about Error Handling in SQL Server. There are several issues as presented in the Table of Contents above.
Introduction
There are many articles written by the best experts and there are complete references about Error Handling in SQL Server. The goal of this article is to provide a simple and easy to use error handling mechanism with minimum complexity. Therefore I will try
to address this topic from a problem-solving approach and particularly in SQL Server 2012 (and later) versions. So the road map of this article is to cover the above questions as well as providing a step by step tutorial to design a structured mechanism for
error handling in SQL Server 2012 (and up) procedures.
Solution
Is there any structured Error Handling mechanism in SQL Server?
Yes, there is. The TRY/CATCH construct is the structured mechanism for error handling in SQL Server 2005 and later. This construct has two parts; we can try executing some statements in
TRY block and handling errors in the CATCH block if they occur. Therefore, the simplest error handling structure can be like this:
- TRY
- Try executing statements
- CATCH
- Handle the errors if they occur
Here is a sample code to provide the above structure in the simplest form:
SET
NOCOUNT
ON
;
BEGIN
TRY
-- Start to try executing statements
SELECT
1 / 0; /* Executing statements */
END
TRY
-- End of trying to execute statements
BEGIN
CATCH
-- Start to Handle the error if occurs
PRINT
'Error occurs!'
/* Handle the error */
END
CATCH
-- End of Handling the error if occurred
—result
Will all statements in TRY block try to execute?
When executing statements in the TRY block, if an error occurs the flow of execution will transfer to the
CATCH block. So the answer is NO!
We can see this behavior with an example. As we can see after executing the following code, the statement no. 3 does not try executing, because the flow of execution will transfer to the CATCH block as soon as statement no. 2 raises
an error.
SET
NOCOUNT
ON
;
BEGIN
TRY
-- Start to try executing statements
PRINT
'Before Error!'
-- Statement no1
SELECT
1 / 0;
-- Statement no2
PRINT
'After Error!'
-- Statement no3
END
TRY
-- End of trying to execute statements
BEGIN
CATCH
-- Start to Handle the error if occurs
PRINT
'Error occurs!'
/* Handle the error */
END
CATCH
-- End of Handling the error if occurred
—result
Does the CATCH part automatically handle the errors?
No. The role of the TRY/CATCH construct is just providing a mechanism to try executing SQL statements. Therefore, we need to use another approach to handle the errors in the CATCH block that I’ll explain later. For instance, the following code will try to
execute a divide by zero statements. It does not automatically handle any errors. In fact, in this sample code, when an error occurs the flow control immediately transfers to the CATCH block, but in the CATCH block, we do not have any statement to tell us
that there was an error!
SET
NOCOUNT
ON
;
BEGIN
TRY
-- Start to try executing statements
SELECT
1 / 0;
-- Statement
END
TRY
-- End of trying to execute statements
BEGIN
CATCH
-- Start to Handle the error if occurs
END
CATCH
-- End of Handling the error if occurred
—result
In the CATCH block we can handle the error and send the error message to the application. So we need an element to show what error occurs. This element is
RAISERROR. So the error handling structure could be like this:
- TRY
- Try executing statements
- CATCH
- Handle the error if occurs
- RAISERROR
- Handle the error if occurs
Here is sample code to produce the above structure:
SET
NOCOUNT
ON
;
BEGIN
TRY
-- Start to try executing statements
SELECT
1 / 0;
-- Statement
END
TRY
-- End of trying to execute statements
BEGIN
CATCH
-- Start to Handle the error if occurs
RAISERROR(
'Error!!!'
, 16, 1);
END
CATCH
-- End of Handling the error if occurred
—result
The RAISERROR itself needs other elements to identify the error number, error message, etc. Now we can complete the error handling structure:
- TRY
- Try executing statements
- CATCH
- Handle the error if occurs
- RAISERROR
- ERROR_NUMBER()
- ERROR_MESSAGE()
- ERROR_SEVERITY()
- ERROR_STATE()
- ERROR_PROCEDURE()
- ERROR_LINE()
- RAISERROR
- Handle the error if occurs
Here is sample code to produce the above structure:
SET
NOCOUNT
ON
;
BEGIN
TRY
-- Start to try executing statements
SELECT
1 / 0;
-- Statement
END
TRY
-- End of trying to execute statements
BEGIN
CATCH
-- Start to Handle the error if occurs
DECLARE
@ErrorMessage NVARCHAR(4000);
DECLARE
@ErrorSeverity
INT
;
DECLARE
@ErrorState
INT
;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,
-- Message text.
@ErrorSeverity,
-- Severity.
@ErrorState
-- State.
);
END
CATCH
-- End of Handling the error if occurred
—result
Is it a good idea to use a general procedure as a modular Error Handler routine?
From a modular programming approach, it’s recommended to create a stored procedure that does the RAISERROR job. But I believe that using a modular procedure (I call it spErrorHandler) to re-raise errors is not a good idea. Here are my reasons:
1. When we call RAISERROR in procedure “spErrorHandler”, we have to add the name of the procedure that the error occurs within to the Error Message. This will confuse the application end-users
(Customer). Customer does not want to know which part of his car is damaged. He prefers that his car just send him a simple message which tells him there is an error in its functions. In the software
world it’s more important to send a simple (English) message to the customer because if we send a complex error message, he will be afraid of what will happen to his critical data!
2. If we accept the first reason and decide to resolve this issue, we need to send a simple message to the client application. So we will lose the procedure name that the error occurs within and other useful information for debugging unless
we insert this useful information in an Error-Log table.
You can test this scenario with the following code:
CREATE
PROCEDURE
spErrorHandler
AS
SET
NOCOUNT
ON
;
DECLARE
@ErrorMessage NVARCHAR(4000);
DECLARE
@ErrorSeverity
INT
;
DECLARE
@ErrorState
INT
;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,
-- Message text.
@ErrorSeverity,
-- Severity.
@ErrorState
-- State.
);
go
-----------------------------------------
CREATE
PROCEDURE
spTest
AS
SET
NOCOUNT
ON
;
BEGIN
TRY
-- Start to try executing statements
SELECT
1 / 0;
-- Statement
END
TRY
-- End of trying to execute statements
BEGIN
CATCH
-- Start to Handle the error if occurs
EXEC
spErrorHandler;
END
CATCH
-- End of Handling the error if occurred
go
exec
spTest;
—result
As is illustrated in this figure, when using spErrorHandler, the values of ERROR_PROCEDURE() and ERROR_NUMBER() are changed in the output. This behavior is because of the RAISERROR functionality. This function always re-raises
the new exception, so spErrorHandler always shows that the value of ERROR_PROCEDURE() simply is “spErrorHandler”. As I said before there are two workarounds to fix this issue. First is concatenating this useful data with the error message and raise it, which
I spoke about in reason one. Second is inserting this useful data in another table just before we re-raise the error in spErrorHandler.
Now, we test the above sample without using spErrorHandler:
CREATE
PROCEDURE
spTest
AS
SET
NOCOUNT
ON
;
BEGIN
TRY
-- Start to try executing statements
SELECT
1 / 0;
-- Statement
END
TRY
-- End of trying to execute statements
BEGIN
CATCH
-- Start to Handle the error if occurs
DECLARE
@ErrorMessage NVARCHAR(4000);
DECLARE
@ErrorSeverity
INT
;
DECLARE
@ErrorState
INT
;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,
-- Message text.
@ErrorSeverity,
-- Severity.
@ErrorState
-- State.
);
END
CATCH
-- End of Handling the error if occurred
go
exec
spTest;
—result
As you see in this figure, the procedure name and error number are correct. By the way, I prefer that if one customer reports an error, I go for SQL Server Profiler, simulate the environment completely, and test those SQL statements
in SSMS to recreate the error and debug it based on the correct error number and procedure name.
In the THROW section, I will explain that the main advantage of THROW over RAISERROR is that it shows the correct line number of the code that raises the error, which is so helpful for a developer in debugging his code.
3. Furthermore, with the THROW statement introduced in SQL SERVER 2012, there is no need to write extra code in the CATCH block. Therefore there is no need to write a separate procedure except for tracking the errors in another error log table.
In fact, this procedure is not an error handler, it’s an error tracker. I will explain the THROW statement in the next section.
What are the benefits of THROW when we have RAISERROR?
The main objective of error handling is that the customer knows that an error occurred and reports it to the software developer. Then the developer can quickly realize the reason for the error and improve his code. In fact, error handling is a mechanism
that eliminates the blindness of both customer and developer.
To improve this mechanism Microsoft SQL Server 2012 introduced the THROW statement. Now I will address the benefits of THROW over RAISERROR.
The correct line number of the error!
As I said earlier this is the main advantage of using THROW. The following code will demonstrate this great feature:
create
proc sptest
as
set
nocount
on
;
BEGIN
TRY
SELECT
1/0
END
TRY
BEGIN
CATCH
declare
@msg nvarchar(2000) = error_message();
raiserror( @msg , 16, 1);
THROW
END
CATCH
go
exec
sptest
—result
As you can see in this figure, the line number of the error that RAISERROR reports to us always is the line number of itself in the code. But the error line number reported by THROW is line 6 in this example, which is the line
where the error occurred.
Easy to use
Another benefit of using the THROW statement is that there is no need for extra code in RAISERROR.
Complete termination
The severity level raised by THROW is always 16. But the more important feature is that when the THROW statement in a CATCH block is executed, then other code after this statement will never run.
The following sample script shows how this feature protects the code compared to RAISERROR:
create
proc sptest
as
set
nocount
on
;
BEGIN
TRY
SELECT
1/0
END
TRY
BEGIN
CATCH
declare
@msg nvarchar(2000) = error_message();
raiserror( @msg , 16, 1);
CREATE
TABLE
#Saeid (id int
)
INSERT
#Saeid
VALUES
( 101 );
SELECT
*
FROM
#Saeid;
DROP
TABLE
#Saeid;
THROW
PRINT
'This will never print!!!'
;
END
CATCH
go
exec
sptest
—result
Independence of sys.messages
This feature makes it possible to re-throw custom message numbers without the need to use
sp_addmessage to add the number.The feature is in real time, as you can see
in this code:
create
proc sptest
as
set
nocount
on
;
BEGIN
TRY
SELECT
1/0
END
TRY
BEGIN
CATCH
THROW 60000,
'This a custom message!'
, 1;
END
CATCH
go
exec
sptest
—result
Tip
The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
I want to check a condition in the TRY block. How can I control the flow of execution and raise the error?
This is a simple job! Now I change this question to this one:
“How can I terminate the execution of the TRY block?”
The answer is using THROW in the TRY block. Its severity level is 16, so it will terminate execution in the TRY block. We know that when any statement in the TRY block terminates (encounters an error) then immediately execution
goes to the CATCH block. In fact, the main idea is to THROW a custom error as in this code:
create
proc sptest
as
set
nocount
on
;
BEGIN
TRY
THROW 60000,
'This a custom message!'
, 1;
END
TRY
BEGIN
CATCH
THROW
END
CATCH
go
exec
sptest
—result
As you can see, we handle the error step by step. In the next session, we will complete this structure.
Does the CATCH part automatically rollback the statements within the TRY part?
This is the misconception that I sometimes hear. I will explain this problem with a little example. After executing the following code the table “dbo.Saeid” still exists. This demonstrates that the TRY/CATCH block does not implement
implicit transactions.
CREATE
PROC sptest
AS
SET
NOCOUNT
ON
;
BEGIN
TRY
CREATE
TABLE
dbo.Saeid --No1
( id
int
);
SELECT
1/0
--No2
END
TRY
BEGIN
CATCH
THROW
END
CATCH
go
-------------------------------------------
EXEC
sptest;
go
SELECT
*
FROM
dbo.Saeid;
—result
Can someone use TRANSACTION in the TRY/CATCH block?
The previous question showed that if we want to rollback entire statements in a try block, we need to use explicit transactions in the TRY block. But the main question here is:
“Where is the right place to commit and rollback? “
It’s a complex discussion that I would not like to jump into in this article. But there is a simple template that we can use for procedures (not triggers!).
This is that template:
CREATE
PROC sptest
AS
SET
NOCOUNT
ON
;
BEGIN
TRY
SET
XACT_ABORT
ON
;
--set xact_abort option
BEGIN
TRAN
--begin transaction
CREATE
TABLE
dbo.Hasani
( id
int
);
SELECT
1/0
COMMIT
TRAN
--commit transaction
END
TRY
BEGIN
CATCH
IF @@TRANCOUNT > 0
--check if there are open transaction?
ROLLBACK
TRAN;
--rollback transaction
THROW
END
CATCH
go
EXEC
sptest;
go
SELECT
*
FROM
dbo.Hasani;
—result
The elements of this structure are:
- TRY block
- XACT_ABORT
- Begin transaction
- Statements to try
- Commit transaction
- CATCH block
- Check @@TRANCOUNT and rollback all transactions
- THROW
Here is a short description of two parts of the above code:
XACT_ABORT
In general, it’s recommended to set the XACT_ABORT option to ON in our TRY/CATCH block in procedures. By setting this option to ON if we want to roll back the transaction, any user-defined transaction is rolled back.
@@TRANCOUNT
We check this global variable to ensure there is no open transaction. If there is an open transaction it’s time to execute rollback statements. This is a must in all CATCH blocks, even if you do not have any transactions in that
procedure. An alternative is to use XACT_STATE().
Conclusion
Introduction of the THROW statement is a big feat in Error Handling in SQL Server 2012. This statement enables database developers to focus on accurate line numbers of the procedure code. This article provided
a simple and easy to use error handling mechanism with minimum complexity using SQL Server 2012. By the way, there are some more complex situations that I did not cover in this article. If you need to dive deeper, you can see the articles in the
See Also section.
BOL link http://technet.microsoft.com/en-us/library/ms175976.aspx
See Also
Related Wiki Articles
- Error Handling within Triggers Using T-SQL
- T-SQL: Error Handling for CHECK Constraints
- Transact-SQL Portal
- SQL Server 2012
- List of Award Winning TechNet Guru Articles
Error
Handling in SQL Server 2000
- Error Handling in SQL 2000 – a Background written by Erland Sommarskog
- Implementing Error Handling with Stored Procedures in SQL 2000 written by Erland Sommarskog
Other Languages
- Mecanismo de Tratamento de erros em SQL Server 2012 (pt-BR)