Category Archives: Microsoft SQL

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network.

sql server

Taking all databases offline and/or dropping through SQL query

Use the following script to take all databases offline and towards the bottom of the post you can run that script to drop the offline databases.

use master;
declare @nsql nvarchar(max);
select @nsql=coalesce(@nsql+CHAR(13)+CHAR(10),'')+
'ALTER DATABASE ['+name+'] SET OFFLINE WITH NO_WAIT;'
from master..sysdatabases where sid <> 0x01
exec (@nsql)

Once all databases are offline you can drop them to clear out the entire SQL instance
Note:This is extremely destructive so please use this with care. I only would use this when clearing out an old instance on a server that I want to repurpose without doing a new SQL install.
use master;
declare @nsql nvarchar(max);
select @nsql=coalesce(@nsql+CHAR(13)+CHAR(10),'')+
'DROP DATABASE ['+name+'];'
from master..sysdatabases where sid <> 0x01
exec (@nsql)

Use the following script to take all databases online

use master;
declare @nsql nvarchar(max);
select @nsql=coalesce(@nsql+CHAR(13)+CHAR(10),'')+
'ALTER DATABASE ['+name+'] SET ONLINE WITH NO_WAIT;'
from master..sysdatabases where sid <> 0x01
exec (@nsql)

GFI Archiver resource usage

I recently had a server with GFI Archiver (Mail Archiving) that had the SQL Server process balloon over 8GB causing multiple performance issues. The following procedure will reduce the usage by about half and fix many performance issues.

1. Stop all MailArchiver and SQL services
2. Create a backup of ..MailArchiverStoreDataproduct.config
3. Edit product.config and add the following key within the <AppSettings> section: <add key=”MaxPoolSize” value=”20″ />
4. Re-start the services

*Note GFI has built-in scripts made to stop all NON-SQL services. Run the Stop script then stop SQL Server Instance, When starting start SQL Server Service before running start script. The scripts default location are: ..MailArchiverToolsScripts

After performing this procedure the SQL Server Process is now under 4GB.

Configuring SQL database to accept SQL Authentication

This article is taken from http://kb.gfi.com/articles/SkyNet_Article/How-to-configure-SQL-Server-2005-2008-to-accept-SQL-Authentication and all credit goes to them for the writing. I’m posting it here simply as a reference in case there site changes the above link.

There are 2 methods of authentication by which GFI ReportCenter can authenticate to SQL Server 2005/2008. These are:

  • Windows authentication
  • SQL Server authentication (or mixed authentication).

In order to use SQL Server authentication you must first configure your server using the steps below.

  1. Right-click on the server node and select ‘Properties’
  2. Select ‘Security’ from the left menu under ‘Select a page’Server Properties - Security
  3. Under ‘Server Authentication’, select the ‘SQL Server and Windows Authentication mode option’
  4. Click ‘OK’ to close the dialog
  5. Right click on the server node and choose ‘Restart’ for the changes to take affect

Enable SQL Server login

  1. In the server node expand ‘Security’ and ‘Logins’
  2. Right click on the login name and select ‘Properties’
  3. Enter a password and confirm the password for the login
  4. Select ‘Status’ from the left menu under ‘Select a page’Login Properties - Status
  5. Set the ‘Login’ option to ‘Enabled’
  6. Click ‘OK’ to close the dialog

Archiving and Reducing Exchange Mailboxes and Stores (Part 2)

