Home » Database Maintenance

Database Maintenance

sql server DBA 999

How to check Deadlocks in SQL Server

What is a SQL Server deadlock?What is a SQL Server deadlock?

A deadlock in SQL Server occurs when 2 processes/sessions are competing for exclusive access to data in the tables but is unable to obtain exclusive access to it because the other process/session is preventing it. This results in a situation where neither process can proceed and gets struck. The only way out of this deadlock when happens is that for one of the processes to be terminated. SQL Server automatically detects when deadlocks have occurred and takes action by killing one of the processes known as the victim.
Deadlocks are not confined to table locks now but from SQL Server 2012 onwards it can happen with memory, Multiple Active … Read the rest

sql server DBA 999

SQL Server Database Mail Configuration Step by Step

    SQL Server Database DB Mail Configuration Step by Step:

This Article will help Configuring SQL Server Database mail in SQL Server for Alerts and Notifications.

Database Mail is very Useful and powerful ability in SQL Server. Send email in SQL Server is one of the best ability for monitoring SQL Server

One can configure SQL Server Database Mail so that it sends you important notifications and alerts for example

1. Alerts for any high severity errors, corruptions etc..
2. Notifications Agent jobs Success or failure
3. You can also use Database Mail to email query results and reports

Configuring Database Mail and Enabling it on the SQL Server Agent for Alerts can be done by following below steps

Step 1.Read the rest

sql server DBA 999

What is SQL Server Health Check? Why is it important?

What is SQL Server Health Check? Why is it important?

A SQL Server health check looks at all aspects of your SQL Server environment in terms of best practices in the areas of Performance, Configuration, Security, Disaster Recovery and the ability of your environment to scale to projected future loads.

With a SQL Server Health Check, one should make a report of our findings and recomendation/solutions.

Fixing the Problems
You may choose to have us fix some or all of the problems found by the SQL Server health check or
you may prefer to have your own team to discuss and work through them.

What are the important aspects of SQL Server Health Check?
SQL Server Disaster Recovery Plan:
1.Design … Read the rest

sql server DBA 999

Database File Free space and checking Database Growth Trends

Database File Free space and checking Database Growth Trends is one of the important tasks for a DBA for Pre-Sizing the Database to avoid the Autogrowths plus to have a idea of Space Requirements on the SQL Server etc.

Below Query can be really helpful for store this information into a Central location and monitor my Database Growth Trends.


SELECT S.FILEID,
CONVERT(decimal(12,2),ROUND(S.size/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty(S.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND((S.size-fileproperty(S.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],
S.name as [DATABASENAME],
S.FILENAME as [FILENAME]
FROM dbo.sysfiles S
Read the rest

sql server DBA 999

Moving system databases in SQL Server 2008/2005

Moving system databases in SQL Server 2008, 2005

To move all the system and user databases from “C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA” folder to “C:System databases” location.

We can use below query to check current location of system databases:

Select name, physical_name from sys.master_files


1.Moving Tempdb database:

We need to use the Alter database command to move tempdb database files from one location to another as shown in the below screenshot.
How to move system databases sql server 3

Use master
go
Alter database tempdb modify file (name=tempdev,filename='C:System databasestempdb.mdf')
go
Alter database tempdb modify file (name=templog,filename='C:System databasestempdb.ldf')
go

Once we execute the above command the o/p will be something like,

The file “tempdev” has been modified in the system catalog. The new path will be used the next time
Read the rest

sql server DBA 999

When do we use SQL Server Database in Restricted User Mode?

Why do we need restricted user SQL server?

When performing some maintenance tasks, particularly in recovery situations, it is essential to limit access to databases by general users by restricting the user access by putting into restricted user sql server or for sql database in single user mode after restore. restricted user sql server access mode can be employed to limit connections to high-level users only.

How do we put restricted user SQL server?

In order to put SQL server restricted user mode we need to use the alter database set restricted_user below. This command can help with sql database in single user mode after restore.

ALTER DATABASE Command
The ALTER DATABASE command allows a database administrator to modify SQL … Read the rest