Server Busy? Who is there?

Server Busy? Who is there?

To see the connected session in your database use this SQL statement

SELECT HOSTNAME,

                 PROGRAM_NAME,

                STATUS,

                 SPID

FROM    MASTER..SYSPROCESSES

WHERE DBID= DB_ID('TestDB')

                AND SPID != @@SPID

It provide you the information like

Hose Name        --- The name of computer in case of multi user environment

Program_Name — The name of Application that use the SQL server

Status                ---  Whether it is active session or not

SPID                   — The id number by this you can kill the session

How to start sql server without tempdb

How to start sql server without tempdb

When i try to take image of sql server for installation to new server  i could not restart sql server service on new server due to tempdb database files missing. In that image server where tempdb database files were placed on E:\mssql\data drive and  new sql server only got C: drive  and all system dbs there on c: drive  files. This solution is tested and worked fine.

--  SQL Server Path
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
Step:1  You are going to  start sql using single user with minimal configuration on command prompt
 cmd-- > sqlservr -f -m
Now you create Folder on 'C:\MSSQL\DATA\
-- Open the second window for command prompt
c:\sqlcmd
1>
use master
go
ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='C:\MSSQL\DATA\Tempdb.MDF')
go
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='C:\MSSQL\DATA\Templog.LDF')
go
>quit

Attach and Detach files and Database in SQL Server

How to attach a data file MDF without log file (LDF)

USE master
go
CREATE DATABASE AdventureWorks2008R2
ON (FILENAME = 'C:\MSSQL\Data\AdventureWorks2008R2_Data.mdf') -- change the drive and file path
FOR ATTACH_REBUILD_LOG ;

Moving Master Data base

Moving Master Data base

MOVING MASTER DATABASE

* Create two folders and grant read write permissions to service account
d:\master_data
e:\master_log
* Find the current path
sp_helpdb master
* Stop SQL Server
net stop mssqlserver
* Move the files (master.mdf, mastlog.ldf) into new folders
* Go to SSCM --> R.C on respective instance SQL Server Service --> properties --> Advanced --> Startup Parameters--> Change the path of data and Log file
-dd:\master_data\master.mdf;-e....
-le:\master_log\mastlog.ldf
* Apply --> OK
* Start the service.
* Go to SSMS --> check the new path
sp_helpdb master

Script to take backups for all databases at a time in SQL Server

