Showing posts with label Sql Server. Show all posts
Showing posts with label Sql Server. Show all posts

March 18, 2014

Size of Individual Tables in Sql Server

I recently created a sql server database which was huge and so I wanted to find out individual sizes of the tables. So, with the help of few suggestions on the internet I ended up writing a script that displays the size of individual tables in KB, MB and the number of rows those tables have.

Here’s the script that I wrote.

  1. SELECT
  2.     t.NAME AS TableName,
  3.     s.Name AS SchemaName,
  4.     p.rows AS RowCounts,
  5.     SUM(a.total_pages) * 8 AS TotalSpaceKB,
  6.     SUM(a.used_pages) * 8 AS UsedSpaceKB,
  7.     SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
  8.     SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB
  9. FROM
  10.     sys.tables t
  11.       INNER JOIN
  12.     sys.indexes i ON t.OBJECT_ID = i.object_id
  13. INNER JOIN
  14.     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  15. INNER JOIN
  16.     sys.allocation_units a ON p.partition_id = a.container_id
  17. LEFT OUTER JOIN
  18.     sys.schemas s ON t.schema_id = s.schema_id
  19. WHERE
  20.     t.NAME NOT LIKE 'dt%'
  21.     AND t.is_ms_shipped = 0
  22.     AND i.OBJECT_ID > 255
  23. GROUP BY
  24.     t.Name, s.Name, p.Rows
  25. ORDER BY
  26.     t.Name

 

And here is a screen shot of the results produced when run on Adventure Works database.

image

March 3, 2014

Drop Database–Currently in use

I am trying to drop a database but is getting the following error – “Cannot drop database as it is currently in use”. This really means that there are open connections to the database that needs to be closed first. To do so, we can use the SP_WHO and KILL commands.

SP_WHO will provide a list of connections to the database servers. Check for the spid and the dbname and note the corresponding spid for the database you are trying to drop.

Once we got the spids which will be numbers, run the command KILL spid. For example, if you receive 51 and 52 as the spids. Then run

KILL 51; KILL 52;

Then, run the command to drop the database and it should work.

February 28, 2014

Sql: Order by Email Domain Name

I was working on getting a report in Sql Server and it was actually simple report of a list email addresses and some and other data. Apparently, while sorting the report by email address, I realised that I need to sort by the domain name of the email address to get a better understanding. So, how can you sort the result by domain name of the email address. Here is how.

Sql Script
  1. SELECT EmailAddress
  2. FROM tblUser
  3. ORDER BY SUBSTRING(EmailAddress,(CHARINDEX('@',EmailAddress)+1),1)

February 27, 2014

I usually create a Sql Server database using Sql Server Management Studio and use asp.net or mvc application to create a database connected application. Using Entity Framework’s model first approach, I can also visually create the model and then create a database using it. in this post, I will show how to create a data model using entity framework and then create a database using it.

I will use an empty asp.net application and add an entity data model to it. To do so, add a new file and choose Data > ADO.NET Entity Data Model .

image

I have renamed the model to "EmployeeModel and clicked OK. In the next screen, I chose Empty Model and clicked Finish.

image

Clicking Finish creates the EmployeeModel.edmx and EmployeeModel.Context.tt files in the solution explorer.

image

The designer should be opened by default and I will write click and Add a New  Entity.

image

In the next prompt, fill in the Entity name, and choose a unique id property which I have set as EmployeeId.

image

Clicking OK will generate the entity as below.

image

Next, I will add a few properties to this entity like FirstName, LastName, EmailAddress, DateJoined, IsActive and Salary.

To add the properties, right click on the Entity and choose Add New > Scalar Property

image

After adding the properties, set the properties of individual fields. Like FirstName, LastName, EmailAddress will be string, isActive will be Boolean, DateJoined will be DateTime and Salary will be double. Other properties like Nullable and default value can also be set. I have set 0 for the default value for Salary. Notice that the string fields have additional properties like Fixed Length and Max Length. I have set 50 to be the Max Length for all string properties.

