MSSQL - Finding the database restore history

7. August 2012 21:12

 

Did you know you can view the database restore history from MSSQL server. The following SQL will provide a list of all database restores that have been performed on a server at some point.

 

 

SELECT 
	destination_database_name, server_name AS 'SourceServer', database_name AS 'SourceDB', physical_device_name, type, backup_start_date, restore_date
	FROM msdb.dbo.restorehistory AS rh
		INNER JOIN msdb.dbo.backupset AS bs ON bs.backup_set_id = rh.backup_set_id
		INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.media_set_id = bs.media_set_id
	ORDER BY restore_date DESC;

 

 

It will produce output that looks like this.

 

 

Stev	DC01	Stev	E:\MSSQL-Backup\Stev.bak	D	2011-09-17 08:43:58.000	2011-09-17 09:10:25.283

 

E-mail Kick it! DZone it! del.icio.us Permalink


MSSQL - Checking Uptime

24. September 2011 18:00

 

In MSSQL Server it is posisble to fine the date and time the server was started. This can be useful if you have an issue should sql server be crashing or some such and somebody has set the service to automatically restart if it crashed. This could go unnoticed by people except for mayby a random sql disconnect error messages from an application then they appear to work perfectly a minute later.

 

To get the sql startup time we can use the following.

 

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

 

The above will give up the sql start date time. However for this we want the time in minutes.

 

SELECT DATEDIFF(MINUTE, sqlserver_start_time, GETDATE()) FROM sys.dm_os_sys_info

 

Using the above information we can now generate a simple script and send out an email alert when sql server restarts.

 

DECLARE @TimeFromStart int
SELECT @TimeFromStart=DATEDIFF(MINUTE, sqlserver_start_time, GETDATE()) FROM sys.dm_os_sys_info

IF (@TimeFromStart <= 60)
BEGIN
	EXEC msdb.dbo.sp_send_dbmail
		@recipients = 'example@example.com',
		@body = 'SQL Server Was Restarted!',
		@subject = 'Alert: SQL Server Restarted'
END

 

You can then add this to a job and set it to run hourly. At this point you will then get email alerts whenever your sql server is restarted.

E-mail Kick it! DZone it! del.icio.us Permalink


C# - MSSQL Random Timeouts

30. August 2011 21:44

 

If you have ever come across a c# application or any asp.net application for that matter that appears to work perfectly then without warning starts to fail with database timeout issues. There is one of two things normally wrong. Either it is a database problem. Or another rather common problem is actually in the code inside the application. This is when the developers and the database guys normally start screaming at each other. This normally happens because the developers cannot reproduce any problems in their enviroment and the dba's know that there database is in perfect working order and see little or no load on the sql server.

This can happen if you have the following code ...

 

 

SqlConnection Conn = new SqlConnection(Args);
SqlCommand cmd = new SqlCommand("SELECT * FROM TABLE WHERE x = y");
Conn.Open();

cmd.Connection = Conn;
cmd.ExecuteNonQuery();
cmd.Dispose();

Conn.Dispose();
Conn.Close();

 

 

It looks like some pretty simple code. It works too ... Or does it?

Lets have a look for a minute at the same code which handles exceptions.

 

