Monday, 23 May 2016

Error: 17053, Severity: 16, State: 1. The operating system returned error 6851

Error: 17053, Severity: 16, State: 1.
K:\MSSQL\DATA\tempdb.mdf: Operating system error 6851(failed to retrieve text for this error. Reason: 15100) encountered.

Error: 17053, Severity: 16, State: 1.
I:\MSSQL\DATA\templog.ldf: Operating system error 6851(failed to retrieve text for this error. Reason: 15100) encountered.

The operating system returned error 6851(failed to retrieve text for this error. Reason: 15105) to SQL Server during a write at offset 0000000000000000 in file
'K:\MSSQL\DATA\tempdb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error
condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors;
for more information, see SQL Server Books Online.


The operating system returned error 6851(failed to retrieve text for this error. Reason: 15105) to SQL Server during a write at offset 0000000000000000 in file
'K:\MSSQL\DATA\tempdb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition
that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for more information, see SQL Server Books Online.

Error: 5149, Severity: 16, State: 3.

When you see above ERROR messages?

This will happen for many reason,
       1. might not re-started since you created new tempdb files.
       2. Tempdb files corrupted.

SOLUTION

--MY SERVER ONLY HAVE ONE INSTANCE(LOCALHOST)..
--GO TO CONFIGURATION MANAGER AND STOP ALL SERVICES INCLUDING SQL SERVER BROWSER OR ANY OTHER

First Search for sqlservr.exe in you server just take a copy of the path





Open Cmd Prompt as an administrator my path is

-->>>> CD C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

and Run -->>>>    Sqlservr.exe /f /c

Once Done you will see some MESSAGE like some database are recovered. CLOSE THE CMD PROMPT NOW.

Now open another cmd prompt as an administrator

NOW run -->>>>NET START MSSQLSERVER /T3608

NOW YOU WILL SEE SQL SERVER (MSSQLSERVER) IS STARTING AND SUCESSFULLY STARTED..

NOW ENTER
--->>>>>SQLCMD
--->>>>>USE MASTER
--->>>>> ALTER DATABASE TEMPDB MODIFY FILE (NAME = tempdev, FILENAME = 'C:\TEMPDB.MDF')   ---REPLACE (C:) WITH YOUR OWN PATH
--->>>>> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'C:\Tempdblog.LDF') --REPLACE (C:) WITH YOUR OWN PATH
--->>>>>GO
NOW YOU SHOULD BE ABLE TO SEE MESSAGE
CHANGED DATABASE CONTEXT TO 'MASTER'
THE FILE "tempdev" HAS BEEN MODIFIED IN THE SYSTEM CATALOG. THE NEW PATH WILL BE USE THE NEXT TIME THE DATABASE IS STARTED.



NOW YOU GO TO CONFIGURATION MANAGER AND RESTART THE SQLSERVER..


Posted by at 3 comments:

Friday, 13 May 2016

How to copy indexes from one table to another temp table


--HOW TO COPY INDEXS FROM ONE TABLE TO ANOTHER TEMP TABLE?

-- BLOW SPROC WILL COPY INDEX FROM ONE TABLE TO ANOTHER TEMP TABLE?

CREATE PROCEDURE [dbo].[uspCopyIndex]
      @STName varchar(50)
      ,@SSName varchar(50)
      ,@DTName varchar(50)
      ,@DSName varchar(50)
      ,@ClusteredOnly bit
