Database With Fun (While 1 = 1)

Issue with BLOB rows- Method 1 Set the SubscriptionStreams parameter of the Distribution Agent to a value of 1. Method 2 You can set the OleDbStreamThreshold parameter of the Distribution Agent to a value that is greater than the largest data size for binary large object (BLOB) columns that have to be replicated. Then, the Distribution Agent will not bind binary large object (BLOB) data as a stream. Starting from SQL Server 2008, binary large object (BLOB) data is bound as a stream when the data size of the binary large object (BLOB) data is less than the value of the OleDbStreamThreshold parameter. This behavior is true by default. USE TestDB; GO EXEC sp_configure 'show advanced options', 1 ; RECONFIGURE ; GO EXEC sp_configure 'max text repl size'; GO -- OUTPUT: -- name                    minimum  maximum  ...

Setting up transactional replication using T-SQL Step 1: Set up a shared folder for snapshots. Step 2: Configure the distributor and publisher: use master exec sp_adddistributor @distributor = N'SSLMATTB2' , @password = N'' GO exec sp_adddistributiondb @database = N'distribution' , @data_folder = N'C:\MSSQL\SQLData' , @log_folder = N'C:\MSSQL\SQLLogs' , @log_file_size = 2 , @min_distretention = 0 , @max_distretention = 72 , @history_retention = 48 , @security_mode = 1 GO use [distribution] if (not exists ( select * from sysobjects where name = 'UIProperties' and type = 'U ')) create table UIProperties(id int) if (exists ( select * from ::fn_listextendedproperty('SnapshotFolder' , 'user' , 'dbo' , 'table' , 'UIProperties' , null, null))) EXEC sp_updateextendedproperty N'SnapshotFolder' , N'C:\MSSQL\SQL_Share' , 'user...

If you get the error message for Tempdb Transaction log is full. Msg 9002, Level 17, State 4, Procedure sp_helpdb, Line 19 The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases Solution: 1 -- First check Tempdb Tran log file size using  dbcc sqlperf(logspace) -- tempdb 999.9922 99.99628 0 USE MASTER GO ALTER DATABASE TEMPDB MODIFY FILE (NAME='templog', SIZE=1500MB) Solution:2   ALTER DATABASE Tempdb  ADD LOG FILE  ( NAME = tempdblog2, FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf', SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 10MB)

This script is for SQL Server 2008 Enterprise Edition. Run the script in SSMS with "results to text". It will generate the compression statements for all tables and indexes for the database it which is run in. It creates the compression scripts in ascending order based on table/index size so smaller tables/indexes are compressed first creating additional space for larger tables/indexes to be compressed. --Creates the ALTER TABLE Statements SET NOCOUNT ON SELECT 'ALTER TABLE ' + '[' + s.[name] + ']'+'.' + '[' + o.[name] + ']' + ' REBUILD WITH (DATA_COMPRESSION=PAGE);' FROM sys.objects AS o WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id] INNER JOIN sys.schemas AS s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id] INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK) ON i.[object_id] = ps.[object_id] AND ps.[index_id] = i.[index_id] WHERE o.[type] = 'U' ORDER BY ps.[rese...

The last script will basically loop through the entire server/database your specify and create the indexes for you, sending you an email for each failed index that fails to get created. The first script creates a table to store the indexes. The second script creates the SQL Agent Job.  You can eitherpaste the main segment of code directly into the job step where it says "INSERT CODE FROM ABOVE INTO THIS JOB STEP", or create a stored-procedure, and use that instead. Deploy the job/procedure to any server you wish to keep back up your index definitions, setting an appropriate schedule for it to run. Use the final portion of code to loop through the table created in step 1, to automagically create the indexes on the target server. /* Create table to hold indexes */ CREATE TABLE [dbo].[MasterIndexes]( [ServerName] [varchar](75) NOT NULL, [DBName] [varchar](75) NOT NULL, [IndexTable] [varchar](75) NOT NULL, [Type] [varchar](3) NOT NULL, [IndexName] [varchar](500)...

Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced. CREATE PROC dbo.ProcName AS SET NOCOUNT ON ; --Procedure code here SELECT column1 FROM dbo.TblTable1 -- Reset SET NOCOUNT to OFF SET NOCOUNT OFF ; GO Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan...

If we will follow following tips or rules while write sql queries we can improve the performance of sql queries: 1. Help Query optimizer to estimate correct statistics In sql server execution plan is decided by: 1. Cardinality: The total number of rows processed at each level of query. 2. Query Plan: Algorithm to use to perform task like searching, sorting etc. When we execute a sql queries, create index, insert records in a table etc sql server automatically creates or updates the statistics of key fields of tables. In simple words we can say statistics is object which keeps information about total numbers of distinct records in a table. There a few cases where query optimizer is unable to get correct information about statistics which decrease the performance of a sql query. For examples: First we are creating a tblMessage table and inserting 97526 records into it and creating three indexes on it: CREATE TABLE tblMessage (     ntMessageID BIGINT IDENTITY PRIMAR...