Data World

Posts Tagged ‘sql server’

SQL Server -DBA Must Know commands

Posted by Pramod Singla on March 28, 2017


Following are couple of DBA must know commands.

–Get the db details
EXEC SP_HELPDB ‘Db_name’
–know the definition of the objects
EXEC SP_HELPTEXT ‘object_name’
–Identify the size of database and table
EXEC SP_SPACEUSED
–Know the details of the disk drives 
EXEC MASTER..XP_FIXEDDRIVES
—Quick view of SQL server sessions
EXEC SP_WHO2
–It’s advanced version of sp_who2 but not shipped with SQL server.Get it from the following link. 
EXEC SP_WHOISACTIVE
–Used in Query tuning for getting the logical reads
SET STATISTICS IO ON
–Used in Query tuning for getting the CPU time
SET STATISTICS TIME ON
–Clean the data buffers.Used in query tuning.
DBCC DROPCLEANBUFFERS
–Free the procedure cache.Used in query tuning.
DBCC FREEPROCCACHE
–Check the Error logs in efficient way
EXEC MASTER.DBO.XP_READERRORLOG 0, 1, ‘search String 1’, ‘search String 2’, ‘2017-01-10’, ‘2017-02-20’, N’desc’
–Check the query a session is running.
DBCC INPUTBUFFER(<session_id>)
–Get current log file path  
SELECT Serverproperty(‘ErrorLogFileName’)
–Database consistency check
DBCC CHECKDB WITH ESTIMATEONLY;

DBCC CHECKDB WITH NO_INFOMSGS;

DBCC CHECKDB(DBNAME) WITH NO_INFOMSGS, ALL_ERRORMSGS SELECT Databasepropertyex (‘kcc_dw’, ‘STATUS’)         

DBCC sqlperf(‘logspace’) 

Advertisements

Posted in DBA | Tagged: , | Leave a Comment »

SQL Server:Write on “???” failed: 1453(Insufficient quota to complete the requested service.

Posted by Pramod Singla on August 3, 2016


Error:Write on “???” failed: 1453(Insufficient quota to complete the requested service.

solution: Restrict the max server memory setting of the Sql server to allow other application to run properly.

Untitled

Posted in DBA | Tagged: , , , , | Leave a Comment »

SQL Server -Find Nth Highest Salary

Posted by Pramod Singla on August 15, 2014


Problem: Find Nth highest salary in Minimal time.

Solution: Interviewer often asks to find Nth Highest Salary in an interview. Many solutions are available on internet to do so. Couple of commonly used solutions are Solution 1 & Solution 2.

All these solutions are bit slow. But by using Common Expression Tables (CTE) you can find the Nth highest salary very efficiently and impress your interviewer.

  • Create a Sample Table and  Insert Sample Data in it:

CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)

After creating the table, execute the above code to insert the data into the table as follows:

INSERT INTO #T1 VALUES (1, ‘pds’, 10000)
INSERT INTO #T1 VALUES (2, ‘vicky’, 10000)
INSERT INTO #T1 VALUES (3, ‘divyan’, 30000)
INSERT INTO #T1 VALUES (4, ‘deepak’, 40000)
INSERT INTO #T1 VALUES (5, ‘anu’, 50000)
INSERT INTO #T1 VALUES (6, ‘jai’, 60000)
INSERT INTO #T1 VALUES (7, ‘abhi’, 20000)
INSERT INTO #T1 VALUES (8, ‘disha’, 40000)
INSERT INTO #T1 VALUES (9, ‘anika’, 60000)
INSERT INTO #T1 VALUES (10, ‘vijay’, 50000)

To find the highest salary we can simply use the Max function as given below:

Select Max(Salary) as [Highest Salary] from T1

  • Following Query will Find Nth Highest Salary in minimal time:

         declare @n int;

        set @n =4

        with cte as

       (

          SELECT name, salary,
                     dense_rank() OVER( ORDER BY salary DESC) AS rowid
                   FROM   #t1
              )

     SELECT *
    FROM   cte
    WHERE  rowid = @n

     This will create row number for each row after it has been sorted by salary in the descending order.

  • Comparison Stats with Other Solutions:

Posted in Interview Questions | Tagged: , , , | 4 Comments »

SQL Server -Window XP useful commands for DBA

Posted by Pramod Singla on October 10, 2012


run>msinfo32
  • It gets the system information like OS, version name, components and many more.
run>sqlservermanager11.msc
  • Opens the SQL server configuration manager if the latest SQL server installed on the machine is SQL 2012.
run>sqlservermanager10.msc
  • Opens the SQL server configuration manager if the latest SQL server installed on the machine is SQL 2008.
run>compmgmt.msc
  • It opens the computer management window which in turn used for user management, event viewer, disk management etc.
run>inetmgr
  • It opens the IIS window which int-run used for managing FTP, Web sites and SMTP.
run>cmd>osql -L
  • It fetches the list of all the SQL server installed on the current network.
run>cmd>netstat -na|find "1433"
  • It finds the status of the 1433 port.
run>c:\windows\system32\drivers\etc\hosts
  • This is the host file path. In case client SSMS is not able to connect to the SQL server using the server name but can connect using the IP address then try making an entry (associate IP address with the Server name) into this file.
run>secpol.msc
  • Opens the local security setting window which is used to set security options, IP security, password policy etc.

Posted in DBA | Tagged: , , , , | Leave a Comment »

SQL Server -Find currently Running jobs

Posted by Pramod Singla on June 19, 2012


If you want to find the currently running jobs then following queries will help you.

  • List All Currently Running SQL Agent Jobs:
exec msdb..sp_help_job @execution_status=1
  • List Extra Information about your Job:
exec msdb..sp_help_job @Job_name=<yourjobname>
  • Possible values of @execution_status:
Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retires.
4 Idle.
5 Suspended.
7 Performing completion actions.

Links : http://msdn.microsoft.com/en-us/library/ms186722(SQL.105).aspx

Posted in DBA | Tagged: , , | Leave a Comment »