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
MCITP

Friday, August 5, 2011
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":
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":
Subscribe to:
Posts (Atom)