Today I received the following error while trying to check in some code after a marathon night of coding:

TF30042: The database is full. Contact your Team Foundation Server administrator.

I got one of those “oh crap” sinking feelings, that some how my TFS server had decided to just die.  After doing a little research on this error, which there is very little (read close to none) information about on the internet.  So I gave up searching and decided to do a little trial and error adhock testing, and I found out that this error has nothing to do with the database but everything to do with the size of the database’s log file.  I came up with the following solution, that you will want to run in Microsoft SQL Server Management Studio:

WARNING!!! My TFS server is in a non-production environment and I am basically the only one who uses it.  Make sure to check with your network administrator and make a back up before you run the following code.

USE [master]

ALTER DATABASE [ReportServer] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [ReportServerTempDB] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [TfsWorkItemTracking] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [TfsIntegration] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [TfsVersionControl] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [TfsWorkItemTrackingAttachments] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [TfsActivityLogging] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [TfsBuild] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [STS_Config_TFS] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [STS_Content_TFS] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [TFSWarehouse] SET RECOVERY SIMPLE WITH NO_WAIT

ALTER DATABASE [ReportServer] SET RECOVERY SIMPLE
ALTER DATABASE [ReportServerTempDB] SET RECOVERY SIMPLE
ALTER DATABASE [TfsWorkItemTracking] SET RECOVERY SIMPLE
ALTER DATABASE [TfsIntegration] SET RECOVERY SIMPLE
ALTER DATABASE [TfsVersionControl] SET RECOVERY SIMPLE
ALTER DATABASE [TfsWorkItemTrackingAttachments] SET RECOVERY SIMPLE
ALTER DATABASE [TfsActivityLogging] SET RECOVERY SIMPLE
ALTER DATABASE [TfsBuild] SET RECOVERY SIMPLE
ALTER DATABASE [STS_Config_TFS] SET RECOVERY SIMPLE
ALTER DATABASE [STS_Content_TFS] SET RECOVERY SIMPLE
ALTER DATABASE [TFSWarehouse] SET RECOVERY SIMPLE 

DBCC SHRINKDATABASE(N'ReportServer')
DBCC SHRINKDATABASE(N'ReportServerTempDB')
DBCC SHRINKDATABASE(N'TfsWorkItemTracking')
DBCC SHRINKDATABASE(N'TfsIntegration')
DBCC SHRINKDATABASE(N'TfsVersionControl')
DBCC SHRINKDATABASE(N'TfsWorkItemTrackingAttachments')
DBCC SHRINKDATABASE(N'TfsActivityLogging')
DBCC SHRINKDATABASE(N'TfsBuild')
DBCC SHRINKDATABASE(N'STS_Config_TFS')
DBCC SHRINKDATABASE(N'STS_Content_TFS')
DBCC SHRINKDATABASE(N'TFSWarehouse')

The above code will actually put all the TFS databases in Simple Recovery mode, which basically means no log file, and then shrinks all the log files that were previously in use. After you run this script in Microsoft SQL Server Management Studio you should not get this error message anymore, when you try to check in your files.

Tags: SQL Server, TF30042, TFS

Posted in How To, SQL | | | View blog reactions | 3 Comments »

I just recently read Jeff Atwood’s Deadlocked! article. I just wanted to give some more insight in to the read committed snapshot so that it is not perceived as “magic”. It has some definite advantages when dealing with deadlocks, however if your code relies on row level locking you are not going to be able to use this type of reading in SQL Server.

First lets talk about how you enable it. It is not a transactional isolation level, so if you set it, it will effect your whole database. You have been warned!

alter database [YourDatebaseHere]
set read_committed_snapshot on
go

Basically what this does is create a snapshot or read-only database of your current results that is separate from your live database. So when you run a SELECT statement, to read your data, you are reading from a read-only copy of your database. When you change your database, it happens on the live database, and then a new copy or snapshot is created for reading against.

