Jan 11, 2021

Create a Database

 Create a database in SQL Server by using SQL Server Management Studio or Transact-SQL

Method 1 –

To create a database, using: Using SQL Server Management Studio

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  2. Right-click Databases, and then click New Database.

 

3. In New Database, enter a database name.

          











4.    To create the database by accepting all default values, click OK;

otherwise, continue with the following optional steps.

5.    To change the owner name, click (...) to select another owner.

6.    To change the default values of the primary data and transaction log files, in the Database files grid, click the appropriate cell and enter the new value. For more information,

7.    To change the collation of the database, select the Options page, and then select a collation from the list.

8.    To change the recovery model, select the Options page and select a recovery model from the list.

9.    To change database options, select the Options page and then modify the database options.

10. To add a new filegroup, click the Filegroups page. Click Add and then enter the values for the filegroup.

11. To add an extended property to the database, select the Extended Properties page.

1.    In the Name column, enter a name for the extended property.

2.    In the Value column, enter the extended property text. For example, enter one or more statements that describe the database.

12. To create the database, click OK.

 

  • Method 2 –

To create a database, Using Transact-SQL

1.    Connect to the Database Engine.

2.    From the Standard bar, click New Query.

3.    Copy and paste the following example into the query window and click Execute. This example creates the database Sales. Because the keyword PRIMARY is not used, the first file (Sales_dat) becomes the primary file. Because neither MB nor KB is specified in the SIZE parameter for the Sales_dat file, it uses MB and is allocated in megabytes. The Sales_log file is allocated in megabytes because the MB suffix is explicitly stated in the SIZE parameter.


USE master ; 

GO 

CREATE DATABASE Sales 

ON  

( NAME = Sales_dat, 

    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Sales_dat.mdf', 

    SIZE = 10, 

    MAXSIZE = 50, 

    FILEGROWTH = 5 ) 

LOG ON 

( NAME = Sales_log, 

    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Sales_log.ldf', 

    SIZE = 5MB, 

    MAXSIZE = 25MB, 

    FILEGROWTH = 5MB ) ; 

GO


Limitations and Restrictions

  • A maximum of 32,767 databases can be specified on an instance of SQL Server.

Recommendations

  •  When you create a database, make the data files as large as possible based on the maximum amount of data you expect in the database.

Permissions

  • Requires CREATE DATABASE permission in the master database, or requires CREATE ANY DATABASE, or ALTER ANY DATABASE permission.
  • To maintain control over disk use on an instance of SQL Server, permission to create databases is typically limited to a few login accounts.

Aug 30, 2018

How to Enable Multiple RDP Sessions in Windows 2016


How to Enable Multiple RDP Sessions in Windows Server 2016


By default, Windows Server 2016 servers allow a single Remote Desktop session. If only one session is available you may choose to enable multiple RDP sessions. This article describes the process for enabling multiple sessions.

Enable Multiple RDP Sessions
1.    Log into the server using Remote Desktop.
2.    click on the Search Windows and type gpedit.msc and open it
3.    Go to Computer Configuration > Administrative Templates > Windows Components > Remote Desktop Services > Remote Desktop Session Host > Connections.
4.    Set Restrict Remote Desktop Services user to a single Remote Desktop Services session to Disabled.
5.    Double click Limit number of connections and set the RD Maximum Connections allowed to 999999.

Jul 20, 2017

How to move TempDB from one drive to another drive (New Drive)

Get Logical File Name and location of TempDB

USE TempDB
GO
EXEC sp_helpfile
GO

Or

SELECT name ,physical_name AS Location
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
GO

Change the location of TempDB

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:\datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:\datatemplog.ldf')
GO



Need to restart the Service of SQL Server to effect the changers

Mar 21, 2016

Taking backup of all the databases in SQL Server

Using below script you can back up all databases on your SQL Server. Using this you can backup databases in the multiple disk drives in a compress mode.

If you need please change the script according to your requirement.

First create a Backup folder on disk drives.

