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


MSSQL - Extracting Part Of An EMail

24. June 2011 17:10

 

Somethnig that I wanted to do the other day was to parse out the different parts of and email address. The user and the domain part. However the data I was using was quite poor. I also wanted to be able to use this information in a computed column.

 

First of all I tried it this way.

 

User part

 

left([EMailAddress],charindex('@',[EMailAddress])-(1))

 

Domain part

 

right([EMailAddress],len([EMailAddress])-charindex('@',[EMailAddress]))

 

Which of course would crash on poor data. So they very quickly turned into functions so they were easyier to change and also decided that they should return null if there was no '@' in the data.

 

 

CREATE FUNCTION EMailExtractDomain
      (@EMail VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
      DECLARE @idx INT
      DECLARE @domain VARCHAR(MAX)

      SELECT @idx = CHARINDEX('@', @EMail)
      IF @idx = 0
            RETURN NULL

      SELECT @domain = SUBSTRING(@EMail, @idx + 1, LEN(@EMail))
      RETURN @domain
END
GO

CREATE FUNCTION EMailExtractUser
      (@EMail VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
      DECLARE @idx INT
      DECLARE @domain VARCHAR(MAX)

      SELECT @idx = CHARINDEX('@', @EMail)
      IF @idx = 0
            RETURN NULL

      SELECT @domain = SUBSTRING(@EMail, 0, @idx)
      RETURN @domain
END
GO

 

 

I also ran them though a set of quick tests.

 

SELECT dbo.EMailExtractUser('nobody@example.com'), dbo.EMailExtractDomain('nobody@example.com')
SELECT dbo.EMailExtractUser('example.com'), dbo.EMailExtractDomain('example.com')
SELECT dbo.EMailExtractUser(''), dbo.EMailExtractDomain('')
SELECT dbo.EMailExtractUser(NULL), dbo.EMailExtractDomain(NULL)

 

Enjoy Smile

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


C# / MS SQL Get inserted value of NEWSEQUENTIALID()

23. February 2011 18:22

Here is a quick guide to get the new value of NEWSEQUENTIALID() when inserting a row into a table in ms sql. If you have attempted to use NEWSEQUENTIALID() before you will know that it is only possible to use as a generated value in the table when the row is being inserted. This is a guide to get this value from c# when inserting the row. More...

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


MS SQL - Kill connections by username

22. February 2011 18:47

This can be really useful if you need to kill all the connections by a user. An example might be a out of control web application which is running under a certain user. Or because you just want to abort a single users quries because they have left an open transaction and a pile of tables locked for example.

 

CREATE PROCEDURE [dbo].[KillConnectionsUser] @username varchar(MAX)
AS
	DECLARE @spid int
	DECLARE @sql varchar(MAX)

	DECLARE cur CURSOR FOR
		SELECT spid FROM sys.sysprocesses P
			JOIN sys.sysdatabases D ON (D.dbid = P.dbid)
			JOIN sys.sysusers U ON (P.uid = U.uid)
			WHERE loginame = @username
			AND P.spid != @@SPID

	OPEN cur

	FETCH NEXT FROM cur
		INTO @spid
		
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT CONVERT(varchar, @spid)

		SET @sql = 'KILL ' + RTRIM(@spid)
		PRINT @sql
		EXEC(@sql)

		FETCH NEXT FROM cur
			INTO @spid
	END

	CLOSE cur
	DEALLOCATE cur
GO

It is really simply to use.

Just call the stored procedure with in the following way where 'Test' happens to be the name of the user you want to kill off.

 

Here is a q quick exmaple.

 

EXEC KillConnectionsUser @username = 'Test'

 

Note: If you use this on your own username it will not attempt to kill its self in the process.

 

 

 

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


MSSQL - All temp tables are global

20. February 2011 20:23

Normally temp table's are only visible to the current connection that they are originally created on. However this is not the case. This can lead to a race situation occuring between mutliple connections using temp tables. Or worse between applications that are using the same constraint name in two seperate temp tables.

If you run the following code on a connection and then on a 2nd connection (in any other database) it will fail!

IF (OBJECT_ID('tempdb..#Import') IS NOT NULL)
      DROP TABLE #Import

CREATE TABLE #Import (
      ID int
      CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED 
      (
            [ID] ASC
      )
)

The code above will fail with the following error message.

There is already an object named 'PK_ID' in the database.
Could not create constraint. See previous errors.

So there we have it. In sql server 2005/2008 normal temp table's are not just visible to the current connection. The constraint name is global in the temp table. Of course the solution to this problem is easy. That would be to use a guid for the constraint name.

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