Introduction I use a few richtextboxes to show some information on a screen. That information gets refreshed every 10 seconds. This application runs 24/7 and I don’t want to spend more time on it than the bare minimum. It’s cool to have but not business critical. The problem was that it had a small memoryleak. The problem The problem, it turns out, is that the richtextbox retains an unlimited amount of undo information. So everytime you update the richtextbox it adds to this undo information. The first reactions could be to use a new richtextbox every time you use need to put new text in it. However that also has some problems because it does not dispose all that well. I already blogged about that problem a while back “Not all memoryleaks are the same and USER objects.”

This past weekend I made my first foray into SQL Azure. SQL Azure has been on my list of “to learn” technologies and lately it made it closer to the top. While I had picked up some knowledge from random links in the twitter-sphere, I hadn’t made a concerted effort to learn about it yet. I started with a site named Microsoft Virtual Academy. This site (despite some usability issues with the dialog boxes prior to signup) offers a number of virtual “courses” that consist of links to external resources grouped into modules, each of which finishes with a short assessment. As I started working my way through the modules for SQL Azure it so happened that Brian Harry (blog) posted a link to a free Azure trial, which was handy since I no longer have an MSDN account (MSDN accounts are also offered a free trial, check out your subscription page for more information).

SQL Server has several options available for a stable DR/HA solution at a minimal cost. There is a grey area in which the time to start looking to products from third party vendors may allow more manageable DR/HA solutions. This grey area typically becomes more prevalent when database size grows or geographical distance is added to a SQL Server landscape. However, the features in SQL Server can scale to the higher percentages of database size that DBAs are handling. This can be seen in the survey on SQL Skills that most databases are in 100GB to 500GB range.

Please leave me a comment if you have written or have seen a trigger that is written in such a way that it will send an email when a value changes in a table. I am looking at the following question: Email trigger when data is changed ALTER TRIGGER [dbo].[RVABestellingenAantalWijzigenTrigger] ON [dbo].[RVA_Bestellingen] AFTER UPDATE AS --Vars DECLARE @body varchar(500) DECLARE @BestellingID int DECLARE @CategorieID int DECLARE @SubCategorieID int DECLARE @AantalOrigineel int DECLARE @AantalNieuw int DECLARE @LocatieNaam varchar(255) DECLARE @ComponentNaam varchar(255) DECLARE @CategorieNaam varchar(255) DECLARE @SubCategorieNaam varchar(255) DECLARE @Datum datetime if update(Aantal) /*and (SELECT Datum FROM inserted) = cast(floor(cast(dateadd(day,1,getdate()) as float)) as datetime) */ and (convert(varchar,getdate(),108)>'11:00') begin --Zetten aantallen SET @AantalOrigineel = (SELECT Aantal FROM deleted) SET @AantalNieuw = (SELECT Aantal FROM inserted) SET @BestellingID = (SELECT BestellingID FROM inserted) SET @CategorieID = (SELECT CategorieID FROM inserted) SET @SubCategorieID = (SELECT SubCategorieID FROM inserted) --Zetten locatienaam en componentnaam SELECT @LocatieNaam = ('RVA Aanpassingen Locatie: '+LocatieNaam), @ComponentNaam=OfficieleNaam, @Datum=Datum FROM RVA_Bestellingen r LEFT OUTER JOIN Locaties l on l.LocatieID = r.LocatieID LEFT OUTER JOIN Componenten c on c.ComponentID = r.ComponentID WHERE r.BestellingID = @BestellingID SELECT @CategorieNaam = Categorie FROM RVA_HoofdCategorie WHERE HoofdCategorieID = @CategorieID SELECT @SubCategorieNaam = Categorie FROM dbo.RVA_SubCategorie WHERE SubCategorieID = @SubCategorieID --Zet boyd SET @body = ( SELECT 'HoofdCategorie: ' + @CategorieNaam+ char(10)+char(13) +'SubCategorie: ' + @SubCategorieNaam+ char(10)+char(13) + 'Componentnaam: ' + @ComponentNaam + char(10)+char(13) + 'Origineel aantal: ' + CAST(@AantalOrigineel as varchar(50) ) + char(10)+char(13) + 'Nieuw aantal: ' + CAST(@AantalNieuw as varchar(50) ) + char(10)+char(13) + 'Leverdatum: ' + + convert(varchar(50),@Datum,105) ) --Mailen naar Adeline EXEC master..xp_sendmail @recipients = 'fake@fake.fake', @message = @body, @subject = @LocatieNaam end And I am just shaking my head, for one it doesn’t take into account muliple rows being updated, see Best Practice: Coding SQL Server triggers for multi-row operations for more on that topic

Prior to SQL Server 2005, determining index needs was a much more intense process. The use of tracing and reviewing queries running on SQL Server would have to be performed. In SQL Server 2005 and on, DMVs have been added which make the process more efficient and stable. Determining index needs can be done with a query instead of a resource consuming process. With these new abilities, another ugly practice arose out of SQL Server; applying all the indexes the DMVs said to apply.

Recently I presented at SQLSat 83 about VLF’s and their performance impact on our databases. I decided to write this post as well for everyone who couldn’t make it all the way to Johannesburg, South Africa. To provide a bit of history on the subject, I was tasked in March to evaluate a server which came under severe strain. After investigating the numerous issues from insufficient memory to slow internal disk space ( raised alerts in SQL log, and monitoring some of the buffer and memory counters in perfmon), I came to the intermediate solution to redo the log allocation.

Tip 11 of this series is being added in as the topic has come up during the series on several occasions. Specifically, the question was raised on the Twitter hash tag #SQLHELP of, “What are some good use-cases where setting min-server memory makes sense?” There really isn’t an answer to this question other than: the minimum memory value should always be set given the physical resources. We’ve discussed the importance of setting the MAX Memory value. Today we are going to go over the MIN Memory value. 

Welcome to T-SQL Tuesday #18, hosted by Bob Pusateri (blog | twitter). Bob, thanks for hosting. From past experience, I know it takes time and effort. Bob asked us to write about something I embraced many years ago: Common Table Expressions (CTEs). “Have you ever solved or created a problem by using CTEs? Got a tip, trick, or something nifty to share? I’d love to see your posts about any of the above.”

Introduction I did a post about [printing to a zebra printer][1] many moons ago (I had more hair back then). The Zebra printers I had (those are label printer if you really want to know) were connected to several windows xp machines. I use the EPL method because it is much faster than using windows printing on theses machines. Yesterday I installed Windows 7 on those machines. And that caused the previous function to not work anymore. The problem is in the Createfile method and it not finding a handle anymore. You get this error when you try to print.

A primary purpose of retaining data is to view that data at some point. Even when database servers have a largely OLTP basis, the need to retrieve data from the database will arise. Retrieving data in some manner is referred to as reporting. This reason the term reporting is defined here is to focus on the concept of reporting as something not only done with a feature like SQL Server Reporting Services. SQL Agent Jobs, notifications, proactive troubleshooting steps, Data Warehouse loading and other ETL processing are all a form of retrieving data. These can all be identified as a form of reporting on the database.