To fetch size of all Databases in SQL Server

For 2000:

SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sysaltfiles mf
INNER JOIN sysdatabases d ON d.dbid = mf.dbid
WHERE d.dbid > 4 — Skip system databases
GROUP BY d.name
ORDER BY d.name

For 2005 & above:

SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sysaltfiles mf
INNER JOIN sys.databases d ON d.database_id= mf.database_id
WHERE d.database_id > 4 — Skip system databases
GROUP BY d.name
ORDER BY d.name

How to Rename SQL Server Cluster Virtual Server Name(Network name)

Before you begin the renaming process, review the items below.

  • SQL Server does not support renaming servers involved in replication, except in the case of using log shipping with replication. The secondary server in log shipping can be renamed if the primary server is permanently lost.
  • When renaming a virtual server that is configured to use database mirroring, you must turn off database mirroring before the renaming operation, and then re-establish database mirroring with the new virtual server name. Metadata for database mirroring will not be updated automatically to reflect the new virtual server name.

SQL Server 2005, 2008, 2008 R2

  1. Using Cluster Administrator, change the SQL Network Name to the new name.
  2. Take the network name resource offline. This takes the SQL Server resource and other dependent resources offline as well.
  3. Bring the SQL Server resource back online.

After a virtual server has been renamed, any connections that used the old name must now connect using the new name.

To verify that the renaming operation has completed, select information from either @@servername or sys.servers. The @@servername function will return the new virtual server name, and the sys.servers table will show the new virtual server name. To verify that the failover process is working correctly with the new name, the user should also try to fail the SQL Server resource over to the other nodes.

To minimize network propagation delay of a virtual server renaming operation, use the following steps:

  • Issue the following commands from a command prompt on the server node:
    ipconfig /flushdns
    ipconfig /registerdns
    nbtstat –RR

For SQL 2012, Additionally registry settings also should be changed.

sql server comsumes more memory than maximum memory allocation

Sometimes, SQL Server may start consuming more than allocated MAX Memory setting. Reason for this is as follows:

The max server memory sets the size of the buffer pool (data cache, plan cache and a bunch of others). SQL also takes memory outside of the buffer pool (back on 32-bit that was called the MemToLeave)
That’s for backup buffers, CLR memory, linked server drivers, extended stored procs, thread stacks and a few other things. It’s not usually more than a few hundred MB but, especially if lots of CLR is used, it can become noticeably large.

How to Identify this memory pressure issue?

You can monitor this using “Windows Sysinternals tool – “VMMAP”

VMMap is a process virtual and physical memory analysis utility. It shows a breakdown of a process’s committed virtual memory types as well as the amount of physical memory (working set) assigned by the operating system to those types. Besides graphical representations of memory usage, VMMap also shows summary information and a detailed process memory map. Powerful filtering and refresh capabilities allow you to identify the sources of process memory usage and the memory cost of application features.

Besides flexible views for analyzing live processes, VMMap supports the export of data in multiple forms, including a native format that preserves all the information so that you can load back in. It also includes command-line options that enable scripting scenarios.

VMMap is the ideal tool for developers wanting to understand and optimize their application’s memory resource usage.

Changing the existing service account to new service account for SQL Services

Steps to change the service account to new service account for SQL Services as follows:

  1. Create new login for this new service account on SQL Server instance with SYSADMIN Privileges for which service account is going to be changed.
  2. In Group policy (gpedit.msc). Please enable privilege to “Lock pages in memory” for this new service account.
  3. Go To

    Local Computer policy –> Windows settings –> security settings –> local policies –> user rights assignment –> “Lock pages in memory” –> add new service account to this. (Without adding this account SQL agent will not start)

  4. After enabling group policy. Please check the sp_configure settings as follows:
    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    sp_configure ‘Agent XPs’, 1;
    GO
    RECONFIGURE
    GO

  5. After performing the above three steps in sequence.

Change the service account name and password for the SQL Services on services.msc.

If above three steps are not performed correctly. SQL Services and SQL Agent services will not start/restart.