Find columns with special characters

Standard

Problem

im specialImagine a database where you want to know how many times a certain column contains a special character. The reason I had to do this was because I needed to know the impact of an export interface which wasn’t working properly with special characters.

But as lazy as I am as it comes to do repetitive tasks, I had to find a way I could easily get all columns with special characters in a data set without a lot of hassle.

The solution

Fortunately for me I can use the internal INFORMATION views of a database in SQL Server to get all the columns with their table, schema and type of data. Because this database only used varchar columns for saving text data I only had to search for this data type. You can easily extend the selecting by adding the neccesary types like TEXT, NVARCHAR etc.

To create the script I had to accomplish the following things:

  1. Retrieve all the colums that could contain text
  2. Set up a pattern to check the columns with
  3. Loop through the columns
  4. In every loop save the results
  5. Make up a data set of the data retrieved

I eventually got to this script:

-- Replace the mydatabase with the name of your database 
USE [mydatabase] 

-- Delete possible present temporary tables 
IF Object_id('tempdb..#columns') IS NOT NULL 
  DROP TABLE #columns 

IF Object_id('tempdb..#results') IS NOT NULL 
  DROP TABLE #results 

IF Object_id('tempdb..#rows') IS NOT NULL 
  DROP TABLE #rows 

-- Create a table for holding all the columns 
CREATE TABLE #columns 
  ( 
     id            INT NOT NULL PRIMARY KEY IDENTITY(1, 1), 
     database_name VARCHAR(100), 
     schema_name   VARCHAR(50), 
     table_name    VARCHAR(200), 
     column_name   VARCHAR(200), 
     position      INT 
  ) 

-- Create a table for holding the results 
CREATE TABLE #results 
  ( 
     database_name VARCHAR(100), 
     schema_name   VARCHAR(50), 
     table_name    VARCHAR(200), 
     column_name   VARCHAR(200), 
     count         INT 
  ) 

-- Declare the variables needed 
DECLARE @max INT, 
        @cnt INT 
DECLARE @sql     NVARCHAR(max), 
        @pattern VARCHAR(200) 

-- Get all the columns 
INSERT INTO #columns 
SELECT table_catalog, 
       table_schema, 
       table_name, 
       column_name, 
       ordinal_position 
FROM   information_schema.columns 
WHERE  data_type = 'varchar' 
ORDER  BY table_catalog, 
          table_name, 
          ordinal_position 

-- Get the minimum id and max that are needed for the loop 
SELECT @cnt = Min(id) 
FROM   #columns 

SELECT @max = Max(id) 
FROM   #columns 

--Setup the pattern 
SELECT @pattern = 'ëïöüäéíóáèìòàËÏÖÄÉÍÓÁÈÌÒÀ' 

-- Make sure the @sql variable is empty 
SELECT @sql = '' 

