Monday, February 27, 2017

Find what script is being run by specific SPID number


DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 53
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

SQL Script to find all Date/time columns in database


select
    so.name table_name
   ,sc.name column_name
   ,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME', 'DATE', 'TIME')

Friday, February 17, 2017

Tuesday, February 7, 2017

Generate Script of All indexes in a SQL Server database

SELECT ' CREATE ' +
       CASE
            WHEN I.is_unique = 1 THEN ' UNIQUE '
            ELSE ''
       END +
       I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
       I.name + ' ON ' +
       SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
       KeyColumns + ' )  ' +
       ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
       ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
       CASE
            WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
            ELSE ' PAD_INDEX = OFF '
       END + ',' +
       'FILLFACTOR = ' + CONVERT(
           CHAR(5),
           CASE
                WHEN I.fill_factor = 0 THEN 100
                ELSE I.fill_factor
           END
       ) + ',' +
       -- default value
       'SORT_IN_TEMPDB = OFF ' + ',' +
       CASE
            WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
            ELSE ' IGNORE_DUP_KEY = OFF '
       END + ',' +
       CASE
            WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
            ELSE ' STATISTICS_NORECOMPUTE = ON '
       END + ',' +
       ' ONLINE = OFF ' + ',' +
       CASE
            WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
            ELSE ' ALLOW_ROW_LOCKS = OFF '
       END + ',' +
       CASE
            WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
            ELSE ' ALLOW_PAGE_LOCKS = OFF '
       END + ' ) ON [' +
       DS.name + ' ] ' +  CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM   sys.indexes I
       JOIN sys.tables T
            ON  T.object_id = I.object_id
       JOIN sys.sysindexes SI
            ON  I.object_id = SI.id
            AND I.index_id = SI.indid
       JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name + CASE
                                                                       WHEN MAX(CONVERT(INT, IC1.is_descending_key))
                                                                            = 1 THEN
                                                                            ' DESC '
                                                                       ELSE
                                                                            ' ASC '
                                                                  END
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column =
                                                          0
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                          ORDER BY
                                                 MAX(IC1.key_ordinal)
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) KeyColumns
                           FROM   sys.index_columns IC2
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp3
            )tmp4
            ON  I.object_id = tmp4.object_id
            AND I.Index_id = tmp4.index_id
       JOIN sys.stats ST
            ON  ST.object_id = I.object_id
            AND ST.stats_id = I.index_id
       JOIN sys.data_spaces DS
            ON  I.data_space_id = DS.data_space_id
       JOIN sys.filegroups FG
            ON  I.data_space_id = FG.data_space_id
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column =
                                                          1
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) IncludedColumns
                           FROM   sys.index_columns IC2
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp1
                WHERE  IncludedColumns IS NOT NULL
            ) tmp2
            ON  tmp2.object_id = I.object_id
            AND tmp2.index_id = I.index_id
WHERE  I.is_primary_key = 0
       AND I.is_unique_constraint = 0
           --AND I.Object_id = object_id('Person.Address') --Comment for all tables
           --AND I.name = 'IX_Address_PostalCode' --comment for all indexes 

Wednesday, January 25, 2017

Difference between Detaching database and bringing database Offline

SQL Server Offline and Detach Database




Detach Database/Attach Database: The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.
Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached.

you can attach a copied or detached SQL Server database. When you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2016 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server When you attach a database, all data files (MDF and NDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.

The requirement for attaching log files depends partly on whether the database is read-write or read-only, as follows:
  • For a read-write database, you can usually attach a log file in a new location. However, in some cases, reattaching a database requires its existing log files. Therefore, it is important to always keep all the detached log files until the database has been successfully attached without them.
    If a read-write database has a single log file and you do not specify a new location for the log file, the attach operation looks in the old location for the file. If it is found, the old log file is used, regardless of whether the database was shut down cleanly. However, if the old log file is not found and if the database was shut down cleanly and has no active log chain, the attach operation attempts to build a new log file for the database.
  • If the primary data file being attached is read-only, the Database Engine assumes that the database is read-only. For a read-only database, the log file or files must be available at the location specified in the primary file of the database. A new log file cannot be built because SQL Server cannot update the log location stored in the primary file.
Database Offline: Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.


Use OFFLINE and ONLINE
1) If you are trying to make the database temporary unavailable for a period of time, you could take the database OFFLINE, and make it available by bringing it ONLINE whenever it is or you are ready.

