Category Archives: SQL Server

Get SQL Server Database Owner, Size, Last Backup Times and other useful info

I’ve been working on Databases Inventory and wrote this small query to get information for our SQL Server database repositary. This is a very basic query and I was hoping to get something from Google, but couldn’t find anything that includes all the essential information about databases such as Data and Log files sizes, last backup time and size, etc. So, I had to write it by myself and decided to share it with anybody who wants to save 5 minutes of their time by googling it. This T-SQL query will work on any version starting from SQL Server 2005.

SELECT sd.name AS 'DatabaseName'
       , SUSER_SNAME(sd.owner_sid)  AS 'DbOwner'
       , sd.compatibility_level AS 'Compatibility'
       , sd.recovery_model_desc AS 'Recovery'
       , sd.page_verify_option_desc AS 'ChecksumLevel'
       , sd.snapshot_isolation_state_desc AS 'Snapshot'
       , sd.state_desc AS 'Status'
       , sd.user_access_desc  AS 'UserAccess' 
       , CAST(DATABASEPROPERTYEX(sd.name, 'Updateability') AS varchar(256)) AS 'Updateability'
       , CAST(mfdata.DataSize*8/1024 AS int) AS 'DataSizeMB'
       , CAST(mflog.LogSize*8/1024 AS int) AS 'LogSizeMB'
       , CAST(b.backup_size/1024/1024 AS int) AS 'LastBackupSizeMB'
       , m.physical_device_name AS 'BackupDevice'
       , b.backup_start_date AS 'LastBackupStart'
       , b.backup_finish_date AS 'LastBackupFinish' 
       , sd.create_date AS 'DbCreateDate'
       , sd.collation_name AS 'Collation'
       , sd.is_auto_close_on AS 'IsAutoClose'
       , sd.is_auto_shrink_on AS 'IsAutoShrink' 
       , sd.is_auto_create_stats_on AS 'IsAutoCreateStatistics' 
       , sd.is_auto_update_stats_on AS 'IsAutoUpdateStatistics' 
       , sd.is_fulltext_enabled AS 'IsFulltextEnabled'
       , sd.is_parameterization_forced AS 'IsParameterizationForced'
FROM master.sys.databases AS sd
LEFT JOIN (SELECT database_id, SUM(size) DataSize FROM master.sys.master_files WHERE type = 0 GROUP BY database_id, type) mfdata 
ON mfdata.database_id = sd.database_id
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM master.sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog 
ON mflog.database_id = sd.database_id
LEFT OUTER JOIN	msdb.dbo.backupset b 
ON (sd.name = b.database_name) AND b.backup_start_date = 
	         (SELECT MAX(backup_start_date) FROM msdb.dbo.backupset WHERE database_name = b.database_name AND type = 'D')
LEFT OUTER JOIN msdb.dbo.backupmediafamily m 
ON (b.media_set_id = m.media_set_id)
WHERE sd.database_id > 4 
--Last condition omits all system databases. It can be changed to include them as well

How to disable remote access to Microsoft SQL Server

You can find a lot of resources describing how to enable remote access to MS SQL Server, but I couldn’t find anything describing how to restrict remote access.

You might need to disable ability to connect to SQL Server instance from network altogether if you place SQL Server in the DMZ (screened subnet) and the web/application server located on the same box is the only service that needs an access to the database.

There are several ways we can do that:

1. Using Windows Firewall. Just make sure that Firewall is enabled and firewall rules deny an access to all ports your SQL Server uses. We don’t need to do anything if all rules are defined by default and haven’t be changed.

2. Using IPSec. We can define rules for IPSec driver to drop any pockets that are related to SQL Server ports. This is the best option if you need to restrict access from specific networks or devices. There are a lot of documentation and  blog posts on how to set up IPSec, so you should be able to find this information.

3. If we cannot enable Firewall or IPSec for whatever reason then we can disable remote communication in SQL Server itself by using Network Configuration  Manager.

  • Open TCP/IP Properties in SQL Server Network Configuration. One of the options would be disabling all protocols except for Shared Memory. However, many application require TCP/IP even if they run on the same box with SQL Server. That’s why I recommend to leave TCP/IP enabled and restrict access to local host only.

SQL Server Configuration Manager

  • To do that we need to disable listening on all network adapters

TCP IP properties

  • Choose network adapters and port numbers that SQL Server will be listening on. In our case we want to leave only local loppback for IPv4 and IPv6. That means that local applications and services will be able to access SQL Server by using ‘localhost’ name and loopback IP address 127.0.0.1 (::1 for IPv6)

TCP/IP Properties disabled

  • Restart SQL Server service and check SQL Server logs to make sure that server is listening on locahost only (127.0.01 and ::1). Make sure that no other IP addreses are on the current log.

Log File Viewer

Now your SQL Server available only on the local computer and unavailable from the network.

The only downside of this configuration is that you need to run SQL Server Management Studio and SQLCMD as any other application from the server itself. Hopefully you have remote desktop access to the server.

