Read our high-quality articles on SQL Server administration. Learn how to efficiently administer the world’s biggest DBMS, that is SQL Server.
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…
—— 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…
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…
—— 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…
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…
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…
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…
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…
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…
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…
In this article, we will discuss about migrating to a contained database in SQL Server To this end, we will see how we can convert a “normal” database to a partially contained database in SQL Server. A typical security/user access configuration for a database would be set up as follows: Create the login (SQL or … Read more…
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…
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…
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…
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…