Thoughts and experiences of a DBA
DBCC CHECKINDENT can be used to reset a tables identity value. So I will create a small table to demonstrate this. CREATE TABLE Table_1 ( ID INT IDENTITY(1,1) NOT NULL, DateAdded DATETIME NOT NULL ) Insert some rows…. INSERT INTO Table_1(DateAdded) VALUES(GETDATE()); INSERT INTO Table_1(DateAdded) VALUES(GETDATE()); INSERT INTO Table_1(DateAdded) VALUES(GETDATE()); INSERT INTO Table_1(DateAdded) VALUES(GETDATE()); INSERT INTO [...]
I’m going to be looking at sp_change_users_login as a continuation to a previous post where I looked at a couple of ways to transfer logins from one SQL Server to another and touched upon the issue of the orphaned ”security identifier” (SID). A typical scenario that arises is when the DBA quickly realises that the logins on the [...]
With any server migration, ideally you want things to run smoothly and re-creating SQL Server logins from scratch is not something you really want to or should be doing. I am currently involved in a complete server replacement for one of our SQL Server clusters. In this environment, mixed authentication is enabled. Back in the [...]
It’s important to be able to determine SQL Server version number. DBA’s need to know what SQL Server version numbers they are supporting so that during patching windows, they can source and apply the correct patches or service packs. This information is useful as a reference to what issues may be contained in a certain [...]
This post applies to both MySQL and Microsoft SQL Server as both these products allow you to query something called INFORMATION_SCHEMA. What is INFORMATION_SCHEMA? It is database metadata. In MySQL, there is a complete virtual database dedicated to it called “information_schema” and in Microsoft SQL Server, there are views which you can query, all prefixed [...]
Auditing SQL Server logins is done by way of writing audit information to the SQL Server Logs found under “Management->SQL Server Logs” in Management Studio. In order to enable auditing of SQL Server logins, a simple operation needs to be performed by changing settings in the properties of the SQL Server. Auditing levels vary. It’s [...]
I came across a problem this week with one of our SQL Servers whereby one of the drives was very low on space. Whenever I come across a disk space problem, I use my trusty friend Treesize which is a free download and it enables me to quickly find where the space is being consumed. [...]
In my last post on sp_who2 I demonstrated how this handy utility can help you in your troubleshooting efforts. Continuing on from this, I am going to cover DBCC INPUTBUFFER What is DBCC INPUTBUFFER ? It is a command used to identify the last statement executed by a particular SPID. You would typically use this after [...]
If you haven’t used sp_who2 before then it is a great utility to help in diagnosing a problem with your database application. What is sp_who2 ? It’s a stored procedure which is installed with SQL Server which when run, outputs a row for each “SPID”. SPID stands for Server Process ID and one is created [...]
I was recently asked, what are the differences between primary keys and unique indexes? Well, they are very similar but here are the differences. Only one primary key is allowed on a table but multiple unique indexes can be added up to the maximum allowed number of indexes for the table (SQL Server = 250 [...]
Database views are virtual tables which are built up using a SELECT query. The query may contain one or more tables. To help explain database views, here is a quick script created using SQL Server to create some tables and data. (If you want to create the tables using MySQL, then substitute the part “IDENTITY(1,1)” [...]
Recent Comments