Infrastructure at your Service

SQL Server 2008 Archives - Blog dbi services

Stéphane Haby

How to be sure that tempdb size is good to run a DBCC CHECKDB?

By | Database Administration & Monitoring, Database management | No Comments

In many blogs or forums, you can read that the answer is to use the option: WITH ESTIMATEONLY. With this option, you can easily have the space estimation needed to check the database in tempdb. But be careful, only since SQL Server 2014, this estimation has been good! See the PS from Paul Randal’s blog for this information, here But between SQL server 2008, 2012 and 2014…and 2016, the result of this query changed!  

 
Read More
Stéphane Haby

SQL Server tips: how to list orphaned logins

By | Database management | No Comments

I read a lot of about orphaned database users in SQL Server, but I have almost never read about orphaned logins. Many of my customers migrate or remove databases in SQL Server. They forget – not every time but often – to remove the logins and jobs associated with these databases. I have created a script – without any cursors, YES, it is possible – allowing to search all logins who are not “attached” to…

 
Read More
Stéphane Haby

SQL Server tips: Executing a query with the EXECUTE command

By | Database management | No Comments

This short SQL Server blog post is meant to help people who have experienced the error messages 2812 and 203 with the EXECUTE command. The goal is to execute a simple query from a variable in a string format with the EXECUTE (exec) command. Very easy, isn’t it? Ok, let’s GO! If I execute “select * from sys.databases” in SSMS, in SQLCMD or in PowerShell – no problem, I have the list of all databases…

 
Read More
David Barbarin

SQL Server: DBCC CHECKDB does not detect corruption

By | Database management | 3 Comments

During my audits at customer places, it still happens very often to find SQL Server databases with page verification option configured to “none”. I always alert my customers on this configuration point because it can have an impact on the overall integrity of their databases. One of my customer told me that the integrity task of its maintenance will detect the corruption anyway and alert him by email – but is it really the case?…

 
Read More
Stéphane Haby

SQL Server: How to find the default data path?

By | Database management | 2 Comments

I have read a lot of SQL Server blog postings and articles in order to find the default data path. This post covers different SQL Server versions (SQL Server 2012, SQL Server 2014, SQL Server 2008, SQL Server 2005) and provides a generic script with different methods. Search with SERVERPROPERTY Since SQL Server 2012, we can use the parameter “InstanceDefaultDataPath” in the T-SQL command SERVERPROPERTY. More information on msdn here. Test with SQL server 2008…

 
Read More
Stéphane Haby

SQL Server 2012: new permissions

By | Technology Survey | No Comments

For this end of the year, I decided to present you the new permissions on SQL Server 2012. How to have a list of all permissions ? It’s very simple with this query: SELECT * FROM sys.fn_builtin_permissions(”) SQL 2008 R2 SQL 2012 With this comparison, you can see that there are 19 new permissions in SQL Server 2012. To have a great overview, I prefer use this query ordering by the class description: SELECT class_desc,count(*) as Permission_Number…

 
Read More
Stéphane Savorgnano

SQL Server 2012 SP1: First Cumulative Update (CU1) now available

By | Technology Survey | No Comments

A couple of days ago (20 November 2012), Microsoft rolled out the first cumulative update (CU1) for SQL Server 2012 Service Pack 1 (SP1). It’s just two weeks after the release of the first Service Pack (SP1) we are talking about here. What this first CU for SQL Server 2012 SP1 features: This CU1 contains 44 hotfixes: 33 for the Engine 3 for SSAS 4 for SSRS 4 for SSIS If you want to have…

 
Read More
Stéphane Haby

SnapManager for SQL Server: databases limitation policy

By | Database management | No Comments

I recently talked to a customer about SnapManager, its recommendation on the number of databases and about a way to prevent the creation of more than 35 databases. The NetApp recommendation is no more than 35 databases on a single volume. The reason is that SQL Server has to use 4-5 worker threads per database in order to manage the freezing and un-freezing I/O for each database. Look at this note: More information on this…

 
Read More
Stéphane Haby

SQL Server 2012: Cumulative Update 3 (CU3) now available

By | Technology Survey | No Comments

This week, the third cumulative update was made available on the Microsoft website. What is in this CU for SQL Server 2012? This CU has 36 hotfixes: 25 for the engine 1 for SSAS 4 for SSIS 6 for SSRS To have more information and download it, click here Historic of SQL Server 2012 If you update your SQL Server 2012, you will have a new build version: 11.00.2332 I will give you a quick…

 
Read More