Feeds:
Posts
Comments

Archive for the ‘SQL SERVER TIPS’ Category

I came across this scenario many times when you need to find the largest tables in the database, specially at the time of maintenance or support. Fortunately we do have a solution in the shape of different scripts that you can easily find in many blogs and sites. In fact, I have written a solution earlier. In this article, I will discuss how to find the largest object without writing a single line of script.

Let me explain it step by step by step.

Step 1 :
First, open SQL Server Management Studio (SSMS) and select the target database (where you need to find the largest object) and right click on it as shown below.

Find largest table 01

Step 2 :
Once you right click on the target database you will see a lot of options. Select reports \ standard reports \ Disk Usage by Top Tables as shown below. There are other very useful reports that give you the statistical data about your SQL Server.

Find largest table 02

Step 3 :
Once you click on reports \ standard reports \ Disk Usage by Top Tables, SSMS will open a report showing which tables are using more Disk spaces, reserved space & having highest record counts etc as shown below.

Find largest table 03

Step 4 :
Now you have the report and by default it is sorted by Reserved (KB), but you can sort it with any column (# Records (Actually record count), Reserved(KB), Data(KB), Indexes(KB), Unused(KB)) by clicking on up and down sign as shown below.

Find largest table 04

The beauty of this feature (Standard reports) is that you can generate the statistical report within few seconds, just by click of a menu. In addition you can export / print this report in a nice format without any extra effort as shown below.

Note : Please do not forget to refresh this report before export or print ;).

Let me know if you tried this solution and how did you like it ?

Advertisements

Read Full Post »

How to delete recent connection from Connect to Server window in SSMS is a very common issue and it becomes frustrated if you connect many servers on daily basis and SQL Server Management Studio (SSMS) pile up all the new server names in the list of Connect to Server window as shown below.

list of connection.1.1

I have been facing this common problem since SQL Server 2005 and it has been reported on Connect as well. Fortunately we do have a solution explained in this article that demonstrate step by step how to delete the recent connection list using mru.dat and SqlStudio.bin in SQL Server 2005  and SQL Server 2008 respectively. However, this is NOT smart solutions because it deletes all your recent connections including the active ones as well.

In SQL Server 2012, a proper solution came in the picture and I believe, this is much better than the earlier solution. Let me demonstrate it step by step.

Step 1 :
In order to open “Connect to Server” window, first of all you need to select file menu and click on connect object explorer.. in SSMS as shown below.

list of connection.1.3

Step 2 :
Once you open the Connect to Server window, you will find all recent connections in the server name as shown below but the drawback is you cannot determine what belongs to SQL Authentication and what to Windows.

list of connection.1.1

Step 3 :
Now, you need to eliminate the connection. In order to do it, you need to select the particular connection and press DELETE button from the keyboard. Once you press delete button, it will delete that particular connection but it will remain as selected in the server name dropdown list as shown below. If you need to delete multiple connections, you need to select server one by one and press delete button to delete  it,  as multiple selection is NOT allowed.

list of connection.1.2

Let me know if you come across this scenario and how did you resolve it.

Read Full Post »

Sometimes lack of knowledge about the product irritates us. No matter, what product we are dealing with.
IntelliSence is one of the marvelous tools, that has been with us Since SQL Server 2008. It really helps us to speed up the development. However, sometimes it irritate as well ;). In SQL Server 2012, IntelliSence came up with two different modes inside to make our life more easier. Given below are the details.

  • Completion Mode
  • Suggestion Mode

Before proceeding with the explanation of these modes, I would like to enable Text Editor Toolbar to toggle between these two modes. Please note that it is not mandatory to enable text editor toolbar for toggling between modes but it is preferable to add because it helps us to see which mode we are in. Let me enable it step by step.

Step 1 :
First of all, you need to open SQL Server Management Studio (SSMS) and select Tools and Customize…  option as shown in the image below.

toggle suggestion and completion mode.2.1

Step 2 :
Once you select Customize… option, it will pop up Customize screen. You need to select Text Editor toolbar in it and press Close as shown below.

toggle suggestion and completion mode.2.2

Step 3 :
In this step, you can see that Text editor toolbar is available in SSMS and Toggles between suggestion and standard completion modes and mode button is highlighted as shown below.

toggle suggestion and completion mode.2.3

Once you enable the Text editor toolbar, let me explain what happens with each mode, with examples.

  • Completion mode : All of us are aware of completion mode because in earlier days completion mode was selected by default in IntelliSence. However, the problem with this mode is, if the object names are symmetrical in database (not necessary) and we search for desired object and by mistake if the criteria matches the other object name (not required), it will select the other object automatically and if this activity is continuous, it irritates us. Please note that, this mode is enabled by default in SQL Server. Given below is the screen image of completion mode in which Toggles between suggestion and standard completion modes button is switched OFF and it means that we are in completion mode.

toggle suggestion and completion mode.1.1

  • Suggestion Mode : This mode is good for the developers if they are working on symmetrical object name database, and this mode of intelliSence just suggest the object name for us, it does not select it automatically like completion mode. You can Press CTRL + ATL + Space bar to switch in Suggestion mode. Given below is the screen image of suggestion mode in which Toggles between suggestion and standard completion modes button is switched ON and it means you are in suggestion mode.

