How to move tempdb?

Today I got a call from our Window system admin that our production server is running low on C: drive. The cause turns out that the tempdb was bloated up and ate a huge chunk of disk space on our system drive. Since we have a separate drive F with plenty of free space, the task became how to move our tempdb from C: to F:. Here is an article that sheds much light on this solution. I am giving a more detailed action path here.

First of all, locate tempdb's files:

use tempdb
go

select * from sys.sysfiles
go

Then you'll have something like this:

fileid groupid size maxsize growth status perf name filename
1 1 1024 -1 10 1048578 0 tempdev c:\temp\tempdb.mdf
2 0 64 -1 10 1048642 0 templog c:\temp\templog.ldf

And the following script will move the database files to the designated locations:

alter database tempdb
modify file (name = tempdev, filename = 'F:\SQLDATA\tempdb.mdf')
go

alter database tempdb
modify file (name = templog, filename = 'F:\SQLDATA\templog.ldf')
go

But you'll also get a message saying:

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

The final step will be to restart SQL Sever service either from the SSMS directly or through windows services. Probably it is a better idea to do it outside working hours.

DAC connection problem from SSMS

Here is a common error when using SQL Server's Dedicated Administrator Connection:

'Dedicated administrator connections are not supported. (ObjectExplorer)'

A quick answer is to NOT use 'new query' button, but instead use 'new database engine query' button.

Here is a more detailed article regarding this issue.

How to grant viewing privileges of database diagrams to a user?

This is a biting question that I just bumped into today, it turns out MS has not supplied with established mechanisms to support distributing viewing privileges for database diagrams at least for SQL 2005 or 2008. According to BOL:

"Although any user with access to a database can create a diagram once it
has been created, the only users who can see it are the diagram's creator and
any member of the db_owner role."

Drop User: The database principal owns a schema in the database, and cannot be dropped

This is a common error when dropping a user from a database when it owns some schema. Usually you can use SSMS UI to uncheck the user from the owned schema.

After locating the owned schema by the user, the correct sql statement to change the owner of a schema is:

alter authorization on schema::schema_name to user_name

How to reset a remote desktop session when you can't remote desktop to the target server

A refusing remote server can be annoying when you have ever tried to connect to it because of some urgent issue.

Solution is in this link.