|
|
|
|
-
As promised, upon return from a trip to the frozen tundra of Northern Ontario, I am posting the latest updates from last week's release of SQL Server 2005 Service Pack 3. For those who have been waiting to test the service pack until the post-SP3 cumulative update is available, you can get it in KB #959195 and read more about it over at the SQL Server Release Services blog. I apologize for being a few days behind, as the release was pushed out on Saturday, but I had very little access to anything during my trip. For those of you keeping track, SP3 would bump your SQL Server instance to build # 9.00.4035, while the CU#1 update will bump it all the way up to 9.00.4207.
Also, if you are not ready to migrate to Service Pack 3, there is a security bulletin you should be aware of, surrounding an exploit using the extended stored procedure sp_replwritetovarbin. You can read about the issue in Microsoft Security Advisory (961040) and get some more background information at the MSRC and SVRD blogs. Note that SQL Server 2005 instances patched with SP3 or later are not affected by this vulnerability, nor is any RTM+ build of SQL Server 2008.
|
-
I recently posted about the availability of SQL Server 2005 SP3. I have a bit more information about cumulative updates and the service pack, for those of you who might be confused about the whole mess involving CU#10, CU#11, post-SP3 CU#1, and are wondering whether you need to install some pre-SP3 CUs and then SP3, or SP3 and then the post-SP CU1, or just SP3, or some other combination. I don't really blame anyone for the confusion. The Problem
The documentation that shipped with SP3 (which you can find online) has a "What's New" page that currently states:
| "Microsoft SQL Server 2005 Service Pack 3 (SP3) contains all the cumulative updates for SQL Server 2005, and fixes to issues that have been reported through our customer feedback platforms." |
The part about "all the cumulative updates" is incorrect and in the process of being changed (they added a comment to the article but it takes longer to roll out these changes to MSDN properties, never mind the Books Online application itself). The Bigger Problem
SP3 does NOT include the fixes in CU#10 and CU#11, since they were published after the service pack had begun final testing. The purpose of the post-SP3 CU#1 (which we should see no later than Friday) is to fill that gap and apply those fixes to an SP3 install. When this new cumulative update is available, at least the last time I checked, it will be posted in KB #959195. (Currently this KB yields a 404, and it has already been changed once from #960828, so I wouldn't be surprised if it changes again.)
So, if you want to be up to date, my suggestion is to wait on SP3 until CU#1 is available. If you have CU#10 or CU#11 and you apply SP3, you will LOSE YOUR CHANGES from those CUs. There will be no warning; it will just happen. Do NOT say I didn't warn you! Long Story Short
Here is a quick summary of what I think you should do. Run the following:
If the version returns < 9.00.3294:
Apply SP3 now, and then apply SP3 CU#1 when it is made available.
This will ensure you will eventually have all of the fixes from CU#10, CU#11 and SP3.
If the version returns >= 9.00.3294: You should NOT install SP3 until the forthcoming CU#1 is available.
Otherwise you will lose the fixes from CU#10/11 and will not be able to restore them until CU#1 is ready.
Of course maybe you didn't know about CU#10 and CU#11, and they have fixes you want to take advantage of now. In that case, I suggest installing CU#11 (9.00.3301, available in KB #958735) and then following the second point above... wait on SP3 until CU#1 is available. But read the rest of this article first, because there may be a more advantageous fix for you in SP3.
Other Random Notes
This is the challenge when fully testing a service pack before releasing it; there are bound to be issues in the meantime that need to be rushed out to customers, and so you have the potential for gaps on both sides: the less-tested CUs and the more stable but slightly older SPs. In this case we can see that there are fixes in SP3 that weren't in CU#11, and fixes in CU#10 and CU#11 that didn't make it in time to be in SP3. It is an interesting problem to solve without putting all potential fixes on lockdown for the duration of an SP testing cycle. Obviously this approach is not always going to be feasible, and becomes less so over time as SQL Server becomes a more complex product.
On the bright side...
With all of this confusion also comes some nice "surprise" fixes that are in SP3 but were not released in any of the CUs. One of the fixes you get in SP3 that are not included in either CU#10 or CU#11 is a CPU timing issue, which caused errors in the logs such as:
The time stamp counter of CPU on scheduler id x is not synchronized with other CPUs. |
...and...
CPU time stamp frequency has changed from x to y ticks per millisecond. The new frequency will be used. |
The details of these changes can be found in KB #931279, and have been blogged about in more detail by Bob Dorr of CSS. You can also see some earlier background on the problem, and how it was fixed for good in SQL Server 2008, here and here.
For a lot of people, this fix alone will be worth the trouble. I wonder how many people have seen these error messages in the log, and picked up the phone and blasted their reps at <insert SAN vendor here> because they assumed the error messages about I/O stalls were really indicative of poor hardware performance. I'll admit that I will need to review some of the catalysts over the past couple of years that have made me blame the hardware when the "problem" was merely a fabrication. Stay On Top Of It
You should keep an eye on the SQL Server Release Services blog over the next few days, as they will be publishing more information about these releases. I am going to be offline for several days and don't want anyone expecting me to keep updating these blog entries every 20 minutes. :-) Happy Holidays!
|
-
I have seen several people make this assumption over the past few weeks, and thought I should write up a little something to drive the point home. Primarily people are expecting to take a flat file, like a log file, bulk insert it into SQL Server, and expect SQL Server to "remember" the original ordering of the file. Some even go to the trouble of using an IDENTITY column, and rigging up a format file to skip this column, assuming that SQL Server will assign the IDENTITY values in the same order that the rows are listed in the file. Well, this may be true if you don't have a clustered index, but it is not guaranteed. Let me show you what happens when you do have a clustered index, since a lot of the time you will. First, let's have a very simple CSV file, which I will save as C:\blat.txt:
Then, a very simple format file, which I will save as C:\format.fmt:
8.0
3
1 SQLCHAR 0 12 "," 2 A ""
2 SQLCHAR 0 12 "," 3 B ""
3 SQLCHAR 0 12 "\r\n" 4 C ""
|
Now, given those two files, a repro script:
CREATE DATABASE BulkTest_Aaron;
GO
USE BulkTest_Aaron;
GO
CREATE TABLE dbo.A
(
id INT IDENTITY(1,1),
A INT,
B INT,
C INT
);
GO
CREATE CLUSTERED INDEX A ON dbo.A(A);
GO
CREATE TABLE dbo.B
(
id INT IDENTITY(1,1),
A INT,
B INT,
C INT
);
GO
CREATE CLUSTERED INDEX B ON dbo.B(B);
GO
CREATE TABLE dbo.C
(
id INT IDENTITY(1,1),
A INT,
B INT,
C INT
);
GO
CREATE CLUSTERED INDEX C ON dbo.C(C);
GO
BULK INSERT dbo.A FROM 'c:\blat.txt'
WITH (ROWTERMINATOR = '\r\n', FORMATFILE = 'c:\format.fmt');
GO
BULK INSERT dbo.B FROM 'c:\blat.txt'
WITH (ROWTERMINATOR = '\r\n', FORMATFILE = 'c:\format.fmt');
GO
BULK INSERT dbo.C FROM 'c:\blat.txt'
WITH (ROWTERMINATOR = '\r\n', FORMATFILE = 'c:\format.fmt');
GO
SELECT [t] = 'A',[id],A,B,C FROM dbo.A
UNION ALL
SELECT 'B',[id],A,B,C FROM dbo.B
UNION ALL
SELECT 'C',[id],A,B,C FROM dbo.C
ORDER BY [t],[id];
GO
USE [master];
GO
DROP DATABASE BulkTest_Aaron;
GO |
Result:
Note that only in table C, which has a clustered index on the third column (which also happens to represent the row number in the file), do we see that SQL Server has assigned IDENTITY values in the same order as the rows in the file.
People also assume that if you have data in the table that CAN be used to identify the order (e.g. a sequence number of some kind, or date/time data), and you specify the ORDER option in the BULK INSERT statement, that this will fix the "problem." Not so, as shown if you simply change the BULK INSERT statements above:
BULK INSERT dbo.A FROM 'c:\blat.txt'
WITH (ROWTERMINATOR = '\r\n', FORMATFILE = 'c:\format.fmt', ORDER(C));
GO
BULK INSERT dbo.B FROM 'c:\blat.txt'
WITH (ROWTERMINATOR = '\r\n', FORMATFILE = 'c:\format.fmt', ORDER(C));
GO
BULK INSERT dbo.C FROM 'c:\blat.txt'
WITH (ROWTERMINATOR = '\r\n', FORMATFILE = 'c:\format.fmt', ORDER(C));
GO
|
Result:
The results are the same: only the table with the clustered index that happens to match a column in the file that dictates the order do we see SQL Server obey the original ordering in the data file.
So, the moral of the story is, do not expect SQL Server to order your bulk data, logically or physically, exactly as it appears in the file. If the order in the file is important, then consider including information in the file, such that the order can be reassembled using a query against the data -- regardless of how SQL Server decided to store it.
|
-
I have been involved in several discussions over the past week involving getting more diagnostic information out of the DMVs in SQL Server 2008 and beyond. Yesterday, I asked for procedure recompiles to be tracked in the new sys.dm_exec_procedure_stats DMV: http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=388218 Today, Erland followed up with a similar request, but at the statement level (so the stats would be tracked in sys.dm_exec_query_stats): http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=388416
I also pushed an item today asking for page split information to be tracked in sys.dm_db_index_physical_stats. There is probably not enough meat there for everyone to be satisfied, but it is a start: http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=388403
There is a reluctance to add more data to the DMVs. So I urge you, if any of this information is important to you, go and vote, or otherwise make your voice heard. If you think there are better ways to derive this data, say so!
XEvents is a new way to collect trace-like data, without the overhead of SQL Trace, and without the overhead of generically collecting everything under the sun in the DMVs. The problem many have with this approach is that you still have to know what to look for, and set up XEvents to capture it, before it happens. You can read more on XEvents on MSDN: http://msdn.microsoft.com/en-us/library/bb630354.aspx And in Paul Randal's TechNet article: http://technet.microsoft.com/en-us/magazine/dd314391.aspx More specifically, Eladio Rincón talks about how to use XEvents to determine which queries are causing page splits.
|
-
UPDATE 2008-12-16, from the team, regarding SP3 CU1:
|
CU#1 on SP3 is nothing but a rollup of fixes from SP2 CU#10 and SP2 CU#11 that did not make it into SP3 release as these hotfix requests had come in during the stabilization phase of the SP3 development cycle. SP3 CU#1 does not contain any new fixes other than the ones in SP2 CU#10 and SP2 CU#11. This is just a Post Service Pack Rollup for customers who are going to upgrade from SP2 CU#10 and SP2 CU#11 to catch up on their hotfixes.
SP3 CU#1 will be released in a few days which is the first time ever we are releasing a Post Service Pack rollup in record time. Previously customers had to wait for a month at-least to get their hotfixes rolled up into the latest Service Pack. The KB article for SP3 CU#1 was published before time and as a result had to be taken down. This will become live in a few days.
|
At long last, SP3 is here. I will save my comments for later but wanted to get the news out right away!
http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4
For those that keep track, the build number is 9.00.4035.
Remember, if you are on 32-bit (including 32-bit on x64), download the file with x86 in the name. If you are running true 64-bit SQL Server on Windows x64, download the file with x64 in the name. And if you are on Itanium, you want the file with IA64 in the name...
|
-
I was tagged by Denis Gobo to write a bog post about career challenges and how I overcame them. I'm not going to tag anyone else because I can't seem to find anyone that hasn't already been tagged, and I think this thread has already "made the rounds" as it were. But I didn't want to leave Denis hanging, so here goes. :-) I'll start out by saying that some of the less technical questions I see circulated are often the most difficult to answer. Maybe I am lucky and haven't faced a whole slew of challenges in my career, and maybe it is hard to decide what to write about for fear of my perception of a "challenge" to be laughable to some of you, and downright scary to others. I'm going to stick with a couple of safe ones. Like Jason Massie, I taught myself SQL Server the hard way; in fact, I taught myself virtually everything I knew about computers before I was thrown into the industry. I did not take computer science as a degree or even in any elective courses, as I took economics in University. I bought a computer primarily to help subsidize my income, getting a quick grasp on graphic design and charging other students big bucks for high quality overheads, presentations and papers. Eventually I moved on to web sites and ASP, and ramped up quite quickly, becoming a prolific poster on a couple of IE-HTML mailing lists and all of the ASP newsgroups that the ancient Internet Mail & News program would let me consume. This community involvement led to my first job out of school (in fact the offer came before I had graduated, much to the chagrin of my classmates), a position which has evolved over time (and survived the company changing hands) but is essentially the same today. The involvement also led to my first MVP award, I think in 1998, an honor I continue to hold to this day -- even though several years ago I changed disciplines from the dying ASP technology to my current rank in the SQL Server group. In general it has been a long road for me, and while I was kind of startled by my first introduction to MS Access (I remember thinking, "Hey, this is like Excel, but harder!"), I have barely looked back. So what were a couple of bumps along the way? DBA Personality Conflicts Once I started working with databases, I was somewhat motivated and somewhat pushed by management to become the resident expert. For the first four or five years of my career, I was the only "database guy" in house. Until we bought a company with a DBA who knew SQL Server but was trained in Oracle, and they had an existing system I had to become acquainted with. This guy had the strangest ideas (to me) about how to design a database and how to write code to interact with that database. He was a big pusher of the "central identity" scheme, where one table served as the clearing house for all IDENTITY values generated in the entire system, because he didn't want any customer to have the same identity value as any book. Sensible to a point, until that clearing house table can't keep up anymore. He was also the guy who named *almost* all of his columns with the simple data type prefix, e.g. intCustomerID, strAddress1, decDiscountRate. But scattered throughout his tables were random exceptions to the rule, e.g. ContactID instead of intContactID. So programming against this nightmare schema was not fun at all; you constantly had to remember which tables had these exceptions to the rule. Performance tuning was a lost cause because everything was done using a cursor, there were nested triggers all over the place, I brought these problems (and many others) to his attention, but like most arrogant DBAs, we each felt that we were right, and the conversations went nowhere. Eventually I rebuilt the existing system completely from the ground up, and the old system was retired. This of course did not finish happening until the other DBA had moved on to browner pastures. The biggest satisfaction I got out of this was that the new system handled more volume, even though it used less hardware - we went from 3 clustered database servers + 18 application servers to a single database cluster and 6 application servers. We needed new SANs to handle the disk space for the additional volume we could handle, but that's a good problem, right? In any case, I am once again the only database guy, which works out well, except for the week prior to a 3-week vacation (hint: that is right now).
Drawing the line between design and development A less critical but more frequent challenge I come across is resisting the urge to jump across the partition wall and fix the GUI or the middle tier. People do some really strange things sometimes, and it's hard to stay in my little corner and worry only about data integrity and performance. I am big into aesthetics, and as you might have noticed from connect, I am a pretty big advocate for usability; I see failures in these areas all the time, and once in a while it is very hard to keep quiet about it... I am no C# guru, but several times I have fixed things in our web applications because I just couldn't take it anymore. I would love to be the GUI guy, and the business logic guy, and the interaction guy, and the database guy, but there just aren't enough hours in the day (nor is my head big enough) to wear all of those hats. So I continue to struggle to pick my battles.
|
-
Don't be fooled by the poor title on the page, which still says "SQL Server 2005 Books Online (September 2007)" (clearly a leftover from an earlier BOL update). The text under the overview section also mentions the September 2007 update. And the page thanking you for downloading says:
Thank You for Downloading
SQL Server 2005 Books Online (September 2007)
The file to download is definitely SqlServer2K5_BOL_Dec2008.msi so this really isn't the September 2007 update. Curious, though, when I install the update my Books Online document explorer title becomes "Books Online - SQL Server 2005 Books Online (November 2008) ..."
It would be great if the publishing process on the downloads site were less prone to these errors and inconsistencies. Most people will be fine, because they will just know that they have the latest version, in spite of the stuff around. But I think a lot of people will not discover the download because the title still says September 2007 (an easy fix, let's hope). And I think there will be other confusion when colleagues ask, "did you get the December BOL update?" "No, I only have the November, is there a newer one?"
https://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f
What has changed in BOL, you might ask? Or in other words, "should I bother downloading it?" Well, there is a new topic called "What's New in SQL Server 2005 SP3," which will be of interest to any^H^H^Hall of you interested in applying this update when it is released - probably also available online, but as others have discussed before, I want that stuff local also, even if it means I wait longer for updates. And the following sections had topic updates:
- Installation (1 update)
- Database Engine (9 updates)
- Analysis Services (1 update)
- Integration Services (3 updates)
- Replication (1 update)
- Reporting Services (8 new topics, 9 updates)
- Language Reference (15 updates)
- Tutorials, Samples, and Sample Databases (2 updates)
- Tools and Utilities Reference (1 update)
- Error Messages (9 new topics)
So, if you like having Books Online local, there is no reason you should put off downloading and installing this update wherever you use it.
|
-
| UPDATE 2008-12-03
The download packages for the database engine have returned to the download page. Make sure you download the file labeled "SQL_Server_2008_Cumulative_Update_2" to apply to machines where the SQL Server engine is present, and the file "SQL_Server_2008_RTM_CU2_SNAC" to apply to any machines that don't have a SQL Server instance but do connect to any SQL Server 2008 instance. I have asked for better descriptions on the download page to differentiate between the files, because I am sure a lot of people using SQL Server 2008 don't know the difference. Also, be sure to download the file for the correct platform! x64 if you are running 64-bit SQL Server on 64-bit Windows; x86 if you are running 32-bit SQL Server (regardless of whether the underlying OS is x86 or x64); and IA64 if you are on Itanium.
I have not yet downloaded the file and verified that the bits weren't swapped out quietly, though I have been assured internally that this is not what has happened... |
I am sure this is kind of "Chicken Little" and might only be valid for a short time, but I thought I should mention it anyway, since I am already seeing people download the native client patch and scratch their heads, wondering why it won't patch the engine. Sometime after Denis reported the availability of Cumulative Update Package 2 for SQL Server 2008 (Nov. 19th), and even after that because I downloaded it well after PASS, it has disappeared from the download page. At this moment, all that is available is the SNAC client for x86, x64 and IA64:
The files above will only patch the native client / drivers on the machine, so while it is worth the download (especially for client machines that do not host a SQL Server 2008 instance), it will not patch any engine components.
My fear, of course, is that we are re-entering familiar territory; after SP2 for SQL Server 2005 was released, one of the packages was rebuilt and redeployed, with the same build number, causing a lot of confusion. I complained about that in March of 2007 and I wasn't the only one.
Now I am on the lookout for it, and hope it is returned without incident:
Hopefully, it was not pulled intentionally, but rather inadvertently excluded from the download page temporarily. When I have more information about this, I'll post here; if you see the downloads restored in the meantime, please let me know!
|
-
Last week I was trying to troubleshoot some activation stored procedures in Service Broker. One nice thing about this framework is that it logs activation procedure errors in the SQL Server error log, since activation is not an interactive experience. You will see error messages like this:
The activated proc dbo.procedure_name running on queue database_name.dbo.queue_name output the
following: '<error message>.' |
(And yes, I could have disabled activation and debugged manually, but since the errors were few and far between and depended upon user input, this was not trivial. You can see this article for a tutorial on this, as well as some of the limitations on actively debugging a Service Broker queue. There is also useful some information in this page from Books Online.)
On the down side, loading these logs through SQL Server Management Studio is cumbersome, especially if your instance has been up for some time. You have to wait for the entire set of log data to load, and only then can you filter to restrict the results to only include error messages like the above. So I started thinking about a better way of reviewing the error logs without relying on the GUI within SSMS.
My first attempt, of course, was to load the error log files into a work table through BCP or BULK INSERT, then filter and query against it. This was troublesome because (a) I had to dynamically determine the location of the log files for each instance, and (b) the most current log file cannot be accessed in this way because it is currently in use by SQL Server. So, I could get at it this way by shutting down SQL Server and accessing the log files from another instance (or copying the most current log file and then restarting SQL Server), but this is not exactly ideal.
My next attempt was to use xp_cmdshell 'type <path>\ERRORLOG[.x]' with the same intent. This got around the "in use" problem, but it still had the issue that I needed to determine <path> on each instance; also, it introduced a new problem: the output is truncated at 255 characters, which made troubleshooting very difficult indeed, since I could not see a single complete error message.
So my third and final attempt was to use the undocumented stored procedures sp_enumerrorlogs and sp_readerrorlog. Two things you should know about these procedures: (1) they are undocumented, and therefore unsupported; and (2) the behavior can change between SQL Server releases. In fact, the output of sp_readerrorlog DID change between SQL Server 2000 and SQL Server 2005. When I first tried this procedure in SQL Server 2005, I was quite puzzled, and am amazed to still find that most references on the web still show its former behavior. However, I feel fairly confident using both procedures in SQL Server 2005 and SQL Server 2008 instances, since that is what SSMS uses to generate the log viewer within the GUI. Something to keep in mind, though, when there is a new release of Management Studio in a few years.
I wrote a stored procedure that basically provides the same output as the SQL Server error log viewer, but allows me to filter on content and on date range. On one of my more interesting systems I have 500+ databases, so another important thing I filter out of the output by default is database/log backup activity. You can add your own filters to leave out information having to do with replication, or mirroring, or cluster failovers, etc.
One surprise was realizing that the LogDate is stored in UTC and not local time. This is logical of course, but led to some finger-scratching when I was searching for errors that happened in the last 5 minutes. It would be tricky to convert all log data to local time, especially if your log files straddle a daylight savings time boundary; so just remember that when passing datetime values into the stored procedure. If you use your local time for boundaries, you might unknowingly leave out relevant events, or include irrelevant ones. Anyway, here is the procedure:
USE [MyUtilityDatabase];
GO
IF OBJECT_ID('dbo.SearchSQLErrorLogs', 'P') IS NOT NULL
DROP PROCEDURE dbo.SearchSQLErrorLogs;
GO
CREATE PROCEDURE dbo.SearchSQLErrorLogs
@SearchString NVARCHAR(255) = '',
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@IgnoreBackups BIT = 1,
@CurrentLogOnly BIT = 0
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Log', 'U') > 0
DROP TABLE #Log;
IF OBJECT_ID('tempdb..#Logs', 'U') > 0
DROP TABLE #Logs;
DECLARE
@FirstLog INT,
@LastLog INT,
@CurrentLog INT;
IF @StartDate IS NULL
SET @FirstLog = 6;
IF @EndDate IS NULL
SET @LastLog = 0;
SELECT
@StartDate = COALESCE(@StartDate, '19000101'),
@EndDate = COALESCE(@EndDate, GETUTCDATE()),
@SearchString = '%' + COALESCE(@SearchString, '') + '%';
IF @CurrentLogOnly = 0 OR COALESCE(@FirstLog, @LastLog) IS NULL
BEGIN
CREATE TABLE #logs
(
ArchiveNo TINYINT,
dt DATETIME,
sz BIGINT
);
INSERT #logs
EXEC [master].dbo.sp_enumerrorlogs
@p1 = 1;
SELECT
@FirstLog = MAX(ArchiveNo),
@LastLog = MIN(ArchiveNo)
FROM
(
SELECT
ArchiveNo,
[Start] = COALESCE(
(
SELECT dt FROM #Logs
WHERE ArchiveNo = L.ArchiveNo + 1
), '19000101'),
[End] = dt
FROM
#Logs AS L
) AS x
WHERE
[Start] BETWEEN @StartDate AND @EndDate
OR [End] BETWEEN @StartDate AND @EndDate;
IF OBJECT_ID('tempdb..#Logs', 'U') > 0
DROP TABLE #Logs;
END
IF @CurrentLogOnly = 1
BEGIN
SELECT @FirstLog = 0, @LastLog = 0;
END
CREATE TABLE #Log
(
LogDate DATETIME,
ProcessInfo VARCHAR(64),
MessageText NVARCHAR(MAX)
);
CREATE CLUSTERED INDEX d ON #Log(LogDate);
SELECT @CurrentLog = @FirstLog;
WHILE @CurrentLog >= @LastLog
BEGIN
INSERT #Log
EXEC [master].dbo.sp_readerrorlog
@p1 = @CurrentLog;
SET @CurrentLog = @CurrentLog - 1;
END
IF @IgnoreBackups = 1
BEGIN
DELETE #Log
WHERE ProcessInfo = 'Backup'
AND (
MessageText LIKE 'Database backed up%'
OR MessageText LIKE 'Log backed up%'
);
END
SELECT LogDate, MessageText
FROM #Log
WHERE LogDate BETWEEN @StartDate AND @EndDate
AND MessageText LIKE @SearchString
ORDER BY LogDate;
IF OBJECT_ID('tempdb..#Log', 'U') > 0
DROP TABLE #Log;
END
GO
|
And here is the syntax I used to discover all service broker activation errors over the past few days:
DECLARE @start SMALLDATETIME;
SET @start = DATEADD(DAY, -3, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP));
EXEC [MyUtilityDatabase].dbo.SearchSQLErrorLogs
@StartDate = @start,
@SearchString = 'activated proc%myActivationProc%on queue%myQueueName';
|
Hope you find the procedure useful. I'm all ears, of course, if you spot problems or have enhancement suggestions.
|
-
The keynote today was delivered by Ted Kummert, Corporate VP, Data and Storage Platform Division at Microsoft.
Before Ted started, Wayne Snyder showed up on stage, sitting on a hog and surrounded by smoke or dry ice or something. He talked about how PASS has grown over the past year in several areas: - chapters (136)
- membership (32,000+)
- summit + pre-con registrations (60% over last year)
He also talked about the launch of the new sqlpass.org web site, and the fact that membership is now free.
When Wayne was introducing him, he mentioned that Ted once worked at Apple, and that it was a big deal that Microsoft forgave him for that. It struck me as funny, because Apple is not the competitor that Microsoft should fear the most, IMHO.
Ted came on and gave kudos to PASS. He explained how product team members are here for us, and this is much easier to do obviously when the conference is here in Seattle.
How SQL Server 2008 Has "Delivered"
Then he started talking about how SQL Server 2008 has delivered on its four "pillars" in the company's vision for SQL Server and its surrounding technologies.
Enterprise Data Platform
- transparent data encryption
- policy-based management
Beyond Relational
- spatial / geography
- filestream
Developer Productivity
- entity framework
- IntelliSense
Pervasive Insight - data warehouse scale- query processor (star joins)
- data compression
- partition table parallelism
- change data capture
- resource governor
Ayad Shommout, Lead Technical DBA of CareGoup Healthcare Systems in Boston, talked about his environment (2000, 2005 and 2008). He explained that he intends to have all 400+ production databases on SQL Server 2008 in the short term, and was an early adopter (went live December 2007). By upgrading alone, without changing any code, they experienced a 25% performance increase. Top three features for Ayad: - PBM - enforce standards and apply best practices across various vendors and ad hoc users.
- TDE - sensitive data is encrypted even if backup is stolen.
- Audit - HIPAA compliance and can capture all actions.
Ted talked about ICE, an internal 30 | | |