SQL Server dba Common commands

Sp_lock
sp_who
Sp_who2
Sp_who2 ' active ';
DBCC INPUTBUFFER (64)

-- check for blocked spid

Select Blocking_session_id,wait_duration_ms,session_id,wait_type from
Sys.dm_os_waiting_tasks
Where blocking_session_id is not null

-- search for spid content

sp_who spid

-- unblock spid

KILL spid

-- search for spid content

DBCC InputBuffer (spid)

-- create account in this machine Test

CREATELOGIN[Test] withPASSWORD=N ' a123456 ',Default_database=[Workflow],Check_policy=OFF

GO

-- Create an account in the library if it's a bak file or an additional repository, you don't need this.

Use Workflow

GO

CREATE USER [test] for LOGIN [test]

GO

-- repair Orphaned account

Use Workflow

GO

execute sp_change_users_ Login ' Update_One ' test test

GO

-- empowering dbo rights

Use [Workflow]

GO

EXEC sp_addrolemember N ' db_owner ' , N ' Test '

GO

-- Show information about the archive

sp_helpdb Workflow

/* View Log size */

DBCC SQLPERF (logspace)

GO

-- cut transaction records

BACKUP LOG ' Datadasename ' with no_log

BACKUP LOG ' Datadasename ' with truncate_only

-- The above two phrases will intercept the transaction records, as long as they are executed, the transaction record is not successful until the next complete preparation!

-- subtract log the size of the file (LOG file name reduced by up to

DBCC Shrinkfile (beedb_log,+)

/******** The position of the DB ********/

-- Check the location of DB

Selectname,Physical_name fromSYS.master_fileswheredatabase_id=db_id(' Salepony ')

-- swap out the location of the DB

Alter Database Sale

modify file Span style= "color: #808080;" > ( name = sale filename Span style= "color: #808080;" >= L:\DataBase\dbfile\ Sale.mdf ' )

Go

Alter Database Sale

modify file Span style= "color: #808080;" > ( name = sale_log Span style= "color: #808080;" > filename Span style= "color: #808080;" >= L:\DataBase\translog\ Sale.ldf ' )

Go

/* Modify the Repository password * /

Alterlogin[SA]withpassword=N ' newpassword '

-old password can not be provided

SQL Server dba Common commands