DECLARE @FileName01 AS VARCHAR(200) -- Filename for backup 1
DECLARE @FileName02 AS VARCHAR(200) -- Filename for backup 2
DECLARE @FileName03 AS VARCHAR(200) -- Filename for backup 3
DECLARE @FileName04 AS VARCHAR(200) -- Filename for backup 4
DECLARE @FileDate AS VARCHAR(10) -- Used for file name (Backup date)
DECLARE @DBName AS VARCHAR(100) -- Database name
DECLARE @Path01 AS VARCHAR(200) -- Path for backup files 1
DECLARE @Path02 AS VARCHAR(200) -- Path for backup files 2
DECLARE @Path03 AS VARCHAR(200) -- Path for backup files 3
DECLARE @Path04 AS VARCHAR(200) -- Path for backup files 4
DECLARE @BKPName AS VARCHAR(200) -- Backup name
DECLARE @ErrorMsg AS VARCHAR(200)

Set @Path01 ='G:\Backup\'
Set @Path02 ='H:\Backup\'
Set @Path03 ='M:\Backup\'
Set @Path04 ='I:\Backup\'

DECLARE db_Cursor CURSOR FOR
  Select  name FROM master.dbo.sysdatabases 
  WHERE name NOT IN ('tempdb') ORDER BY name -- Exclude Tempdb databases

OPEN db_Cursor
FETCH NEXT FROM db_Cursor INTO @DBName

WHILE @@FETCH_STATUS=0
BEGIN
       SELECT @FileDate =CONVERT(VARCHAR(10),GETDATE()-1,112)

       SET @FileName01 =@Path01+@DBName+'_01_'+@FileDate+'.BAK'
       SET @FileName02 =@Path02+@DBName+'_02_'+@FileDate+'.BAK'
       SET @FileName03 =@Path03+@DBName+'_03_'+@FileDate+'.BAK'
       SET @FileName04 =@Path04+@DBName+'_04_'+@FileDate+'.BAK'

       SET @BKPName =@DBName + '-Full Database Backup'
       /* Backup */
       BACKUP DATABASE @DBName TO  DISK = @FileName01, 
                                   DISK = @FileName02, 
                                   DISK = @FileName03, 
                                   DISK = @FileName04 WITH NOFORMAT, INIT, 
              NAME = @BKPName, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

       /* Verify Backup */
       declare @backupSetId as int
       select @backupSetId = position from msdb..backupset 
       where database_name=@DBName and backup_set_id=(
            select max(backup_set_id) from msdb..backupset where database_name=@DBName )
       SET @ErrorMsg = 'Verify failed. Backup information for database '
                      +@DBName+' not found.'
       if @backupSetId is null begin raiserror(@Error, 16, 1) end
       print '****************** '+ @DBName  +' Verify Backup ******************'
       RESTORE VERIFYONLY FROM  DISK = @FileName01,  DISK = @FileName02, DISK = @FileName03,         DISK =@FileName04 WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
       print '******************************************************************'

       FETCH NEXT FROM db_Cursor INTO @DBName
END

CLOSE db_Cursor
DEALLOCATE db_Cursor


And also you can use Maintenance Plan, it will create the script and job for you.

Feb 26, 2016

Queries currently run on SQL Server

Using below Dynamic Management View (DMV) query, you can view the queries which are currently running. 

SELECT * FROM sys.dm_exec_requests

Each row represents a currently running query.

Using below query, you can view most required details of the currently running queries.

Output columns of the query:
Blocking Session ID, login  Name, Data Base Name, Status, Query Statement, Duration, Wait Type, Query Plan, Complete Percentage (If applicable eg. Backups), Estimate Completion Time (If applicable eg. Backups), Host Name