2a) If you want to move the database or log files to different physical location or changing the database and/or log physical file name, AND keep them within the same SQL Server instance.
- Before you take the database offline, you need to know the logical name of the file. The name field is the logical name for the physical file name that you are about to change the path. Record the logical name for later use.

SELECT name, type, type_desc, physical_name, state, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabase')

- Take the database OFFLINE.
ALTER DATABASE YourDatabase SET OFFLINE
WITH ROLLBACK IMMEDIATE;

-Move the database and/or log files to different location.
- Update the database and/or log files path registered on its SQL Server instance. Execute the T-SQL command for each database and log files,

ALTER DATABASE YourDatabase 
MODIFY FILE (NAME = YourFileLogicalName,
FILENAME = 'Your Database or Log new physical path')

Bring the database ONLINE.
ALTER DATABASE YourDatabase SET ONLINE;

If you just want to change the logical name, you dont have to take the database OFFLINE. You could change it on SSMS or T-SQL.

Right click on the database > Properties > Select Page > Change it under Logical Name column > OK.

ALTER DATABASE YourDatabase 
MODIFY FILE (NAME = YourFileLogicalName,
NEWNAME = YourFileNewLogicalName)

2b) If you are relocating a file during part of the scheduled disk maintenance process, you may need to change the file path registered on its SQL Server instance before taking the database OFFLINE. This assure the server has the right path for the database and log files when the instance restart, after being shut down for maintenance.
- When the database and/or log file path is successfully updated on the instance, a message will show; 'The file "YourDatabaseFile" has been modified in the system catalog. The new path will be used the next time the database is started.'

Use DETACH and ATTACH,
1) If you want to move the database and log files to a different SQL Server instance or another server.
- Rollback active transaction and gain exclusive access. Detach the database. (optional: update statistics) 

ALTER DATABASE YourDatabase SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

EXEC sp_detach_db 'YourDatabase', 'true'

- Move database and log files to different location.
- Attach the database on different instance on same/another server. 

USE master;

CREATE DATABASE YourDatabase
ON (FILENAME = 'the physical path of your database file')
LOG ON (FILENAME = 'the physical path of your log file')
FOR ATTACH;

- If the database has previously set to single user, you may want to set it to multiple user access option.

ALTER DATABASE YourDatabase SET MULTI_USER

2) Even though you can choose to detach and attach the database back on the same SQL Server instance for scenario like changing physical path or file name, it is recommended to use the OFFLINE and ONLINE methods due to the restriction and limitation on DETACH. When you detach a database, you remove the database from the instance. It is required to remove the database from any participation of replication, mirroring and snapshot.

Reference: 







Tuesday, January 24, 2017

Query to List all the partitioned tables in SQL Server database

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 order by [schema]

Tuesday, December 6, 2016

Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator

Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator


DBCC CHECKDB('databaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS 


--Resulted In
Msg 8909, Level 16, State 1, Line 5
Table error: Object ID 0, index ID 12341, page ID (1:5880). The PageId in the page header = (9728:16777220).
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.
 

DBCC results for 'TableName'.
Msg 8928, Level 16, State 1, Line 5
Object ID 871674153, index ID 0: Page (1:5880) could not be processed. See other errors for details.

 
There are 20993 rows in 584 pages for object 'TableName'.
CHECKTABLE found 0 allocation errors and 8 consistency errors in table 'TableName' (object ID 871674153).
Msg 8909, Level 16, State 1, Line 5
Table error: Object ID 1109413712, index ID 24940, page ID (1:5883). The PageId in the page header = (25198:1632843825).
CHECKTABLE found 0 allocation errors and 1 consistency errors in table '(Object ID 1109413712)' (object ID 1109413712).


Although its not an ideal solution, you can use DBCC CHECKTABLE which in our case fixed the issue:

--Put the database into single user mode
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH NO_WAIT
--Check the erors and fix any issues found (that you can)
DBCC CHECKTABLE ('Orders', REPAIR_REBUILD)
--Put the database back into multiuser mode
ALTER DATABASE [DatabaseName] SET MULTI_USER WITH NO_WAIT