I came across a couple of subtle gotchas around the permissions required to restore a database in SQL Server 2008+ recently. I’m interested in situations where a DBA needs to allow certain users the ability to restore a given database, for example refreshing UAT, while maintaining minimum permission levels… Read more
There’s a lot of things to consider when migrating databases between SQL Server instances. I want to provide some tips for dealing with Database Master Keys, and in particular the case when those keys are encrypted by the Service Master Key.
Before getting into the details, let’s take a quick… Read more
Actually I’m not sure if anyone still believes this anymore. If you read the BOL description carefully it states that truncate de-allocates the pages rather than deleting the rows – meaning only the de-allocations are logged – resulting in far fewer log records. Incidentally this is also why the truncate… Read more
Here are a couple of quick database and table level queries that I have found useful lately.
How many rows in each table of a database?
The first temptation with this one was to use something like sp_MSforeachtable with a count(*), until I recalled that the catalog view sys.indexes has… Read more
A question that a DBA will often be asked is what databases are actively being used on my SQL Server? We get asked it all the time, usually as part of a consolidation or rationalisation project.
Page 9 in file 1 (of the primary filegroup) in a SQL Server database is the header page and contains a wealth of metadata about the database. We can have a look at this with DBCC PAGE or DBCC DBINFO.
In order to redirect the output of the DBCC command… Read more
A lot of a DBA’s time is spent managing and investigating transaction log files.
A commonly used tool is the command:
which returns a list of all databases, the size of their transaction log file, and the percentage of that space that is used for active transactions. One… Read more
In SSAS we build what’s known as a UDM, or Unified Dimensional Model, over the top of a database schema, usually a data warehouse. The UDM consists of several objects designed to aid analysis of the data. The UDM is built in BIDS (Business Intelligence Development Studio) and in terms… Read more
“You’re only as good as your last restore”
I’ve no idea who originally said that – but it probably qualifies as DBA folklore. I blogged recently about backups in SSAS so to close the loop here are the basics of restoring SSAS databases.
In the GUI:
Connect to the SSAS… Read more
Analysis Services databases should be backed up at regular intervals like any other database. Here are the basics.
Using the GUI: Connect to the SSAS service in SQL Server management studio. Right click on the database and select backup.
The Backup dialog presents a number of options.
Allow file overwrite… Read more
SQL Server stores its authentication mode in the registry. The registry can be read and updated using the T-SQL commands xp_regread and xp_regwrite.
Step 1: Construct our registry key path, run the following query and record the result:
DECLARE @InstanceName NVARCHAR(1000), EXEC master..xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\', N'MSSQLSERVER', --for…
I’ve been doing a fair bit of research and investigation into the behavior of the plan cache recently. So I was concerned when I read somewhere that the reconfigure statement causes the SQL Server plan cache to be flushed. If this is true then it is a pretty crucial piece… Read more
An interesting case arose at work the other day. SAN issues had caused SQL Server to shutdown and it was unable to be restarted because the data files were unavailable.
Once the SAN was back online the instance still could not be restarted. The error logs were reporting that tempdb… Read more
Service Broker is SQL Server’s internal messaging system. It has been designed as an asynchronous, reliable and transactional messaging system for inter or intra database messaging, or even inter SQL instance messaging.
It is a powerful tool for decoupling extended transactions across time and across databases. It is designed to… Read more
I was curious to look at the effect of database options, on the SQL server plan cache.
I have restored the Adventure works sample database onto my SQL Server 2008 R2 RTM instance four times as AW1, AW2, AW3 and AW4.
I have used, and slightly modified, a query from… Read more
In order to query the configuration settings of a server you can run this sproc:
On a default install of sql server this will return 16 rows. In order to see all rows you need to run this:
exec sp_configure 'show advanced options',1 reconfigure
Now when you run… Read more
Replication is one of the more complex of the SQL native “HA/DR” technologies. There are a lot of moving parts. Here are the steps to configure a basic transactional replication topology, with a publisher and distributor on the same instance and using push subscriptions. Most of the stored procedures have… Read more
I was working with a group of students on a design brief. The brief was to partially implement a database design based on the LinkedIn web site. A discussion came up around the possibility of implementing the contact preferences (essentially a set of boolean values) as bitwise data, so I… Read more
This is a very cool and useful tip if you find yourself locked out of the sysadmin role in sql server.
I have a SQL Server instance with the sa account disabled and all other logins removed to simulate a situation were I am locked out of SQL Server. My… Read more
I came across an issue today where a backup process was in a blocked state with a wait type of MSSEARCH. The issue boiled down to an issue with communication/contention between the SQL Server engine service and the SQL Server Full Text Search service.
An attempt to kill the backup… Read more