I am putting database mail on SQL server 2005 into use more and more these days with my consutling projects and it is making things much easier on me.
It is very easy to trigger emails based on transactions that happen on the database - like payments posted, account goes below a certain balance etc.
It is also nice to use some of the included views to see what is the status of your email messages.
If a message that I send did not reach the recipient I can check:
SELECT * FROM msdb.dbo.sysmail_faileditems
to look for that message.
Once I find it there I can can look at the mailitem_id in the msdb.dbo.sysmail_event_log view.
Happy mailing!
Thursday, January 10, 2008
Wednesday, November 21, 2007
Where did my SQL Server Memory go?

In managing one of my servers I had to make sure everything was running the way it should. I am working on fairly big databases (in the order of 10s of gigabytes) and I wanted to make sure SQL is using all the memory it has available.
The server I am working on is an x64 windows 2003 enterprise running SQL 2005 enterprise 32 bit. The server is also equipped with 12GB of ram.
The server I am working on is an x64 windows 2003 enterprise running SQL 2005 enterprise 32 bit. The server is also equipped with 12GB of ram.
Whenever I would look into the task manager or the performance monitor the only memory allocation I see for SQL server is around 202 MB which makes no sense.
If you look at the screen capture enclosed you can see the memory allocation for SQL server.
I did some research on the subject and found the answer. SQL has a dynamic management view called sys.dm_os_memory_clerks. This view described in http://technet.microsoft.com/en-us/library/ms175019.aspx has detailed memory allocation information for SQL server. When you examine this view you can see a column that details AWE memory allocations that are detailed in the awe_allocated_kb column, in my case this was summed to the 10GB I assigned to the server. There are other useful rows columns in the view and they are documented in the technet article mentioned here.
One useful query i run constantly:
SELECT
SUM(awe_allocated_kb)/1024 AS [AWE_MEM_MB],
SUM(virtual_memory_reserved_kb)/1024 AS [VM_RES_MB],
SUM(virtual_memory_committed_kb)/1024 AS [VM_COM_MB],
SUM(shared_memory_reserved_kb)/1024 AS [SM_RES_MB],
SUM(single_pages_kb )/1024 [SNG_PAGE_MB],
SUM(multi_pages_kb )/1024 [MLT_PAGE_MB]
FROM sys.dm_os_memory_clerks
This returns the memory allocation totals in megabytes.
Thursday, November 15, 2007
Running C# and VB.Net Code on Microsoft SQL Server - Slides code and database
Thanks for coming to the November PSSUG Meeting at Microsoft.
All and all it went pretty well with a couple of Visual Studio crashes, SQL server acting funky but that comes with the territory.
As promised the code is available by clicking below:
Code, database and slides from Running C# and VB.Net Code on Microsoft SQL Server talk
I had to remove the ANTLR part of the demo since there are IP issues involved.
To get this running extract the zip file to drive c:\
it will create a folder c:\SQL_CLR
Attach the database in c:\SQL_CLR\db to sql server
You can look at a word document in c:\SQL_CLR\powerpoint\ for instructions to run the demo.
The slides are in c:\SQL_CLR\powerpoint\ also.
To run the solution open:
C:\SQL_CLR\SimpleFunctions\SimpleFunctions.sln
Good luck and let me know if you have any questions!
Sagi
All and all it went pretty well with a couple of Visual Studio crashes, SQL server acting funky but that comes with the territory.
As promised the code is available by clicking below:
Code, database and slides from Running C# and VB.Net Code on Microsoft SQL Server talk
I had to remove the ANTLR part of the demo since there are IP issues involved.
To get this running extract the zip file to drive c:\
it will create a folder c:\SQL_CLR
Attach the database in c:\SQL_CLR\db to sql server
You can look at a word document in c:\SQL_CLR\powerpoint\ for instructions to run the demo.
The slides are in c:\SQL_CLR\powerpoint\ also.
To run the solution open:
C:\SQL_CLR\SimpleFunctions\SimpleFunctions.sln
Good luck and let me know if you have any questions!
Sagi
Monday, September 17, 2007
Updated Code and Slides for the SQL CLR Integration Talk
I updated the code and the slide for the SQL Integration talk that I gave in Code Camp 2007.2 in Philadelphia.
The slides code and slides are available here:
http://www.simplicityc.com/CodeCamp2007_CLR_Integration.zip
I added a readme file that explains how to run these samples.
There are samples for:
-SQL Stored Procedure written in C#, it uses return parameters, the message channel, internal connections to the database.
-User defined Aggregate function in C#
-Table valued function in VB.Net
-Scalar Values functions in C# and VB.Net
And a couple more
The slides code and slides are available here:
http://www.simplicityc.com/CodeCamp2007_CLR_Integration.zip
I added a readme file that explains how to run these samples.
There are samples for:
-SQL Stored Procedure written in C#, it uses return parameters, the message channel, internal connections to the database.
-User defined Aggregate function in C#
-Table valued function in VB.Net
-Scalar Values functions in C# and VB.Net
And a couple more
Saturday, September 15, 2007
Thanks for coming to my talk in code camp!
Thanks for coming to my talk in code camp.
I will post the revised code on the blog tomorrow - need to run to a wedding right now.
There were serveral examples I didn't have a chance to go through but they will be in my code and slided that will be posted - tomorrow.
Enjoy the rest of the weekend!
I will post the revised code on the blog tomorrow - need to run to a wedding right now.
There were serveral examples I didn't have a chance to go through but they will be in my code and slided that will be posted - tomorrow.
Enjoy the rest of the weekend!
Friday, September 14, 2007
Code for Philly Code Camp 2007.2
The code for my session in Code Camp is available here:
http://www.simplicityc.com/CodeCamp2007_CLR_Integration.zip
If you want to run the samples you will need SQL server 2005 and Visual Studio.Net 2005.
The slides are not yet ready but they will be posted here before the talk.
http://www.simplicityc.com/CodeCamp2007_CLR_Integration.zip
If you want to run the samples you will need SQL server 2005 and Visual Studio.Net 2005.
The slides are not yet ready but they will be posted here before the talk.
Thursday, September 6, 2007
Code Camp 2007.2 Running C# and VB.Net Code on Microsoft SQL Server
I will be speaking at the Philly Code Camp 2007.2 on the subect of integrating C# and VB.Net Code on MS-SQL server 2005.
I will post my slides and code files here in a couple of days.
This is a link to the event on the PhillyDotNet website:
http://www.phillydotnet.org/Default.aspx?tabid=598
Abstract for the talk
SQL server 2005 allows .NET code to be run as functions, stored procedures, aggregate functions and other objects. In this session we will explore the integration of the .Net CLR with SQL server. We will go over the implications of running managed and unmanaged .Net code within SQL server 2005. We will talk about some useful applications for this type of integration and then we will get our hands dirty. We will create several objects in Visual Studio and deploy them in SQL server. Time permitting we will either go into debugging or informal performance testing by comparing .Net functions to native T-SQL code. This talk is geared towards DBAs and programmers alike. The code and slides will be posted ahead of time at http://blog.shkedy.com (check the night before the talk). If you are interested in running the code you will need SQL server 2005 and Visual Studio 2005 although it is not required in order to benefit from the session.
I will post my slides and code files here in a couple of days.
This is a link to the event on the PhillyDotNet website:
http://www.phillydotnet.org/Default.aspx?tabid=598
Abstract for the talk
SQL server 2005 allows .NET code to be run as functions, stored procedures, aggregate functions and other objects. In this session we will explore the integration of the .Net CLR with SQL server. We will go over the implications of running managed and unmanaged .Net code within SQL server 2005. We will talk about some useful applications for this type of integration and then we will get our hands dirty. We will create several objects in Visual Studio and deploy them in SQL server. Time permitting we will either go into debugging or informal performance testing by comparing .Net functions to native T-SQL code. This talk is geared towards DBAs and programmers alike. The code and slides will be posted ahead of time at http://blog.shkedy.com (check the night before the talk). If you are interested in running the code you will need SQL server 2005 and Visual Studio 2005 although it is not required in order to benefit from the session.
Subscribe to:
Posts (Atom)