Get rowcount of all sql tables

A quick method of getting the rowcount of all tables in a database is to query the sys.objects and sys.partitions views – returning the sys.partitions rows column

The query filters on sys.objects  : U = Table (user-defined) , V=View   objects.

 

select sobj.name,spar.rows
FROM sys.objects sobj 
INNER JOIN sys.partitions spar
ON sobj.object_id = spar.object_id 
WHERE sobj.type IN ('U','V')
AND spar.index_id IN (0,1)
ORDER BY sobj.name

 

Read More

SQL Server - All tables row count - SQL Server DBA

How to check for SQL ghost records? - SQL Server DBA

SQL Server - sys.allocation_units - SQL Server DBA

Add an extra SQL Transaction log file – ALTER DATABASE ADD LOG

Question: I need to create an extra sql transaction log file to a SQL Server database. What is the correct syntax?

I’ve run out of disk space on the dedicated log file drive and need to run a very large query one-off query.The storage administrators have added a temporary extra disk , for the extra sql transaction log file.  

Answer:  Use the ALTER DATABASE command to add an extra SQL transaction log file  with a destination location . The example below adds an extra log file to an existing database

USE [master]
GO
ALTER DATABASE [database_name] ADD LOG FILE ( NAME = N'database_nameExtraLog', FILENAME = N'N:\ database_nameExtraLog.ldf' , SIZE = 20480KB , FILEGROWTH = 1024KB )
GO

 

Read More

Modify sql transaction log file size - increase or decrease

SQL Server - Find Default Data location and Default Log Location

SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size

SQL Server – Monitor SQL Transaction Log AutoGrowth for performance issues

Drop Connections with ROLLBACK IMMEDIATE to allow a Detach database

If connections exist on a database , and you attempt to detach a database with EXEC master.dbo.sp_detach_db @dbname = N'database_name' the detach will fail.

Best practise is to identify the connection sources and communicate with the users, and closing the connection gracefully. Communicating with the users is not always possible .

Instead of using the KILL command ,use this command to set the database into SINGLE_USER mode. The SINGLE_USER mode indicates for only one user to access the database. The ROLLBACK IMMEDIATE option terminates ,rolling back all transactions not completed and disconnecting all other connections immediately.

 

 

USE [master]
GO
ALTER DATABASE [database_name] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'database_name'
GO

 

Read More

Find number of connection attempts since SQL Server start - @@connections

SQL Server – Database Connectivity test with Powershell

Cannot connect to SQL Server

Make a database read only – READ_ONLY

If there’s a requirement to not change data on a SQL Server database , a DBA may  make a database read only (READ_ONLY).   There are some benefits  to making a database read only outlined below.

Some notes on READ_ONLY

 1)    A small performance gain is possible due to no locking , which leads to no blocking. Read more on SQL Server - How to detect and troubleshoot blocking and deadlocks

2)    No  schema changes or data changes are possible for the duration of the database read only status

3)    Consider running optimization processes , such as full UPDATE STATISTICS and eliminate logical fragmentation before changing the status to read only . Check SQL Server Update Statistics with FULL SCAN - SQL Server DBA

 

 --to check the current status 
SELECT name, is_read_only 
FROM sys.databases 
WHERE name = 'database_name'

--to make a SQL Server database read only 
ALTER DATABASE database_name SET READ_ONLY
--to make a SQL Server database read-write
ALTER DATABASE database_name SET READ_WRITE

 Read More

SQL Database Status with sys.databases - SQL Server DBA

SQL Server – read only database status - SQL Server DBA

SQL Server DBA Scripts

Find number of connection attempts since SQL Server start - @@connections

Question: How can I find the number of sql server connection attempts on a SQL Server Instance since the last SQL Server restart?

Answer: According to SQL Server BOL  @@connections “Returns the number of attempted connections, either successful or unsuccessful since SQL Server was last started”

select @@connections as 'Login Attempts'

 

Sometimes there is confusion between user count and connections. These two are not always the same. Multi – tier applications , can open multiple connections as part of the interaction with the database server. Within one application process, it is not unusual for an application to open and close connections ,  either as explicit or implicit

 

Read More

SQL Kill connections to a database - SQL Server DBA

SQL Sever - Dedicated Administrator Connection - SQL Server

An existing connection was forcibly closed by the remote host