Home » SQL Server

Category Archives: SQL Server

500+ Facebook Like


Thanks every one for loving me, joining me & providing you extreme support.

I am very happy to share that we reach to 500+ milestone.

Please keep supporting us & liking us.

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

Strange Issue of waittype – PREEMPTIVE_OS_GETPROCADDRESS


ISSUE : Today, My Friend is facing issue with SQL Server error logs on one of my production server. SQL Server is not able to read SQL server error log files. We have checked that SQL Server Error log files are accessible on file system.

1) When we are running SP_readerrorlog, XP_readerrorlogs or XP_enumerrorlogs, query keeps running without showing results.

2) When We try to browse error logs from SQL Server GUI, the wizard is also hanging.

FINDINGS :

1) We found all sessions running & stuck on waittype “PREEMPTIVE_OS_GETPROCADDRESS”

PREEMPTIVE_OS_GETPROCADDRESS is a new wait type that was introduced in SQL Server 2008 to track time taken by GetProcAddress to load the entrypoint in the DLL when Extended Stored Procedure calls are made by the SQL Server instance.

2) We check what is actually SP_readerrorlog, XP_readerrorlogs or XP_enumerrorlogs trying to execute. As we all know, all this 3 store procedures are extended store procedure. Moreover, running same xpstart.dll

3) Other extended store procedures are working fine.

RESOLUTION :We have rebooted the server so that call to xpstar.dll will work properly.

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

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


The 16th cumulative update release for SQL Server 2012 Service Pack 1 is now available for download at the Microsoft Support site. Cumulative Update 16 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 CU16 in test environment & then move to Production after satisfactory results.

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

NOTE: This is the final Cumulative Update for SQL Server 2012 Service Pack 1. SQL Server 2012 SP1 exits service pack support on July 14, 2015.

KB Article For CU16 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 – 6 for SQL Server 2012 Service Pack 2 Is Now Available !


The 6th cumulative update release for SQL Server 2012 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 6 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 CU6 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 CU6 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

SQL Server 2014 Service Pack 1 (SP1) Is Now Available !


Finally after a long waiting period, Microsoft release SQL Server 2014 Service Pack 1 (SP1), Same is now available for download at the Microsoft Support site. SQL Server 2014 Service Pack 1 (SP1) contains all the hotfixes released since the initial release of SQL Server 2014 RTM.

I suggest to test SQL Server 2014 Service Pack 1 (SP1) in test environment & then move to Production after satisfactory results.

KB Article For SQL Server 2014 Service Pack 1 (SP1) – https://support.microsoft.com/en-us/kb/3058865

Download Link – http://www.microsoft.com/en-us/download/details.aspx?id=46694

Express Edition Download Link – http://www.microsoft.com/en-us/download/details.aspx?id=46697

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

Follow

Get every new post delivered to your Inbox.

Join 593 other followers