How to move SQL Server database files to different location

How to move SQL Server database files to different location

(Works for SQL 2005/2008)

In case you need to move database files (data or log) to new location one way to do it is to detach the database, move the files to location and attach it back

1)      Detach database: Detaching a database removes it from the instance of the Microsoft SQL Server Database Engine but leaves intact the database, with its data files and transaction log files. Following script can be used

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase'

GO

2)      Move files to new location: You can manually copy the files but I prefer to use XCOPY. Here is how

xcopy "C:\DATA\MyDatabase_Data.mdf" "D:\DATA\MyDatabase_Data.mdf" /P

3)      Attach the database: Attaching a database places it in exactly the same state that it was in when it was detached. Here is how

USE master;

GO

CREATE DATABASE MyAdventureWorks

    ON (FILENAME = 'D:\DATA\MyDatabase_Data.mdf')

    FOR ATTACH;

GO

4)    Final step is to verify that files are where you expect them to be

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'MyDatabase');

Adding and removing TempDB files

Add/Remove data file to avoid TempDB Running out of space

Today I ran into a situation where there was heavy use of TempDB which caused data files to grow beyond the capacity of the drive.

So as an immediate solution I decided to add one more file to TempDB on a separate drive where there was space available. As you know this is going to be a NDF file.

To avoid new drive running out of space this new file needs to be restricted in maximum growth.

Use following script to add the file

USE [master]

GO

ALTER DATABASE [tempdb]

ADD FILE ( NAME = N'tempdev01',

FILENAME = N'D:\DATA\tempdev01.ndf' ,

SIZE = 10240000KB ,

MAXSIZE = 76800000KB ,

FILEGROWTH = 102400KB )

GO

Now once you know use of TempDB has receded and it is safe to remove the NDF file following script should be used. This is done in 2 parts.

1)      File can only be removed if it is empty so first we empty the file

use tempdb

go

DBCC SHRINKFILE ('tempdev01' , EMPTYFILE) ;

go

2)      After the file is empty then file can be removed

USE Master

GO

alter database tempdb REMOVE file [tempdev01] ;

So in short it is possible to temporarily allocate disk space to TempDB to avoid stoppage.

PS: No need to restart SQL Service for removing TempDB files

SQL Server 2008 R2 Installation error

While installing SQL Server 2008 R2 enterprise edition on WIndows Server 2008 R2 the SQL install fails with following error

TITLE: Microsoft SQL Server 2008 R2 Setup
------------------------------
The following error has occurred:
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1&EvtType=0xD15B4EB2%25400x4BDAF9BA%25401306%254024

Cause:
This happens because account under which SQL is running is not able access protected folders on C:

To resolve this issue

1. Uninstall SQL
2. Remove all folders created during install (C:\program files, C:\Program Files (X86)
3. Add SQL servier service account (Domain account in my case) to local administrators group on the server
4. Run the setup again

Some useful DMVs

/*--Gives portion of buffer used by an object. Includes index ID (if 0 then heap)
--Breaks down buffers by object (table, index) in the buffer pool
--This should be run for individual database*/

USE TESTDB
GO
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], p.[object_id],
p.index_id, COUNT(*)/128 AS [Buffer size(MB)], COUNT(*) AS [Buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
GROUP BY p.[object_id], p.index_id
ORDER BY buffer_count DESC;

/*--Provides login name and session counts for that login.
--This is useful when there are multiple app users connecting*/
USE Master
GO
SELECT login_name as [Login Name] , COUNT(session_id) AS [Session count]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY COUNT(session_id) DESC;

/*--Gives logical/physical CPU count, hyper threading and total memory*/
USE Master
GO
--SQL 2005
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time
FROM sys.dm_os_sys_info;
--SQL 2008
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)]
FROM sys.dm_os_sys_info;

64 bit OS and 32 bit perfmon

If you have a server with 64 bit OS installed on it then by default running PERFMON will start 64 bit PERFMON. This will show counters only for 64 bit applications

It is highly likely that there are 32 bit apps which are running on the server which you need to monitor. In my case it was DTEXEC used by SSIS which was 32 bit version.

So to run 32 bit PERFMON you need to do following

Start --> Run --> MMC /32 PERFMON.MSC

This will start 32 bit performance monitor and all things 32 bit as well as 64 bit can be monitored

Planning trip to Florida

When we went we took SFO-Orlando-SFO flight. Stayed in Orlando for 2 days then drove from Orlando to Key west. On way went to Miami south beach and everglades national park. Then spend 2 days in Key west. Orlando to Miami is approx 3-4 hours drive and Miami to Key west is another 3-4 hours.

Orlando -
First day we went to Epcot (http://disneyworld.disney.go.com/parks/epcot/) which is worth going to. It has small sets from different countries around the world. You can try world food and drinks.
Second day we went to Cape Canaveral - JFK Space Center which is space museum (http://www.kennedyspacecenter.com/). Cape Canaveral is about 1 hour drive from Orlando. And in end of Feb they have LAST shuttle launch scheduled which draws huge crowds so check that schedule.
Disney world is also there but mostly for kids and we have been to one in San Diego. :)
Look for park tickets online since there are good deals available. Also check in Costco they also have package deals. (I booked via AAA). Orlando we stayed at a friends place but there are many hotel options. Try to stay close to Disney downtown (all parks are around it)
Miami - South beach is THE place in miami. Good beaches so u can spend some time on way to key west. have food etc. we spent couple of hours there
Everglades national park - it is on way from Miami to Key west (http://www.nps.gov/ever/index.htm). They have small zoo and air boat rides (which is awesome). Spent about 3-4 hours here

Key west - in key west hotels r expensive so we stayed in banana bay resort Marathon, FL (http://www.bananabay.com/activities.php). Hotel is old but ok. Booked room for $67/day. They have pool side tiki bar and live music in eve . Key west is about 50 mins drive from here. In key west we did para sailing, glass boat ride, scuba diving with Fury adventures. (check their website they have a package for everything www.furycat.com). They have AAA discount.

This schedule was little hectic specially since we drove from Orlando to Key west and went to other places on the way. But the trip was totally worth money and time spent.

NetFlix Error N8156-6022: Problem with Internet connection

NetFlix Error N8156-6022: Problem with Internet connection

I got this error several times and found out that this is related to Microsoft silverlight

You will see this error when you plan some movie online (on computer) and get an error saying there is “some problem with your internet connection”

Basically this is problem with Silverlight

To fix go to
1. Close all open Internet Explorer browsers
2. Start --> Run
3. Type %allusersprofile%
4. It will take you to C:\Documents and Settings\All Users.WINDOWS
5. Make sure Show hidden files is ON in Tools --> folder options menu on windows explorer
6. Then browse to following directory
C:\Documents and Settings\All Users.WINDOWS\Application Data\Microsoft\PlayReady
7. Delete the file MSPR.HDS
8. Start IE and play the movie.