Search results for 'app:weblogs' matching tag 'SQL Server 2008 R2'

Last Execution Date Time of a Stored Procedure

Anonymous — Sun, 11 Mar 2012 06:00:00 GMT

On many forums I have found a very basic yet important query - “How can I know when was my Stored Procedure last executed?” And today through this blog I will try to answer this question.

Actually speaking, without explicit logging or tracing, it is not possible to get this information for each and every Stored Procedure of our Database. However, we can get this detail along with many other relevant information for the stored procedure having it’s execution plan currently cached on the server by using - sys.dm_exec_procedure_stats It’s a system dynamic view that returns aggregate performance statistics for cached stored procedures.

The important thing to note is that this view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

So, let’s see a way, using which at least we can find out the some important facts for the cached stored procedures -

     USE DBName

    GO

    SELECT 

      O.name,

      PS.last_execution_time

    FROM 

      sys.dm_exec_procedure_stats PS 

    INNER JOIN sys.objects O 

      ON O.[object_id] = PS.[object_id]

P.S. Please replace the DBName with the actual name of the Database.

The above script will return the name of all the cached stored procedure of the current database with their last execution time.

For more details on this dynamics view, please refer - sys.dm_exec_procedure_stats (Transact-SQL)

Download free eBook for SQL Server 2012

Anonymous — Wed, 29 Feb 2012 06:00:00 GMT

Microsoft has released one more free eBook, this time it is on SQL Server 2012. However, this is the second draft of this book. SQL Server MVP Ross Mistry and Stacia Misner has covered Administration and BI part, there are 10 chapters in total divided in two part, they are:

PART I   Database Administration
CHAPTER 1   SQL Server 2008 R2 Editions and Enhancements 3
CHAPTER 2   Multi-Server Administration 21
CHAPTER 3   Data-Tier Applications 41
CHAPTER 4   High Availability and Virtualization Enhancements 63
CHAPTER 5   Consolidation and Monitoring 85

PART II   Business Intelligence Development
CHAPTER 6   Scalable Data Warehousing 109
CHAPTER 7   Master Data Services 125
CHAPTER 8   Complex Event Processing with StreamInsight 145
CHAPTER 9   Reporting Services Enhancements 165
CHAPTER 10   Self-Service Analysis with PowerPivot 189

Want to read more and download an eBook for free ? Link –> Introducing Microsoft SQL Server 2012

For those who missed downloading eBook on SQL Server 2008, here is the link –> Introducing Microsoft SQL Server 2008 R2

