• DBA

    Table Partition View partition data DMVs part 2

    Code to view the partition table data. use Test_Tbl_Partition go /* -- find no.of partition table select object_schema_name(i.object_id) as [schema], object_name(i.object_id) as [object], i.name as [index], s.name as [partition_scheme] from sys.indexes i join sys.partition_schemes s on i.data_space_id = s.data_space_id */ go SELECT ISNULL(quotename(ix.name),'Heap') as IndexName ,ix.type_desc as type ,prt.partition_number ,case when ix.index_id < 2 then prt.rows else 0 END as Rows ,cast (rv.value as datetime2(7)) as [Upper_Boundary] ,prt.data_compression_desc ,ps.name as PartitionScheme ,pf.name as PartitionFunction ,fg.name as FilegroupName ,au.TotalMB ,au.UsedMB ,case when pf.boundary_value_on_right = 1 then 'less than value column' when pf.boundary_value_on_right is null then '' else 'less than or equal to value column' End as Comparison ,fg.name as FileGroup FROM…

  • DBA

    Table Partition Microsoft SQL server part 1

    What is table partitioning? Table partitioning is a way to divide a large table into smaller pieces as a partition logical unit. By default a table will be stored into single partition that is partition number 1. The data of partitioned tables and indexes divided into logical units that may be spread across more than one file and filegroup in case if we use file and filegroup method in the database creation, otherwise it will be stored into a single default MDF Primary file and filegroup. Having multiple filegroup or single file group is depends on the application requirement it is not going to do anything with table partition for…

  • DBA

    The log scan number passed to log scan in database is not valid. This error may indicate data corruption

    Database went into suspect mode database corruption One of our servers got migrated from one data center to other data center using third party tool called Zerto. After migration two databases went into suspect mode. Errors in the log: The log scan number (40359:103:2) passed to log scan in database <db name> is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup During undoing of a logged operation in database <db name>  , an error occurred…

  • DBA

    MS SQL server remote DBA training course topics Chennai India

    Microsoft SQL server training course topics:   Overview of IT environment with daily DBA activity and health check A brief of history (RDBMS & DBMS) SQL server internal architecture SQL server overview (Editions) SQL Installation overview (System DBs) Review of new features Transact -SQL overview Normalization TABLES AND CONSTRAINTS Standard data types Tables and columns in SQL server Objects, Tables and columns in T-SQL Modifying tables and columns Database diagram architecture Constrains TRANSACT SQL QUIERES Querying SQL server tables SQL and transact SQL Using the select statement Transact SQL built-in function Summary queries Sub queries MERGING DATA WITH JOINS&UNIONS Working with inner joins Using left outer joins Multiplying data with…