While managing multiple databases, it is often needed to check database backup history like when did we taken backup? Where did we take last few backups? What was the size of last few backup? And many other information.

I have setup maintenance plan for few database, few databases backup taken by third party software and all works on schedule on particular time even I keep one small T-SQL script handy to check all information I have mentioned above.

SELECT     

      bkSet.Backup_Start_Date as BackupStartDate,

      bkSet.Backup_Finish_Date as BackupEndDate,

    MedFam.Physical_Device_Name AS BackupPath,

    MedSet.Software_Name AS SoftwareUsedForBackup,

    bkSet.User_Name AS BackupTakenBy,

    bkSet.Server_Name AS ServerName,

    bkSet.Database_Name As DatabaseName,

    CASE bkSet.Type     

            WHEN 'L' THEN 'TransactionLogBackup'

            WHEN 'D' THEN 'FullBackup'

            WHEN 'F' THEN 'FileBackup'

            WHEN 'I' THEN 'DifferentialBackup'

        WHEN 'G' THEN 'DifferentialFileBackup'

        WHEN 'P' THEN 'PartialBackup'

        WHEN 'Q' THEN 'DifferentialPartialBackup'

        ELSE NULL END AS BackupType,

    CAST((bkSet.Backup_Size/1048576) AS NUMERIC(10,2)) AS BackupSizeInMB

FROM       

      msdb..BackupMediaFamily MedFam

INNER JOIN 

      msdb..BackupMediaSet MedSet

ON

      MedFam.Media_Set_ID = MedSet.Media_Set_ID

INNER JOIN 

      msdb..BackupSet bkSet

ON

      bkSet.Media_Set_ID = MedSet.Media_Set_ID

WHERE     

      --keep your database name in condition

      bkSet.Database_Name = 'Adventureworks'

AND       

      --put the date between which you want to find details of backup

      bkSet.Backup_Finish_Date BETWEEN '2011-07-01' AND '2011-07-10'

ORDER BY   

      bkSet.Backup_Finish_Date DESC

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Well, today I would like to share one very quick and useful simple TSQL which can help you to generate script (definition) of stored procedure, trigger, functions etc. Generally you can do it with SSMS easily by right click on object and click on option like “Script Stored Procedure as->Create to->File”  or  “Script function as->Create to->File” etc. but if you want to generate script for many object at a time, there is one very small TSQL can come to your help which I myself used to use so many time.

Look at the TSQL below:

select so.name as ObjectName,sc.text as ObjectDefination from sysobjects so join syscomments sc on so.id=sc.id

--P for Stored Procedure

--FN for scalar function

--IF for Inlined table function

--TF for Table function

--TR for trigger

where so.type in ('P','FN','IF','TF','TR')

--if you want defination of specific function or SP, include below condition too

--and so.name='YourSPorFunctionName'

Order by so.name,sc.colid

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

I have seen many SQL Developer used to use Database tuning advisor and many of them meet with warning like below.

30% of consumed workload has syntax errors. Check tuning log for more information.

You can see above error (percentage ratio may be changed in case to case) and think that your query is perfect and there is not error at all than why DTA (Database Tuning Advisor) showing syntax error?

Actually DTA is not much powerful to understand if you are using temp table in your stored procedure or calling sub stored procedure or may be using user define functions and it shows above warning. DTA works well only with simple Vanilla type plain SELECT statement.

So in that case, you have to find out the query on which you have doubts, run that query and records it in profiler and get help of DTA.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

There were major changes in architecture & engine of SQL Server 2005 as compare with SQL Server 2000. So as per my observation and experience in live environment, many different kind of error used to come while upgrading, I don’t mean that upgrade doesn’t work at all but personally I have came across so many different kind of error so rather than upgrading from SQL Server 2000 to SQL Server 2005, I would like to follow different approach which has been given below.

1.)    Detach all use databases (don’t need to have system databases with us)

2.)    Back up all the job if you have any in your SQL Server 2000

3.)    Create login transfer script so that all logins of your SQL Server 2000 works fine in SQL Server 2005 with same passwords. There are few different approaches for this but I would like to point out one of the Microsoft’s link for this task. http://support.microsoft.com/kb/246133

4.)    Now, this is the time to uninstall SQL Server 2000

5.)    Install SQL Server 2005 and upgrade it with latest hot fixes and patches

6.)    Attach all your databases which were detached from SQL Server 2000

7.)    Restore all jobs which were backed up from SQL Server 2000

