Diadem Tech Weblogs

Sometimes we just can’t get our hands on that right “command” or the perfect “script”, that will simply “do my job (no bells or whistles required)”. After some hard googling, you may find one like a gift you have always wished or had to go the toiling way of writing, testing and putting into use, your own code for that particular need. In the following post I will discuss about some SQL commands and scripts (intended for MSSQL DB hosting) that comes handy in performing those tasks, that would otherwise take a lot of effort and time. You may find them scattered around the world wide web in fragments, but in my opinion finding them in one place have obvious advantage.

Take backup of a particular database and set it offline

This is a likely situation in a database migration scenario. You take backup of a database and immediately take it offline to avoid further data operations into it. You have to run the following SQL from sa login.

BACKUP DATABASE <dbname>
TO DISK = '<backup_path><dbname>.Bak'
WITH FORMAT,
NAME = 'Full Backup of <dbname> on <backup_time>, <backup_date>'
GO

ALTER DATABASE <dbname> SET OFFLINE
GO

<backup_path> may be something like “D:\migrate_database\” and <dbname> is the name of the database.

List tables and stored procedures with a specific schema

You may need to modify schema of tables and stored procedures in batch. This situation arises when you need to delete one user from the database who owns a lot of tables and stored procedures. In order to list those tables and procedures, run the following SQL commands.

USE <dbname>;

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = '<dbuser>';

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE INFORMATION_SCHEMA.ROUTINES.SPECIFIC_SCHEMA = '<dbuser>';

Now you can prepare and run a batch SQL script with commands like the following one.

ALTER SCHEMA dbo TRANSFER <dbuser>.<tablename>;

Find log size of all the databases

The following procedure will list the size of log file used by each database.

SELECT INSTANCE_NAME AS [DATABASE],
(CNTR_VALUE/1000) AS Size_In_MB FROM MASTER.dbo.SYSPERFINFO
WHERE COUNTER_NAME LIKE '%Log File(s) Size (KB)%'
AND INSTANCE_NAME NOT IN ('_TOTAL','mssqlsystemresuorce')
ORDER BY Size_In_MB DESC

Find recovery model of all the databases

Run the following procedure to know the recovery model used in each database.

Select name, DatabasePropertyEx(name, 'Recovery') from master..sysdatabases

A database’s log file size is influenced by the recovery model used in it.

Find failed login attempts to ‘sa’ account

Your public SQL server may be under constant brute force attack for ‘sa’ account. The following script will list the numbers and IPs from where the attacks are originated. This may be helpful in implementing your firewall policies.

declare @path VARCHAR(512)
select @path = path from sys.traces where is_default = 1
SELECT loginname, IP, Max(starttime) as LastErrorTime, COUNT(*) AS AttempCounts
FROM (
SELECT t.loginname, REPLACE(SUBSTRING(t.textdata, CHARINDEX(':', t.textdata, CHARINDEX(':', t.textdata, 1) + 1) + 1, 16), ']', '') AS IP, t.starttime
FROM dbo.fn_trace_gettable(@path, DEFAULT) AS t INNER JOIN
sys.trace_events AS e ON t.eventclass = e.trace_event_id
WHERE (eventclass = 20)) AS A
GROUP BY loginname, IP
order by LastErrorTime desc

Find cached page counts and memory uses for each database

The following two SQL commands will show page counts and memory used for each database in a SQL server instance.

SELECT
(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified]
ORDER BY [Page Count], [is_modified];
GO

SELECT count(*)AS cached_pages_count, (count(*) * 8)/1024 As Mbytes,  db_name(database_id)
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
GO

Get list of all current connections to SQL Server instance

The following SQL command lists all current connections to the SQL Server instance. It generates list by code and is flexible and easy to analyze.

SELECT DB_NAME(dbid)AS ConnectedToDB,
hostname, program_name,loginame,
cpu, physical_io, memusage, login_time,
last_batch, [status]
FROM master.dbo.sysprocesses
ORDER BY dbid, login_time, last_batch
GO

Also you can filter the results for a single user or a single database by providing filter criteria in where clause as required. For example to get just connections to database you may add following condition in where clause

WHERE dbid = DB_ID ('AdventureWorks')

Similarly you may filter the results with relevance to any column as required. Above script can be used on all versions of SQL Server.

Complete memory usage report of an SQL server

The following SQL script will generate a complete memory usage report for a SQL server instance.

DECLARE @pg_size INT, @Instancename varchar(50)

SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'

SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'

PRINT '----------------------------------------------------------------------------------------------------'

PRINT 'Memory usage details for SQL Server instance ' + @@SERVERNAME  + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' +  SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4)  + ' - ' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

PRINT '----------------------------------------------------------------------------------------------------'

SELECT 'Memory Configuration on the Server visible to Operating System'

SELECT physical_memory_in_bytes/1048576.0 as [Physical Memory_MB], physical_memory_in_bytes/1073741824.0 as [Physical Memory_GB], virtual_memory_in_bytes/1048576.0 as [Virtual Memory MB] FROM sys.dm_os_sys_info

SELECT 'Buffer Pool Usage at the Moment'

SELECT (bpool_committed*8)/1024.0 as BPool_Committed_MB, (bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,(bpool_visible*8)/1024.0 as BPool_Visible_MB  FROM sys.dm_os_sys_info

SELECT 'Total Memory used by SQL Server instance from Perf Mon '

SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)'

SELECT 'Memory needed as per current Workload for SQL Server instance'

SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Target Server Memory (KB)'

SELECT 'Total amount of dynamic memory the server is using for maintaining connections'

SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Connection Memory (KB)'

SELECT 'Total amount of dynamic memory the server is using for locks'

SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Lock Memory (KB)'

SELECT 'Total amount of dynamic memory the server is using for the dynamic SQL cache'

SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'SQL Cache Memory (KB)'

SELECT 'Total amount of dynamic memory the server is using for query optimization'

SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Optimizer Memory (KB) '

SELECT 'Total amount of dynamic memory used for hash, sort and create index operations.'

SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Granted Workspace Memory (KB) '

SELECT 'Total Amount of memory consumed by cursors'

SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total'

SELECT 'Number of pages in the buffer pool (includes database, free, and stolen).'

SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name= @Instancename+'Buffer Manager' and counter_name = 'Total pages'

SELECT 'Number of Data pages in the buffer pool'

SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Database pages'

SELECT 'Number of Free pages in the buffer pool'

SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free pages'

SELECT 'Number of Reserved pages in the buffer pool'

SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Reserved pages'

SELECT 'Number of Stolen pages in the buffer pool'

SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Stolen pages'

SELECT 'Number of Plan Cache pages in the buffer pool'

SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Plan Cache' and counter_name = 'Cache Pages'  and instance_name = '_Total'

At Diadem Technologies we have been using these scripts in our day to day DBA work and have benefited from these code snippets.

You can download all the scripts shown on this page from here: SQL Scripts (.zip)

(1 votes, average: 5.00 out of 5)

 Loading ...