Database With Fun (While 1 = 1)

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...

Use JOINs rather than subqueries If possible (and if it makes sense), I suggest using JOIN statements rather than subqueries to improve performance. When a subquery is used as criteria in a SELECT statement, the values returned from the subquery are distinct. Returning a distinct list of values requires additional processing, which can slow down your queries. Use explicit transactions When data manipulation occurs in the database, the actions are written to the transaction log. If your statements are executing many DML statements, it might be a good idea to place them inside of a transaction for performance purposes. Placing the statements inside of a transaction will prevent all of the statements from being written to the transaction log serially. Use UNION ALL instead of UNION When you use the UNION clause to concatenate the results from two or more SELECT statements, duplicate records are removed. This duplicate removal requires additional computing to accomplish. If you are no...

There are various types of backups in SQL Server: Full backup : A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored. Transaction Log Backup: Transaction log backups are only possible in the FULL or BULK_LOGGED recovery models. A transaction log backup contains all the transaction log records generated since the last log backup and is used to allow the database to be recovered to a specific point in time. Differential Backup: A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup. Copy Only Backup: A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Myth 1: A full backup only contains data...