For now, let’s assume that the model is ready. In future posts, (I will add more to it.).  Right click on the designer and click on Validate.

image

Notice that Validation will fail with one warning – Entity type ‘Employee’ is not mapped.

image

This is because the model is not mapped to any database. To create the database, right click on the model and choose “Generate database from model”. A prompt will open to choose a connection or to create a new one. In this situation, I have created a new connection to an Employee database previously created using Sql Server Management Studio. The script is generated and when I click on Finish, the database will be generated.

Generated sql script
  1.  
  2. -- --------------------------------------------------
  3. -- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
  4. -- --------------------------------------------------
  5. -- Date Created: 02/27/2014 15:33:14
  6. -- Generated from EDMX file: C:\Users\...\Documents\Visual Studio 2012\Projects\FormsTestApp\EntityPrac\EmployeeModel.edmx
  7. -- --------------------------------------------------
  8.  
  9. SET QUOTED_IDENTIFIER OFF;
  10. GO
  11. USE [Employee];
  12. GO
  13. IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
  14. GO
  15.  
  16. -- --------------------------------------------------
  17. -- Dropping existing FOREIGN KEY constraints
  18. -- --------------------------------------------------
  19.  
  20.  
  21. -- --------------------------------------------------
  22. -- Dropping existing tables
  23. -- --------------------------------------------------
  24.  
  25.  
  26. -- --------------------------------------------------
  27. -- Creating all tables
  28. -- --------------------------------------------------
  29.  
  30. -- Creating table 'Employees'
  31. CREATE TABLE [dbo].[Employees] (
  32.     [EmployeeId] int IDENTITY(1,1) NOT NULL,
  33.     [FirstName] nvarchar(50)  NOT NULL,
  34.     [LastName] nvarchar(50)  NOT NULL,
  35.     [EmailAddress] nvarchar(50)  NOT NULL,
  36.     [DateJoined] datetime  NOT NULL,
  37.     [Salary] float  NOT NULL,
  38.     [IsActive] bit  NOT NULL
  39. );
  40. GO
  41.  
  42. -- --------------------------------------------------
  43. -- Creating all PRIMARY KEY constraints
  44. -- --------------------------------------------------
  45.  
  46. -- Creating primary key on [EmployeeId] in table 'Employees'
  47. ALTER TABLE [dbo].[Employees]
  48. ADD CONSTRAINT [PK_Employees]
  49.     PRIMARY KEY CLUSTERED ([EmployeeId] ASC);
  50. GO
  51.  
  52. -- --------------------------------------------------
  53. -- Creating all FOREIGN KEY constraints
  54. -- --------------------------------------------------
  55.  
  56. -- --------------------------------------------------
  57. -- Script has ended
  58. -- --------------------------------------------------

 

The generated sql file is also saved in the project. This will create the store schema definition language (SSDL) and the mapping specification language (MSL) that is based on the model. Note that the database tables are not really added. The generated script will need to be run from with Sql Server Management Studio to create the tables to the database.

February 25, 2014

I am trying to create a new database and ended up receiving an error like “Directory lookup for the file … failed with the operating system error 2”. The following screen shot tells the full story.

image

With this kind of error, it normally happens when a path is specified on where the database will be created and in my situation I have specified the path but have not created the appropriate folders. Sql Server Management Studio does not automatically create the folders where the database will be created and this needs to be created manually by the administrator.

In the previous post, I showed how to create a new Sql Server database from backup, however, at times the following error might occur -

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'NewDB' database. (Microsoft.SqlServer.SmoExtended)

The above error really states that the new database is not the same the database in the backup. To avoid this error, I will, set couple more options in the Restore database dialog.

Click on Options on the left and select “Overwrite the existing database (WITH REPLACE)”.

image