By the way, be aware that unchecking  ‘Allow remote connection to this server’ under Server Properties in SSMS doesn’t prevent client connections. This option is for server-to-server communication only and controls execution of stored procedures from local or remote servers.

The method described above should work in SQL Server 2000, 2005, 2008, 2012. If you experienced any issues with that approach please share your experience in the comments.

How to move FlightRecorderCurrent.trc and msmdsrv.log

Quick and dirty tip on how to change a location of FlightRecorderCurrent.trc and msmdsrv.log files to the different path.
When you install OLAP Service it puts these files under Log Directory. FlightRecorderCurrent.trc and msmdsrv.log can grow really fast, so you might want to move them on a different drive.
To change the default location:

  1. Connect to Analysis Service in SQL Server Management Studio
  2. Right-click your server and select Properties.
  3. Change LogDir location to the different one
  4. Restart OLAP Service
  5. Remove old FlightRecorderCurrent.trc, FlightRecorderBack.trc and  msmdsrv.log

You can disable Flight Recorder feature altogether by changing Log \  FlightRecorder \ Enabled to False.  Analysis Services log file msmdsrv.log cannot be disabled, but you can control the location as described above.

How to create linked server to SQL Server instance itself (loopback)

I’ve been using this configuration at least for 10 years starting with SQL Server 2000 and was surprised when realized that not many DBAs know about it.

Basically, the idea is to have one or several linked servers pointing to the local instance itself. There are several situations when it might be helpful:

1. In a test environment we can use it for learning of different features of linked servers. This is how I found out about this trick.

2. On development servers we can simulate distributed nature of a production environment. An alternative approach would be using named instances on the same server, but it would require more resources on the development server, especially if your environment consist of more than 2 instances.

3. In production we can use it for a consolidation of several distributed databases in one instance. So, if you have an application that references databases on 2 separate instances of SQL Server and you need to move them to one server you don’t need to change application code right away.

It doesn’t mean that I advise to use it in a production environment instead of changing code of an application. Using a linked server in your code will always have some additional overhead comparing to local calls, but I’ve been to situations when changing all code required longer time than was allocated to this task and the following trick was handy.

So, if you try to use a “Create Linked Server” window in Microsoft SQL Server Management Studio and provide a local name or alias you will get the following message:
“Microsoft SQL Server Management Studio.
You cannot create a local SQL Server as a linked server.”

You have 2 options to solve this problem:

1. You can connect to the server by using a different name or alias. Let’s say you need to reference server X with 2 additional aliases: Y and Z. So, to test a distributed application using just one server X you must create 3 linked server objects: X,Y,Z. If you connect to the server using it’s name (X), you will have no problems creating Y and Z linked server objects, but creation of the linked server named ‘X’ will fail with error message mentioned above. All you need to do is to connect to server by using one of aliases (Y or Z) and you will be able to create ‘X’ linked server object without any problems.

2. Just use a stored procedure instead of SSMS. Here is a code for example above:


EXEC master.dbo.sp_addlinkedserver @server = N'X', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'X',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_addlinkedserver @server = N'Y', @srvproduct=N'Any', @provider=N'SQLNCLI10', @datasrc=N'X'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Y',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_addlinkedserver @server = N'Z', @srvproduct=N'Any', @provider=N'SQLNCLI10', @datasrc=N'X'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Z',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

Assuming that all aliases for X,Y,Z were configured properly in DNS it should work like a charm.

How to choose a name for Linked Server object in SQL Server

At first glance, there is nothing to talk about. Linked server object name defined as a sysname datatype and it means that it is an Unicode string with no more than 128 characters. Hopefully, you won’t use even half of the maximum length. Shorter is better, but there are few tips that might be helpful:

  1. Don’t use generic names like ‘LINKEDSERVER’ or ‘APP’. The name has to give a basic idea where this linked server pointing to without going into details. Not every user (even developers) can see options of linked server objects and they’ll start bugging you or other DBAs with questions.
  2. Don’t use common words that can be found anywhere in the code. Sooner or later you’ll need to find all references to this linked server in a database and it will be painful to look for all occurrences of the same string, especially if it’s a part of the tables’ or columns’ names.
  3. Don’t use an instance name as a linked server name. When you start moving databases around you will quickly find out that the instance name is not relevant anymore. Even moving a copy of databases from production to development or test environments will become problematic.
  4. Create a new linked server for every new application even if an existing linked server object can serve the purpose. In many cases you will find that permissions required for one application are not exactly the same as for another one. As the result one application or another will have higher level of access that it’s required. Troubleshooting on another side of the database link also will be easier if you have separate users associated with different applications.
  5. Ideally a name should identify an application name of the database this linked server pointing to and a name of the application it’s going to be used by. In this case everybody will see where they are going to and which application it should be used in.
  6. Stick to a naming convention in all instances of SQL Server. So, if you use ‘DYNSAGE’ linked server everybody would now that it is used ‘from’ Microsoft Dynamics ‘to’ Sage ERP and not other way around.

If you have other tips please leave them in the comments section. Thanks!