Reduce MSDB File

To reduce the file size of msdb, one of checks is to check the sysjobhistory in msdb database. This will grow with large number of records. This can be purged using the below syntax

sp_purge_jobhistory { [ @job_name = ] 'job_name' [ @job_id = ] job_id } [ , [ @oldest_date = ] oldest_date ]

USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@job_id = N'93B30316-FF0E-42C7-80C7-7CBAA98817DB'
GO

or

USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@oldest_date = N '2010-07-01'
GO

SELECT COUNT(*) FROM dbo.sysjobhistory
USE msdb
EXEC sp_delete_backuphistory '01/20/98'

USE msdb
select count(*) from backupset with (nolock) where backup_start_date < '2011-01-01'
USE msdb
SELECT [Size in MB] = SUM(IDX.reserved)/128, [Object Name] = OBJ.name FROM msdb.dbo.sysindexes IDX JOIN msdb.dbo.sysobjects OBJ ON OBJ.id = IDX.id WHERE IDX.indid IN (0, 1, 255) GROUP BY IDX.id, OBJ.name ORDER BY 1 DESC

SQL Large log file size

There are instance where the log files have grown up too large for example 200 gb of file size, in that case, here are the following steps to be done to reduce the log file.
1.Check the database options if it is simple change it to Full.
2. Backup the database using the below script.
--****************************************************
--Backup database script
--****************************************************
BACKUP DATABASE [lmkdw] TO
DISK = N'd:\lmkdw_backup.bak' WITH NOFORMAT,
INIT, NAME = N'lmkdw_backup', SKIP, REWIND, NOUNLOAD,
STATS = 10
3. Then backup the log file using the below script
--****************************************************
--Backup database script
--****************************************************
BACKUP LOG [apacdw] TO DISK = N'd:\lmkdw.trn'
WITH NOFORMAT, INIT, NAME = N'lmkdw-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
go
4. Now shrink the log file using the below script
DBCC SHRINKFILE (N'lmkdw_Log' , 786)
5. Finall turn the database options back to simple or based on your needs.

Max size of Table, Rows and byte

This query is very useful to find out the tables which has max row count, column count and byte utilized


USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

High Availability

Failover clustering and database mirroring both provide the following:
Automatic detection and failover
Manual failover
Transparent client redirect
Failover clustering has the following constraints:
Operates at the server instance scope
Requires signed hardware
Has no reporting on standby
Utilizes a single copy of the database
Does not protect against disk failure

Database mirroring offers the following benefits:
Uses a single, duplicate copy of the database
Note: If you require additional copies, you can use log shipping on the database in addition to database mirroring.
Uses standard servers
Provides limited reporting on the mirror server by using database snapshots.
When it operates synchronously, provides for zero work loss through delayed commit on the principal database.

Database mirroring offers a substantive increase in availability over the level previously possible with SQL Server and offers an easy-to-manage alternative to failover clustering.
Asynchronous database mirroring is Not supported on standard edition. Asynchronous is only supported in Enterprise Version.

Log shipping

Log shipping can be a supplement or an alternative to database mirroring. Although similar in concept, asynchronous database mirroring and log shipping have key differences. Log shipping offers the following distinct capabilities:

Supports multiple secondary databases on multiple server instances for a single primary database.

Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.
Asynchronous database mirroring has the potential advantage over log shipping of a shorter time between when a given change is made in the primary database and when that change is reflected to the mirror database.
An advantage of database mirroring over log shipping is that high-safety mode is a no data loss configuration that is supported as a simple failover strategy.

Note:
For information about how to use log shipping with database mirroring, see Database Mirroring and Log Shipping.



Replication - Replication offers the following benefits:

Allows filtering in the database to provide a subset of data at the secondary databases because it operates at the database scope

Allows more than one redundant copy of the database

Allows real-time availability and scalability across multiple databases, supporting partitioned updates

Allows complete availability of the secondary databases for reporting or other functions, without query recovery.

Transaction Log Size

The transaction log size gets increased to the size of the disk, if the log file is not shrunk. For example if you see a transaction log file of size 274 GB, which is in my case as consumed all the disk space in the server.

The following need to be checked.

The recovery model option right click database properties - options - is this set to FULL or Simple.

If this set to FULL, then the log
In the full and bulk-logged recovery models, a sequence of transaction log backups is being maintained. The part of the logical log before the MinLSN (Log sequnce number) cannot be truncated until those log records have been copied to a log backup.

This means the following script need to be run

--back up the transaction log file
use Manufacturingarchive
BACKUP LOG [Manufacturingarchive] TO DISK = N'E:\Manufacturing.trn' WITH NOFORMAT, NOINIT,
NAME = N'ManufacturingArchive-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

--shrink the log file
go
use Manufacturingarchive
DBCC SHRINKFILE (N'MANUFACTURINGARCHIVE_Log' , 7)

--shrink the data file
use Manufacturingarchive
DBCC SHRINKFILE (N'MANUFACTURINGArchive_Data' , 786)

