SQL Server Administration

Read our high-quality articles on SQL Server administration. Learn how to efficiently administer the world’s biggest DBMS, that is SQL Server.

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

by

—— 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

by

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

by

—— 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

by

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

by

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

by

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.   T-SQL Example for Creating the Linked Server The below T-SQL code does just that: — –Note: The Entire Operation Takes Place on the Local Instance — … Read more…

Getting the Disk Usage Statistics for all Tables in a Database

by

I know, there are standard reports and other GUI tools for doing this task for SQL Server 2005 or later but what about when you need to extract such information from a SQL Server 2000 instance or when you just want to write some code? 🙂   Get the disk usage statistics using the undocumented … Read more…

How To Get Basic SQL Server Instance Info (T-SQL Script)

by

With the below T-SQL script, you can get basic SQL Server instance info such as: Full instance name SQL Server version Edition Collation Number of databases Product level (i.e. SP-level) …and more   The T-SQL Script The script uses the built-in SQL Server function SERVERPROPERTY. SELECT SERVERPROPERTY(‘ServerName’) AS FullInstanceName, REPLACE(SUBSTRING(@@version,0,CHARINDEX(‘-‘,@@version)),’Microsoft ‘,”) as FullSQLVersion, SERVERPROPERTY(‘ProductVersion’) AS ProductVersion, SERVERPROPERTY(‘ProductLevel’) … Read more…

T-SQL Tip: Retrieving Database File Sizes

by

The following script generates T-SQL statements that when ran, they return file size information for all the user databases on a SQL Server instance. –Script that generates T-SQL providing size information for all database files on a SQL Server InstanceSELECT ‘SELECT ”’+[name]+”’ as DBName,cast(f.name as varchar(25)) as DBFileName,f.Filename as PhysicalFileName,cast (round((f.size*8)/1024.0,2) as decimal(18,2)) as FileSizeinMB … Read more…

Retrieving Log Space Information within a SQL Server Instance – The Stored Procedure!

by

In yesterday’s post, we saw how we can retrieve log space information for all the databases within a SQL Server instance using the command DBCC sqlperf(logspace).   Creating the Stored Procedure Today we will refine the process even more by creating a stored procedure that returns log space information for a given database! –****************************** –CREATING … Read more…

Retrieving Log Space Information within a SQL Server Instance

by

In the everyday life of a Database Administrator there is the task of maintaining the database logs in terms of size they occupy on the disk. Of course, there are automated maintenance and reporting procedures for this task as well as for many other tasks but it is not few the cases where the DBA needs to … Read more…

How to retrieve size information for all the tables in a SQL Server Database

by

In an older post, we have discussed about, how you could rebuild all the indexes of a database in SQL Server by making use of the undocumented stored procedure “sp_MSforeachtable”.   Retrieving Table Size Info using sp_MSforeachtable Another common task is when you want to retrieve size information for all the tables in a database. … Read more…

Useful T-SQL Knowledge for SQL Developers and DBAs

by

In this article, I present useful T-SQL knowledge for SQL Developers and DBAs. A few days ago I started writing an article that it would provide a set of useful tips on SQL Server topics that are met in the everyday life of DBA/Database Developer. The article is now completed and you can find it … Read more…

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed…

by

In this article, we will be discussing about the following error message in SQL Server and how to easily resolve it: Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed…   About this Error Message If you ever encounter issues when trying to create, add, or edit steps for a … Read more…