Categories Tags


Today, I realized that I had accidentally placed the TEMPDB on the wrong disk drive. At first I thought that I may need to re-install SQL Server 2012 because it is a system database. But, luckily you do not!

All I ended up having to do is the following:

  1. Run this script:

USE master


ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'd:\data\tempdb.mdf')


ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'e:\data\templog.ldf')

  1. Restart SQL Server

And BAM. The TEMPDB has been relocated for you J


Posted in sql-server


Today I ran into an issue where the Object Explorer wasn't responding when I tried to get the properties of a database, but I was curious what the recovery model of my database was. Instead of waiting until the process which was blocking access to the database properties, I queried the database itself to see what the recovery model was.  The query to do so was fairly straightforward:


Posted in sql-server


Today I needed to convert an integer into a uniqueidentifier column. At first, I thought that SQL Server would just allow me to do it intrinsically (because I can go the reverse way). However, surprisingly this was not the case. I got the following error message:

Msg 529, Level 16, State 2, Line 1

Explicit conversion from data type int to uniqueidentifier is not allowed.

So, I hit up my trusty Google and found that you actually need to do the following:

  • Create a new column (if putting on a table) of type uniqueidentifier
  • UPDATE dbo.Table SET GuidColumn = CONVERT(VARBINARY(16), IntegerColumn)

And then you are done.

Posted in sql-server