Home » SQL Server

Category Archives: SQL Server

How to Move MSDB & Model SQL Server system Databases ?


Steps of Moving MSDB & Model SQL Server system Database to new locaation:-

1) Check current location of MSDB & Model Databases by executing below query

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id in (DB_ID(‘MODEL’),DB_ID(‘MSDB’));

You can also use “Execc SP_HelpDB ‘<DBNAME>’ for these details.

2) Execute Alter Database command with mofigy file option to set file loccation for dataabase

USE MASTER;

GO

ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBDATA,

FILENAME=’E:SQL2K5_1Modelmodel.mdf’);

GO

ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBLOG,

FILENAME=’E:SQL2K5_1Modelmodellog.ldf’);

GO

USE MASTER;

GO

ALTER DATABASE MODEL MODIFY FILE (NAME = MODELDEV,

FILENAME=’E:SQL2K5_1ModelMSDBDATA.mdf’);

GO

ALTER DATABASE MODEL MODIFY FILE (NAME = MODELLOG,

FILENAME=’E:SQL2K5_1ModelMSDBLOG.ldf’);

GO

OUTPUT :-

The file "MSDBDATA" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "MSDBLOG" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "MODELDEV" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "MODELLOG" has been modified in the system catalog. The new path will be used the next time the database is started.

3) Stop SQL Services

4) Manually Move MSDB & Model Databases files to new location

5) Start SQL Services

6) Check MSDB & Model Databases file location after movement

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Database snapshot captures only data changes or it will also save real database from DDL changes ?


Question : Database snapshot captures only data changes or it will also save real database from DDL changes (like table structure, store procedure etc.)

Answer : Yes.

So here we explian how :-

Databse snapshot is read only databse. It’s a static view database on which only read only operaton can be performed. Database snapshot works on data page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. The same process is repeated for every page that is being modified for the first time.

So DB snapshot works page level & page is the smallest unit database engine can understand. Page contains the actual data, transactions and schema details. So here, I say snapshot works on page (not only data pages) and any change in page will move page to DB snapshot.

Break up of complete storage cycle :- Windows Server > SQL Instance > Database > Database Files > Extends > Pages

Source : https://msdn.microsoft.com/en-us/library/ms175158.aspx

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Cumulative Update – 5 for SQL Server 2012 Service Pack 2 Is Now Available !


The 5th cumulative update release for SQL Server 2012 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 5 contains all the hotfixes released since the initial release of SQL Server 2012 SP2.

Those who are facing severe issues with their environment, they can plan to test CU5 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP3 final release to deploy on your production environment, to have consolidate build.

KB Article For CU5 of SQL Server 2012 SP2

Previous Cumulative Update KB Articles:

Download Link of SQL Server 2012 SP2 :

SQL Server 2012 SP2 : http://www.microsoft.com/en-us/download/details.aspx?id=43340

SQL Server 2012 SP2 EXPRESS : http://www.microsoft.com/en-us/download/details.aspx?id=43351

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Cumulative Update – 15 for SQL Server 2012 Service Pack 1 Is Now Available !


The 15th cumulative update release for SQL Server 2012 Service Pack 1 is now available for download at the Microsoft Support site. Cumulative Update 15 contains all the hotfixes released since the initial release of SQL Server 2012 SP1.

Those who are facing severe issues with their environment, they can plan to test CU15 in test environment & then move to Production after satisfactory results.

Although, I prefer to have SP2 with latest CU in your environment.

KB Article For CU15 of SQL Server 2012 SP1

Previous Cumulative Update KB Articles:

· CU#12 KB Article: http://support.microsoft.com/kb/2991533

· CU#11 KB Article: http://support.microsoft.com/kb/2975396

· CU#10 KB Article: http://support.microsoft.com/kb/2954099

· CU#9 KB Article: http://support.microsoft.com/kb/2931078

· CU#8 KB Article: http://support.microsoft.com/kb/2917531

· CU#7 KB Article: http://support.microsoft.com/kb/2894115

· CU#6 KB Article: http://support.microsoft.com/kb/2874879

· CU#5 KB Article: http://support.microsoft.com/kb/2861107

· CU#4 KB Article: http://support.microsoft.com/kb/2833645

· CU#3 KB Article: http://support.microsoft.com/kb/2812412

· CU#2 KB Article: http://support.microsoft.com/kb/2790947

· CU#1 KB Article: http://support.microsoft.com/kb/2765331

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Cumulative Update – 7 for SQL Server 2014 RTM Is Now Available !


