SQL server upgrade is going lower version of SQL to higher version – the latest version. It can be a minor upgrade i.e. service pack patch apply or major upgrade from one version to another ex: 2016 to 2017. Types: We can perform this in the existing system that is called In-place upgrade. This requires more downtime and better roll back/fall back plan. Since, if anything happens take more time to restore previous state, but quickest one, just taking a backup of everything -VM snapshot. We can install new latest SQL version and copy everything to the new server that is called side by side upgrade. This is something like…
-
-
File and file group method testing for VLDB very large database
File and file group VLDB very large database. There are many factors needs to be looked carefully. Let me put some important points. Choosing a storage hardware & Physical database layout – like Dell compellent, 8 GBs FC Creating a database with physical file layout- File and file group. Layout the logical database files – Like Table Partitioning – Indexes are same file group. Maintenance job for VLDBs – Easy of Backup, piecemeal restore, split checkDB and reindex for TP, TempDB no of file. Overview: Example of new application requirement: The database load data every current year data to the database. Per year ~ 2TB. There will be minimal…
-
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)…
-
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.
-
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://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]…