DBStats: A Stored Procedure for Easily Retrieving Basic DB Information

In this article, I am publishing a stored procedure (DBStats) I have recently written and which given a database name as an input parameter it returns the below basic information: TotalTables TotalViews TotalSPs TotalFunctions TotalDMLTriggers TotalDBSize (MB) LogSize (MB) List of all tables with their number of columns   Sample Output of DBStats The below … Read more…

Monitoring Locking in SQL Server

SQL Server 2005 or later has a specific dynamic management view (DMV) which provides detailed information regarding the active locks within the SQL Server instance that is, locks that have been already granted or they are waiting to be granted. The DMV is called: sys.dm_tran_locks. The following T-SQL query uses sys.dm_tran_locks in order to return … Read more…

Massively Detaching and Re-attaching Databases in SQL Server

In this article, we will be discussing about massively detaching and re-attaching databases in SQL Server.   Why massively detaching and re-attaching all the databases in a SQL Server instance? There are cases where you might need to massively detach and re-attach all the databases in a SQL Server instance. Such scenario can be a … Read more…

The SELECT ALL USER SECURABLES Permission in SQL Server 2014

The SELECT ALL USER SECURABLES permission in SQL Server 2014 is a very useful new server-level permission. The login that gets granted this permission, can view the data in all databases that the user can connect to. For example, consider a scenario where you have the following three databases: db1 db2 db3 Each database has … Read more…

T-SQL Tip: Retrieving Security-Related Info for SQL Server Logins

—— Retrieves Security-Related Information— for all the SQL Server Logins—— SQL Server versions  supported: SQL Server 2005 or later—SELECT [name] as LoginName,LOGINPROPERTY ([name] , ‘DefaultDatabase’) as DefaultDatabase,LOGINPROPERTY ([name] , ‘DaysUntilExpiration’) as DaysUntilExpiration,(CASE ISNULL(LOGINPROPERTY ([name] , ‘IsExpired’),0) WHEN 0 THEN ‘False’ ELSE ‘True’ END) as IsExpired,(CASE ISNULL(LOGINPROPERTY ([name] , ‘IsLocked’),0) WHEN 0 THEN ‘False’ ELSE ‘True’ END) … Read more…

Screencast: Migrating to a Contained Database in SQL Server 2012 or later

Via this post, you can watch an interesting screencast, about Migrating to a Contained Database in SQL Server 2012 or later.   Introduction: What are Contained Databases in SQL Server? Contained Databases is an exciting new feature, originally shipped with SQL Server 2012. Among other, it introduces a more robust security model where you can … Read more…

T-SQL Tip: Getting the File Locations for all DBs in a SQL Server Instance

—— Dynamically builds  T-SQL statements for retrieving the file — locations for all the databases in the SQL Server Instance—— SQL Server versions  supported: SQL Server 2005 or later—SELECT ‘use ‘+ [name]+’; select ”’+[name]+”’ as DBName,cast ([name] as varchar(45)) as LogicalFileName,cast (filename as varchar(100)) as FileName from sysfiles;’ as SQLStatement FROM master.sys.databases Details: Just execute the statements generated … Read more…

Handling Disk Space Issues During Heavy Index Rebuild Operations

There are times where you need to massively rebuild indexes on some really large databases, after indicated by the relevant analysis of course. However, rebuilding indexes, requires also the adequate amount of free disk space that will be used during the rebuild operation (mainly for the sorting process). Usually the required space is the size of … Read more…

There was an unexpected failure during the setup wizard

In this article, we will be discussing how to resolve the following SQL Server error: “There was an unexpected failure during the setup wizard”.   When you might get this error OK folks, this is a little bit “tricky” issue. The scenario is the following: You are trying to install SQL Server 2005, then you … Read more…

T-SQL Tip: How to Create a Simple Linked Server Between SQL Server Instances

If you are looking for a T-SQL tip, on how to create a simple linked server between SQL Server instances, then the below tip might come in handy. The below T-SQL code does just that: — –Note: The Entire Operation Takes Place on the Local Instance — USE [master] GO –Create the linked server object … Read more…