WITH cte AS (
  SELECT
              r.session_id, r.request_id, r.database_id, t.objectid, t.[text],                              r.statement_start_offset/2 AS StatementStartOffset
              , CASE WHEN r.statement_end_offset > r.statement_start_offset THEN                            r.statement_end_offset/2 ELSE LEN(t.[text]) END AS StatementEndOffset
              , p.query_plan,CAST(getdate()-r.start_time as time) Duration
              ,percent_complete, dateadd(second,estimated_completion_time/1000
              , getdate()) as estimated_completion_time,R.status,r.wait_type
  FROM sys.dm_exec_requests r
              CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) t
              OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
  WHERE r.[sql_handle] IS NOT NULL
), spaceUsage AS (
  SELECT
       session_id, request_id
, SUM(user_objects_alloc_page_count - user_objects_dealloc_page_count) / 128 AS UserObjMB
, SUM(internal_objects_alloc_page_count - internal_objects_dealloc_page_count) / 128 AS InternalObjMB
  FROM sys.dm_db_task_space_usage
  GROUP BY session_id, request_id
)
SELECT
       r.Session_id
       ,(SELECT DISTINCT MAX(blocking_session_id) FROM Sys.dm_os_waiting_tasks 
       WHERE blocking_session_id IS not NULL AND session_id = R.session_id) Blocking_Sid
       , REPLACE(s.login_name,'NT AUTHORITY\','') login_name
       , DB_NAME(r.database_id) AS DB_Name,R.Status
       , COALESCE('[' + OBJECT_SCHEMA_NAME(r.objectid, r.database_id) + '].[' +                     OBJECT_NAME(r.objectid, r.database_id) + ']'
       , LEFT(LTRIM(r.[text]), 128)) AS Query_Batch
       , SUBSTRING(r.[text], r.StatementStartOffset
       , r.StatementEndOffset - r.StatementStartOffset) AS Current_Statement
       ,Duration,r.Wait_Type , LEN(LEFT(r.[text], r.StatementStartOffset)) -                         LEN(REPLACE(LEFT(r.[text], r.StatementStartOffset), CHAR(10), '')) + 1 AS Line_Number
       , u.UserObjMB AS [UserObjMB*], u.InternalObjMB, r.Query_Plan
       ,Percent_Complete,   Estimated_Completion_Time,S.[Host_Name]
FROM cte r
  INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
  LEFT JOIN spaceUsage u ON r.session_id = u.session_id AND r.request_id = u.request_id

And also you can use Activity Monitor to view currently running queries

Mar 30, 2007

Business Problems for Data Mining

Data mining techniques can be applied to many applications, answering various types of businesses questions. The following list illustrates a few typical problems that can be solved using data mining:

Churn analysis: Which customers are most likely to switch to a competitor? The telecom, banking, and insurance industries are facing severe competition these days. On average, each new mobile phone subscriber costs phone companies over 200 dollars in marketing investment. Every business would like to retain as many customers as possible. Churn analysis can help marketing managers understand the reason for customer churn, improve customer relations, and eventually increase customer loyalty.


Cross-selling: What products are customers likely to purchase? Crossselling is an important business challenge for retailers. Many retailers, especially online retailers, use this feature to increase their sales. For example, if you go to online bookstores such as Amazon.com or Barnes andNoble.com to purchase a book, you may notice that the Web site gives you a set of recommendations about related books. These recommendations can be derived from data mining analysis.


Fraud detection: Is this insurance claim fraudulent? Insurance companies process thousands of claims a day. It is impossible for them to investigate each case. Data mining can help to identify those claims that are more likely to be false.


Risk management: Should the loan be approved for this customer? This is the most common question in the banking scenario. Data mining techniques can be used to score the customer’s risk level, helping the manager make an appropriate decision for each application. Customer segmentation: Who are my customers? Customer segmentation helps marketing managers understand the different profiles of customers and take appropriate marketing actions based on the segments.


Targeted ads: What banner ads should be displayed to a specific visitor? Web retailers and portal sites like to personalize their content for their Web customers. Using customers’ navigation or online purchase patterns, these sites can use data mining solutions to display targeted advertisements to their customers’ navigators.

Sales forecast: How many cases of wines will I sell next week in this store? What will the inventory level be in one month? Data mining forecasting techniques can be used to answer these types of time-related questions.

Ref. Data Mining with SQL Server 2005