And, in the Files option, I will set the data file folder and log file folder to the folder location that I used to create the database. By default, it will have the paths specified for the original backed up database.

image

Click on OK and continue to create the database as specified in the previous post on how to create a database from backup.

Usually, we take a backup of a database at regular time intervals and to be on the safe side that if something goes wrong, we have a point in time to go back to. However, we can also create a new database from the backup and this might be useful for debugging purposes. In this post, I will show how to create a new database from a backup of different database. 

To create a database from a backup follow the following steps.

Create a new database with the new name.

image

Click OK and this will create the database. Once created, right click on Databases and choose Restore Database. In the Source section, select Device, click on the … icon

image1

Click on Add and select the location and select the backup file.

image

Click OK twice and then select the destination. Select the NewDB that was created earlier.

image

Click on OK and the new database should be setup correctly.

February 23, 2014

The Sql Server has been setup and from the Services panel and Sql Server Configuration panel, I can see that it’s up and running but I cannot connect to it from asp.net solution. At this stage, I would verify if Sql Server is indeed running by logging into the server using Sql Server Management Studio from within the server. In this situation, it will connect from within the server but not when connected from outside – that is, from other servers.

The above issue can happen remote connections are not allowed in the Sql Server. To enable remote connections, open Sql Server Management Studio and connect to the Sql Server. Right click on the server name and click on “Properties”. This will open a up dialog like this.

image

Click on Connections from the left hand panel which will open a dialog like this.

image

Select the “Allow remote connections to the server” option and and click OK.

Now connect to the Sql Server from a remote Sql Server Management Studio or an asp.net solution and it should work.

February 22, 2014

Has this ever happened to you that you have setup Sql Server (any version) and it seems to be up and running but I cannot access it from Sql Server Management Studio. To start with, how do you know it's up an running - I usually check if the  Sql Server related services are up and running from Administrative Tools > Services section.

Once confirmed I connect from SSMS in the same server to the Sql Server but it does not work. At this stage, we need to check if firewall settings allow SSMS to access the sql server. Open firewall settings from Administrative Tools > Windows Firewall and Advanced Security and a new inbound rule to allow the SSMS program to access the sql server like below.

image

In the programs section, browse to the location where SSMS is installed. This setting will allow SSMS to access Sql Server.

February 21, 2014

I have setup Sql Server Express a number of times in Windows Azure within a VM and every time I have had some trouble setting it up so I could connect to it. Today, when I tried to set the sql server up, I received the error
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
which I have received almost every time. As this does take up a lot of time to figure out the issue, this is how I have gone about it.

February 20, 2014

Few days back I was working with our IT team to do an audit on the Sql Servers and to get a list of how many is installed. Later, I realised that I can also do so using SQLCMD command with in the command prompt. Here is how you do it.

Open Command Prompt and type in SQLCMD -L. This will list the servers.

image

Alternatively and even better we can output the list to a text file by using the following command.

SQLCMD -L  > c:\testdata\sqlserver.txt

This will save the text file – sqlserver.txt in C:\testdata

February 7, 2014

There are few ways we can get the size of a Sql Server database. There are built in stored procedures and we can also write our own and even use the sql server management studio to get the size. In this scenario, I am using the AdventureWorks database.

sp_spaceused

The stored procedure "sp_spaceused" returns the database size, index size, data size and unused space of the database. We can use it in the following way.

  1. USE [AdventureWorks2012]
  2. exec sp_spaceused;

 

image

sp_helpdb

The stored procedure "sp_helpdb" returns the size of the database, name, dbid, filename, data size, log size and few other metadata. It can be used like below.

  1. sp_helpdb 'AdventureWorks2012';

 

image

 

Custom Script

We can write a custom script to query from sysfiles to get the size of the database. The script and the result it produces is below.

  1. USE [AdventureWorks2012]
  2. GO
  3.  
  4. SELECT name, [size] * 8 / 1024 as [size MB]
  5. FROM sysfiles;