-Hemantgiri S Goswami (http://www.sql-server-citation.com )

Rebuild System Databases

Anonymous — Sun, 29 Jan 2012 06:00:00 GMT

Yesterday night one of FB Friend have ping me, he has issue with his local SQL Server instance. Our conversation goes like:

FB Friend: Hi

I : Hi

FB Friend: I need your help

FB Friend : My SQL Server is not starting...

I : What is an error you are getting?

FB Friend : Just nothing

I : Ok, check the SQL Server Error Log and EventViewer

I : you can find errologs at C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\Log

I : open it with notepad ....

FB Friend: Ok

FB Friend: Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

I : Wow, seems like your master database is corrupt, you will need to run repair or you may want to rebuild master database.

I : Is this a new installation ?

FB Friend : Yes, this is new installation, and, I don't want to re-run the installation again, what is my option ?

I : you will need to rebuild master or restore it

FB Friend: How do I do it?

I: Here are your steps

Rebuild Master:

Step 1: Insert your setup media CD or folder where you have dumped SQL Server Installation

Step 2: Open command prompt

Step 3: Go to location (wherever you have setup, Step 1)

Step 4: setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS=MyAdmin /SAPWD=somePwd

Step 5: this will pop up new command window and then disappears

Step 6: you will have to refer logs so as to see whether it was successful completed or exit with error

This is it, you system database has been rebuild successfully!!

Restoring Master Database - An alternative method:

Step 1: Take backup of master database, in your case, I assume, you already have your master database backed up.

Step 2: Stop SQL Server Services * you will need Administrator permission for this operation

Step 3: Start SQL Server using -C and -M parameter; this will start SQL Server in single user mode which is required to restore Master database

Step 4: Restore database using SQLCMD

This is it, you have successfully restore your master database!!!

Is it this simple? No, wait, there are few things we need to be careful with....

The above example have save you because below conditions are true:

You have back up of your master database, or
This server is fresh installation or
there are no user databases or
this server is not production box

If the above conditions were not true, you will have to perform below steps:

Restore / Rebuild system databases
Recreate all the Login that you have on your crashed server - backing up master db helps you here
you will have to recreate all the jobs and SSIS/DTS Packages that you have - backing up MSDB database will save you
similarly distribution database backup will help you if you have replication configured

Conclusion : I wish that you have schedule maintenance plan / job to take full backup of your system databases i.e. Master, MSDB, and Distribution(if you have configured replication).

Disclaimer : The example used here is for illustration purpose only.

SQL Server 2008 R2 Service Pack 1 CTP - test and feedback #sql #sqltools #ctp

Anonymous — Wed, 11 May 2011 05:00:00 GMT

SQL Server 2008 R2 service pack 1 contains all the cumulative update pack releases since RTM release. This means SP1 CTP consist the CU Package releases from 1 to 6, as CU7 has released on April 18th 2011. So before you test the SQL2008R2SP1 CTP, I would encourage to go through the release notes that gives information about list of issues that are fixed and what's new with this release: The following features are new in Service Pack 1 (SP1). Dynamic Management Views for increased supportability....(read more)

Cannot resolve the collation conflict

Anonymous — Sun, 27 Feb 2011 06:00:00 GMT

Recently when I was referring to SQL Server Forums I saw a question that reads like:
I get an error Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

What is a Collation?
In simple words Collation is the set of rules that ensure proper use of characters for Language or Alphabet. Here are some recommendation from books online about choosing collation:

Select a BINARY2 collation if binary code point based ordering is acceptable.
Select a Windows collation for consistent comparison across data types.
Use a new 100-level collation for better linguistic sorting support.
If you plan to migrate a database to the upgraded instance of SQL Server, select the collation that matches your existing collation of the database.

Solution:

The error that we are referring here shown up because the two databases have different collation for some reason, now, to overcome this issue one may use COLLATE clause, but do remember that COLLATE clause will work with CHAR,VARCHAR,TEXT, NCHAR, NVARCHAR and NTEXT data type.

The Syntax :

SELECT * 
FROM 
SYS.databases SD
JOIN
SOMEDB.DBO.SOMETBL JC    
ON 
SD.database_id  = JC.DBID  COLLATE Latin1_General_CI_AI

Here are some external resource for further reading

http://msdn.microsoft.com/en-us/library/ms143508.aspx>

http://blogs.msdn.com/b/qingsongyao/archive/2008/08/25/sql-server-2008-katmai-s-new-collations.aspx

Download SQL Server Training Kit

Anonymous — Thu, 20 Jan 2011 06:00:00 GMT

Couple of months back I have wrote a blog article about the availability of Microsoft SQL Server Developer Training Kit which is very helpful resource for the SQL Server Developers, trainers and professionals to understand the improvements comes with the release of SQL Server 2008.

Recently, 18th January 2011 Microsoft has released the updated version of Microsoft SQL Server Developer Training Kit which has

# 31 Presentations (includes Slide decks, videos and transcripts)
# 27 Demos (includes installer scripts, videos and transcripts)
# 12 hands on labs (includes installer scripts)

The main highlight of the updated version is – BI Solution, yes you read it right. The version of developer training kit has the units for BI Solutions like:

Build your first Microsoft BI Solution with SQL Server 2008 R2
Introduction to BI Modelling Techniques

And all of this are free!!

Download the updated version of SQL Server Developer Training kit from here –> http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d

You may want to check the complete list of what is included in SQL Server Developer Training Kit and System Requirements before you begin download.

Regards

Hemantgiri S. Goswami

Cumulative Update package 5 for SQL Server 2008 R2 :: to test & deploy

Anonymous — Wed, 22 Dec 2010 06:00:00 GMT

Another instalment of Cumulative Update package (which is 5 now) for SQL Server 2008 R2 is available to test and deploy on to your production systems. So the build number after you apply this CU5 will be 10.50.1753 (thats an easy number to remember).You have to restart the instance once the CU5 is applied on SQL2008 R2 instance, I didn't get the reboot notification but I was not able to restart SQL instance after the implentation so I have to go for a reboot of machine. Here are the list of builds...(read more)

Make huge space savings by using SPARSE columns

Anonymous — Thu, 16 Dec 2010 06:00:00 GMT

I’ve blogged before about Getting more than 1024 columns on a table , this is done by using sparse columns. Whilst this is potentially useful for people with insane table designs, sparse columns aren’t just for this. My experience over the past few years...(read more)

Get your free SQL Server 2008 R2 System Views Map

Anonymous — Thu, 09 Dec 2010 06:00:00 GMT

System views map is a good resource of information for every DBA to know internals of system catalogs of a product. In this case SQL Server 2008 R2 system views map is available for download in the form of PDF & XPS file. Download link refers: The Microsoft SQL Server 2008 R2 System Views Map shows the key system views included in Microsoft SQL Server 2008 and SQL Server 2008 R2, and the relationships between them. The map is similar to the Microsoft SQL Server 2008 version and includes updates...(read more)

Trace Flag 610 – When should you use it?

Anonymous — Tue, 16 Nov 2010 06:00:00 GMT

Thanks to Marcel van der Holst for providing this great information on the use of Trace Flag 610. This trace flag can be used to have minimal logging into a b tree (i.e. clustered table or an index on a heap) that already has data. It is a trace flag...(read more)