MSSQL Server maintenance

Find the physical location select name, physical_name  from sys.database_files   Backup and shrink use DBnamego alter database DBnameset recovery simplegoDBCC SHRINKFILE (DB_Log_Name,2) --- MSSSNTI_log is the logical file name of the databasegoalter database DBnameset recovery fullgo...

Identify blocking query and kill blocking_session_id in mssql server

Identify the blocking query by executing below SQL SELECTdb.name DBName,tl.request_session_id,wt.blocking_session_id,OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,tl.resource_type,h1.TEXT AS RequestingText,h2.TEXT AS BlockingTest,tl.request_modeFROM sys.dm_tran_locks AS tlINNER JOIN sys.databases db ON db.database_id = tl.resource_database_idINNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_addressINNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_idINNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_idINNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =...

Backup and restore mysql database using mysqldump

To backup: mysqldump -u root -p liveDBname > backup.sqlmysqldump -u root -p liveDBname > backup-$(date +%Y-%m-%d-%H).sqlmysqldump -u root -p liveDBname > backup-$(date +%Y-%m-%d-%H.%M.%S).sql   To restore: mysql -u root -p liveDBname < from_backup.sql To Import: mysqlimport -u root -p liveDBname from_backup.sql Schedule your backup using crontab sudo mysqldump -u 'username' -p'password' DBNAME > /var/www/auto-backup-sql/liveDB-`date...

Scroll to top