Home » 2014 » July (Page 2)

Monthly Archives: July 2014

How to Check SQL Server Installation Date Time ?

You need to run the following query and it will give you the date of your SQL Server Installation.

SELECT @@SERVERNAME SERVERNAME, CREATE_DATE ‘INSTALALTIONDATE’

FROM SYS.SERVER_PRINCIPALS

WHERE SID = 0X010100000000000512000000

SID 0X010100000000000512000000 is belongs to user "NT AUTHORITYSYSTEM". This user create at the time of installation only.

If you liked this post, do like on Facebook at

Reference : Rohit Garg ()

Mainstream Support End || SQL Server 2008 and SQL Server 2008 R2

Mainstream Support for SQL Server 2008 and SQL Server 2008 R2 was ended on July 8th 2014. This will result no enchantment or no future release of SPs. Somehow, Critical bug fixes may be release as QFE etc.

This sequence stop with SQL Server 2008 Service Pack 3 CU16 and SQL Server 2008 R2 Service Pack 2 CU13.

Refer Links :-

http://mssqlfun.com/2014/07/02/cumulative-update-13-for-sql-server-2008-r2-service-pack-2-is-now-available/

http://blogs.msdn.com/b/sqlreleaseservices/archive/2014/03/17/cumulative-update-16-for-sql-server-2008-sp3.aspx

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

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

How to restore MSDB & MODEL database – SQL Server System Databases?

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail. Issues in MSDB restore that may result loss of all scheduling information, as well as the backup and restore history. You cannot restore a database that is being accessed by users. If SQL Server Agent is running, it can access msdb. Therefore, before restoring msdb, stop SQL Server Agent.

The model database is used as the template for all databases created on an instance of SQL Server. SQL Server created tempdb every time SQL Server is started that required existence of model database. Whenever new database created on SQL Server, DB Engine took entire contents of the model database & its database options to copy & create the new database.

System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running. For example, to restore a system database on a server instance that is running on SQL Server 2008 SP2, you must use a database backup that was created after the server instance was upgraded to SQL Server 2008 SP2.

MSDB & Model database restore is almost same as other user database restore.

Steps to restore SQL Server MSDB & MODEL Database :-

1) For MSDB Database Stop SQL Server Agent Services to disconnect SQL Server Agent session from MSDB

2) Close all connection from MODEL & MSDB database

3) Restore MSDB from desired backup

RESTORE DATABASE MSDB

FROM DISK = ‘<Backup File Location>’

WITH REPLACE

RESTORE DATABASE MODEL

FROM DISK = ‘<Backup File Location>’

WITH REPLACE

4) You are done.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

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

How to change TempDB system Database files location?

Steps to change TempDB system Database files location :-

1) Check current file location of TempDB

Exec SP_helpDB TempDB

You can use below query as well for file location details.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(‘TempDB’);

2) Execute Alter Database command with Modify Filename option on Tempdb System Database

USE master

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQL2K5_1\TempDB\tempdb.mdf’)

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = ‘E:\SQL2K5_1\TempDB\templog.ldf’)

OUTPUT :-

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

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

3) Restart SQL Server (TempDB automatically recreate from scratch every time SQL Server restart. To change files location, Just need to alter the file location done in step 2 & after restart SQL server will create files at new mentioned location)

4) Check file location of TempDB – After restart

5) Delete Old TempDB files from previous location to clear disk space from unused & junk files

Note : TempDB file location cannot change with deattach attach method. TempDB availability is mandatory for SQL server to come online.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

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

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

The 2nd cumulative update release for SQL Server 2014 RTM is now available for download at the Microsoft Support site. Cumulative Update 2 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 CU2 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 CU2 of SQL Server 2014 RTM

Previous Cumulative Update KB Articles:

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

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

%d bloggers like this: