Friday, August 5, 2011

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’)
Alter database tempdb modify file (name = templog, filename = ‘E:Sqldata emplog.ldf’)

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

Tuesday, August 2, 2011

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":