AS
BEGIN

      -- Get all existing indexes
      DECLARE cIX CURSOR FOR
            SELECT DISTINCT
                  SCHEMA_NAME(tbl.schema_id) AS [TableSchema],
                  tbl.name AS [TableName]
                  ,i.Object_ID AS [ObjectId],
                  i.name AS [IndexName]
                  ,i.Index_ID
                  ,i.is_primary_key IsPrimaryKey
            FROM sys.tables AS tbl
            INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
            WHERE tbl.name = @STName AND SCHEMA_NAME(tbl.schema_id) = @SSName
                 

      DECLARE @IxSchema SYSNAME
            ,@IxTable SYSNAME
            , @IxTableID INT
            , @IxName SYSNAME
            , @IxID INT
            ,@IsPrimaryKey BIT

      -- Loop through all indexes
      OPEN cIX
      FETCH NEXT FROM cIX INTO @IxSchema, @IxTable, @IxTableID, @IxName, @IxID, @IsPrimaryKey
      WHILE (@@FETCH_STATUS = 0)
      BEGIN

            -- Check if clustered
            IF @ClusteredOnly = 0 OR (@ClusteredOnly = 1 AND INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
            BEGIN

                  DECLARE @IXSQL NVARCHAR(4000)
                 
                  SET @IXSQL = 'IF  NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + @DSName + '].[' + @DTName + ']'') AND name = N''' + @IxName + ''')' + CHAR(10) + CHAR(13)
                 
                  IF @IsPrimaryKey = 0
                  BEGIN
                        SET @IXSQL = @IXSQL + 'CREATE '

                        -- Check if the index is unique
                        IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1) SET @IXSQL = @IXSQL + 'UNIQUE '
                        -- Check if the index is clustered
                        IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1) SET @IXSQL = @IXSQL + 'CLUSTERED '
                              ELSE SET @IXSQL = @IXSQL + 'NONCLUSTERED '
                        SET @IXSQL = @IXSQL + 'INDEX [' + @IxName + '] ON [' + @DSName + '].' + @DTName + ' ('
                  END
                  ELSE IF @IsPrimaryKey = 1
                  BEGIN
                        SET @IXSQL = @IXSQL + 'ALTER TABLE [' + @DSName + '].' + @DTName + ' ADD CONSTRAINT [Aux' + @IxName + '] PRIMARY KEY '
                        IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1) SET @IXSQL = @IXSQL + 'CLUSTERED ('
                              ELSE SET @IXSQL = @IXSQL + 'NONCLUSTERED ('
                  END

                  -- Get all columns of the index
                  DECLARE cIxColumn CURSOR FOR
                    SELECT SC.Name, IC.is_descending_key
                    FROM Sys.Index_Columns IC
                         JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
                    WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID AND IC.is_included_column = 0
                    ORDER BY IC.key_ordinal

                  DECLARE @IxColumn SYSNAME
                  DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1
                  DECLARE @IxIsDescending BIT

                  -- Loop throug all columns of the index and append them to the CREATE statement
                  OPEN cIxColumn
                  FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIsDescending
                  WHILE (@@FETCH_STATUS = 0)
                  BEGIN
                        IF (@IxFirstColumn = 1) SET @IxFirstColumn = 0
                        ELSE SET @IXSQL = @IXSQL + ', '

                        SET @IXSQL = @IXSQL + @IxColumn
                        IF (@IxIsDescending = 1) SET @IXSQL = @IXSQL + ' DESC' ELSE SET @IXSQL = @IXSQL + ' ASC'

                    FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIsDescending
               END
               CLOSE cIxColumn
               DEALLOCATE cIxColumn

            SET @IXSQL = @IXSQL + ') '

                  -- Get all included columns of the index
                  DECLARE cIxIncColumn CURSOR FOR
                    SELECT SC.Name, IC.is_descending_key
                    FROM Sys.Index_Columns IC
                         JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
                    WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID AND IC.is_included_column = 1
                    ORDER BY IC.key_ordinal

                  SET @IxFirstColumn = 1

                  -- Loop throug all columns of the index and append them to the CREATE statement
                  OPEN cIxIncColumn
                  FETCH NEXT FROM cIxIncColumn INTO @IxColumn, @IxIsDescending
                  WHILE (@@FETCH_STATUS = 0)
                  BEGIN
                        IF (@IxFirstColumn = 1)
                              BEGIN
                                    SET @IxFirstColumn = 0
                                    SET @IXSQL = @IXSQL + 'INCLUDE ('
                              END
                        ELSE SET @IXSQL = @IXSQL + ', '

                        SET @IXSQL = @IXSQL + @IxColumn

                        FETCH NEXT FROM cIxIncColumn INTO @IxColumn, @IxIsDescending
                        IF (@@FETCH_STATUS != 0) SET @IXSQL = @IXSQL + ') '
                  END
                  CLOSE cIxIncColumn
                  DEALLOCATE cIxIncColumn
                 
                  SET @IXSQL = @IXSQL + ' WITH (MAXDOP=6)'
                  EXEC (@IXSQL)
            END
            FETCH NEXT FROM cIX INTO @IxSchema, @IxTable, @IxTableID, @IxName, @IxID, @IsPrimaryKey
      END

      CLOSE cIX
      DEALLOCATE cIX

