Home » SQL Server Admin » Page 4

SQL Server Admin

sql server DBA 999

Adding Data Collectors on SQL Server 2008/2008 R2/2012/2014/2016

Adding SQL Server Data Collectors to SQL Server is very important for Troublshooting issues and Root cause analysis of many issues normally occuring on SQL Servers.

These counters are helpful for both Virtual or Physical SQL Servers. They are available in all versions like sql server 2008 data collector, sql server 2012 data collector, sql server 2014 data collector

    The Counters related to SQL Instance which are really important are:


SQLServer:Access MethodsFreeSpace Scans/sec
SQLServer:Access MethodsFull Scans/sec
SQLServer:Access MethodsIndex Searches/sec
SQLServer:Access MethodsPages Allocated/sec
SQLServer:Access MethodsWorkfiles Created/sec
SQLServer:Access MethodsWorktables Created/sec
SQLServer:Buffer ManagerBuffer cache hit ratio
SQLServer:Buffer ManagerCheckpoint pages/sec
SQLServer:Buffer ManagerDatabase pages
SQLServer:Buffer ManagerFree list stalls/sec
SQLServer:Buffer ManagerFree pages
SQLServer:Buffer ManagerLazy writes/sec
SQLServer:Buffer ManagerPage life expectancy
SQLServer:Buffer ManagerReadahead pages/sec
SQLServer:Buffer ManagerReserved pages
SQLServer:Buffer … 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

Active Connections to SQL Server Database 2008, 2005 by Application or User

To Check Active Connections to SQL Server Database grouped by User

Below query is very helpful in finding the Total CPU usage, Waiting Time, Elapsed Time, Number of Reads, Number of Writes, connections grouped by Login name related which are active in SQL Server Database.

To check Active Connections to SQL Server Database grouped by Login name


SELECT SUM(s.cpu_time) AS CPU,
SUM(s.total_scheduled_time) AS WaitTime,
SUM(s.total_elapsed_time) AS ElapsedTime,
SUM(c.num_reads) AS Reads,
SUM(c.num_writes) AS Writes,
COUNT(1) AS Connections,
s.original_login_name AS login
FROM sys.dm_exec_connections AS c
LEFT OUTER JOIN sys.dm_exec_sessions AS s
ON s.session_id = c.session_id
GROUP BY s.original_login_name

Below query is very helpful in finding the Total CPU usage, Waiting Time, Elapsed Time, Number of Reads, Number of Writes, connections Grouped by

… 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

How to check Log shipping status in SQL Server 2008

How to check Log shipping status in SQL Server 2008:

SQL server recovery model has to be Full recovery mode for log shipping which allows log backups.

sql server database log backup is shiped and restore from one server or Primary to another server Secondary/destination server. Reports can be run from Secondary server for business needs. Using sql server nolock is recommended if dirty reads are allowed for reporting purposes.

This can be done by two methods:

1.By using the standard report on Instance level for Transaction log shipping status both on Primary and each secondary/destination servers individually.
On the Primary/source server, it shows when backup jobs succeeded.
On Secondary/Destination servers, it shows both when last copy job succeeded plus … Read the rest

sql server DBA 999

Unable to Shrink Temp DB Size in SQL Server 2008

It occurred in production environment when I was Unable to Shrink Temp DB Size SQL Server 2008 eventhough nothing was using it.

To check the free space you can use the query below:

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

and to get idea on files

sp_helpdb 'Tempdb'

When trying to shrink Tempdb it does not let me shrink with command below to 1000MB.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 1000)
GO

I have tried setting intial sizes for tempdb which did not work either,But when we run below query to set the tempdb appropriately with fixed filegrowth then it works

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', … Read the rest

sql server DBA 999

Bringing Database back from Single User to Multi User mode with active connections

Bringing Database back from Single User mode to Multi User mode for Database with active connection:
Some times you will need to bring a database back from single user mode.
If we issue the below command,

USE [master]
GO
ALTER DATABASE [AdventureWorks] SET multi_USER
GO

It errors out:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'AdventureWorks' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

For conditions like this:
Use the below query to find out the active connection to the database

select d.name, d.dbid, spid, login_time, nt_domain, … 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

sql server DBA 999

Protecting your database using Transparent Data Encryption in SQL Server 2008

Encryption:
Encryption is the process of transforming information in plain text using a cipher, or algorithm, to make it unreadable to everyone other than the person who has the key. There are two types of keys; symmetric and asymmetric. When the same value is used to encrypt and decrypt, then it is known as a symmetric key. An asymmetric key has two parts: one is a private key and the other is a public key. The private key is used to encrypt the data and the public key is used to decrypt the data.

TDE: is a full database level encryption that protects the data files and log files.
As per Microsoft documentation for TDE

“Transparent data encryption (TDE) performs … Read the rest