exec sp_helpfile

Now how do I reclaim the space in the server is the question?

The above query reclaims the logical space, but to relaim the physical space on the change the recovery model option --- right click database --- properties --- options --- to Simple then run
use manufacturingarchive
exec sp_helpfile

dbcc shrinkfile (MANUFACTURINGARCHIVE_Log,100,TRUNCATEONLY)

The above recove the space on the server.

SQL Server space issue

I had situation here with one of our affiliate sql server crashed in USA, we had integrations running between our local (Ireland) and USA server, since the usa server crashed the Ireland server started to slow down with no reason.

Having looked at the log evertime the integrations failed we had log files building up. In the following folders the log files can be deleted.

i. C:\Documents and Settings --- this had log files and pc health cab files
ii. C:\WINDOWS\Temp
iii. C:\WINDOWS\system32\LogFiles
iv. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
v. c:\program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log
vi. C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

Tables to clear when space is an issue

delete from dbo.LMK_POP10100_PURCHASEORDER_HDR where docdate < '2010-01-10'
delete from dbo.LMK_POP10110_PURCHASEORDER_DET where reqdate <= '2010-01-01'
delete from dbo.LMK_AR_Errors_Data_To_GP
delete from dbo.LMK_BSB10100_OUTBOUND_Errors
delete from dbo.LMK_Mat_Issues_Errors_Data_To_GP
delete from dbo.LMK_Payables_Credit_Errors_Data_To_GP
delete from dbo.LMK_Payables_Errors_Data_To_GP
delete from dbo.LMS_GP_MFG_CustomerMaster_error
delete from dbo.BV_AR_Errors_Data_To_GP
delete from dbo.BV_Payables_Credit_Errors_Data_To_GP
delete from dbo.BV_Payables_Errors_Data_To_GP

SQL 2005 PAGE FILE BIG

Today we had a page file size issue, due to this the memory utilization was 16 GB, this stop all the integrations and giving out time out errors, the following actions where taken to resolve this.

1. Restart the sql server.
2. Check the database size.
3. Shrink the database mdb and log file size using the following scripts.
4. Check the max table size.

use manufacturing
exec sp_helpfile
dbcc shrinkfile (Manufacturing_Data)

SQL Database on Suspect Mode

We had a scenario in our US office where the Sql server was abruptly stopped due to power outage.

This has caused the database to go on SUSPECT Mode.

To get the database back on line the following sql scripts can be used.

The below script is used to check the database.

DBCC CHECKDB ('ManufacturingTulsa') WITH NO_INFOMSGS,

ALL_ERRORMSGS

The below script is used bring the database on line.

EXEC sp_resetstatus 'ManufacturingTulsa';

ALTER DATABASE ManufacturingTulsa SET EMERGENCY

DBCC checkdb('ManufacturingTulsa')

ALTER DATABASE ManufacturingTulsa SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('ManufacturingTulsa', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE ManufacturingTulsa SET MULTI_USER

Moving SQL Database Files mdf and ldf

--check the location of the files

Use databasename

GoExec sp_helpfile

--detach the database name
Exec sp_detach_db 'databasename'

--copy the mdf and ldf files to the location

--attach the databasename

Exec sp_attach_db 'databasename',

'D:\sqldata\GPSBSBBVDat.mdf',
'G:\sqldata\GPSBSBBVLog.ldf'

SQL Performance and Optimization

The sql server performance and optimization can be improved in many ways, but it again depends on the number of factors and individual scenariso, here are some the of steps taken by myself.

1. Check the size of the tables, you can use the query from my post which identifies the largest table.

2. Check the max. cpu utilization, you can use the query from my post on this. Then you can work on the sql query.

3. Check the indexes on the table, see if the tables have clustered index and non clustered index, it depends on how the table is queried and what fields are queried.

4.Location of the mdf and ldf files, it is advisable for optimimum performance to locate the temp database file on d:/ drive, mdf on the e:/ and the ldf on the f:/ and leave the operating system on the c:/.

5. Check the views on the database this may used in excess, too many views causes concern on the performance of the database and on the sql server.

SQL Shrink Database

If you see a user database growing abnormal size the following steps could help.

1. Use the query in my post to find out the maimum table size and see if you have some error_history records which can be deleted or moved to archive.

2. The second step is to shrink the database.

3. This can be done by shrinking the mdf file.


use databasename
exec sp_helpfile
dbcc shrinkfile (databasename_Data)

4. The database can also be shrinked using the SSMS, by clicking the database --- task --- shrink ---- database.

5. The database can also be shrinked using the SSMS, by clicking the database --- task --- shrink ---- files, where you can shrink the log file or the database file.

To maintain the log file, the log has to be backed up and it can be shrunk using the below script

BACKUP LOG [databasename] TO DISK = N'Z:\DATABASE\TRANSACTION LOG BACKUPS\globalconac.trn' WITH NOFORMAT, INIT, NAME = N'databasename-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10go

DBCC SHRINKFILE (N' log file name' , 786)
go

SQL - Moving Tempdb to another drive

Moving Tempdb to another drive would see a increase in performance.

1. The size of the tempdb grows and if there is no space this will cause the applciations to slow down eventually to a halt.

2. The space on the other hard disk could give a better performance than the existing one as a result of more space.

The following code can be used to move the tempdb from the existing drive to e:\

USE TempDB
GO
EXEC sp_helpfile
GO

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'e:\sqldata\datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:\sqldata\datatemplog.ldf')
GO