image

SSMS

Lastly, and most easily, we can get the size from the properties of the database. Right click on the database name in object explorer, click on properties and then choose Files from "Select a page".

image

February 6, 2014

incorrect syntax near ':' in SQL Server

I created a project using Sql Server Database template in Visual and generated a changed script from it. The script looked like below.

Sql
  1. :setvar DatabaseName "AdventureWorks2012"

However, when I ran the script, it did not validate and produced the error message: 

Error Message
  1. Msg 102, Level 15, State 1, Line 1
  2. Incorrect syntax near ':'.

January 13, 2014

I have noticed few times that when a new column is added to SQL server database table, the column gets added from design view and I can write a select query for it. But in the select query, the column name gets highlighted and says the column name is not valid even when  the select query works. An example is below.

January 10, 2014

Query xml from sql

Sql Server has the option to store XML data. This is great as it means we can store xml data and also run sql scripts on it. Yes, we can run sql scripts to retrieve certain fields within the xml.  Ti start with, let's use the Customer table that I created in previous post and add a new field Person that is of type xml.

January 6, 2014

A unique key is a field in a table that appears only once in the table and therefore can uniquely identify the table. It's similar to a primary key in the database but it differs in some ways including that it can accept null value. The null value can appear only once for the unique column in the table.

To add a unique key using sql server management studio, and click on indexes / keys option. The following popup will display.

1


2

From the popup options, select the columns that need to be unique, choose Is Unique to Yes and set the type as Unique Key. Click close and save the changes to the database.

January 5, 2014

I recently deployed a site and faced a strange error where the website was failing to connect to Sql Server database. It was giving the error - cannot connect via NTAuthority login. This was strange as I had the username and password for the sql database specified in the connection string. Here's is how it looked like.

December 19, 2013

While working with Sql Server, errors might occur when a table is altered. The table can be altered by adding a new column, deleting an existing column, or changing the data type of any of the columns. The error message that might come up is "Prevent saving changes that require the table re-creation".
To correct the above error in Sql Server Management Studio, click on Options from top menu.


1
From the Options popup, select the "Designers" option from the left menu and then untick "Prevent saving changes that require table re-creation". Setting this option will allow saving the changes in the database.

December 17, 2013

Using the same database and tables setup in previous post, we will add a primary key for the table. The primary key is a column that uniquely identify a row of data within a table. So, for example, in the Customer table, you can have 2 customers with name - Steve Smith and you can easily differentiate between them. This might also mean duplicated fields - like the same customer might have been added multiple times and we cannot differentiate them. To allow for these scenarios, a unique key is required.

