Tag: T-SQL

  • Script to find out the database file size, log file size on SQL Server 2000/2005/2008

    Recently I encountered a situation where i have to find out the database file size on SQL Server 2000. Here is the script which you can use to run on SQL Server 2000. Below script will on SQL Server 2005 or SQL Server 2008 as well.

    You can use sys.master_files instead of sysAltfiles in SQL Server 2005 and SQL Server 2008. Please find all the different scripts below.

    --SQL Server 2000

select 
fileID 
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where db_name(dbid) = 'master'

--SQL Server 2000

select 
fileID 
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where filename like '%.ldf%'

--SQL Server 2005/2008
SELECT DB_NAME(database_id) AS DBName,
Name AS LogicalName,
Physical_Name, (size*8)/1024 SizeMB
Physical_Name, ((size*8)/1024)/1024 SizeGB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Master'
GO

  • Update Statistics Useful Scripts

    Script to find out the statistics update date for all the indexes in the current database

    sp_MSforeachtable 'sp_autostats "?"'

    Script to update the statistics of all the indexes

    EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

  • How to check Lock Pages In Memory is enabled?

    You can use below simple technique to check whether lock pages in memory is enabled or not. If lock pages in memory is enabled you can see the “Using locked pages for buffer pool” message in the SQL Server error log.

    exec xp_readerrorlog 0, 1, 'locked pages'

    To check if it is disabled. You have to check for the “Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.” message.

    exec xp_readerrorlog 0, 1, 'lock pages in memory'

  • 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

  • How to grant the execute permission to User on all the procedure of the database?

    SQL Server 2005 onward, we can create the roles in database. You can follow below simple script to grant the EXECUTE permission to user.

     

    -- creating the database role 
CREATE ROLE Database_Executor
-- granting the execute permission to database role
GRANT EXECUTE TO Database_Executor

-- Here I am granting the SQLDBPool login execute permission
USE [DBName]
GO
CREATE USER [SQLDBPool] FOR LOGIN [sqldbpool]
GO
USE [DBName]
GO
EXEC sp_addrolemember N'Database_Executor', N'sqldbpool'
GO