Category: SQL

SQL error- No process is on the other end of the pipe

September 16, 2019 /

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

To resolve this issue: Switch from Windows to SQL Authentication:

  1. Right click on the server name and select properties
  2. Select security tab
  3. Enable the SQL Server and Windows Authentication mode
  4. Restart the SQL Server service

How to delete mutiple database from sql management studio

April 5, 2019 /

At times you might have come across deleting multiple databases. One of the scenarios may be Sitecore creating many DB’s and if it is sitting stale doing nothing you might want to delete those DB’s. You might first try to run the uninstall script to get the site and all related db’s deleted, but if you are deleting this manually you might wonder how to delete mutiple or all db’s in sql server on dev machine in one go.

  1. Open the SQL server management studio
  2. Press F7 or select View in menu and open Object Explorer Details.
  3. Click on Database folder in Object Explorer all db’s will be listed
  4. Select DB’s of your choice to delete. Right click on selected DB’s
  5. Choose option Close existing connections and Click OK
  6. Selected DB’s will be deleted

 

sc902error4

sc902error5

 

Estimating the Size of a SQL Table

October 5, 2015 /

Estimating the Size of a Table

The following steps can be used to estimate the amount of space required to store the data in a table:

  1. Specify the number of rows present in the table:Number of rows in the table = Num_Rows
  2. If there are fixed-length and variable-length columns in the table definition, calculate the space that each of these groups of columns occupies within the data row. The size of a column depends on the data type and length specification. For more information, see Data Types.Number of columns = Num_ColsSum of bytes in all fixed-length columns = Fixed_Data_SizeNumber of variable-length columns = Num_Variable_ColsMaximum size of all variable-length columns = Max_Var_Size
  3. If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )Only the integer portion of the above expression should be used; discard any remainder.
  4. If there are variable-length columns in the table, determine how much space is used to store the columns within the row:Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_SizeIf there are no variable-length columns, set Variable_Data_Size to 0.This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a lower percentage of the variable-length column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall table size.
  5. Calculate the row size:Total row size (Row_Size) = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap +4The final value of 4 represents the data row header.
  6. Calculate the number of rows per page (8096 free bytes per page):Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row.
  7. If a clustered index is to be created on the table, calculate the number of reserved free rows per page, based on the fill factor specified. For more information, see Fill Factor. If no clustered index is to be created, specify Fill_Factor as 100.Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 – Fill_Factor) / 100) / (Row_Size + 2)The fill factor used in the calculation is an integer value rather than a percentage.Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. As the fill factor grows, more data will be stored on each page and there will be fewer pages.
  8. Calculate the number of pages required to store all the rows:Number of pages (Num_Pages) = Num_Rows / (Rows_Per_Page – Free_Rows_Per_Page)The number of pages estimated should be rounded up to the nearest whole page.
  9. Calculate the amount of space required to store the data in a table (8192 total bytes per page):Table size (bytes) = 8192 x Num_Pages