8.)    Run the transfer login script which we have generated in step 3 above.

Isn’t it simple to go for? I find this easiest way to go for rather than upgrading the server. This is my personal point of view which I used to follow. The main reason of writing this article is, yesterday one of my friend was upgrading his SQL Server 2000 to 2005 and came across one weird error which he was not able to solve. I suggested him to follow these steps in future and also suggested few steps which might resolve the error he was facing. I used to get update from him today, If the way I suggested had helped him to resolve the error, I will provide full case study in my blog so everybody can take advantage of the same.

Reference: Ritesh Shah               

Microsoft Baseline Security Analyzer (MBSA) is an easy-to-use tool that helps small and medium businesses determine their security state in accordance with Microsoft security recommendations and offers specific remediation guidance. Improve your security management process by using MBSA to detect common security misconfigurations and missing security updates on your computer systems. Built on the Windows Update Agent and Microsoft Update infrastructure, MBSA ensures consistency with other Microsoft management products including Microsoft Update (MU), Windows Server Update Services (WSUS), Systems Management Server (SMS), System Center Configuration Manager (SCCM) 2007, and Small Business Server (SBS).

You can check following issues with your SQL Server 2005.

tWindows Administrative Vulnerabilities

tWeak passwords

tIIS administrative Vulnerabilities

tCheck for SQL administrative vulnerabilities

tCheck for security updates

You can download this useful utility from here. MBSA is available in English, German, Japanese and French languages for both 32 and 64 bit OS.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

I used to use BPA (Microsoft SQL Server 2005 Best Practices Analyzer) tool for all my SQL Server 2005 servers. It is really very helpful.

The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

You can download this really very useful tool from here.

BTW, you can use this tool with Microsoft SQL Server 2005 only. BPA for SQL Server 2008 is not available yet.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

After a long time I face above error when I have configured new Microsoft SQL Server 2005 instance in my one of the computer and tried to access it from other computer in network from Asp.Net web application. This is really annoying error but the solution for this error is really quite simple. I knew where to go and that is why I fixed it in even less than 2 minutes but I thought it might be helpful to my reader.

If you want to connect via TCP/IP to remote Microsoft SQL Server and it shows below given error:

Error Fix: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server, Error: -1)

You have to go to:

Start->All Programs->Microsoft SQL Server 2005->Configuration Tools-> SQL Server Surface area configurations

Once you click on “SQL Server surface area configuration”. It will open one dialog box, you have to click on “Surface are configurations for services and connections”.

Now, you are on track, select your instance of SQL Server,->Database Engine->Remote Connections.

You will find two options here.

1.)    Local connection only

2.)    Local and remote connection.

Select second option, click on “Apply” button and then “OK” button . That’s it,  You are done, now, restart your service and enjoy your connection.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

People may face problem in selecting or deleting duplicate records from database. This is not a new problem; every SQL Developer may face this kind of situation at least once in his/her careers. Now a days, especially after SQL Server 2005, it is becoming really easy to find duplicate records and delete it with the help of CTE (Common Table Expression), basically CTE is introduced in SQL Server 2005 version so long way back in SQL Server 2000, there was no CTE and people were using logical trick to overcome this issues.

I have already written articles on that topic which I want to share with you today.

Click here to look at the trick which works in SQL Server 2000/2005/2008.

Click Here to look at the new method with CTE which should work in SQL Server 2005+ version only.

I see many of the .NET developer still now aware with these kinds of techniques and facing problem due to unawareness. This is the main reason I wrote follow up post for my past two articles.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

It’s been more than 6 months I am actively working in few different online forums like Experts-Exchange.com and forums.aspx.net etc. It has been observed that few questions are really coming repeatedly with different forms. So I thought to list out the articles I have already written for those questions for Microsoft SQL Server. It would be interesting to list many question answered in one post.

Let us see it one by one in no particular order:

Backup database with Encrypted data in Restore it

Stored procedure which returns data in page (Paging stored procedure)

