Tag Archives: Database

How much memory is used by each database in SQL Server?

A large percentage of the memory of SQL Server instance is consumed by buffer pool. You can find out the database which consuming more memory using below query.

SELECT
    CASE WHEN database_id = 32767 THEN 'Resource DB' ELSE DB_NAME (database_id) END AS 'DBName',
    COUNT (1) AS 'Page Count',
    (COUNT (1) * 8)/1024 AS 'Memory Used in MB' ,   
   CASE WHEN is_modified = 1 THEN 'Dirty Page' ELSE 'Clean Page' END AS 'Page State' 
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY   db_name(database_id)
GO

Using a PowerShell Script to delete old files for SQL Server

Problem

Many clients are using custom stored procedures or third party tools to backup databases in production environments instead of using database maintenance plans. One of the things that you need to do is to maintain the number of backup files that exist on disk, so you don’t run out of disk space.  There are several techniques for deleting old files, but in this tip I show how this can be done using PowerShell.

Solution

http://www.mssqltips.com/sqlservertip/2726/using-a-powershell-script-to-delete-old-files-for-sql-server/

Different ways to make a table read only in a SQL Server database

Problem

In some cases there may be a need to make a SQL Server table read only. There are several different options for doing this and in this tip we cover various ways that you can make a table read only in a SQL Server database.

Solution

http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/#comments

Dedicated Administrator Connection

DAC: Dedicated Administrator Connection feature is available from the SQL Server 2005. It is available in all the higher editions by default except express edition. DAC will be useful when SQL Server is not responding any connections; in such kind of situation DBA will connect through the DAC and troubleshoot/fix the issue.

You can execute below kind of command for the initial troubleshooting.

-- Locking Info
SELECT * FROM sys.dm_tran_locks
GO
-- Running Sessions
SELECT * FROM sys.dm_exec_sessions
GO
-- Requests Status
SELECT * FROM sys.dm_exec_requests
GO
--Open Sessions
SP_WHO2
--To get the SQL Text
DBCC OPENTRAN (SPID)
--To terminate the curlprit process
KILL SPID

DAC is disabled by default, it is a good practice to enable the DAC. You can enable the DAC using by executing below query.

Use master
GO
--0 = Allow Local Connection,
--1 = Allow Remote Connections*/
sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

You can connect using DAC on of the following method.
Command Prompt
SQL Server Management Studio

Using Command Prompt: Use SQLCMD utility to connect to SQL Server as below.
-A argument is to specify the DAC connection.
-S argument is to specify the server name.
-d argument is to specify the database name.
-E argument is for windows connection with integrated security true

Using Management Studio: Write ADMIN: before the server name in management studio connection window. It will give you the DAC connection.

To enable the DAC connection in SQL Server express edition add ;-T7806 trace flag as startup parameter.

Go into configuration manager — right click on SQL Server Service and select properties — go into advanced tab and add the trace flag ;-T7806. Once done restart the SQL Server Services.