The 7th cumulative update release for SQL Server 2014 RTM is now available for download at the Microsoft Support site. Cumulative Update 7 contains all the hotfixes released since the initial release of SQL Server 2014 RTM.

Those who are facing severe issues with their environment, they can plan to test CU7 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP1 final release to deploy on your production environment, to have consolidate build.

KB Article For CU7 of SQL Server 2014 RTM

Previous Cumulative Update KB Articles:

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Window Server 2008 Failover Clustering Logs


In Windows 2003 failover clustering, Cluster nodes continuously putting details in log file on each node. This log is in readable plain text format & located in %SystemRoot%\Cluster folder on each node in the cluster and the name of the file is CLUSTER.LOG.

In Windows 2008, One of the major change is cluster logs logging. Microsoft change the logging mechanism & now cluster logs logging also work as per rest of Windows handles event logging. The legacy cluster.log file no more exists. In Windows 2008 the cluster log is handled by the Windows Event Tracing (ETW) process. This is the same as the System or Application Event logs you view in Event Viewer.

è Points :-

1. Cluster logs located in %windir%\Cluster\Reports directory on each node of the cluster

2. The log files are stored in an *.etl format.

3. Naming Format in Windows Server 2008

ClusterLog.etl.001
ClusterLog.etl.002
ClusterLog.etl.003

4. Naming Format in Windows Server 2008 R2

Microsoft-Windows-FailoverClustering Diagnostic.etl.001
Microsoft-Windows-FailoverClustering Diagnostic.etl.002
Microsoft-Windows-FailoverClustering Diagnostic.etl.003

5. The default size of these logs is 100MB each. You can change it as per your requirements.

6. We can have maximum 3 *.etl files.

7. The ETL.001 file is the active file being used by the live cluster service to write debug entries.

8. File rollover happens only on reboot. On reboot, new ETL.001 will be created & last file will be deleted.

9. In case, Latest ETL.001 file reach to MAX size, Data inside the file will start truncating on basis of FIFO (First In First Out). But file rollover happen only on reboot.

èHow to check cluster properties ?

Open an elevated command prompt and type the following:

C:\>cluster /prop

èHow to change the MAX size of cluster logs ?

Powershell

C:\PS>Set-ClusterLog –Size X

Command Line

c:\>cluster log /Size:X

èHow to Generate a Cluster Log in readable format ?

Command Line

Switch Effect
c:\>cluster log /gen /COPY:”directory Dumps the logs on all nodes in the entire cluster to a single directory
c:\>cluster log /gen /SPAN:min Just dump the last X minutes of the log
c:\>cluster log /gen /NODE:”node-name Useful when the ClusSvc is down to dump a specific node’s logs

Powershell

Switch Effect
C:\PS> Get-ClusterLog –Destination Dumps the logs on all nodes in the entire cluster to a single directory
C:\PS> Get-ClusterLog –TimeSpan Just dump the last X minutes of the log
C:\PS> Get-ClusterLog –Node Useful when the ClusSvc is down to dump a specific node’s logs

èHow to Change the level of the Cluster Log

Default cluster log level (ClusterLogLevel) is 3. Which is same as windows 2003 logging. You can have max level 5 to have more detailed information for analysis. Using higher level of level may impact system performance as well.

Level Error Warning Info Verbose Debug
0 (Disabled)
1 X
2 X X
3
(Default)
X X X
4 X X X X
5 X X X X X

Powershell

C:\PS>Set-ClusterLog –Level X

Command Line

c:\>cluster log /Level:X

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

How to restore Master database – SQL Server System Database?


Steps to restore SQL Server Master Database :-

1) Stop SQL Server Services

2) Add [-m] startup parameter in SQL Services from configuration manager

Parameter [-m] Starts SQL Server instance in single-user mode. SQL Server in single-user mode can connect with single user only and did not start CHECKPOINT process. Starting SQL Server in single-user mode enables any member of the computer’s local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

From SQL Server 2005 to 2008 R2

From SQL Server 2012

3) Start SQL Services & SQL server will come online in single user mode. Connect using SQLCMD

4) Execute Restore command

RESTORE DATABASE MASTER FROM DISK = ‘C:MASTER.BAK';

Message :

Processed 352 pages for database ‘MASTER’, file ‘master’ on file 1.

Processed 2 pages for database ‘MASTER’, file ‘mastlog’ on file 1.

The master database has been successfully restored. Shutting down SQL Server.

SQL Server is terminating this process.

5) After Master DB restore, You SQL services will be shutdown. Start SQL Services in normal mode by removing [-m] parameter.

6) You are done !

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Follow

Get every new post delivered to your Inbox.

Join 570 other followers