END


E.G
--MAKE SURE BOTH TABLE SHOULD HAVE SAME COLUMNS NAME

EXEC EXEC uspCopyIndex
                              @STName = 'FactActivity'
                              ,@SSName = 'dbo'
                              ,@DTName = 'FactActivityAux'
                              ,@DSName = 'dbo'

                              ,@ClusteredOnly = 0
Posted by at 2 comments:

How to script Index Names into temp table to load data faster

--IN SOME SCENARIOS WHERE WE NEED TO DROP INDEX BEFORE LOADING DATA AND -- CREATING AFTER. BELOW SCRIPTS WILL HELP YOU.. WE MAY NEED NECESSARY CHANGES WHERE NEEDED


-- FIRST CREATE TABLE TO STORE SCRIPTS TO DELETE AND CREATE INDEXS .

CREATE TABLE [dbo].[TempSqlScriptsS](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [BtlId] [int] NOT NULL,
      [TableName] [varchar](500) NOT NULL,
      [Create] [bit] NOT NULL,
      [sql] [varchar](max) NOT NULL,
 CONSTRAINT [PK_TempSqlScriptsS] PRIMARY KEY CLUSTERED
(
      [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--CREATE SPROC NOW WHCIH TAKE INPUT AS SCHEMA , TABLE AND Indexid(IF NEEDED)  AND GENETEA ALL INDEXES AVAILABLE ON PARTICULAR TABLE.

  CREATE PROCEDURE [dbo].[uspScriptIndex]
      @STName varchar(50),
      @SSName varchar(50),
      @Btlid int
AS
BEGIN
      SET NOCOUNT ON
     
      -- Get all existing indexes, but NOT the primary keys
      DECLARE cIX CURSOR FOR
            SELECT DISTINCT
                  SCHEMA_NAME(tbl.schema_id) [TableSchema]
                  ,tbl.name AS [TableName]
                  ,i.Object_ID AS [ObjectId]
                  ,i.name AS [IndexName]
                  ,i.Index_ID
                  ,filegroup_name(i.data_space_id) FileGroup
            FROM sys.tables AS tbl
            INNER JOIN sys.indexes i
                  ON (i.index_id > 0 and i.is_hypothetical = 0)
                  AND (i.object_id=tbl.object_id)
            WHERE i.is_unique = 0
                  AND i.is_primary_key = 0
                  AND tbl.name = @STName
                  AND SCHEMA_NAME(tbl.schema_id) = @SSName

      DECLARE @IxSchema SYSNAME
            ,@IxTable SYSNAME
            ,@IxTableID INT
            ,@IxName SYSNAME
            ,@IxID INT
            ,@FileGroup varchar(50)

      -- Loop through all indexes
      OPEN cIX
     
      FETCH NEXT FROM cIX INTO @IxSchema, @IxTable, @IxTableID, @IxName, @IxID, @FileGroup
     
      WHILE (@@FETCH_STATUS = 0)
      BEGIN
            DECLARE @IXSQL NVARCHAR(4000)
            SET @IXSQL = 'CREATE '

            -- Check if the index is unique
            IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
                  SET @IXSQL = @IXSQL + 'UNIQUE '
                 
            -- Check if the index is clustered
            IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
                  SET @IXSQL = @IXSQL + 'CLUSTERED '
            ELSE
                  SET @IXSQL = @IXSQL + 'NONCLUSTERED '
           
            SET @IXSQL = @IXSQL + 'INDEX [' + @IxName + '] ON [' + @IxSchema + '].[' + @IxTable + '] ('

            -- Get all columns of the index
            DECLARE cIxColumn CURSOR FOR
                  SELECT SC.Name
                        ,IC.is_descending_key
                  FROM Sys.Index_Columns IC
                  JOIN Sys.Columns SC
                        ON IC.Object_ID = SC.Object_ID
                        AND IC.Column_ID = SC.Column_ID
                  WHERE IC.Object_ID = @IxTableID
                        AND Index_ID = @IxID
                        AND IC.is_included_column = 0
                  ORDER BY IC.Index_Column_ID

            DECLARE @IxColumn SYSNAME
                  ,@IxFirstColumn BIT = 1
                  ,@IxIsDescending BIT

            -- Loop throug all columns of the index and append them to the CREATE statement
            OPEN cIxColumn
           
            FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIsDescending
           
            WHILE (@@FETCH_STATUS = 0)
            BEGIN
                  IF (@IxFirstColumn = 1)
                        SET @IxFirstColumn = 0
                  ELSE
                        SET @IXSQL = @IXSQL + ', '

                  SET @IXSQL = @IXSQL + @IxColumn
                  IF (@IxIsDescending = 1)
                        SET @IXSQL = @IXSQL + ' DESC'
                  ELSE
                        SET @IXSQL = @IXSQL + ' ASC'

                  FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIsDescending
            END
           
            CLOSE cIxColumn
            DEALLOCATE cIxColumn

            SET @IXSQL = @IXSQL + ') '

            -- Get all included columns of the index
            DECLARE cIxIncColumn CURSOR FOR
                  SELECT SC.Name
                        ,IC.is_descending_key
                  FROM Sys.Index_Columns IC
                  JOIN Sys.Columns SC
                        ON IC.Object_ID = SC.Object_ID
                        AND IC.Column_ID = SC.Column_ID
                  WHERE IC.Object_ID = @IxTableID
                        AND Index_ID = @IxID
                        AND IC.is_included_column = 1
                  ORDER BY IC.Index_Column_ID

            SET @IxFirstColumn = 1

            -- Loop throug all columns of the index and append them to the CREATE statement
            OPEN cIxIncColumn
           
            FETCH NEXT FROM cIxIncColumn INTO @IxColumn, @IxIsDescending
           
            WHILE (@@FETCH_STATUS = 0)
            BEGIN
                  IF (@IxFirstColumn = 1)
                        SELECT @IxFirstColumn = 0
                              ,@IXSQL = @IXSQL + 'INCLUDE ('
                  ELSE
                        SET @IXSQL = @IXSQL + ', '

                  SET @IXSQL = @IXSQL + @IxColumn

                  FETCH NEXT FROM cIxIncColumn INTO @IxColumn, @IxIsDescending
                 
                  IF (@@FETCH_STATUS != 0) SET @IXSQL = @IXSQL + ')'
            END

            SET @IXSQL = @IXSQL + ' WITH (MAXDOP=6) ON [' + @FileGroup + ']'

            CLOSE cIxIncColumn
            DEALLOCATE cIxIncColumn

            INSERT INTO EQMDC_Staging.dbo.TempSqlScriptsS
                  (Btlid
                  ,TableName
                  ,[Create]
                  ,[sql])
           
            SELECT
                  @Btlid Btlid
                  ,@STName TableName
                  ,1 [Create]
                  ,'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + @IxSchema + '].[' + @IxTable + ']'')
                        AND name = N''' + @IxName + ''')
                        ' + @IXSQL [sql]
           
            UNION ALL
           
            SELECT
                  @Btlid Btlid
                  ,@STName TableName
                  ,0 [Create]
                  ,'IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + @IxSchema + '].[' + @IxTable + ']'')
                        AND name = N''' + @IxName + ''')
                        DROP INDEX [' + @IxName + '] ON [' + @IxSchema + '].[' + @IxTable + '] ' +
                        CASE WHEN INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1
                              THEN 'WITH (MAXDOP=6)'
                              ELSE ''
                              END [sql]

            FETCH NEXT FROM cIX INTO @IxSchema, @IxTable, @IxTableID, @IxName, @IxID, @FileGroup
      END

      CLOSE cIX
      DEALLOCATE cIX
END
GO
-- NOW  HOW TO EXECUTE ABOVE S-PROC TO SCRIPT INDEXES ON TABLE
EXEC dbo.uspScriptIndex @STName = 'TableName', @SSName = 'dbo', @Btlid = 3109

 -- NOW YOU CAN USE YOUR TABLE ( [dbo].[TempSqlScriptsS])WHICH CONTAINS INDEX SCRIPT WHERE NECESSARY
Posted by at No comments:
Subscribe to: Posts (Atom)