Personally I am using it on IdeaPipe, because like most Web 2.0 applications there are a heavy amount of reads and very few updates that effect the row. So chances are if you have a website this will decrease your number of deadlocks. But make sure to test thoroughly before implementing read committed snapshot.

When I was doing my initial research a while ago I found this article talking about how snapshot isolation can bite you where it hurts.

For example, suppose READ COMMITTED SNAPSHOT is not enabled in the database and you want to assign one more ticket to a person, but only if that user does not already have high priority tickets:

BEGIN TRANSACTION
UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 1
AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')
--- do not commit yet

Note that you have not explicitly specified an isolation level, so your transaction runs under the default READ COMMITTED level. If another connection issues a similar update:

UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 2
AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')

it will hang in a lock waiting state. Once you commit your first transaction the second one will complete, but it will not assign ticket 2 to user 6, which is the correct behavior as designed.

However if read committed snapshot is enabled on the database the user will end up with two high priority tickets, because the first read happens against a snapshot and the update happens against the live database. So this will obviously cause problems for specifications and business rules that rely on row level locking. So be careful, and make sure you specifically know what is happening with your code before turning this on

Note: Chances are if you are using LINQ you don’t have to worry about the above scenario, however I am not a DBA expert, only a student of the practice. So take what I say with a grain of salt.

Tags: database, LINQ, read committed snapshot, SQL Server

Posted in How To | | | View blog reactions | 2 Comments »

I have been a huge fan of MySQL for a long time.  It is the perfect database for when the budget is tight or you are not working in a Microsoft Environment.  It performs well, and has a huge following of dedicated professional programmers that use it day in and day out on some of the largest websites on the planet.  Most noteable Facebook, Twitter, Flickr, and Digg.  Even with all these proven capabilities to scale and perform, Microsoft has choosen to ignore it and focus on some of the monolytic providers of databases such as IBM and Oracle when comparing SQL Server.

However that has all changed with the release of Microsoft SQL Server 2008.  Microsoft has set its focus on MySQL.  This is a huge turning point for both companies, because it means Microsoft is starting to take the needs of the Web 2.0 crowd, which MySQL has dominated, just as seriously as the big iron installs they have always catered to.

I am not sure if this comparison has been spured by the purchase of MySQL by Sun Microsystems, or if Microsoft has started to feel the preasure from Web 2.0 MySQL installs, or a little of both.  But none-the-less this is very encouraging, because it means that Microsoft is finally taking the needs of the “cloud developers” seriously.

Tags: Cloud Developers, Microsoft, MySQL, SQL Server, Sun Microsystems

Posted in News | | | View blog reactions | 3 Comments »

Recently I change the network name of one of my servers at work, because the box changed its job from a virtual machine server to the database server. Everything was going great until I decided to setup the server for replication and received the following error message.

New Publication Wizard
——————————

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘old_name’. (Replication.Utilities)

——————————
OK
——————————

So with a little hunting and SQL queries I found out that SQL Server doesn’t use the network name, it only excepts that as an alias. My SQL Server instance was still named “old_name”. I found that out by running these two queries:

sp_helpserver
select @@servername

So in order to get the network name and the SQL Server instance name back in sync I had do these steps:

1. Run this in Microsoft SQL Server Management Studio:

   sp_dropserver 'old_name'
   go
   sp_addserver 'new_name','local'
   go

2. Restart SQL Server service. I prefer the command prompt for this, but you can just as easily do it in Services under the Control Panel
   net stop mssqlserver
   net start mssqlserver

Then after that is done run this again, to make sure everything is changed:

sp_helpserver
select @@servername

I don’t understand why SQL Server uses it’s own name versus the network name, might be due to the fact you can have multiple SQL Server instances install on one machine. It wasn’t too hard to change and probably stems from the days when SQL Server was known as Sybase, all in all, I learned something new and it only took 30 minutes of my day to fine the answer.

Tags: How To, NAT, Network, SQL, SQL Server, T-SQL

Posted in How To, SQL | | | View blog reactions | 15 Comments »