toggle suggestion and completion mode.1.2

Let me know if you used it in the real world examples and how it helped you.

Read Full Post »

Today, I was searching for some script from my library and I came across this script (How do I find all the tables that do not have a primary key?) . I usually use this script, once I complete my deployment on any SQL Server to check if I missed to create primary key on any table.  There are multiple ways to achieve it. However, I will discuss three of them in this article.

METHOD 1 :
This is the shortest method to find all the tables that do not have a primary key. In this method, we need to use OBJECTPROPERTY to check each table property and filter it.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
  SCHEMA_NAME(schema_id) AS [Schema name]
, name AS [Table name]
FROM sys.tables
WHERE
OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
GO
--OUPUT

donot have primary key.1.1

METHOD 2 :
This is one of my favorite methods because I do not need to remember any other view name, I can browse all the data from sys.objects.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
  SCHEMA_NAME(schema_id) AS [Schema name]
, name AS [Table name]
FROM
sys.objects
WHERE [type]='U' AND object_id
NOT IN (
SELECT parent_object_id FROM sys.objects
WHERE [type]='PK'
)
GO
--OUPUT

donot have primary key.1.1

METHOD 3 :
In this method, we need to use two system views (sys.tables & sys.key_constraints) to achieve it.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
 SCHEMA_NAME(schema_id) AS [Schema name]
, name AS [Table name]
FROM sys.tables
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.key_constraints
WHERE type = 'PK'
);
GO
--OUPUT

donot have primary key.1.1

Read Full Post »

I came across this query from one of my SQL Server developers while he was doing some  R&D in SQL Server and suddenly the SQL Server started giving him fixed number of result sets (5 rows of each table/views) as shown in the picture below.

Setrowcount.1.1png

First of all he tried all the way to fix it including the restart of development SQL Server. It seems, he had to re-install his SQL Server ;). He called me, if I can help him to fix it. I asked him few queries and started doing the troubleshooting and finally fixed the issue. Let me reproduce the same issue and fix it step by step.

Step 1 :
Open SSMS (SQL Server Management Studio) and go to Tools >> Options menu as shown in the image below.

Setrowcount.1.3

Step 2 :
Once you select options menu, an options dialogue box will pop up. Now you need to select “Query Execution” from left hand pane and make SET ROWCOUNT =0 as shown in the image below. By mistake my developer put 5 in the SET ROWCOUNT and it returns 5 number of rows in each result sets for each table and view. Once you are done press OK button.

Setrowcount.1.2

Step 3 :
Now open a new query window and execute the same queries. This time, it will give you the exact result set instead of 5 rows.

Setrowcount.1.44

Let me know if you guys came across this issue and how did you fix it ?

Read Full Post »

Sometimes, when you create a new object using SSMS (SQL Server Management Studio) and try to use that newly created object, it shows a red line underneath that object and if you move your mouse cursor on that object, it says the object does not exist, but on the other hand it manipulates the query too, as shown in the picture below.

refresh_cache.1.1

When I came across this issue earlier I thought it could be a bug. But actually it is not, so what is wrong with SQL Server ? Basically once you create a new SQL Server object, your newly created object does not get updated in the IntelliSence Local Cache and due to this, it shows red line underneath that object. So you just need to refresh SSMS IntelliSence Local Cache and once you refresh it, IntelliSence will automatically add newly created object in the cache and the red line will disappear. Now, the question is how to update it ? You can achieve in two different ways. Given below are the details.

  • Shortcut

In this method, you need to open SSMS and just need to press “Ctrl+Shift+R” and Intellisence Local Cache will be updated automatically.

  • Via Menu

In this method, you need to open SSMS then go to Edit menu and point IntelliSence and select Refresh Local Cache and once you select Refresh Local Cache, your Intellisence Local Cache will be updated automatically as shown in the picture below.

refresh_cache.1.2

Let me know, what you guys did, when you came across it first time 😉 ?

Read Full Post »

I received this query recently, when I was recovered one of my blog reader data. Once the recovery is done, I advised him to take a backup on regular basis etc etc. Next morning, I receive an email from him with a screen shot showing that, there is no option to take Transaction Backup in his SQL Server. I recreated the same screen shot, as shown below.

transaction backup.1.1

So What is the solution ? Basically your database is in SIMPLE recovery model due this you do not have Transaction Backup option. So In order to take transaction backup, you need to change your database recovery options from SIMPLE to FULL. Lets do it step by step.

Step 1 :
Open your SQL Server management studio than right click on the database and select database properties as shown in the screen shot below.

transaction backup.1.4

Step 2 :
Select the options and further select the recovery model as FULL on your right hand side and press OK as shown in the screen shot below.

transaction backup.1.2

Step 3 :
Now, you need to go back and take the backup and this time you will find the transaction backup option as shown in the screen shot below.

transaction backup.1.3

Note : It is recommended to change your recovery model from simple to FULL

Read Full Post »

Older Posts »