while (true)
{
    try
    {

        SqlConnection Conn = new SqlConnection(Args);
        SqlCommand cmd = new SqlCommand("SELECT * FROM TABLE WHERE x = y");
        Conn.Open();

        cmd.Connection = Conn;
        cmd.ExecuteNonQuery();
        cmd.Dispose();

        Conn.Dispose();
        Conn.Close();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

 

 

The above looks pretty good to. However there is a pretty nasty bug in it. To a developer in a test enviroment it looks really good and appears to work. So what happens when something invalid happens in the sql command? You are probably thinking that the sql works and is correct. The sql may be correct but what about a bug / issue about a key or a constraint violation? Well the sql is valid but it will still produce an error.

 

The error of course is being logged. However the Conn.Dispose / Conn.Close will never be called this will mean that you are now holding an open connection to the database until the c# garbage collector decides to collect the database connection. This actually creates a problem when you have a number of errors before connection is destroyed in some way. The SqlConnection class uses a "pool" of connections in the background by default which is used to hold open connections to the database server. This is done to improve the performance and latency of opening and closeing database connections to a server. After an application has been running for a while and there is a number of opened connections to the server the SqlConnection open / close functions don't actually open and close connections. It is more like they are only getting and returning a spare connection from the pool in the background. So if you have an unmatch number of open and close calls being made specificly when you call open without calling close then the pool will run out of connections.

 

Try running the following code

 

 

class Program
{
    static void Main(string[] args)
    {
        string Args = "Data Source=SQL;Initial Catalog=Mess;Integrated Security=True;Connect Timeout=10;Timeout=10;Max Pool Size=10;";

        while (true)
        {
            try
            {
                Console.WriteLine("Trying");
                SqlConnection Conn = new SqlConnection(Args);
                SqlCommand cmd = new SqlCommand("SELECT INVALID");
                Conn.Open();

                cmd.Connection = Conn;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                Conn.Dispose();
                Conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

 

The output however will be something like this....

 

Invalid column name 'INVALID'.
Trying
Invalid column name 'INVALID'.
Trying
Invalid column name 'INVALID'.
Trying
Invalid column name 'INVALID'.
Trying
Timeout expired.  The timeout period elapsed prior to obtaining a connection fro
m the pool.  This may have occurred because all pooled connections were in use a
nd max pool size was reached.
Trying
Timeout expired.  The timeout period elapsed prior to obtaining a connection fro
m the pool.  This may have occurred because all pooled connections were in use a
nd max pool size was reached.
Trying

 

Note: there will be a delay of the "Connect Timeout" for each of the timeout messages. In this case to trigger the bug I have limited the connection pool to 10 to produce the error quickly. The default maximum connections in .net is 100 connections. This can explain why a bug like this can hide for a long period of time.

 

Fixing the issue is simple. Move the Conn.Close / Conn.Dispose outside of the exception handler. It sems however lots of developers seem to think this is enough. Then they copy and paste the same sql code all over there application instead of creating a supporting class for running all of their sql. I made a previous post about code organization and database connections.

 

E-mail Kick it! DZone it! del.icio.us Permalink


MSSQL - Saving memory by using stored procedures

11. August 2011 08:00

 

Normally when people are trying to put a point across about using stored procedures in an application. The most common thing that is normally pointed out is about network bandwidth is normally presented. Some other things like memory saving are normally missed. This is an example on how to lower the memory footprint in sql server when using stored procedures instead of sql queries being stored in the application and being passed.

 

If you have looked into this before then you will be aware that using a stored procedure will save sql server compiling execution plans when using stored procedures. This also saves some memory. The following example will show how and just how much memory is being saved for a single query.

 

In the following example I have a service running collecting netflow packets from a cisco router and saving these in an sql table that has the following format.

 

 

CREATE TABLE [dbo].[Flow](
	[FlowID] [uniqueidentifier] NOT NULL,
	[Router] [varchar](15) NOT NULL,
	[Source] [varchar](15) NOT NULL,
	[SourcePort] [int] NOT NULL,
	[Destination] [varchar](15) NOT NULL,
	[DestinationPort] [int] NOT NULL,
	[InterfaceIn] [int] NOT NULL,
	[InterfaceOut] [int] NOT NULL,
	[Packets] [bigint] NOT NULL,
	[Bytes] [bigint] NOT NULL,
	[Protocol] [int] NOT NULL,
	[LoggedAt] [datetime] NOT NULL,
 CONSTRAINT [PK_Flow] PRIMARY KEY CLUSTERED 
(
	[FlowID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Flow] ADD  CONSTRAINT [DF_Flow_FlowID2]  DEFAULT (newsequentialid()) FOR [FlowID]
GO

 

 

The data is being stored with the following insert statement which is being called from the service each time.

 

 

INSERT INTO Flow (Router, Source, SourcePort, Destination, DestinationPort, InterfaceIn, InterfaceOut, Packets, Bytes, Protocol, LoggedAt) VALUES(@Router, @Source, @SourcePort, @Destination, @DestinationPort, @InterfaceIn, @InterfaceOut, @Packets, @Bytes, @Protocol, @LoggedAt)

 

 

This looks stright forward enough. However if you examine the query plan cache by using the following sql.

 

SELECT
	P.usecounts AS TotalCount,
	P.size_in_bytes,
	SqlText.text
	FROM sys.dm_exec_cached_plans AS P
		CROSS APPLY sys.dm_exec_sql_text(P.plan_handle) AS SqlText
	WHERE SqlText.text like '(@Router%'
	ORDER BY P.usecounts DESC

 

 

It will show how many times the sql has been compiled to an execution plan and cached. It will also give usage counts for each of the plan caches. The following has been trimmed. There was originally about 30 rows of output for a single query.

 

 

TotalCount	size_in_bytes	(No column name)
56192	65536	(@Router nvarchar(12),@Source nvarchar(10),@Sourc
32482	65536	(@Router nvarchar(12),@Source nvarchar(11),@Sourc
15267	65536	(@Router nvarchar(12),@Source nvarchar(10),@Sourc
14742	65536	(@Router nvarchar(12),@Source nvarchar(11),@Sourc
11676	65536	(@Router nvarchar(12),@Source nvarchar(10),@Sourc
10953	65536	(@Router nvarchar(12),@Source nvarchar(10),@Sourc
10612	65536	(@Router nvarchar(12),@Source nvarchar(10),@Sourc
9512 	65536	(@Router nvarchar(12),@Source nvarchar(12),@Sourc

 

 

A number of cached plans have been created because the passed sql from the program has a number of differences. In particular as the length of the strings change the sql is using different types because the data length of the type has changed and sql server considers this to be a different type being passed. This causes sql server to create a new execution plan for every different length of string. This can result in a single query producing multiple execution plans which is using more memory. In my example above the total memory usage for all the execution plans was around 5MBytes. This may not sound much but I was only passing 3 strings which are only variable in length between 8 and 15 long.

 

When using a stored procedure the above issue with the execution plans does not occur.

 

When changing the insert statement to a simple stored procedure. 

 

 

CREATE PROCEDURE [dbo].[FlowInsert]
	@Router VARCHAR(15),
	@Source VARCHAR(15),
	@SourcePort int,
	@Destination VARCHAR(15),
	@DestinationPort int,
	@InterfaceIn int,
	@InterfaceOut int,
	@Packets bigint,
	@Bytes bigint,
	@Protocol int,
	@LoggedAt datetime
AS

INSERT INTO Flow
(Router, Source, SourcePort, Destination, DestinationPort, InterfaceIn, InterfaceOut, Packets, Bytes, Protocol, LoggedAt)
VALUES(@Router, @Source, @SourcePort, @Destination, @DestinationPort, @InterfaceIn, @InterfaceOut, @Packets, @Bytes, @Protocol, @LoggedAt)

GO

 

Then looking into the execution plan cache again using the sql above it will be reduced to a single entry using a total of 57kbytes of memory. That is saving around 4-5MBytes of memory which sql server can use else where. This may not sound like much but considering that it is a single query and it can be common for even small applications to have 100 or more quries it can be saving quite significant amounts of memory.

E-mail Kick it! DZone it! del.icio.us Permalink


MSSQL - Last Backup time / size

9. August 2011 18:00

 

In mssql server it is possible to find the last backup time as well as the largest size or compressed size of the backups. This can be down by listing the databases using the system table sys.databases and reading from the msdb.dbo.backupset which is one of the tables that is updated when a backup is taken on the server. The sizes returned will be in bytes.

 

SELECT db.name AS DBName,
	COALESCE(CONVERT(VARCHAR(MAX), MAX(bs.backup_finish_date), 101), 'N/A') as LastBackup,
	MAX(bs.backup_size) AS LargestBackup,
	MAX(bs.backup_size) AS LargestCompressedBackup
	FROM sys.databases db
	LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = db.name
GROUP BY db.name
ORDER BY db.name

 

It would also be possible to include the maximum time a backup has taken for a database by modifiing the sql above and using datediff and max on the start and finish time of the backup. So the following can be used to find out how much space and time a backup will be required for each database.

 

SELECT db.name AS DBName,
	COALESCE(CONVERT(VARCHAR(MAX), MAX(bs.backup_finish_date), 101), 'N/A') as LastBackup,
	COALESCE(CONVERT(VARCHAR(MAX), MAX(DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date)), 101), 'N/A') as RunTime,
	MAX(bs.backup_size) AS LargestBackup,
	MAX(bs.backup_size) AS LargestCompressedBackup
	FROM sys.databases db
	LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = db.name
GROUP BY db.name
ORDER BY db.name

 

The msdb.dbo.backupset is usful for finding out the entire history of backups taken on a database. More information can be found out about it at http://technet.microsoft.com/en-us/library/ms186299.aspx

 

E-mail Kick it! DZone it! del.icio.us Permalink