• DBA

    Filelistonly and headeronly – Find SQL server version from backup and use of header only version, ldf mdf size and LSN restore and file list only logical name.

    Problem: SQL server database refresh / restore running for a long time is suddenly fails every Friday night. The refresh job is running from agent script but the triggering and managing jobs are handled by third party tool. Source server taking backup in a share path and the target server restoring it daily night. To find the issue we need to capture the header information of backup where is coming from and what it’s containing it. We can we can get all from the table – headeronly.   Script: restore filelistonly from disk ='D:\BACKUP\DBA_TEST.BAK' restore headeronly from disk ='D:\BACKUP\DBA_TEST.BAK' --drop table tbl_headeronly --truncate table tbl_headeronly CREATE TABLE tbl_headeronly ( BackupName NVARCHAR(128)…

  • DBA

    SQL server reports GUI Schema change history and CPU  RAM IO

    SQL server reports GUI Schema change history and CPU  RAM IO There are standard reports available in SQL server, we can generate from GUI. It is available from SQL2005 onwards, It is collected from default trace – fn_trae_getdata , since last restart of instance. You can get it from T-SQL or GUI. By default it is enabled. We can use this for sharing some reports – Top usage, high CPU IO used queries and schema change history who dropped a database, table, schema. Right click server — Reports — Standard Reports.

  • DBA

    Error: 9002, Severity: 17 log_reuse_wait_desc = ‘log_backup’ transaction log full

    Shrink log file for log_reuse_wait_desc = ‘log_backup’, there are cases we are getting often that transaction log full due do lack of log_backup or sometimes in simple mode the log is full for larger high IOPS transaction. Take a look on this –> https://www.sqlserverblogforum.com/dba/database-transaction-log-file-full-in-the-full-recovery-model/ Here is the script to shrink transaction log file. schedule it on agent for every hour. /* -- create database DBA_DB --drop table tbl_log_shrink_temp_load use DBA_DB go create table [dbo].[tbl_log_shrink_temp_load]( [db] [nvarchar](128) null, [size_mb] [int] null, [log_reuse_wait_desc] [nvarchar](60) null, [name] [sysname] not null, [recovery_model_desc] [nvarchar](60) null ) go --drop table tbl_log_shrink_history create table [dbo].[tbl_log_shrink_history]( [db] [nvarchar](128) null, [size_mb] [int] null, [log_reuse_wait_desc] [nvarchar](60) null, [name] [sysname] not null, [recovery_model_desc]…

  • DBA,  Junior DBA

    SQL server connection and blocking finding script

    SQL server connection and blocking finding script   -- For SQL 2000 select db_name(dbid),* from master.dbo.sysprocesses where spid> 51 and status <>'sleeping' -- and blocked <>0 -- To find the spid and connections which are all running select db_name (r.database_id),r.command,r.wait_resource,s.status,s.session_id, r.blocking_session_id,r.last_wait_type,r.open_transaction_count,sql_handle, * from sys.dm_exec_sessions s join sys.dm_exec_requests r on s.session_id =r.session_id --where s.status <>'sleeping' and s.session_id >=51 and s.session_id <>@@SPID -- exclude your session -- to find the blockings select db_name (r.database_id),r.command,r.wait_resource,s.status,s.session_id, r.blocking_session_id,r.last_wait_type,r.open_transaction_count, * from sys.dm_exec_sessions s join sys.dm_exec_requests r on s.session_id =r.session_id where r.blocking_session_id<>0 -- list blocking select * FROM sys.dm_exec_requests -- to find the code which is run by spid connections SELECT T.TEXT As Query, R.session_id, R.status, R.command…

  • DBA

    Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped. There is insufficient system memory in resource pool ‘internal’ to run this query.

    Today my colleague had issue that SQL server instance is not starting on. It is a SQL 2016 and had seven instances with 24 GB of total OS RAM. Same again, one of the SQL instance memory configured incorrectly. It is a alwaysON secondary replica. We know where we have to look, when we cannot start SQL service. Run — Eventvwr — Windows logs –Application and system. One more: Open SQL server errorlog file in notepad and review as well. You can get the error log location in the configuration manger — startup parameters.   2019-01-30 04:43:14.73 spid6s      Error: 701, Severity: 17, State: 123. 2019-01-30 04:43:14.73 spid6s      There…