Store Image in SQL Server database and retrieve it back (with help of C#)

Insert and update image or varbinary field in SQL Server 2008/2005

Pass Array or Generic list to SQL Server stored procedure

Change database collation in SQL Server

Create Linked Server with Excel or Access database

BCP in Microsoft SQL Server

Pivot is also one of the interesting as well as frequently asked question in every SQL Server forums. I have written approx 10 different articles on this topic but would like to list three of them here.

Dynamic Pivot with Dynamic month name header in SQL Server 2008/2005

Simple example of Dynamic Pivot

Generic Stored Procedure for PIVOT in SQL Server, you can use this SP with almost all PIVOT need.

CTE (Common Table Expression) is also one of the interesting topic which is being asked in forums very often. I have written few articles on this topic too but would like to list our only one here.

More than one CTE in SQL Server 2008/2005

Apart from all these useful links, I would like to share two most important script which I used to use with all my servers and databases.

Keep an eye on your database file size and shoot email when it is necessary.

Keep an eye on your disk space in SQL Server and shoot an email when it is necessary.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

You might face error “Cannot generate SSPI context” while login to your SQL Server 2005 from SQL Server Management Studio. This is really annoying error. Basically the root cause of this error is current windows user doesn’t have proper permission.

This error occurs only when you are trying to login with “Windows Authentication”. You have two options.

1.)    Login with your SQL Server authentication, if you have id/pass

2.)    Set proper permission for your user.

As a simple option, you can right away switch to SQL Server Authentication. For digging more about permission issue of your windows user, have a look at some of the following link from Microsoft website.

http://support.microsoft.com/kb/811889

http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server 2005 comes up with fabulous Partitioning functions which work well in SQL Server 2008 too. Just because of ignorance or unawareness of these functions, programmer used to iterate BIG BIG loops in front-end. Without much a boring lecture, let me start over functions now.

Row_Number():  Basically Row_Number() function is used to give number to each row in result set. It plays an important and a crucial role in many difficult situations. One can use it for paging purpose too (I probably will post one SP in future article which you can directly use in your front-end for paging purpose).

--create temp table for demo

IF OBJECT_ID('tempdb..#Employee') is not null DROP TABLE #EMPLOYEE

Create Table #Employee

(

      FirstName varchar(20),

      LastName Varchar(20),

      DepartMent varchar(20)

)

--insert few records

Insert Into #Employee

SELECT 'Ritesh','Shah', 'MIS' UNION ALL

SELECT 'Rajan','Shah','ACCT' UNION ALL

SELECT 'Rajan','Mehta','ACCT' UNION ALL

SELECT 'Alka','Shah','MIS'

--simple Row_Number with Order By First Name, Last Name

--this will not make any partition and simply give row number to every row

SELECT ROW_NUMBER() over(order by FirstName,LastName) as Num,FirstName,LastName,DepartMent

FROM #Employee

--this will make a partition on First Name

--so, every first instance of FirstName will have row number 1

--you can find duplicate records with this way too. :)

SELECT ROW_NUMBER() over(Partition by FirstName order by FirstName,LastName) as Num,FirstName,LastName,DepartMent

FROM #Employee

Rank() and Dense_Rank(): These two functions mainly used to use for giving Rank to each row.  You may use it for finding Toppers based on examination results records set or maybe use it for finding few top vendors based on track records of sales you have etc. There is only one small but technically big difference between Rank() and Dense_Rank() functions which I am going to show you in practical script below which will be easy to evaluate as I am going to show you all possible partitioning function in one T-SQL.

--create temp table for demo

IF OBJECT_ID('tempdb..#SampleOrder') is not null DROP TABLE #SampleOrder

Create Table #SampleOrder

(

      OrderID Int Identity(1,1),

      ClientID int,

      TotalSample int,

      SampleDate datetime

)

--insert few records

Insert Into #SampleOrder

SELECT 1,2,GETDATE()-5 UNION ALL

SELECT 2,5,GETDATE()-8 UNION ALL

SELECT 1,22,GETDATE()-3 UNION ALL

SELECT 3,2,GETDATE()-1 UNION ALL

SELECT 1,2,GETDATE()-5

SELECT *,

            ROW_NUMBER() over(order by TotalSample) as RowNum,

            ROW_NUMBER() over(Partition By ClientID order by TotalSample) as RowNumP,

            Rank() over(order by TotalSample) as Ran,

            Rank() over(Partition By ClientID order by TotalSample) as RanP,

            Dense_Rank() over(order by TotalSample) as DRan,

            Dense_Rank() over(Partition By ClientID order by TotalSample) as DRanP

FROM #SampleOrder

If you will observe output of above T-SQL, you will get to know the difference between Rank and Dense_Rank. There is only difference, if you will get same instance based on partition, rank will give same number all, suppose we get three same instance (same clientID three time with same value) rank will give it, suppose 1 for all three and when next instance come, rank will give it 4 rather than 2. In Dense_Rank, you will get 2, it won’t break the chain.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft has recently found memory leak issues in Linked Server query in SQL Server 2008 and SQL Server 2005. If you are using Linked server frequently than please do read this article to know what kind of problem could happens.

