Sql Server : How to query the edition or license level of Sql Server

Quick one today:

More frequently that I would like, we keep getting these questions from customers. “What Sql Server Edition do we have in our production / Development environment?”

There are many easy ways to check, I’ll list a couple here.

Using T-SQL

--
-- Sql Server Edition
--
SELECT @@VERSION                       AS [Complete_Version]
     , SERVERPROPERTY('Edition')       AS [Sql Server Edition]
     , SERVERPROPERTY('EngineEdition') AS [Sql Server Engine Edition]
GO

• @@VERSION variable has complete details.
• SERVERPROPERTY(‘Edition‘) also give you the edition information. Possible values are below:
  • ‘Enterprise Edition’
  • ‘Enterprise Edition: Core-based Licensing’
  • ‘Enterprise Evaluation Edition’
  • ‘Business Intelligence Edition’
  • ‘Developer Edition’
  • ‘Express Edition’
  • ‘Express Edition with Advanced Services’
  • ‘Standard Edition’
  • ‘Web Edition’
  • SQL Database
• Similarly, SERVERPROPERTY(‘EngineEdition‘), gives the same information in INT format. Possible values are below:
  • 1 = Personal or Desktop Engine (Not available in SQL Server 2005 and later versions.)
  • 2 = Standard (This is returned for Standard, Web, and Business Intelligence.)
  • 3 = Enterprise (This is returned for Evaluation, Developer, and both Enterprise editions.)
  • 4 = Express (This is returned for Express, Express with Tools and Express with Advanced Services)
  • 5 = SQL Database

Sql Server Edition Result

Using SSMS

Go to SSMS, right click on the Sql Server Instance and go to properties. See below:

Sql Server Edition

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Query all the indexes or tables that are COMPRESSED

Quick one today:

How to get a list of objects (or indexes) that are compressed or have COMPRESSION enabled?

I frequently use the below script to get a complete list. Just modify it as needed to fit your specific need.

--
--  Get a list of indexes that have COMPRESSION enabled
--
SELECT	  O.name                      AS [Table_Name]
        , O.object_id                 AS [Object_ID]
        , S.name                      AS [Schema_Name]
        , P.rows                      AS [Number_of_Rows]
        , P.data_compression_desc     AS [Compression_Description]
        , I.name                      AS [Index_Name]
        , I.type_desc                 AS [Index_Type]
        , I.index_id                  AS [Index_ID]
        , D.name                      AS [FileGroup]
FROM sys.partitions AS P
INNER JOIN sys.objects AS O
	ON P.object_id = O.object_id
INNER JOIN sys.schemas AS S
	ON S.schema_id = O.schema_id
INNER JOIN sys.indexes AS I
	ON I.index_id = P.index_id
	AND I.object_id = O.object_id
INNER JOIN sys.data_spaces AS D
	ON D.data_space_id = I.data_space_id

WHERE O.type = 'U'               -- only user objects
AND P.data_compression > 0       -- only the ones with COMPRESSION enabled
AND S.name NOT IN ('sys')        -- exclude system objects

ORDER BY  O.name ASC
	, I.index_id ASC
	, D.name ASC
GO

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server: Assign permissions on table TYPE to users

Quick one today:

How do we assign permissions on TABLE TYPEs to user accounts?

Earlier last week, this was a point that needed to be covered in our deployment code. We had permissions script that lists out permissions for each database user on all the objects. Since we recently started adding TABLE TYPE’s, this was never included. Now we are, so how do we assign permissions on TABLE TYPE to user accounts?

--
--    Assign permissions on TABLE TYPE to user account
--
GRANT CONTROL		ON TYPE::ExternalKeyTableType					TO [User_account]
GO

CONTROL permission implies all the other permissions on a TABLE TYPE.

When TABLE TYPE is a parameter in a stored procedure, then you want to grant REFERENCES permission as well.

--
--    Assign REFERENCES permission, if table type is used as parameter in stored procedures
--
GRANT REFERENCES		ON TYPE::ExternalKeyTableType					TO [User_account]
GO

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server Error Messages : The transaction log for database ‘tempdb’ is full due to ‘ACTIVE_TRANSACTION’

Quick one today: Earlier last week, I was playing with one of our customer databases in our lab environment. As part of implementing table partitioning, some tables changes were necessary; Moving data from one set of tables to another. In the middle of running those scripts, this error occurred:

Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage: 140751663071232' 
in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by 
deleting unneeded files, dropping objects in the filegroup, adding additional files 
to the filegroup, or setting autogrowth on for existing files in the filegroup.

Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'. 

All the heavy data movement, between tables and reindexing, has incurred a lot of growth in the transactional log file of the user database; And equivalent growth in the data/log files of tempdb. So, we took the following steps to rectify it: As the script took a few hours to execute and complete all the data movement. We did the following steps, every few minutes:

• Issue CHECKPOINT on the tempdb

USE tempdb
GO
CHECKPOINT
GO

• Take Transactional log backup on the user database

BACKUP LOG CustomerXS TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\XS and RT\XS_Movement.trn'
WITH COMPRESSION
GO

This allowed us to create more free space in the database files to accommodate space for new activity.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Transaction log full in tempdb and user database (tidbits 2)

Quick one today:

Sometimes, in our lab, we need to do some preparatory work before doing the actual work. This involves loading or moving large volumes of data from one table to another or something similar. While in the zone of doing such activities, the transactional log gets full and my work gets stopped in the middle. It could be tempdb or user db, but usually it is transactional log file getting full. This is always frustrating as we rarely have frequent transactional backup jobs running on lab servers.

So, right before I get into the zone of doing this preparatory work, I do this on the lab database to prevent any transactional log full errors, either from tempdb or user database.

--
--  Sample code to clear out transactional log from tempdb and user db
--
USE tempdb
GO

Declare @Cnt INT = 0

WHILE @Cnt < 100
BEGIN
 PRINT '@Cnt' + CONVERT(VARCHAR, @Cnt)

 CHECKPOINT

BACKUP LOG UserDB_Name TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\TempTRNBackups\Delete_this_later.trn'
 WITH COMPRESSION
 , STATS = 1

WAITFOR DELAY '00:05:00'

SELECT @Cnt = @Cnt + 1
END
GO

In a way it also solves this error that we run into when doing such major data modification/transformation tasks. This topic was discussed in one of the previous posts as well.

Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage: 140751663071232' 
in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by 
deleting unneeded files, dropping objects in the filegroup, adding additional files 
to the filegroup, or setting autogrowth on for existing files in the filegroup.

Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

Hope this helps,
_Sqltimes

Read Full Post »