Move master database from one drive to another

Sometime you may need to move master database from one drive then you may help from given below info

Open SQL Server Configuration manager from program files. In SQL Services Nodes, right clieck on SQL Server(i.e.MSSQLSERVER) and choose Properties and go to Advanced TAB and Edit Startup Parameters values to point to planned location for the master database data abd log files and then click on ok.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf


If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf

stop the instance by right click and choose stop and move the master.mdf and master.ldf to new location.

and restart the instance.

Verify the file change for the master database by running the following query.

CopySELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO

Move TempDB from one drive to another drive

Sometime you may face some problem related to Tempdb like Tempdb is full or it's affecting drive then that we need to move tempdb from one drive to another.
Please follow following steps for that.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '{new location}\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{new location}\templog.ldf');
GO

after executing above command. Please restart the SQL server Servvices.

To varify the location of the tempdb please execute following commands

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

Split Function in Separated by delimeter in a Strings of a column

Some time we need to extract the values from a string that time we use to do a lots of RND that time we can use given below function. This function is similar to the VB split function. It takes a nvarchar delimeted list and delimeter and returns a table with the values split on delimeter.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))

returns @temptable TABLE (Data varchar(8000))

as

begin

declare @idx int

declare @slice varchar(8000)

select @idx = 1

if len(@String)<1>

while @idx!= 0

begin

set @idx = charindex(@Delimiter,@String)

if @idx!=0

set @slice = left(@String,@idx - 1)

else

set @slice = @String

if(len(@slice)>0)

insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)

if len(@String) = 0 break

end

return

end

How to use this function?

Ok Let me explain

we have column jobids in a table TABLE1 which is a string. We have to extract the data separated with comma.

select * from Table1

Result

300-35,300-34

Now we have to separate the delimited by comma.

Declare @jobids as varchar(50)

select @jobids=jobids from Table1

select * from dbo.split(@jobids,',')

Result-

Data

300-35

300-34