Moving the system Database in SQL Server

Moving the system Database in SQL Server

Below are the simple tips for moving system databases to differnet location.


Master Database =>

right-click on SQL Server in Enterprise Manager (EM) and choose Properties.
click the Startup Parameters

Replace the path for -d and -e parameter with new one

-d New patha\msater.mdf.
-l New patha\msatlog.ldf

Stop SQL Server and move the master data and log files to its new location.
start SQL Server and you have successfully moved your Master



Tempdb Database =>
open query analyzer and run the following query:

use master go
Alter database tempdb modify file (name = tempdev, filename = ‘E:Sqldata empdb.mdf’)
go
Alter database tempdb modify file (name = templog, filename = ‘E:Sqldata emplog.ldf’)
Go

Stop sql server move tempdb files to new location and delete the older one.
Restart sql server


MSDB Database =>

Right-click the SQL-Server name and click properties.
choose your startup parameters.
enter the parameter -T3608.
Restart SQL Server.
detach the database and move them to their appropriate place.
and reattach it

Checkpoint Vs LazyWriter

Difference between Checkpoint and LazyWriter

C: Flush dirty pages to Disk
L: Flush dirty pages to disk.

C: Flush only Data pages to disk
L: Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)

C: Default, Occurs approximately every 1 minute
L: Occurs depending upon memory pressure and resource availability

C: Can be managed with sp_confige -recovery interval option
L:It is lazy, Sql server manages by its own.

C: Does not check the memory pressure
L:Monitor the memory pressure and try maintain the available free memory.

C: crash recovery process will be fast to read log as data file is updated.
L: No role in recovery

C: Occurs for any DDL statement
L: Occurs per requirement

C: Occurs before Backup/Detach command
L: Occurs per requirement

C: Depends upon the configuration setting, we can control.
L: Works on Least recent used pages and removed unused plans first, no user control.

C: for simple recovery it flush the tlog file after 70% full.
L: No effect on recovery model.

C: can manually /Forcefully run command “Checkpoint”
L: No command for Lazy Writer

C: Very Less performance impact
L: No performance impact

Checkpoint can be run manullay using command "Checkpoint":