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

GO

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

GO

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

2. Restart SQL Server

And BAM. The TEMPDB has been relocated for you J

Reference: http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/

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:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'RECOVERY')

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.

Today I needed to figure out how big a bunch of tables were in a database (we were in the process of pruning it and wanted to spend the correct amount only pruning what was necessary).  Luckily this is actually fairly straight forward!

SET NOCOUNT ON 
 
DBCC UPDATEUSAGE(0) 
 
-- DB size.
EXEC sp_spaceused
 
-- Table row counts and sizes.
CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [ROWS] CHAR(11),
    reserved VARCHAR(18), 
    DATA VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 
 
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 
 
SELECT *
FROM   #t
 
-- # of rows.
SELECT SUM(CAST([ROWS] AS INT)) AS [ROWS]
FROM   #t
 
DROP TABLE #t

Hope this helps you as much as it helped me!

Reference: http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx

With databases sometimes you need to run tasks that take a while in order to complete. In the past I have found it hard to judge the status of a request. For example, we do backups and restores of a 40 GB database.

Normally this doesn’t take very long to accomplish (30-40 minutes) but there is no sort of progress bar on the query to judge complete a specific task is. Luckily after hunting through Google for a while I found a query that you are able to run to find how complete system tasks are. The query is as follows:

SELECT session_id, command, percent_complete FROM sys.dm_exec_requests

This query will return a list of the commands currently running as well as the percentage complete.

session_id	command	percent_complete
##	BACKUP DATABASE	25.6985%

With databases sometimes you need to run tasks that take a while in order to complete. In the past I have found it hard to judge the status of a request. For example, we do backups and restores of a 40 GB database.

Normally this doesn’t take very long to accomplish (30-40 minutes) but there is no sort of progress bar on the query to judge complete a specific task is. Luckily after hunting through Google for a while I found a query that you are able to run to find how complete system tasks are. The query is as follows:

SELECT session_id, command, percent_complete FROM sys.dm_exec_requests

This query will return a list of the commands currently running as well as the percentage complete.

session_id	command	percent_complete
##	BACKUP DATABASE	25.6985%