Script to take backups for all databases at a time in SQL Server

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
    select
        DATABASE_NAME   = db_name(s_mf.database_id)
    from
        sys.master_files s_mf
    where
       -- ONLINE
        s_mf.state = 0

       -- Only look at databases to which we have access
    and has_dbaccess(db_name(s_mf.database_id)) = 1

        -- Not master, tempdb or model
    and db_name(s_mf.database_id) not in ('tempdb')
    group by s_mf.database_id
    order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    declare @DBFileName varchar(256)  
    set @DBFileName = datename(dw, getdate()) + ' - ' +
                       replace(replace(@DBName,':','_'),'\','_')+'.bak'

    exec ('BACKUP DATABASE [' + @DBName + '] TO  DISK = N''D:\2008r2_backups\' +
        @DBFileName + ''' WITH NOFORMAT, INIT,  NAME = N''' +
        @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,compression,STATS = 100')

    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR 

Unable to Create Linked Server for SSAS Provider MSOLAP

Recently I ran into an odd scenario. Net new SQL servers were built in Dev, QA and Prod. I had to create Linked Servers to SSAS. All of these were on SQL 2012 enterprise Sp2 + Kb2969896. All was great till I was unable to create linked server for SSAS provider MSOLAP.

The 32-bit OLE DB provider “MSOLAP” cannot be loaded in-process on a 64-bit SQL Server. (SQL Server, Error: 7438)

I had forgotten the fact that SQL server works in mysterious ways when it wants to!

The error was self explanatory. SQL was trying to use 32-bit drivers instead of 64-bit. Please note that this is a 3 phase job:

1. Unregister DLLs
2. Register DLLs in the correct order
3. Restart SQL Services

Locate the file location for msolap110.dll and then run the following on the command prompt:

Unregister DLLs

1 2 3

regsvr32 /u "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll" regsvr32 /u "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

Register DLLs in the correct order

1 2 3

regsvr32 "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll" regsvr32 "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

Once the services were up, I tested the linked server and it was successful. If you are doing in on a cluster ensure you start with the passive node(s).

How to Create Database in SQL Server

2015-08-24 by suresh

Database is a storage location where we can store our business data. Database uses tables to store our information in a normalizes way so that, We can easily Select, Update and Delete the business data. In this article we will show you, How to Create Database in SQL Server, Renaming Database and Deleting Database in SQL with example.

How to Create Database in SQL Server 2014 with example

Before we start creating new database, Let us see the available databases. Below screenshot will show you the databases available in current instance.

SQL Syntax to create Database is:

1	CREATE DATABASE Database_Name

In this example, we are going to create new database called New_Database. So, Replace the Database_Name with New_database in SQL query window as shown below

1	CREATE DATABASE New_Database;

Click on the Execute button to execute the create database command

From the below screenshot you can observe that, Command is executed successfully and you can see the New_Database in our object explorer

TIP: If you didn’t find the newly created database, Please click on the refresh button in object explorer

Let us see what will happen, When we execute the same command again. From the below screenshot you can observe that, it is throwing error saying: New_database already exists. Choose different database name.

NOTE: In an organization, we may or may not have the privileges to know the available databases so, it is always advisable to check whether the database name already exists or not

How to Check whether Database name exists or not

This can be done in two ways:

Following statement will only execute Create Database Statement, if the New_database in not available in system database

SQL CODE

1 2 3 4 5 6

IF NOT EXISTS    (      SELECT name FROM master.dbo.sysdatabases      WHERE name = N'New_Database'     ) CREATE DATABASE [New_Database]

We just replaced the If Not Exists with If Exists and added select statement to display the message. STeps involved in the Following statement are:

• If the New_database already exists then following query will display a message saying database already exists
• Create database only execute if the New_database in not available in system database

1 2 3 4 5 6 7 8 9 10 11 12 13

IF EXISTS    (      SELECT name FROM master.dbo.sysdatabases     WHERE name = N'New_Database'     ) BEGIN     SELECT 'Database Name already Exist' AS Message END ELSE BEGIN     CREATE DATABASE [New_Database]     SELECT 'New Database is Created' END

ANALYSIS

Following statement will check for the database name New_database in system database.

1 2	SELECT name FROM master.dbo.sysdatabases      WHERE name = N'New_Database'

If the database does not exists, then only following create statement will be executed

1	CREATE DATABASE [New_Database]

otherwise, following command will be executed. It’s going to display a message saying that the database already exists

1	SELECT 'Database Name already Exist' AS Message

OUTPUT

How to Create Database in SQL Server using GUI

Within the object explorer, Right click on the Databases folder will open the context menu. Please select New database.. option from the context menu as shown below

Once you select New database.. option, following window will be opened. Here we left Owner as default and database name as New_database as shown below.  Click OK to create new database

Let us see what will happen, When we create the database with existing name. From the below screenshot you can observe that, it is throwing error

How to Delete Database in SQL Server

To Delete database in SQL server, we can simple use the following syntax

Syntax for Deleting database or Dropping Database in SQL server is:

1	DROP DATABASE [Database Name]

In this example we are going to delete New_Database So, within the SQL query window, Please write the following query

1	DROP DATABASE [New_Database]

Let us see, What will happen when we execute the same command once again:

From the above screenshot you can observe that, it is throwing an error saying: New_database doesn’t exists.

Better approach is check whether the database name exists or not because sometimes, your college or your team leader may deleted the database which you are trying to delete.

1 2 3 4 5 6

IF EXISTS    (      SELECT name FROM master.dbo.sysdatabases      WHERE name = N'New_Database'     ) DROP DATABASE [New_Database]

ANALYSIS

Following statement will check for the database name New_database in system database.

1 2	SELECT name FROM master.dbo.sysdatabases      WHERE name = N'New_Database'

If the database exists, then only following drop statement will be executed

1	DROP DATABASE [New_Database]

 OUTPUT

How to Rename Database in SQL Server 2014

To rename database in SQL server, we can simple use the system stored proceduresp_renamedb

Syntax for renaming database in SQL server is:

1	SP_RENAMEDB [Old Database Name],[New Database Name]

In this example we are going to rename New_Database with New_Db So, within the SQL query window, Please write the following query

1	SP_RENAMEDB  [New_Database],[New_Db]

Thank You for Visiting Our Blog