Data World

Posts Tagged ‘dba’

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 Always On-Secondary database is NOT Synchronizing

Posted by Pramod Singla on August 1, 2016


Due to disk space crunch secondary database had gone into not synchronizing mode and it remained into that state even I reclaimed the space. Tried below command and it worked.

DBCC traceon(3605,-1)

ALTER DATABASE yourDB SET HADR RESUME

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

SQL server 2012: Kill SSIS package Execution in Integration Services catalog

Posted by Pramod Singla on June 8, 2015


Problem: How to kill a SSIS package execution in the Integration Services catalog?

Solution: This can be achieved via two methods i.e. using GUI or TSQL. The GUI is very slow in fetching list of active sessions so you can use TSQL to kill the SSIS package session.

Use following script to Kill SSIS package in Integration Services catalog:

–change “YourpackageName” with the actual package name
DECLARE @package_name SYSNAME =‘YourpackageName.dtsx’
DECLARE @execution_id BIGINT

SELECT @execution_id = Max(execution_id)
FROM   ssisdb.catalog.executions
WHERE  package_name = @package_name
AND end_time IS NULL

SELECT @execution_id

EXEC ssisdb.catalog.Stop_operation
@operation_id =@execution_id  

Posted in SSIS | Tagged: , , , | 1 Comment »

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 »