Also please note the sql server service has to be stopped and restarted to have this new tempdb location to work.

SQL Optimization

Here are the basic rules for a start on the optimization, I had read this on a article, i am using this here on my blog as I agree based on my experience.

1. Table should have primary key
2. Table should have minimum of one clustered index
3. Table should have appropriate amount of non-clustered index
4. Non-clustered index should be created on columns of table based on query which is running
5. Do not to use Views or replace views with original source table
6. Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
7. Remove any adhoc queries and use Stored Procedure instead
8. Check if there is atleast 30% HHD is empty – it improves the performance a bit
9. If possible move the logic of UDF to SP as well
10. Remove * from SELECT and use columns which are only necessary in code
11. Remove any unnecessary joins from table
12. If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

sql indexing, optimization

When considering optimization, indexing is one of the options. The two types of indexing.
1. clustering index and 2. non- clustering index.

Clustering index is referred to like book shelf where the files in the shelf are refered to as pages, records in the files are referred to as the rows in the table, the shelf drawers on its own by alphabetical order is referred to as intermediate level.

Non clustered index is referred to as the index at the back of the book, where in a book when looked for a particular topic you can see references of certain pages based on that topic, in similar way non clustered index references the pages of similar key.

The next step will be to how effectivily shall we use the indexing. The main two functions of a indexing is to provide uniqueness and to return results much faster.

The next step is choosing between cluster and non-cluster index. We can have only one cluster index per table, where we can have 249 non - cluster index on a table. So in most cases we have the primary key set as the cluster index and work on the selectivity to determine the need of the number of non-cluster indexes.

Now how to determine and set up indexes.

For example if you are looking for a word "customer" in a book then a index will help you to find all the pages where the customer is available.

For example if you are looking to search all the words in a book then it is better to read the ent book which is table scan in our sql query, so in this case a index will be of no beneficial only burden.

There are a few ways to determine the need for a index, sql profiler is one of our best tools to start with, to check where there is more time spent.

Then based on the time, when the tables are identified this query can be used to determine the selectivity ratio.

selectivity

select count(distinct salesordernoitem) as '# unique',
count(*) as '# rows',
str(count(distinct salesordernoitem) / cast (count(*)as real),4,2) as
'selectivity' from transactionpricing

if the results are as below

#unique #rows selectivity
75000 75000 1.00

in this case an index on the sales order number in the transaction pricing table will be appropriate.

Error: 18456, Severity: 14, State: 16

Error: 18456, Severity: 14, State: 16


This error is caused based on the user password is not correct from the client system. This message appears in the log file in this location C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

This error also appears on the event viewer.

Error: 18456, Severity: 14, State: 16
Login failed for user 'sa'. [CLIENT: 192.168.x.xxx]

If this message appears continously check with client system if there is any sql job running and trying to attempt to connect to the server and password is not set right.

In our case we had a diagnostic tool running from this client and the password was changed, this has caused this error message

SQL Server Profiler results

To check the sql server profiler results in case of monitoring SSAA the following are the ways to measure.

1. The integer data displays the numerical information associated with the events.

2. The text data column shows the text description of the event such as MDX statement

3. The EventSubclass column shows the event subclass, such as the ExecuteSQL, WriteData, BuildIndex, or other subclass.

4. The duration displays the event duration.

SQL DTS Jobs and schedules - Maintenance

--to identify the sql jobs in a server, this is run under msdb
select * from dbo.sysjobs
--to identify the sql jobs schedules in a server, this is run under msdb
select * from dbo.sysjobschedules
--to identify the sql jobs history in a server, this is run under msdb
select * from dbo.sysjobhistory

--this gives the complete schedules listed under the sql agent jobs
select * from dbo.sysschedules

Find all Table size in SQL 2005

DECLARE @TableName VARCHAR(100) --For storing values in the cursor
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
--Open the cursor
OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults
SELECT * into tbltables_size
FROM #TempTable
--Final cleanup!
DROP TABLE #TempTable
GO
SELECT * from tbltables_size ORDER BY CAST(LEFT(dataSize,LEN(dataSize)-3) AS NUMERIC(18,0)) DESC

SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.

If you come across following errors in log file,
Source: MSSQLSERVER
Event ID: 17052
Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE

Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow.

Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to other drive.
1) TempDB grows big and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.

Follow direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:).

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.
USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB
along with other information related to the database. The names of the files are usually tempdev and demplog by default. These names will be used in next statement. Run following code, to move mdf and ldf files.
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:datatemplog.ldf')
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.