SQL Server Tips and Tricks

MS SQL Server
Checking SQL Service Running Status !!

SELECT dss.[status_desc],servicename,startup_type_desc,service_account
FROM sys.dm_server_services dss
WHERE dss.[servicename] LIKE N’SQL%’

To query the space used by the transaction logs use the command,

use DBCC SQLPERF(LOGSPACE). The command displays the Database Name, Log Size in Mega Bytes, the % of log file used, remember the value is not the size it is the percentage used from the actual space of the log files. The results displayed is for all databases on the SQL Server instance.

Find the script belowÖ

    DBCC SQLPERF(LOGSPACE)
How to know the SQL Server Version
SELECT @@VERSION AS [VERSION DETAILS]
Choosing how the query result are displayed

To switch from other format to Grid format press Ctrl + D.

To make the results displayed in text format press Ctrl + T.

NOTE : Switching from one result format to other format will be effective from the next time query is executed.

Backup Information for a Database

DECLARE @DBNAME VARCHAR(50)

SET @DBNAME = ‘LSWB’

SELECT CEILING(((backup_size / 1024)/1024)/1024)
Bkup_Size_In_GB,
CEILING(((backup_size / 1024)/1024)) Bkup_Size_In_MB,
[type] Bkup_Type,
backup_start_date StartTime,backup_finish_date
FROM msdb.dbo.backupset
WHERE DATABASE_NAME = @DBNAME
ORDER BY backup_start_date DESC

Finding Shared Drives on a Cluster

select * from sys.dm_io_cluster_shared_drives

Renaming database and database objects using T-SQL

use master
GO

EXEC SP_RENAMEDB ‘OLDNAME’, ‘NEWNAME’

RENAMING a table

use testdb
go

EXEC SP_RENAME ‘dbo.table1’ , ‘tabletoberenamed’

How to find the Table which has Maximum Columns in a database

SELECT NAME AS Table_Name, max_column_id_used AS Total_Columns
From SYS.tables
WHERE max_column_id_used =
(SELECT MAX(max_column_id_used) FROM SYS.tables)

Using sp_delete_backuphistory to delete backup history

In order to delete all the backup history before 1/7/2011, you will have to pass the date value 1/7/2011 to the stored procedure as follows.

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/7/2011'

It would be good to purge the backup history on a regular basis such as weekly or daily using a SQL Agent job.

How to find when was a database object created in SQL Server?

select name,create_date from SYS.objects where name = ‘objectname’

select name,create_date from SYS.procedures where name = ‘objectname’

How to find the last time SQL Server was started?

SELECT @@VERSION AS SQLVERSION ,sqlserver_start_time from sys.dm_os_sys_info

SQL SERVER ñ Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

SELECT CURRENT_TIMESTAMP
GO

SELECT {fn NOW()}
GO

SELECT GETDATE()
GO

List of all Data and Log Files of a SQL Instance

SELECT * FROM SYSALTFILES

This query can be further tweaked to get only the data files or only the log files by adding where clause and filter by griupid column. Groupid = 1 indicates it is a data file and groupid = 0 indicates it is a log file.

Verifying SQL Server Database Backups

There are 2 ways you can verify whether or not a backup is reliable; one way would be to try restoring from the backup and the other way is to try a restore verifyonly.

Letís say you stored your database backup on C: Drive as dbbackup.bak, use the following command to verify if that backup is valid.

RESTORE VERIFYONLY FROM DISK = ‘C:\dbbackup.bak’

Finding Log Size for all Databases in SQL Server

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

Dropping a Column from a table using T-SQL ñ SQL Server

ALTER TABLE EmployeeTable DROP COLUMN Gender
Go

Creating Primary Key in a SQL Server table

CREATE TABLE EmployeeTable ( EmployeeID INT , FName VARCHAR (25)
, LName VARCHAR (25), Gender VARCHAR (1),
CONSTRAINT [PK_EMP_EMPID] PRIMARY KEY
( EmployeeID ASC ) ) ON [PRIMARY] GO

—-Some points to remember about Primary Keys

A table can consist of one and only one Primary Key.
By default a clustered Primary Key is created.
A Primary Key on a table can be based on one or more columns of that table.
A Primary Key ensure no duplicate values can exist in that column(s).
A Primary Key on one column can be used as a parent key on another table and helps in ensuring referential integrity between those tables.
A Primary Key is required when creating a Transactional Publication in Replication

Find Databases without recent full backups

SELECT @@VERSION AS [SQL SERVER VERSION]

SELECT NAME FROM sys.databases
WHERE NAME != ‘TEMPDB’
AND NAME NOT IN ( SELECT DISTINCT database_name FROM msdb..backupset
WHERE backup_start_date > DATEADD(DAY,-8,GETDATE())
AND TYPE = ‘D’ )

SQL Server Backup File info using T-SQL Query

DECLARE @DBNAME VARCHAR(128)

SET @DBNAME = ‘DATABASENAME’

SELECT A.database_name, B.physical_device_name
,A.media_set_id,A.backup_size,
A.backup_start_date,A.backup_finish_date
FROM msdb.dbo.backupset A
INNER JOIN msdb.dbo.backupmediafamily B
ON A.media_set_id = B.media_set_id
WHERE A.Database_Name= @DBNAME
ORDER BY A.backup_finish_date DESC

Location of Errorlog when SQL Server is running and you are able to connect:
sp_readerrorlog