Search results matching tags 'maintenance' and 'Troubleshooting'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=maintenance,Troubleshooting&orTags=0Search results matching tags 'maintenance' and 'Troubleshooting'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Analyzing the errorloghttp://sqlblog.com/blogs/tibor_karaszi/archive/2012/07/05/analyzing-the-errorlog.aspxThu, 05 Jul 2012 11:53:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44203TiborKaraszi<p>How often do you do this? Look over each message (type) in the errorlog file and determine whether this is something you want to act on. Sure, some (but not all) of you have some monitoring solution in place, but are you 100% confident that it really will notify for all messages that you might find interesting? That there isn't even one little message hiding in there that you would find valuable knowing about? Or how about messages that you typically don't are about, but knowing that you have a high frequency can be valuable information?</p><p>So, this boils down to actually reading the errorlog file. Some of you probably already have scripts and tool that makes this easier than just reading every simple message from top to bottom. I wanted to share how I do it, and this is why I wrote my&nbsp;<a href="http://www.karaszi.com/SQLServer/util_analyze_sql_server_logs.asp">Analyze SQL Server&nbsp;logs</a> article.&nbsp;Check it out. And, feedback is always welcome!</p>Article released about moving databaseshttp://sqlblog.com/blogs/tibor_karaszi/archive/2011/07/10/article-released-about-moving-databases.aspxSun, 10 Jul 2011 13:26:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:36757TiborKarasziJust a short notice that I've released an article about moving databases between SQL Server instances: You find it at <a href="http://www.karaszi.com/SQLServer/info_moving_database.asp">http://www.karaszi.com/SQLServer/info_moving_database.asp</a><a title="http://www.karaszi.com/SQLServer/info_moving_database.asp" href="http://www.karaszi.com/SQLServer/info_moving_database.asp"></a>.Why we never want to trick SQL Server into rebuilding a log filehttp://sqlblog.com/blogs/tibor_karaszi/archive/2010/10/14/why-we-never-want-to-trick-sql-server-into-rebuilding-a-log-file.aspxThu, 14 Oct 2010 17:04:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:29398TiborKaraszi<P>"Never" is a strong word, so let me say that we really really want to avoid it, if at all humanly possible. In short, we will have a (potentially) broken database, both at the physical level and at the logical level. Read on.</P>
<P>Just to be clear, when I refer to a "log file" here, I'm talking about a transaction log file, an .ldf file. Ever so often we see in forums how log files are "lost", "disappears" or so. Now, SQL Server relies on the ldf file at startup to bring the database to a consistent state. This is known as "recovery", or "startup recovery". This is pretty well known, I for instance teach this at every intro level admin course. So, what if</P>
<UL>
<LI>The ldf file isn't there? </LI>
<LI>Or isn't accessible to the db engine? </LI>
<LI>Or is broken somehow? </LI>
<LI>Or is from a different point in time from the data file? (You'd be surprised to all the things one see over the years.)</LI></UL>
<P>Well, SQL Server will do the only reasonable, refuse us into the database and produce an error message (in eventlog etc). </P>
<P>What we see from time to time, is trying to "trick" SQL Server into re-creating an ldf file. So, why is this so bad? I will try to explain why. Let me first say that SQL Server doesn't do these things to be mean to us, or to prove a point. If SQL Server know that the ldf file is <STRONG>not</STRONG> necessary for recovery (the database was "cleanly shutdown"), then it can and will re-create a log file for us at startup. The problem is that it isn't these cases we see in forum. The cases we see in the forums is when this <STRONG>didn't</STRONG> happen. SQL Server relied on the ldf file in order to bring the database to a consistent state. </P>
<P>Enough beating around the bush, here is an example of why we don't want to trick SQL Server to forcefully re-create a log file:</P>
<P>Say you have a transaction in which you add a row to the order header table (oh) and five rows to the order details table (od). Physically, each command is reflected in the tlog, the page is modified (but still cached) and at commit, the log records are written to the ldf file. There are lots written about these algorithms, but the concepts are pretty simple. For more details, read <A title=this href="http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx">this</A>. </P>
<P>Say that we forcefully stop SQL Server, delete the ldf file, start SQL Server, see that the database isn't accessible and somehow "trick" SQL Server into creating an ldf file for us. What is the problem? Why is this so bad? The problem is that you can have no confidence in the state of your data, both at the physical level and at the logical level. Let me start explaining what I mean by the logical level and problems at this level:</P>
<P><STRONG>The logical level<BR></STRONG>By the logical level, I consider the user data. The rows in the tables, quite simply. We inserted one row in the oh table and five rows in the od table. These can be inserted into a total of two pages or 6 pages (in reality more, since each table is likely to have indexes etc, I'll touch on this when we get to the logical level). Say that three of order details rows have been written to disk, but not the order header row, and not the other two order details rows. This is just an example; you can pick and choose any combo you want. It can get just as bad as you can imagine! You might think that the pages are in the end written in the same sequence as we modified them. No, that is now how the cookie crumbles (read the article I posted link to). You can probably imagine other scenarios, closer to your data. For instance, we will not do some certain modification to a table unless some other prior modification was also performed. In essence, rebuilding a log file leave us with no confidence in the data. Are you prepared to go through your database and manually verify all the data in there? Majority of you will say "no", and it might not even be doable (data volumes, lack of information to verify etc). So, logical inconsistencies are bad. Really bad. We don't want this. Ok?</P>
<P><STRONG>The physical level</STRONG><BR>This log is not only used for "user-data". It i also used for system data. Tables has indexes, where each row in a table is reflected by a row in each index. We have allocation pages stating what storage is used. We have IAM pages and PFS pages. We have linked lists. And whatnot. You probably realize that these structures also require that some modification is performed in full or not at all. (an extent allocation will be reflected in both the GAM or SGAM page and also in th extent data itself, etc). What do you think is used to protect this? Yes, you got it, the ldf file. Trick SQL Server into re-creating an ldf file and you have all sorts of physical inconsistencies. Actually, physical inconsistencies are a bit better than logical since we do have a way to check for these. I'm of course talking about the mighty DBCC CHECKDB command, a command with lots of smarts (right Paul?) to check that the db is consistent at the physical level. And what if it isn't? CHECKDB spits out errors. Sure it has repair options, but those generally mean&nbsp;(unless you are the type of person who wins are lotteries) that the repair will rip out whatever makes the db inconsistent resulting in data loss (which also has no knowledge of what you might consider logical consistent data). </P>
<P><STRONG>So, what to do?</STRONG><BR>I hope the answer is obvious. Don't get into this situation in the first place. Don't go deleting ldf files for instance. Do take backups frequently enough so you don't end up in a situation like "I need to rebuild the log or I'm toast.". If something strange happens, don't start mucking about with the system unless you know what you are doing. Hire a specialist, call MS support&nbsp;or so. If I had a brand new Ferrari, I wouldn't&nbsp;disassemble the engine&nbsp;in case I hear weird noise from the it. Heck, I barely pop the hood of my Beamer! </P>
<P>And no, I won't get into what commands can be used to re-build the ldf file. I expect all readers of this to not get into a situation where it is needed. :-)</P>
<P>(I understand one can encounter a machine with no backups and something happens to the ldf file, and such scnearios. Of course I do. I feel really really bad every time I read about such a situation, because there is no graceful way to handle it. That is why I have such a&nbsp;harsh tone above. I don't want this to happen to anybody. One would hope that this is obvious, but nothing ever is on the Net. So I've learned. So, please leave such unconstructive comments out of the discussions!)</P>Do maintenance plans require SSIS?http://sqlblog.com/blogs/tibor_karaszi/archive/2009/08/26/do-maintenance-plans-require-ssis.aspxWed, 26 Aug 2009 10:06:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:16318TiborKaraszi<P>To many, this is a non-issue, since SSIS is installed anyhow. But not everyody installs SSIS. I for instance prefer to keep my production systems as clean as possible and only have what I really need (a principle which is harder and harder to live after as years&nbsp;go by...).&nbsp;Also, not all Editions of SQL Server comes with SSIS. </P>
<P>I did a test&nbsp;some months ago for SQL Server 2005 with a recent sp and also on SQL Server 2008.&nbsp;SQL Server 2008 did require SSIS (I tested both without and with SSIS installed), where 2005 sp2 didn't. I recently learned from Phil Brammer in MVP group that there has been progress. Here's the run-down, you don't need SSIS installed to execute maint plans:</P>
<P>SQL Server 2005 requires sp2.</P>
<P>SQL Server 2008 requires CU3 or sp1 (see <A href="http://support.microsoft.com/kb/961126/">http://support.microsoft.com/kb/961126/</A>). I dodn't test this combo (2008 sp1 without SSIS), but I have no reason to doubt that KB article. </P>Watch out for that autogrow bughttp://sqlblog.com/blogs/tibor_karaszi/archive/2009/08/05/watch-out-for-that-autogrow-bug.aspxWed, 05 Aug 2009 12:14:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15794TiborKaraszi<P>Under some circumstances, autogrow for database files can be set to some 12000 percent. I think this is limited to SQL Server 2005 and for databases upgraded from SQL Server 2000 (I didn't bother to search - feel free to comment if you know). So, if you have a reasonably sized database and autogrow kicks in, you can do the maths and realize that pretty soon you are out of disk space.</P>
<P>I wrote a proc that I schedule that check for out-of-bounds values in sys.database files. The proc generates a bunch of messages it prints (handy if you have as Agent job with output file) and also constructs an error message and does RAISERROR (handy if you implemented alerting, for instance according to <A href="http://www.karaszi.com/SQLServer/util_agent_alerts.asp">http://www.karaszi.com/SQLServer/util_agent_alerts.asp</A>). </P>
<P>I prefer to schedule below as Agent job and use Agent alerts to notify me if we do have db with autogrow out-of-whack. As always, don't use code if you don't understand it.</P><CODE style="FONT-SIZE:12px;"><SPAN style="COLOR:blue;">USE&nbsp;</SPAN><SPAN style="COLOR:black;">maint <BR>GO <BR><BR></SPAN><SPAN style="COLOR:blue;">IF&nbsp;</SPAN><SPAN style="COLOR:magenta;">OBJECT_ID</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:red;">'check_autogrow_not_percent'</SPAN><SPAN style="COLOR:gray;">)&nbsp;</SPAN><SPAN style="COLOR:blue;">IS&nbsp;</SPAN><SPAN style="COLOR:gray;">NOT&nbsp;NULL&nbsp;</SPAN><SPAN style="COLOR:blue;">DROP&nbsp;PROC&nbsp;</SPAN><SPAN style="COLOR:black;">check_autogrow_not_percent <BR>GO <BR><BR></SPAN><SPAN style="COLOR:blue;">CREATE&nbsp;PROC&nbsp;</SPAN><SPAN style="COLOR:black;">check_autogrow_not_percent <BR></SPAN><SPAN style="COLOR:blue;">AS <BR>DECLARE&nbsp; <BR>&nbsp;</SPAN><SPAN style="COLOR:#434343;">@db&nbsp;</SPAN><SPAN style="COLOR:black;">sysname <BR></SPAN><SPAN style="COLOR:gray;">,</SPAN><SPAN style="COLOR:#434343;">@sql&nbsp;</SPAN><SPAN style="COLOR:blue;">NVARCHAR</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">2000</SPAN><SPAN style="COLOR:gray;">) <BR>,</SPAN><SPAN style="COLOR:#434343;">@file_logical_name&nbsp;</SPAN><SPAN style="COLOR:black;">sysname <BR></SPAN><SPAN style="COLOR:gray;">,</SPAN><SPAN style="COLOR:#434343;">@file_phyname&nbsp;</SPAN><SPAN style="COLOR:blue;">NVARCHAR</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">260</SPAN><SPAN style="COLOR:gray;">)&nbsp; <BR>,</SPAN><SPAN style="COLOR:#434343;">@growth&nbsp;</SPAN><SPAN style="COLOR:blue;">VARCHAR</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">20</SPAN><SPAN style="COLOR:gray;">) <BR>,</SPAN><SPAN style="COLOR:#434343;">@did_exist&nbsp;</SPAN><SPAN style="COLOR:black;">bit <BR></SPAN><SPAN style="COLOR:gray;">,</SPAN><SPAN style="COLOR:#434343;">@msg&nbsp;</SPAN><SPAN style="COLOR:blue;">NVARCHAR</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">1800</SPAN><SPAN style="COLOR:gray;">) <BR>,</SPAN><SPAN style="COLOR:#434343;">@database_list&nbsp;</SPAN><SPAN style="COLOR:blue;">NVARCHAR</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">1000</SPAN><SPAN style="COLOR:gray;">) <BR><BR></SPAN><SPAN style="COLOR:blue;">SET&nbsp;</SPAN><SPAN style="COLOR:#434343;">@did_exist&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:magenta;">CAST</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">0&nbsp;</SPAN><SPAN style="COLOR:blue;">AS&nbsp;</SPAN><SPAN style="COLOR:black;">bit</SPAN><SPAN style="COLOR:gray;">) <BR></SPAN><SPAN style="COLOR:blue;">SET&nbsp;</SPAN><SPAN style="COLOR:#434343;">@database_list&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:red;">'' <BR><BR></SPAN><SPAN style="COLOR:green;">--For&nbsp;each&nbsp;database <BR></SPAN><SPAN style="COLOR:blue;">DECLARE&nbsp;</SPAN><SPAN style="COLOR:black;">dbs&nbsp;</SPAN><SPAN style="COLOR:blue;">CURSOR&nbsp;FOR <BR>&nbsp;SELECT&nbsp;</SPAN><SPAN style="COLOR:black;">name&nbsp;</SPAN><SPAN style="COLOR:blue;">FROM&nbsp;</SPAN><SPAN style="COLOR:black;">sys.databases <BR></SPAN><SPAN style="COLOR:blue;">OPEN&nbsp;</SPAN><SPAN style="COLOR:black;">dbs <BR></SPAN><SPAN style="COLOR:blue;">WHILE&nbsp;</SPAN><SPAN style="COLOR:black;">1&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:black;">1 <BR></SPAN><SPAN style="COLOR:blue;">BEGIN <BR>&nbsp;&nbsp;FETCH&nbsp;</SPAN><SPAN style="COLOR:black;">NEXT&nbsp;</SPAN><SPAN style="COLOR:blue;">FROM&nbsp;</SPAN><SPAN style="COLOR:black;">dbs&nbsp;</SPAN><SPAN style="COLOR:blue;">INTO&nbsp;</SPAN><SPAN style="COLOR:#434343;">@db <BR>&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">IF&nbsp;</SPAN><SPAN style="COLOR:#434343;">@@FETCH_STATUS&nbsp;</SPAN><SPAN style="COLOR:gray;">&lt;&gt;&nbsp;</SPAN><SPAN style="COLOR:black;">0&nbsp;</SPAN><SPAN style="COLOR:blue;">BREAK <BR><BR>&nbsp;&nbsp;SET&nbsp;</SPAN><SPAN style="COLOR:#434343;">@sql&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:red;">'DECLARE&nbsp;files&nbsp;CURSOR&nbsp;FOR <BR>&nbsp;SELECT&nbsp;CAST(growth&nbsp;AS&nbsp;varchar(20)),&nbsp;physical_name,&nbsp;name <BR>&nbsp;FROM&nbsp;'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:magenta;">QUOTENAME</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:#434343;">@db</SPAN><SPAN style="COLOR:gray;">)&nbsp;+&nbsp;</SPAN><SPAN style="COLOR:red;">'.sys.database_files <BR>&nbsp;WHERE&nbsp;is_percent_growth&nbsp;=&nbsp;1 <BR>&nbsp;AND&nbsp;growth&nbsp;&gt;&nbsp;20' <BR>&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">EXEC</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:#434343;">@sql</SPAN><SPAN style="COLOR:gray;">) <BR>&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">OPEN&nbsp;</SPAN><SPAN style="COLOR:black;">files <BR>&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">WHILE&nbsp;</SPAN><SPAN style="COLOR:black;">1&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:black;">1 <BR>&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">BEGIN <BR>&nbsp;&nbsp;&nbsp;&nbsp;FETCH&nbsp;</SPAN><SPAN style="COLOR:black;">NEXT&nbsp;</SPAN><SPAN style="COLOR:blue;">FROM&nbsp;</SPAN><SPAN style="COLOR:black;">files&nbsp;</SPAN><SPAN style="COLOR:blue;">INTO&nbsp;</SPAN><SPAN style="COLOR:#434343;">@growth</SPAN><SPAN style="COLOR:gray;">,&nbsp;</SPAN><SPAN style="COLOR:#434343;">@file_phyname</SPAN><SPAN style="COLOR:gray;">,&nbsp;</SPAN><SPAN style="COLOR:#434343;">@file_logical_name <BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">IF&nbsp;</SPAN><SPAN style="COLOR:#434343;">@@FETCH_STATUS&nbsp;</SPAN><SPAN style="COLOR:gray;">&lt;&gt;&nbsp;</SPAN><SPAN style="COLOR:black;">0&nbsp;</SPAN><SPAN style="COLOR:blue;">BREAK <BR>&nbsp;&nbsp;&nbsp;&nbsp;SET&nbsp;</SPAN><SPAN style="COLOR:#434343;">@did_exist&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:magenta;">CAST</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">1&nbsp;</SPAN><SPAN style="COLOR:blue;">AS&nbsp;</SPAN><SPAN style="COLOR:black;">bit</SPAN><SPAN style="COLOR:gray;">) <BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">SET&nbsp;</SPAN><SPAN style="COLOR:#434343;">@database_list&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:#434343;">@database_list&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:red;">'["'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:#434343;">@db&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:red;">'":&nbsp;"'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:#434343;">@file_logical_name&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:red;">'"]'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:blue;">CHAR</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">13</SPAN><SPAN style="COLOR:gray;">)&nbsp;+&nbsp;</SPAN><SPAN style="COLOR:blue;">CHAR</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">10</SPAN><SPAN style="COLOR:gray;">) <BR>&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">SET&nbsp;</SPAN><SPAN style="COLOR:#434343;">@msg&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:red;">'Out-of-band&nbsp;autogrow&nbsp;in&nbsp;database&nbsp;"'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:#434343;">@db&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:red;">'"'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp; <BR>&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="COLOR:red;">'&nbsp;with&nbsp;growth&nbsp;of&nbsp;'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:#434343;">@growth&nbsp;</SPAN><SPAN style="COLOR:gray;">+ <BR>&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="COLOR:red;">',&nbsp;logical&nbsp;file&nbsp;name&nbsp;"'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:#434343;">@file_logical_name&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:red;">'"'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp; <BR>&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="COLOR:red;">',&nbsp;physical&nbsp;file&nbsp;name&nbsp;"'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:#434343;">@file_phyname&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:red;">'"'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp; <BR>&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="COLOR:red;">'.' <BR>&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">RAISERROR</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:#434343;">@msg</SPAN><SPAN style="COLOR:gray;">,&nbsp;</SPAN><SPAN style="COLOR:black;">10</SPAN><SPAN style="COLOR:gray;">,&nbsp;</SPAN><SPAN style="COLOR:black;">1</SPAN><SPAN style="COLOR:gray;">)&nbsp;</SPAN><SPAN style="COLOR:blue;">WITH&nbsp;</SPAN><SPAN style="COLOR:black;">NOWAIT <BR>&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">END <BR>&nbsp;&nbsp;CLOSE&nbsp;</SPAN><SPAN style="COLOR:black;">files <BR>&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">DEALLOCATE&nbsp;</SPAN><SPAN style="COLOR:black;">files <BR></SPAN><SPAN style="COLOR:blue;">END <BR>CLOSE&nbsp;</SPAN><SPAN style="COLOR:black;">dbs <BR></SPAN><SPAN style="COLOR:blue;">DEALLOCATE&nbsp;</SPAN><SPAN style="COLOR:black;">dbs <BR></SPAN><SPAN style="COLOR:blue;">IF&nbsp;</SPAN><SPAN style="COLOR:#434343;">@did_exist&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:magenta;">CAST</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">1&nbsp;</SPAN><SPAN style="COLOR:blue;">AS&nbsp;</SPAN><SPAN style="COLOR:black;">bit</SPAN><SPAN style="COLOR:gray;">) <BR>&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">BEGIN <BR>&nbsp;&nbsp;&nbsp;SET&nbsp;</SPAN><SPAN style="COLOR:#434343;">@msg&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:red;">'Databases&nbsp;with&nbsp;out-of-control&nbsp;autogrow&nbsp;in&nbsp;databases:&nbsp;'&nbsp;</SPAN><SPAN style="COLOR:gray;">+&nbsp;</SPAN><SPAN style="COLOR:blue;">CHAR</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">13</SPAN><SPAN style="COLOR:gray;">)&nbsp;+&nbsp;</SPAN><SPAN style="COLOR:blue;">CHAR</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:black;">10</SPAN><SPAN style="COLOR:gray;">)&nbsp;+&nbsp;</SPAN><SPAN style="COLOR:#434343;">@database_list <BR>&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">RAISERROR</SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:#434343;">@msg</SPAN><SPAN style="COLOR:gray;">,&nbsp;</SPAN><SPAN style="COLOR:black;">16</SPAN><SPAN style="COLOR:gray;">,&nbsp;</SPAN><SPAN style="COLOR:black;">1</SPAN><SPAN style="COLOR:gray;">)&nbsp;</SPAN><SPAN style="COLOR:blue;">WITH&nbsp;</SPAN><SPAN style="COLOR:magenta;">LOG <BR>&nbsp;&nbsp;</SPAN><SPAN style="COLOR:blue;">END <BR></SPAN><SPAN style="COLOR:black;">GO <BR></SPAN></CODE>Where's that sys.messages management pack?http://sqlblog.com/blogs/tibor_karaszi/archive/2009/02/23/where-s-that-sys-messages-management-pack.aspxMon, 23 Feb 2009 19:37:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:12121TiborKaraszi<P>I've often been thinking that "somebody" should produce a list and perhaps some ready-made TSQL code of the SQL Server error messages we want to be alerted for. Something like a Management Pack, but not tied to any particular sys mgmt software. Essentially, what error messages would we typically want to be alerted for, and some ready made TSQL code which defines an alert (msdb.dbo.sp_add_alert, see also <A href="http://www.sqlskills.com/BLOGS/PAUL/post/Easy-monitoring-of-high-severity-errors-create-Agent-alerts.aspx">http://www.sqlskills.com/BLOGS/PAUL/post/Easy-monitoring-of-high-severity-errors-create-Agent-alerts.aspx</A>) and operator for each of those. </P>
<P>Honestly, how many of you have been going through all SQL Server messages and determined which of those we want to be alerted for? Sure, we can decide to specify alerts for all high-severity level&nbsp;messages (like 19, 20, 21, 22, 23, 24 and 25). But unfortunately the severity classification isn't as coherent and we would hope. </P>
<P>For instance, Paul Randal just recently blogged about error 825, which has as low severity as 10! Do we want to be alerted for 825? You bet! Check out Paul's blog at <A href="http://www.sqlskills.com/BLOGS/PAUL/post/A-little-known-sign-of-impending-doom-error-825.aspx">http://www.sqlskills.com/BLOGS/PAUL/post/A-little-known-sign-of-impending-doom-error-825.aspx</A>.</P>
<P>We can, however make it a little bit easier for us. Since Agent detects the message by sniffing the eventlog, we can immediately discard all messages which aren't written to the eventlog (there's no use defining alert for something which the alerting mechanism never detects, right?):</P>
<P><CODE style="FONT-SIZE:12px;"><SPAN style="COLOR:blue;">SELECT&nbsp;</SPAN><SPAN style="COLOR:gray;">*&nbsp; <BR></SPAN><SPAN style="COLOR:blue;">FROM&nbsp;</SPAN><SPAN style="COLOR:black;">sys.messages <BR></SPAN><SPAN style="COLOR:blue;">WHERE&nbsp;</SPAN><SPAN style="COLOR:black;">language_id&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:black;">1033 <BR></SPAN><SPAN style="COLOR:gray;">AND&nbsp;</SPAN><SPAN style="COLOR:black;">is_event_logged&nbsp;</SPAN><SPAN style="COLOR:blue;">=&nbsp;</SPAN><SPAN style="COLOR:black;">1 <BR></SPAN><SPAN style="COLOR:blue;">ORDER&nbsp;BY&nbsp;</SPAN><SPAN style="COLOR:black;">severity</SPAN></CODE> </P>
<P>Now we are down to 699 messages. It used to be that we could configure whether a message should be written to eventlog (sp_altermessage), but this functionality disappeared in 2005. I haven't checked Connect, but my guess is that if there is such a wish/entry in the first place, it doesn't have too many votes.</P>
<P>My suggestion is that we create some small community project somewhere where we add what messages we want to be alerted for. This can be as simple as a table with error numbers and some insert, and then we use this to generate our sp_add_alert commands based on that. Question is where to do this, so it can be maintained and refined over time? Or if it has already been done by somebody...</P>
<P>&nbsp;</P>