You have analyzed your current exchange store and deciding to buy a 3rd party solution so your users can maintain the large amount of emails they are accustomed to and retain all emails for legal/business purposes, and here are the options.

  1. Buy the Barracuda Message Archiver ( website link: http://www.barracudanetworks.com/ns/products/archiver-overview.php) which is a really simple installation and maintenance. I have used this before, and it gets the job done. I did have issues with restoring emails after being exported, but after some conversion and importing to pst files it worked. This isn’t my first pick, but is widely popular for message archiving.
  2. Buy GFI Mail Archiver (http://www.gfi.com/mailarchiver/) which is probably the most popular software mail archiving programs out there. Installation is a bit complex, but not extremely difficult. When purchasing buy at least 1 year of maintenance because you will be on the phone with them for the first month or two after implementation. This has various platforms to run on (SQL only, SQL + File System, and 2 others using SQL Express that you SHOULD NOT use). I wish they would simplify the installation with only one option, because it’s the best in almost any situation. SQL + File System gives the best reliability and scalability.

Using any of the above solutions will work wonders for retaining and keeping a lean exchange store. You will use Exchange’s built-in retention policies to keep all mailboxes at reasonable levels and never again will you have users screaming of slow outlook that is bloated beyond belief. I will mention that doing any of these solutions is a considerable time commitment when you factor importing old emails to these systems. In the end, it will give you peace of mind that you will always have copies of ANY email that your company receives, no matter what the user does with it. All these systems use journaling to essentially copy the message to these systems before the user touches it.

Grow Database File in SQL

I use both SQL Express 2005 on a server and SQL 2008 on another server. The following script is nice to manually grow a file. By default, SQL auto-grows database files by 10% when it runs low on space. In most cases this is fine, but I’m a perfectionist and hate receiving errors about low DB Space in my SCE (System Center Essentials) console.

use OperationsManager
declare @dbname nvarchar(300)
declare @dbsize dec(15)
declare @UsedSpace dec(15)
declare @FreeProc int
set @dbname=REPLACE(db_name(),””,”””)
EXEC(‘DBCC UPDATEUSAGE (”’[email protected]+”’)’)
set @dbname=QUOTENAME(@dbname)
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @UsedSpace=sum(convert(dec(15),reserved))
from sysindexes where indid in (0, 1, 255)
select @dbsize,@UsedSpace
select @FreeProc=((@[email protected])*100)/@dbsize
–If free under 20%, grow database by 20%
–The first datafile is used
if @FreeProc<20 begin declare @filename nvarchar(300) declare @bytesperpage dec(15) declare @pagesperMB dec(15) declare @newsize dec(15) select @filename=(select top 1 [name] from dbo.sysfiles where (status & 64 = 0) order by groupid,fileid) set @filename=REPLACE(rtrim(@filename),'''','''''') select @bytesperpage = [low] from master.dbo.spt_values where number = 1 and type = 'E' set @pagesperMB = 1048576 / @bytesperpage set @newsize=(@dbsize*12/10)/@pagesperMB exec('ALTER DATABASE '[email protected]+' MODIFY FILE (NAME = N'''[email protected]+''', SIZE = '[email protected]+')') end

System Center Essentials 2007 OperationsManager SQL DB too large (4GB +)

I run System Center Essentials 2007 and by default it installs into SQL Server Express on EBS Installations. This is normally not an issue, but if the database grows larger than 4GB than you will no longer get the benefit of SCE 2007 as SQL Server Express has a 4GB limitation on its databases. I noticed that my database was growing considerably and before I knew it my OperationsManager DB was larger than 4GB.

First, I groomed the database and reduced all retention periods via the EBS teams SCE 2007 blog post. For instructions on how to groom SCE 2007, here is their blog post. This outlined how to reduce the retention period of various SCE 2007 buckets in both SQL Server Management Studio and SCE 2007 Administrative Console.

Second, I used shrink on the OperationsManager and OperationsManagerDW Databases in SQL Management Studio. This is done via SQL Query or by Right Clicking Database -> Tasks -> Shrink -> Database. On next screen leave options default and hit OK.

These both helped me considerably, but I noticed the OperationsManager was still quite large. I found a solution on Experts Exchange that finally reduced the Database well below the 4GB limit. There is aparently a bug in SCE 2007 SP1 that bloats the dbo.LocalizedText table to several gigs if left unchecked for a year. Here is a complete Solution step-by-step.

Step 1: Create a Backup of the Database
-Right-Click OperationsManager -> Tasks -> Back Up…

Step 2: Verify you have a large dbo.LocalizedText table
-Run the Following SQL Query on OperationsManager. This is also great for checking other SQL Databases to see which table is eating up all the space.

USE OperationsManager
DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL,
row varchar(10),
reserved varchar(10),
data varchar(10),
index_size varchar(10),
unused varchar(10)
)
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT BEGIN SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I INSERT INTO @TblNames EXEC sp_spaceused @str SET @I = @I +1 END -- Display results in Sorted order SELECT tbl_name as TableNm, CAST(REPLACE(data, ' kb', '') as int) as TblSize, CAST(REPLACE(index_size, ' kb', '') as int) as IdxSize FROM @tblNames ORDER BY TblSize DESC

Step 3: Clean Up Localized Text
-Run the following SQL Query on OperationsManager. (This will take about 10 minutes per gig.)


– Create temp table to speed up looking for a PublisherId when we know the MessageId
BEGIN TRY
CREATE TABLE #PublisherMessageReverseIndex(MessageStringId UNIQUEIDENTIFIER,
MessageId INT)
CREATE CLUSTERED INDEX #PublisherMessageReverseIndex_CI ON #PublisherMessageReverseIndex(MessageStringId)
INSERT INTO #PublisherMessageReverseIndex (MessageStringId, MessageId)
SELECT MessageStringId, MessageId
FROM dbo.PublisherMessages
— Create temp table of message lengths, message id, and Message Hash with the
— Message String Id so that we can efficiently figure out whether a given message
— is duplicated. The duplicate messages generated by the converted MP have
— different PublisherId’s, but everything else is identical. INDEX this TABLE so that
— we can look up quickly by the MessageStringId and also by values we expect to see
— duplicated.
CREATE TABLE #LTHashStrings (MessageStringId UNIQUEIDENTIFIER,
LTValueLen INT,
LTValueHash VARBINARY(32),
MessageId INT NULL)
CREATE CLUSTERED INDEX #LTHashStrings_CI ON #LTHashStrings(MessageStringId)
CREATE NONCLUSTERED INDEX #LTHashStrings_NCI1 ON #LTHashStrings(LTValueLen, MessageId, LTValueHash)
— Create temp table for the Orphaned PublisherStrings that we find. These
— are rows in PublisherMessages whose corresponding Events have already been groomed away.
— They still have corresponding rows in LocalizedText. We won’t add rows for PublisherMessages
— which are not for a duplicated message.
CREATE TABLE #OrphanedPublisherStrings (PublisherId UNIQUEIDENTIFIER,
MessageStringId UNIQUEIDENTIFIER)
CREATE CLUSTERED INDEX #OrphanedPublisherStrings_CI ON #OrphanedPublisherStrings(MessageStringId)
— Create temp table to use in looking up whether a PublisherMessages row still
— has a corresponding Event. Event_01 etc. have no index on PublisherId, so we
— don’t want to do a query that keeps seeking into EventAllView.
— If a PublisherId occurs multiple times in the Event tables we will only need it
— once in our temp table, hence the unique clustered index with IGNORE_DUP_KEY.
— This keeps the temp table relatively small and will be a time saver for
— seeing which PublisherMessages are orphaned.
CREATE TABLE #EventAllPublishers (PublisherId UNIQUEIDENTIFIER)
CREATE UNIQUE CLUSTERED INDEX #EventAllPublishers_CI ON #EventAllPublishers (PublisherId)
WITH (IGNORE_DUP_KEY = ON)
— Populate temp table by scanning EventAllView one time
INSERT INTO #EventAllPublishers(PublisherId)
SELECT PublisherId
FROM EventAllView
— Populate first Temp table for figuring out which messages are duplicated
INSERT INTO #LTHashStrings (MessageStringId, LTValueLen, LTValueHash, MessageId)
SELECT LTStringId, len(LTValue), HashBytes(‘SHA1’, LTValue), MessageId
FROM dbo.LocalizedText LT
JOIN #PublisherMessageReverseIndex PM ON PM.MessageStringId = LTStringId
— Create second table for figuring out which messages are duplicated.
CREATE TABLE #LTCountByMessage( LTValueLen INT,
MessageId INT,
LTValueHash VARBINARY(32),
MsgCount INT)
CREATE CLUSTERED INDEX #LTCountByMessage_CI ON #LTCountByMessage(LTValueLen, MessageId, LTValueHash)
— Populate second message for duplicate message detection by scanning INDEX of
— the first one and doing a grouped count.
INSERT INTO #LTCountByMessage (LTValueLen, MessageId, LTValueHash, MsgCount)
SELECT LTValueLen, MessageId, LTValueHash, COUNT(1)
FROM #LTHashStrings
GROUP BY LTValueLen, MessageId, LTValueHash
— Now that we are set up to detect both Orphans and duplicated messages by
— joining to our relatively small (and properly indexed) temp tables,
— figure out the OrphanedPublisherStrings that have duplicate messages
INSERT INTO #OrphanedPublisherStrings (PublisherId, MessageStringId)
SELECT PM.PublisherId, PM.MessageStringId
FROM dbo.PublisherMessages PM
JOIN #LTHashStrings LTS ON (LTS.MessageStringId = PM.MessageStringId AND LTS.MessageId = PM.MessageId)
JOIN #LTCountByMessage LTC ON (LTC.LTValueLen = LTS.LTValueLen AND
LTC.MessageId = LTS.MessageId AND LTC.LTValueHash = LTS.LTValueHash)
WHERE PM.PublisherId NOT IN (SELECT PublisherId FROM #EventAllPublishers) AND
LTC.MsgCount > 1
— Deleting all of the OrphanedPublisherStrings and corresponding LocalizedText rows
— at once may be too large for the transaction log to handle. Create a
— numbered / ordered table so that we can delete them in relatively small batches
— and not overtax the transaction log.
CREATE TABLE #NumberOrphanPublisherStrings(OrphanNum INT IDENTITY,
PublisherId UNIQUEIDENTIFIER,
MessageStringId UNIQUEIDENTIFIER)
CREATE CLUSTERED INDEX #NumberOrphanPublisherStrings_CI on #NumberOrphanPublisherStrings(OrphanNum)
— Populate Numbered TABLE
INSERT INTO #NumberOrphanPublisherStrings (PublisherId, MessageStringId)
SELECT PublisherId, MessageStringId FROM #OrphanedPublisherStrings
END TRY
BEGIN CATCH
GOTO Error
END CATCH
— Set up variables so that we can delete our orphaned rows
— If transaction log fills up, try reducing the @OrphanIncrement value,
— which controls the number of rows that we delete at a time
DECLARE @OrphanNum INT
DECLARE @OrphanIncrement INT
DECLARE @OrphanLimit INT
SET @OrphanNum = 0
SET @OrphanIncrement = 10000
SELECT @OrphanLimit = MAX(OrphanNum) FROM #NumberOrphanPublisherStrings
BEGIN TRY
WHILE @OrphanNum < @OrphanLimit BEGIN DELETE dbo.LocalizedText FROM #NumberOrphanPublisherStrings OPS JOIN dbo.LocalizedText LT ON LT.LTStringId = OPS.MessageStringId WHERE OPS.OrphanNum >= @OrphanNum AND OPS.OrphanNum < @OrphanNum + @OrphanIncrement DELETE dbo.PublisherMessages FROM #NumberOrphanPublisherStrings OPS JOIN dbo.PublisherMessages PM ON PM.PublisherId = OPS.PublisherId WHERE OPS.OrphanNum >= @OrphanNum AND OPS.OrphanNum < @OrphanNum + @OrphanIncrement SET @OrphanNum = @OrphanNum + @OrphanIncrement END END TRY BEGIN CATCH GOTO Error END CATCH Error: IF @@ERROR <> 0
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
— Try to drop all of the Temp tables
BEGIN TRY
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘#PublisherMessage%’)
DROP TABLE #PublisherMessageReverseIndex
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘#OrphanedPublisherStrings%’)
DROP TABLE #OrphanedPublisherStrings
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘#LTHashStrings%’)
DROP TABLE #LTHashStrings
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘#EventAllPublishers%’)
DROP TABLE #EventAllPublishers
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘#LTCountByMessage%’)
DROP TABLE #LTCountByMessage
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘#NumberOrphanPublisherStrings%’)
DROP TABLE #NumberOrphanPublisherStrings
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Step 4: Reindex Database and finish
-Run the following SQL Query on the OperationsManager.

USE OperationsManager
go
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
EXEC SP_MSForEachTable “Print ‘Reindexing ‘+’?’ DBCC DBREINDEX (‘?’)”