SQL 2012 Full Restore to a new database - error Database in use

A colleague decided to use the new(ish) SQL 2012 Management Studio to restore a database to a new named database. Something that is simple in previous versions, and very useful when you don't want to overwrite an existing database. However, whenever he tried it always failed with Database in use error. Weird.

So I had a look. The user interface has changed a little, maybe to make it less confusing, however it does pretty much the same thing. After selecting the Device radio button, selecting the backup file, you now have a separate Database in the Destination section. So far so good.

Off to the Files page. This is a little clearer than before, quite like the Relocate all files to folder option. Here is where you rename the Restore As path just as before.

Next is the Options page - this is new and has a few more options. Very welcoming is the Close existing connections to destination database, however that also scares me a bit as I can see people using that to force a restore through when the error lies elsewhere. Not least because my colleague suggested using this to overcome the error. Luckily I said no !

By default the Tail-Log backup option is checked.

So if you now click OK, you get a nice progress bar at the top and the error.

Curious - this is a restore to a new database, no one is on it (I did an sp_who2 just to be sure).

The Solution

The Tail-Log backup option is interesting. On a whim, I unchecked this (it's a full backup full restore no logs to a new database - why do I want to take a Tail-Log backup ?) and it worked. In fact it is the sub option - Leave source database in restoring state - that is the issue. Just uncheck that option and it will all work.


I can see the usefulness of the Tail-Log backup option in a disaster recovery situation. Very cool option in the UI. However, I'm not sure why the Database in use error occurs and I think it is very dangerous to have to check to Server connections option. Not a good habit to get into.

Anyhow, a solution (of sorts).

TSQL Disk Usage

Only because I have a server with the Reports...Disk Usage doesn't like to work....

       
SELECT Name,  
  (CONVERT(FLOAT,size))  * (8192.0/1048576) File_Size,  
  (CONVERT(FLOAT,FILEPROPERTY(name,'SpaceUsed')))
                         * (8192.0/1048576) MB_Used,  
  ((CONVERT(FLOAT,size)) * (8192.0/1048576)
                         - (CONVERT(FLOAT,fileproperty(name,'SpaceUsed')))
                         * (8192.0/1048576)) MB_Free  
 FROM sysfiles  
ORDER BY FILEPROPERTY(name,'IsLogFile')  
 

This will show actual files sizes and MB used internally.

TFS2010 View and unsubscribe another users alerts

Unfortunately in TFS2010 there is on UI that allows you to view and remove another users post. The issue I had was that a user left, however emails were still being sent from TFS to his account.

The solution - a bit messy.

To view all alerts in a collection:

USE [Tfs_DefaultCollection]

-- Find all subscriptions
SELECT * FROM tbl_EventSubscription ORDER BY Address

*Note that the USE will need to reflect the correct collection database name


Next to unsubscribe. The follow SQL will create a command line to do that :

-- Create script to unsubscribe subscriptions
SELECT 'bissubscribe /unsubscribe /id ' + CAST(id as varchar(255))+ ' /collection http://tfs-server-name:8080/tfs/collection-name'
FROM dbo.tbl_EventSubscription
WHERE Address = 'someuser@mycompany.com'

*Note change tfs-server-name to your TFS server name, and collection-name to reflect the collection you are interested in.


Next, remote onto the TFS server. Open an elevated command line prompt. cd to the TFS installation folder \Tools.
Paste in the result line from the SQL above into the command line prompt and execute (or create a batch file, copy over to TFS server and execute it) .

Now the alert should be removed from the system.

Transactional replication, identity and NOT FOR REPLICATION

Ouch.

Here's the scenario. Have a database server (call it DBS-A, DB1) transactionally replicating to another server (DBS-B, DB1). The tables have identities on them as the unique index (to save space, and as DBS-B is readonly have no problems with duplicate data). All works fine.

So now have another server (DBS-C, DB2) replicating data into DBS-A (into another database DB2) using transactional replication. However, the replication is non standard, in that the sp_MSins_xxxx routine has been amended. This change calls a stored procedure which copies data in the DBS-A, DB1 database. This is a SELECT..INSERT type of affair which works perfectly when called from SSMS or via an application.

However, SQL Server thinks that this data is being created by replication, so obeys the NOT FOR REPLICATION flag on the IDENTITY field, and subsequently returns a not null error, expecting a value etc.

What is annoying is that when you add a table into a replication article, it automatically adds NOT FOR REPLICATION onto any IDENTITY fields and I can not see how to remove this via the UI.

After some investigation I found the following system stored procedure which will do this for you:

DECLARE @tableID INT


SELECT @tableID = object_id('myTable')
EXEC sys.sp_identitycolumnforreplication @tableID, 0

Nice !


Problem 2...
Now this seems to work perfectly on empty tables, however to my horror I found that when I tried it on a populated table (over 1million rows) that the log reader blew up with :

The process could not execute 'sp_replcmds' on 'mydatabase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Get help: http://help/MSSQL_REPL20011

The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {000017d6:000016d7:00b9}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
Get help: http://help/18805

The process could not execute 'sp_replcmds' on 'mydatabase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

Arrrrgggh !
This was traced to be definitely the table that I changed, as the error disappeared once the article was dropped.

After struggling through dropping and recreating the replication several times, the (unconfirmed as I don't want to try it again) solution appeared to be an order thing:

• Create an empty table with the same schema as the one you want to add to the replication
• Add empty table to replication
• Remove NOT FOR PUBLICATION using above stored procedure call
• Populate the data from full table to empty table
• Move any foreign keys over from referencing tables

That seems to work.

SSRS load image from external assembly

This has been a bit of and adventure to get this to work.

 

Scenario
I had a standalone C# assembly for creating data matrixes (2D barcodes) and it exported as a bitmap. This is really no different from loading a bmp from disk.

 

I wanted to create a report that displayed a data matrix per part in a table.

 

All sounds plausable!

 

Issues
There are a few issues with this:

 

1. The custom assemblies need to be installed and referenced. This needs to be set on the development PC and the server that the report is published to.

 

2. The Image control in SSRS (2005) needs to have the Value poperty set.

 

3. The Image Image expects a Byte array.

 

Configuration
Assuming that you have already created your assembly...

 

1. Copy the assembly to the following locations:
Development environment (may also be reporting server):
Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblie

 

Reporting Server:
Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

 

Note that these may be different depending on your installation.

 

2. Add reference to the assembly in your report.

• Open the report that will reference the custom assembly.
• On the Report menu, click Report Properties.
• In the Report Properties dialog box, click the References tab.
• Under References, click the ellipsis (...) button that is next to the Assembly name column header.
• In the Add References dialog box, click Browse. (In SQL Server 2005, click the Browse tab.)
• Locate and then click the custom assembly. Click Open. (In SQL Server 2005, click Add instead of Open.)
• In the Add References dialog box, click OK.
• In the Report Properties dialog box, click OK.

 

That's the reference installed.

 

3. Create custom code to use the assembly
This custom code will also need to convert the bitmap to a byte array...

• Right click the report and choose properties. Select the Code tab and enter code similar to the following :

Public Function GetMatrixImage(ByVal code As String) As Byte()

 

Dim photo as System.Drawing.Image
photo = myAssembly.GetTheImage(code)

 

Dim ms AS System.IO.MemoryStream = new System.IO.MemoryStream()
photo.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp)
Dim imagedata as byte()
imagedata = ms.GetBuffer()

 

return imagedata

 

End Function

 

4. Go to Layout tab and drop your Image control. Set it up as follows:

• Set Source = Database
• Set MIMEType = image/bmp (from the pulldown)
• Choose Expression from the Value drop down. In the Expression editor enter :

=Code.GetMatrixImage(Fields!ID.Value)

 

Where ID.Value is the key to creating/finding the image.

 

 

And that's it. Pretty easy really, however the documentation on how to do it is a bit thin. Hopefully this will help people out !

Solid State Drives for SQL Server

Interesting article on solid state drives and SQL Server.

SQL Server monitoring

Good article on SQL Server monitoring toolkit.

SSIS projects for monitoring your servers.