MS SQL

Custom Index Defrag Script for SQL Server 2008

uspDbaIndexDefrag xx_dba_parseString_udf xx_dba_indexDefragStatus xx_dba_indexDefragExclusion xx_dba_indexDefragLog Result Executing: ALTER INDEX [PK_tbTable01] ON [MyDB01].[dbo].[tbTable01] REORGANIZE Executing: ALTER INDEX [XPK_tbTable02] ON [MyDB01].[dbo].[tbTable02] REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON) Executing: ALTER INDEX [PK_tbTable03] ON [MyDB01].[dbo].[tbTable03] REORGANIZE Executing: ALTER INDEX [XPKtbTable04] ON [MyDB01].[dbo].[tbTable04] REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON) Executing: ALTER INDEX [PK_tbTable05] ON [MyDB01].[dbo].[tbTable05] REORGANIZE

SQL Server Error: BobMgr::GetBuf: Sort Big Output Buffer write not complete after 60 seconds.

Error: BobMgr::GetBuf: Sort Big Output Buffer write not complete after 60 seconds. SQL Server has encountered  x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file . The OS file handle is 0x000006A4. The offset of the latest long I/O is: 0x00000 (or) BobMgr::GetBuf: Sort Big Output Buffer write not complete... » read more

SQL Server Add Windows User Account

Create Linked Server

Creating a linked server called “RPT” that connect into SERVER01… Note: When generating the linked server from a script from an existing database server, the rmtpassword parameter will not be set. After the linked server has been created, go to properties of the linked server and set the security context.

The filegroup xxxx has no files assigned to it.

Error: The filegroup "xxxx" has no files assigned to it. Tables, indexes, text columns, ntext columns, and image columns cannot be populated on this filegroup until a file is added. Fix: Sources: https://forums.asp.net/t/1397838.aspx?The+filegroup+comn_data+has+no+files+assigned+to+it+sql

Database Backup Compression

This topic describes the compression of SQL Server backups, including restrictions, performance trade-off of compressing backups, the configuration of backup compression, and the compression ratio. Backup compression is supported on SQL Server editions: Enterprise, Standard, and Developer. Every edition of SQL Server 2008 and later can restore a compressed backup. Benefits Because a compressed backup... » read more

No Global Profile Is Configured

Error: No global profile is configured. Specify a profile name in the @profile_name parameter Fix: 1.  Open database mail configuration by logging into SQL Server Management studio and expanding Management and right clicking on DatabaseMail, then choosing configure: 2.  Choose Manage Profile Security. 3.  Configure your profile to be the default profile: Sources:

Unable to Open Up Integration Services from SSMS on Windows Vista and later

Error: Connecting to the Integration Services service on the computer “xxxx” failed with following error: “Access is denied” Resolution: Open up SSMS via “Run as Administrator”

List all permissions for all table types.

Grant EXEC permission on Data Type

SSIS – File System Vs MSDB

I’ve tended to use filesystem which has the advantages of:- 1) Not bloating MSDB if you have thousands of packages. 2) Ease of deployment of packages – DTSX files can simply be copied into the filesystem using Windows Explorer. 3) Multi-user security – using NTFS permissions within the filesystem it’s possible to grant rights to... » read more