SQL Server 2008

• ANY remote stored procedure execution will leak memory for each execution on the local server (the server where you initiated the remote procedure execution). The leak is not large for each execution (around 40 bytes) but over time this can add up. This specific problem involves the RETURN status of a stored procedure so there is no way to avoid it (even if you don’t use RETURN in your proc a return status is sent back to the client) if you execute remote stored procedures.
• If you use sql_variant SQL Server data types you could also face a different memory leak (again on the local server only). These leaks can occur under the following conditions: 
• If you use a sql_variant type for an OUTPUT parameter of a remote stored procedure you will leak around 40 bytes just for using a sql_variant OUTPUT parameter (the leak is per parameter). If the value of the parameter is of type char, varchar, nchar, nvarchar,binary, or varbinary, you will also leak the size of the value itself for each parameter. So a large character string over time could result in a considerable memory leak.
• if you run a remote query that returns a result with a sql_variant column AND the value of the column is a char, varchar, nchar, nvarchar, binary, or varbinary value, you will leak the value of that variant for each row returned to the local server. This one has a potential to cause a fairly significant leak depending on how big the values are and how many rows are returned.

SQL Server 2005

• You are only affected by the sql_variant problems listed above.

Please click here to read complete article on official Microsoft website.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

Today I have seen interesting question in one forum that how can I find modified Stored Procedure and Views after certain date with its text. Question is really interesting but the answer is really very small T-SQL.

If you use Sys.Objects, you can get list of all available objects in your database. Sys.Object has much important information about that object along with “Modify_Date” but this system view doesn’t contain text of those objects.

Well, in this scenario Object_Defination method comes to rescue us. If you pass Object’s ID in Object_Defination method, it will return Text for that object. So now let me show you short T-SQL for this task.

select name,OBJECT_DEFINITION(object_id),modify_date from sys.objects

where [type] in ('V','p')  and convert(varchar,modify_date,112)>'20090601'

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Well, recently I have observed that data file of my database started growing like anything and I wonder which table(s) is causing this. Generally by looking at datafile you can’t assume that which table(s) is growing high so you need to check size of table and index. I decided to first look at the size of table, how can do that? Well there is one stored procedure in SQL Server which is really very handy in this scenario.

use adventureworks

go

--look at overall scenario about total size of table

--and index and database size etc.

EXEC sp_spaceused

--now let us look at the size of perticular table

EXEC sp_spaceused 'Production.ProductProductPhoto'

Last T-SQL statement would give you total number of rows in table, reserved size, data size, index size and unused space. WOW, really quick and handy SP, isn’t it????

But this SP will give you information about just one table and what, if you want to look at the details about all tables in your database.

There are two ways to go for in this scenario.

1.)    Use following T-SQL which uses SP_MSForEachTable (undocumented SP), If you want to know more about this SP, click here to look at my past article.

exec sp_MSforeachtable @command1='print ''?'' exec sp_spaceused ''?'''

2.)    Another way is really easy to go for, it’s a readymade graphical report provided by SQL Server itself. Just open up your SSMS, right click on database, Select Reports, click on Standard Reports and click on “Disk Usage by Top Tables”. There are so many other useful report too, which you can study and can use whenever you need it.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

Though Encryption and Decryption process is resource intensive, it is become necessary in some cases. If you look back in SQL Server 2000 and 7.0 days, you didn’t have any in-built mechanism and had to depend on 3^rd party tools. But from SQL Server 2005, Microsoft started providing in-built support for encryption and decryption. Let us look in details about what is it? How does it work?

Since this is pretty big topic, it is not desirable to have it in one article so I will upload it in part. Before we move further in topic, let us find out Architecture (Hierarchy) of Encryption and Decryption mechanism in SQL Server 2008. Following image will give you an idea about that.

SQL Server 2008 encryption model inherits Windows Crypto API to encrypt and decrypt data in your database and supports layered approach. At the second level of encryption, there will be SMK (Service Master Key) . You can find one SMK (Service Master Key) per instance. It used to get generated by its own when it needs to encrypt any other key.  Well as I just told you that each server instance can have only one SMK but every database in your instance can have separate DMK (Database Master Key) which is encrypted by SMK.

At the bottom level of Encryption, you can find Certificates, Asymmetric key and Symmetric key. Detailed article with example about each of these are going to come soon. Keep Reading!!!!

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com