In one of the previous posts, I showed how to create a database from Sql Server Management Studio, in this one I will showed how to create the database from script. To do so, I will use the same database to show the create database script.
Once a database is created, scripts can be generated for it by right clicking the database name and selecting "Script Database as" > "Create To" as shown below.
1
This generates the scripts as per below.
Sql Script
  1. USE [master]
  2. GO
  3.  
  4. /****** Object:  Database [TestDB]    Script Date: 13/12/2013 2:13:54 PM ******/
  5. CREATE DATABASE [TestDB]
  6. CONTAINMENT = NONE
  7. ON  PRIMARY
  8. ( NAME = N'TestDB', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
  9. LOG ON
  10. ( NAME = N'TestDB_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
  11. GO
  12.  
  13. ALTER DATABASE [TestDB] SET COMPATIBILITY_LEVEL = 110
  14. GO
  15.  
  16. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
  17. begin
  18. EXEC [TestDB].[dbo].[sp_fulltext_database] @action = 'enable'
  19. end
  20. GO
  21.  
  22. ALTER DATABASE [TestDB] SET ANSI_NULL_DEFAULT OFF
  23. GO
  24.  
  25. ALTER DATABASE [TestDB] SET ANSI_NULLS OFF
  26. GO
  27.  
  28. ALTER DATABASE [TestDB] SET ANSI_PADDING OFF
  29. GO
  30.  
  31. ALTER DATABASE [TestDB] SET ANSI_WARNINGS OFF
  32. GO
  33.  
  34. ALTER DATABASE [TestDB] SET ARITHABORT OFF
  35. GO
  36.  
  37. ALTER DATABASE [TestDB] SET AUTO_CLOSE OFF
  38. GO
  39.  
  40. ALTER DATABASE [TestDB] SET AUTO_CREATE_STATISTICS ON
  41. GO
  42.  
  43. ALTER DATABASE [TestDB] SET AUTO_SHRINK OFF
  44. GO
  45.  
  46. ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS ON
  47. GO
  48.  
  49. ALTER DATABASE [TestDB] SET CURSOR_CLOSE_ON_COMMIT OFF
  50. GO
  51.  
  52.   ALTER DATABASE [TestDB] SET CURSOR_DEFAULTGLOBAL
  53. GO
  54.  
  55. ALTER DATABASE [TestDB] SET CONCAT_NULL_YIELDS_NULL OFF
  56. GO
  57.  
  58. ALTER DATABASE [TestDB] SET NUMERIC_ROUNDABORT OFF
  59. GO
  60.  
  61. ALTER DATABASE [TestDB] SET QUOTED_IDENTIFIER OFF
  62. GO
  63.  
  64. ALTER DATABASE [TestDB] SET RECURSIVE_TRIGGERS OFF
  65. GO
  66.  
  67.   ALTER DATABASE [TestDB] SETDISABLE_BROKER
  68. GO
  69.  
  70. ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
  71. GO
  72.  
  73. ALTER DATABASE [TestDB] SET DATE_CORRELATION_OPTIMIZATION OFF
  74. GO
  75.  
  76. ALTER DATABASE [TestDB] SET TRUSTWORTHY OFF
  77. GO
  78.  
  79. ALTER DATABASE [TestDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
  80. GO
  81.  
  82. ALTER DATABASE [TestDB] SET PARAMETERIZATION SIMPLE
  83. GO
  84.  
  85. ALTER DATABASE [TestDB] SET READ_COMMITTED_SNAPSHOT OFF
  86. GO
  87.  
  88. ALTER DATABASE [TestDB] SET HONOR_BROKER_PRIORITY OFF
  89. GO
  90.  
  91. ALTER DATABASE [TestDB] SET RECOVERY SIMPLE
  92. GO
  93.  
  94.   ALTER DATABASE [TestDB] SETMULTI_USER
  95. GO
  96.  
  97.   ALTER DATABASE [TestDB] SET PAGE_VERIFY CHECKSUM
  98. GO
  99.  
  100. ALTER DATABASE [TestDB] SET DB_CHAINING OFF
  101. GO
  102.  
  103. ALTER DATABASE [TestDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
  104. GO
  105.  
  106. ALTER DATABASE [TestDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
  107. GO
  108.  
  109.   ALTER DATABASE [TestDB] SETREAD_WRITE
  110. GO

If you notice, it has lot of options that we skipped while creating the database. For simplicity, we will remove these options. This options will automatically default to its default value. Here's the shortened script.
Sql Script - Shortened
  1. USE [master]
  2. GO
  3.  
  4. /****** Object:  Database [TestDB]    Script Date: 13/12/2013 2:13:54 PM ******/
  5. CREATE DATABASE [TestDB]
  6. CONTAINMENT = NONE
  7. ON  PRIMARY
  8. ( NAME = N'TestDB', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
  9. LOG ON
  10. ( NAME = N'TestDB_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
  11. GO

Once the script is run, the database will be created. Note that, if the database already exists, it won't work. To test the script in that case, just rename the database name and file names and then execute the script.
Reference: Shahed Kazi at AspNetify.com