-- Start loop 
WHILE( @cnt < ��@max ) 
  BEGIN 
      -- Setup the sql statement 
      SELECT @sql = 'INSERT INTO #results SELECT ''' + database_name + ''', ''' 
                    + schema_name + ''', ''' + table_name + ''', ''' 
                    + column_name + ''', COUNT(1) AS count FROM [' + database_name + '].[' 
                    + schema_name + '].[' + table_name + '] WHERE [' 
                    + column_name 
                    + '] COLLATE Latin1_General_BIN2 LIKE ''%[' 
                    + @pattern + ']%'' ESCAPE ''E'' ' 
      FROM   #columns 
      WHERE  id = @cnt 

      -- Execute the sql statement 
      EXEC Sp_executesql 
        @sql 

      -- Increae the counter 
      SELECT @cnt += 1 
  END 

-- Select the data 
SELECT results.database_name, 
       results.schema_name, 
       results.table_name, 
       results.column_name, 
       results.count AS 'count_results' 
FROM   #results results 
WHERE  results.count >= 1

This might take a while when you need to search through a lot of columns and records.  When the script you will get a result like below:

results specialcharacters

results specialcharacters

 

 

 

 

 

 

 

The pattern can contain any character that you want. I only searched for some of the most common special characters but any character will do.

I hope you find this post helpfull and I appreciate any comment.

Split large data file into smaller data files

Standard

Problem

split_large_databasefileThe business uses a database which is 350 GB database in size and has only one data file and one transaction logfile. The previous DBA didn’t increase the amount of data files which lead to a large data file. Due to the size of the data file I got into trouble with the performance and restoring the database wasn’t a walk in the park either. So how can split a large data file into smaller data files.

What are the options?

  1. Do nothing and let the file grow when needed
  2. Split the file up into multiple data files

Solution number 1 is not a solution and will bite you in the long run because the database will grow so large it will not be easy to maintain anymore.

Solution number 2 will make sure the database has multiple files what could deliver an increase in performance and will be easier to restore. This solution can have be done in two ways:

  1. Have one file group with all the data files
  2. Work with multiple file groups and multiple files

In option number one you’ll have a single file group (the PRIMARY file group) which holds all the files. SQL Server will spread out the data over all the files in that file group equally.

In option number 2 you can make one or more file groups containing at least one data file where you can specifically move tables to.

Because I’m not 100% familiar with the performance per table and I didn’t have enough time to investigate this, I chose to use the PRIMARY file group and create several data files. I wanted to spread out the data over multiple data files because I wanted the original file to be emptied and the new files to be filled.

This was not a one-stop go action because I had to face several issues:

  1. When the data from the original file is moved to other files, the original data file maintains the original size.
  2. Because of issue 1 I had to create more space. I didn’t have the space on the original server and had to place the data elsewhere temporarily.
  3. Placing the files on another server or share isn’t possible because SQL Server doesn’t allow you to place database files on file shares
  4. Attaching an extra disk wasn’t possible in a timely fashion

Solution

To solve the issue that SQL Server doesn’t allow database files to be placed on file shares I enabled trace 1807. This trace bypasses several check of SQL Server from where it’s possible to place the files on file shares. See the code below:

DBCC TRACEON(1807)

The information about the trace flag was found at Varun Dhawan’s blog post which can be found here.

I created a file share on another server which pointed and gave the SQL Server service account modify rights on that share on NTFS and full control on the share.

I than created 10 data files to make sure the data files get a size around 35 GB each which is easy to handle.
TIP: Make sure the growth of the new data files is set to at least 1 GB because the continuous growth will have a negative impact on the performance of the data move.

To empty the original data file I used the following script:

USE [dbname]
DBCC SHRINKFILE('datafilename', EMPTYFILE)

This code will run for hours! Make sure there is enough space for the new data files to grow otherwise the process will exit. If the process in some way quits just run the shrinkfile command again and SQL Server will continue the move of data.

I used the script below to see how the files got filled:

SELECT a.fileid, 
       CONVERT(DECIMAL(12, 2), Round(a.size / 128.000, 2))           AS 
       [FILESIZEINMB], 
       CONVERT(DECIMAL(12, 2), 
       Round(Fileproperty(a.NAME, 'SpaceUsed') / 128.000, 2)) 
                                             AS [SPACEUSEDINMB], 
       CONVERT(DECIMAL(12, 2), Round( 
       ( a.size - Fileproperty(a.NAME, 'SpaceUsed') ) / 128.000, 2)) AS 
       [FREESPACEINMB], 
       a.NAME                                                        AS 
       [DATABASENAME], 
       a.filename                                                    AS 
       [FILENAME] 
FROM   dbo.sysfiles a

This return the following result:

split_large_file_databasefiles

 

As you can see the new data files are filled proportionally with the data from the original data file. The transaction log-file is begin used because the data goes through the transaction log-file and than gets written to the new data files. In the same time the free space in the original data file increases.

When the process finished I was still having a large data file with a lot space available.  Because this was the primary file which contains all the system objects for the database I first shrunk the primary data file with the following command:

USE [dbname]
GO
DBCC SHRINKFILE (N'datafile' , 0, TRUNCATEONLY)
GO

Because the file didn’t bother me I adjusted the initial size of the data file to be very small like 100MB and set it to have a maximum size of 100MB:

USE [dbname]
GO
DBCC SHRINKFILE (N'datafile' , 100)
GO
USE [master]
GO
ALTER DATABASE [dbname] MODIFY FILE ( NAME = N'datafile', MAXSIZE = 100MB , FILEGROWTH = 1MB )
GO

This will make sure the primary data file will never grow again and all the data that gets written will be written to the new data files.

Although the database works I wouldn’t want the files to be like they were spread across multiple locations. I detached the database to be able to move the data files around

USE [master]
GO
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'dbname'
GO

… then moved the files from the fileshare to the databaseserver data directory and attched the database again where I needed to lookup the new datafiles because originally they were placed in an alternate location.

At last I had to clean up the changes I made by removing the file share and undo the trace. Undoing the trace is done with the following command

DBCC TRACEOFF(1807)

Conclusion

The process took a very long time. In this situation it took me more than 17 hours to get the database configured and to get the data moved.

Besides the fact that this took very long I’m glad with the result. The next step could be identity possible performance killer tables that can be placed in separate files on other file groups but that is a whole different article all together.

I hope you can use this information for your own actions and comments are always welcome!

Count all objects in a database

Standard

Count von Count
Problem
I want to count all the objects in a particular database and with a good description of the type groups by the type.

Solution
I love Google and people are amazing with all the solutions they already offer. I found a script by Buck Woody which created a nice solution.
I adjusted the script by adding more types to the script and convert the date to an easier timestamp.

SELECT 'Count' = Count(*), 
       'Type' = CASE type 
                  WHEN 'AF' THEN 'Aggregate function (CLR)' 
                  WHEN 'C' THEN 'CHECK constraint' 
                  WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)' 
                  WHEN 'F' THEN 'FOREIGN KEY constraint' 
                  WHEN 'FN' THEN 'SQL scalar function' 
                  WHEN 'FS' THEN 'Assembly (CLR) scalar-function' 
                  WHEN 'FT' THEN 'Assembly (CLR) table-valued function' 
                  WHEN 'IF' THEN 'SQL inline table-valued function' 
                  WHEN 'IT' THEN 'Internal table' 
                  WHEN 'P' THEN 'SQL Stored Procedure' 
                  WHEN 'PC' THEN 'Assembly (CLR) stored-procedure' 
                  WHEN 'PG' THEN 'Plan guide' 
                  WHEN 'PK' THEN 'PRIMARY KEY constraint' 
                  WHEN 'R' THEN 'Rule (old-style, stand-alone)' 
                  WHEN 'RF' THEN 'Replication-filter-procedure' 
                  WHEN 'S' THEN 'System base table' 
                  WHEN 'SN' THEN 'Synonym' 
                  WHEN 'SO' THEN 'Sequence object' 
                  WHEN 'SQ' THEN 'Service queue' 
                  WHEN 'TA' THEN 'Assembly (CLR) DML trigger' 
                  WHEN 'TF' THEN 'SQL table-valued-function' 
                  WHEN 'TR' THEN 'SQL DML trigger' 
                  WHEN 'TT' THEN 'Table type' 
                  WHEN 'U' THEN 'Table (user-defined)' 
                  WHEN 'UQ' THEN 'UNIQUE constraint' 
                  WHEN 'V' THEN 'View' 
                  WHEN 'X' THEN 'Extended stored procedure' 
                END, 
       CONVERT(VARCHAR(19), Getdate(), 120) AS 'Timestamp' 
FROM   sysobjects 
GROUP  BY type 
ORDER  BY type 

GO

Hope you enjoy the script.

Get Active Session per database

Standard

Sometimes we have the issue that a database is locked due the fact that someone is using the database for some apparent reason.

The same happened to me when I wanted to restore a database but failed because someone was using it.I wanted a script that showed me the sessions for a specific database and the login name.

The code below helps you with that:

USE master
GO
SELECT
db_name(dbid) as DatabaseName
, loginame as Username
FROM sys.sysprocesses
WHERE dbid > 0
AND db_name(dbid) = '[DATABASENAME]'
GROUP BY dbid, loginame
GO

To see all the connections for a;; databases you can use the following script:

USE master
GO
SELECT
db_name(dbid) as DatabaseName
,count(dbid) as Amount
, loginame as Username
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
GO

DDL Trigger on Database Drop

Standard

I recently got a problem that somebody from the organisation was deleting databases. Because a few users had the local administrator right rights they had the ability to drop tables. Because SQL Server does not log who dropped the database I had no choice than to log this with a ddl trigger.

The table is going to be created in a database called “DBA”. You can save the data in any table/database you want but be sure that this is also changed in the code of the trigger.

I wanted to log these actions in a table. This is SQL code for the table:

CREATE TABLE [dbo].[ddl_EventLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EventTime] [datetime] NULL,
[EventType] [varchar](15) NULL,
[ServerName] [varchar](25) NULL,
[DatabaseName] [varchar](25) NULL,
[ObjectType] [varchar](25) NULL,
[ObjectName] [varchar](25) NULL,
[UserName] [varchar](15) NULL,
[CommandText] [varchar](max) NULL
) ON [PRIMARY]

The DDL trigger code:

CREATE TRIGGER [trg_dropdatabase_log]
ON ALL SERVER -- Create Server DDL Trigger
FOR DROP_DATABASE -- Trigger will raise when dropping a database
AS
SET NOCOUNT ON

DECLARE @xmlEventData XML

-- Capture the event data that is created
SET @xmlEventData = eventdata()

-- Insert information to a EventLog table.
-- Make sure the database and table exists and that the script points to the right database and table.
INSERT INTO [DBA].dbo.ddl_EventLog
(
EventTime,
EventType,
ServerName,
DatabaseName,
ObjectType,
ObjectName,
UserName,
CommandText
)
SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))

GO

If you want to edit or remove the trigger you can find it in the “Server Objects”, “Triggers” in SQL Server Management Studio.

Hope you enjoy the script. Any comments are appreciated.

Collect database file information

Standard

I’ve been documenting a lot the last few years and one of the things I want to know is where the files of the databases reside.

The script below displays the following information about the database files:

  • Database name
  • File name
  • Physical file name
  • File type
  • File size
  • File growth
SELECT
db.name AS 'Database name'
,mf.name AS 'File name'
,mf.physical_name AS 'Physical file name'
,mf.type_desc AS 'File type'
,mf.size AS 'File size'
,mf.growth AS 'File growth'
FROM
master.sys.databases AS db
INNER JOIN master.sys.master_files AS mf
ON mf.database_id = db.database_id
ORDER BY
db.name

Rebuilding Indexes In One Database

Standard

I came across a question that we needed to rebuild the indexes only on a particular database.

The script  I created selects all the tables from a certain database and puts them into a cursor. Than the script runs the index command with the table in line.

DECLARE @databasename VARCHAR(20)
DECLARE @fulltablename VARCHAR(50)

SET @databasename = '[yourdatabase]'

DECLARE tables CURSOR FOR
SELECT name AS tblname
FROM @database.dbo.sysobjects
WHERE (xtype = 'U')

OPEN tables

FETCH NEXT FROM tables INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fulltablename = @databasename + '.' + @tablename
ALTER INDEX ALL ON @fulltablename
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
END

CLOSE db_cursor
DEALLOCATE db_cursor

Prepare SQL Server 2005 in a clustered environment for SP3

Standard

Introduction

Recently I was asked to create a new high availability solution for MS SQL Server 2005. When I finished the installation of SQL Server 2005, I found myself troubled as to why the installation of SP3 failed with the update of the database services. After a lot of searching I found the solution to my problem.

Because I spend a lot of time figuring out why my installation failed I decided to write this article so other people won’t have to spend so much time and frustration installing the service pack. This article also covers the problem for Service Pack 2. I haven’t tested Service Pack 1 with this method.

What exactly goes wrong?

When I looked closely at the installation I saw that at a certain moment the setup program starts some actions for services and databases. After watching the databases being created and deleted, I noticed that something went wrong at the “mssqlresource” database. The setup program tries to overwrite the “mssqlresource” database but for some reason fails, which results in a rollback of that part of the installation. It looks like the “mssqlresource” database is still in use by SQL Server 2005 what creates teh error.

Below are the steps that you can take to install SP3 on your SQL 2005 cluster nodes.

1: Create Backups

As for any DBA you should always backup your databases before you start the installation of updates or complete service packs. In my case I didn’t have many databases on my server because it was a fresh installation. I created backups of all the present databases and I copied the “master” and the “mssqlresource” backup files to a different location.

2: Take the SQL Services offline

Before you stop the services for SQL Server you should take that part of the cluster down. If you don’t do this, the cluster will try to start the service as soon as it goes down. Go to the “Cluster Administrator” and select the resourcegroup where the services for that node reside. Right click the “SQL Server (instancename)” and click on “Take offline”. Do this for every SQL Service.

3: Copy and rename the mssqlresource database

Now that the services are down, we can copy and rename the “mssqlresource” database.

The “mssqlresource” database isn’t visible in SSMS (SQL Server Management Studio) so I had to copy the files from this database.

Go to the shared array where the database is installed and copy and paste them in that directory. In my case I rename it to “mssqlresource_old.mdf” and “mssqlresource_old.ldf” but you can use any name you want.

4: Start SQL Server in single-user mode

Before we can make any changes to the system databases we put the SQL Server in the single-user mode so nobody except the admins can connect to the server. Open a command prompt and type the following command:

NET START MSSQLSERVER /f /T3608

For servers with multiple instances you can use the following command:

NET START MSSQL$instancename /f /T3608

5: Alter the mssqlresource database

Through the SQLCMD program we can send queries to the SQL Server. In the command prompt type the following command:

SQLCMD S servername\instance -U adminuser -P password

If you’re authenticated you’ll see a prompt like “1>”. Now we can send the ALTER queries. Type the following commands for the MDF and the LDF files and press the Enter-key after each command. For the MDF File:

ALTER DATABASE mssqlsystemresource
MODIFY FILE (
NAME = data,
FILENAME = 'mssqlsystemresource_old.mdf'
)
GO

For the LDF File:

ALTER DATABASE mssqlsystemresource
MODIFY FILE (
NAME = log,
FILENAME = 'mssqlsystemresource_old.ldf'
)
GO

After the completion of both commands you’ll get a message that the changes will take effect after the restart of the SQL Server.

6: Restart the SQL Server

Stop the service with the following command:

NET STOP MSSQLSERVER

For servers with multiple instances you can use the following command:

NET STOP MSSQL$instancename

Start the service with the following command:

NET START MSSQLSERVER

For servers with multiple instances you can use the following command:

NET START MSSQL$instancename

If you complete all these steps, you’ve done the necessary preparations for installing SP2 and SP3 in a clustered environment. The database files that are renamed will be replaced by new files during the installation with the original filenames.

This article is based on the Technet article on this location: http://technet.microsoft.com/en-us/library/ms345408.aspx. I wrote this so that you don’t have to search for that solution in the first place when you install SP3.