Untangle Database Names

Generally, when you set up a database, you call it 'SALESDB' or whatever, and that's the end of it.  The logical file names will default to SALESDB.MDF AND SALESDB_log.LDF, and so will the physical names on disk.  But over the years those names can get changed.  In my case I am in the middle of migrating databases from one system to another, and part of that involves changing the names which were created by a company which was taken over - you could consider it as airbrushing traces of the old system out of existence, I couldn't possibly comment. 

I found a good explanation in this MSSQLTip.    In their case, the project had changed and the old name was no longer relevant.  It could be a typo that irritates you enough to do something about it.  It could be that you are anally retentive enough that, like me, you like your database names to be consistent.  

Consistent?  Well, consider - the database name that you see every day isn't necessarily the underlying logical name of the database files.  Still less can you be sure that it is the name of the physical .mdf and .ldf files on disk.   Here's the Official word.  But what this means is that there can be three names - the name of the database, the logical name of the database files, and the physical name of the files on the disk.  They can all be different if you want them to be.

Caution - that way lies madness...

What I'll try to do in this post is untangle these various names and explain how to change them to be consistent.  SQL Server is reasonably easygoing as to what you call your database - but be sensible about it.  

First of all, get details of the database. The stored procedure sp_helpdb provides quite a lot of information about the database, (more than is shown here) but the results below are what we are interested in.  The name in square brackets is the name of the database.  Column 1 contains the logical names of the two database files.  Column 3 contains the physical names of the .mdf and .ldf files as they are found on the disk. Save this somewhere handy like an Outlook Task - you'll want to refer to it later. 

exec sp_helpdb [DQTHL]

DQTHL                 1              E:\MSSQL\Data\DQTHL.mdf

DQTHL_log          2              F:\MSSQL\Log\DQTHL_log.ldf

Let's all dance around singing halleluiah! Everything is consistent - the logical and physical names all match the database name.

If it was always so, there would be no point to this blog.  But it ain't like that all the time.  In a sense, it doesn't really matter - SQL Server would tolerate my next example, even though the database name doesn't match the logical file names, and the physical file names are something entirely unrelated.   It keeps track of what things are called and where they are.  The problem comes when a human being gets involved - maybe you want to do some housekeeping, and discover that the names don't tie up.  Humans are not good at telling which file belongs where, especially if there are lots of them, all with similar names. 

exec sp_helpdb [DQTHL]

  

DQ_Data                1              E:\MSSQL\Data\OldSales.mdf
DQ_log                  2              E:\MSSQL\Log\OldSales_log.ldf

OK, first step is to make sure that nobody else is doing something with the database.
You might get away without bothering, but you know what Sod's Law is like...

-- get exclusive access

alter database  [BEC_BASES]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Go

It's easy to change the database name - just right click on it and Rename is one of the options.  There's also a stored procedure you can use:

EXEC sp_renamedb 'old_name' , 'new_name'

 

However, I have a feeling that this is deprecated (meaning that Microsoft will wait until the most inconvenient time for you and then drop it).  Use Alter Database instead:

--Change DATABASE name

ALTER DATABASE  [BEC_BASES]

       MODIFY NAME = [BASES]

Go

Then we need to change the logical name of the files.  Remember we did sp_helpdb a few minutes ago?  This will give you the current names.  Almost always you will have an MFD and and LDF file,  but you could also have one or more NDF files as well, and in fact you can use any suffix you want. I came across a database where one of my predecessors had renamed the LDF file as MDF, so that there were two MDF files on the disk and no LDF files. 

Caution - that way lies madness...

-- Change Logical name

ALTER DATABASE [BASES] MODIFY FILE (NAME=N'BEC_BASES', NEWNAME=N'BASES')

GO

ALTER DATABASE [BASES] MODIFY FILE (NAME=N'BEC_BASES_log', NEWNAME=N'BASES_log')

go

Then we want to change the names of the physical files.  I used to Detach the database, change the file names, and then re-attach, and that's fine, but I think this way is actually easier.

Let go of the database (or you won't be able to do the next bit):

use master

go

Take the database offline

ALTER DATABASE BASES SET OFFLINE

GO

Tell it the file names that you are going to use:

ALTER DATABASE BASES MODIFY FILE (NAME = BASES, FILENAME = 'E:\MSSQL\Data\BASES.mdf')

GO

ALTER DATABASE BASES MODIFY FILE (NAME = BASES_log, FILENAME = 'F:\MSSQL\Log\BASES_log.ldf')

GO

The files are modified in the system catalog. The new path will be used the next time the database is started.

Now go to File Explorer or however you like to carry out operating system tasks like Rename.  I suppose in theory that you could do this with XP_CMDShell, but you would have to remember to enable it and then disable it again afterwards - more trouble than it's worth.

Navigate to the data folder and rename the MDF file, then to the log folder and rename the LDF file. 

Put the database back online

ALTER DATABASE BASES SET ONLINE

GO

Open up the access to everyone (remember you made it single user?)

ALTER DATABASE [BASES]

       SET MULTI_USER

And just to be sure that you have done everything right, run sp_helpdb again and check the results against your original:

exec sp_helpdb [BASES]

DQTHL                 1              E:\MSSQL\Data\DQTHL.mdf

DQTHL_log          2              F:\MSSQL\Log\DQTHL_log.ldf

We've untangled it!  Let's all dance around singing halleluiah!  

The Curse of SQL Server Embedded Edition

Help!  The database is writing a log file which has filled up drive C of server XYZMGT02!

Huh?  That isn’t one of our database servers – in fact I’ve never even heard of it!  Not only that, I don’t even have permission to log onto it!  Nuffink to do with me, guv!

 It turned out that there was a database involved, sure enough, which is why the DBA team got called.  But it wasn’t something that we had ever set up.  Windows Server Update Services or WSUS  downloads updates from Microsoft and sends them out to the computers in the corporate network.  It runs under a freebie cut-down version of SQL Server called Embedded Edition  - SSEE for short – and not unlike Express Edition, when you want to manage it, the things you need have more often than not been disabled. 

The underlying problem in this case was that normally, updates get distributed to the network and can then be purged from the WSUS system.  But if for some reason a computer on the network is unavailable, that update cannot be delivered, and therefore it is not purged.  Drive F:\ which contains the WSUS data had filled up.  And then the software writes a message in the log on Drive C to say something like:

“Could not allocate space for object 'dbo.tbXml'.'PK__tbXml__0E6E26BF' in database 'SUSDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."

53,881 error messages – all but a dozen say that. Keep on writing that message for long enough, and you fill up 10 Gb of Drive C, which then grinds to a halt, bringing the whole server down.  

Now in an ideal world I would have configured that log so that it gets located somewhere else - drive D has twice the space on it, and even if it filled right up, it wouldn't give the server heart failure.  But as far as I can tell, there is no way to change the destination drive - the edit option has been disabled.  Alternatively I might get SQL Server to send an email message to the WSUS administrator - but email has been disabled too. 

Hmm, tricky.  Let's think about those error logs for a minute.  By default, SQL Server carries on writing an error log until it gets restarted - which might mean forever.   This can mean that the error log gets very large indeed, and slow to open if you ever want to have a look at the contents.   So on most of the servers I work with, I like to create a new log every month, by setting up an agent job to run this:

exec master..sp_cycle_errorlog

exec msdb..sp_cycle_agent_errorlog

That's one for the error log, and one for the agent error log - which of course doesn't exist in SSEE (duh, because it has been disabled).

Again by default, SQL Server keeps the current log, plus the six previous logs.  This seems very sensible  - you are probably never going to want to check further back than six months.  And you can change that default if you do.  

But in this case we don't have room on the disk to save all that stuff, and since every error message is in effect identical, we don't really care.  So what I did was set up a scheduled task to cycle the error logs daily.   So it retains the error messages for the past seven days, and then slings them.  

A scheduled task is a Windows option, and not nearly as flexible as SQL Server Agent - but if you can't use Agent , it can come in handy.  

So - I created a folder called scripts on drive C.  

I created a text file called Cycle_Errorlog.sql which contains exec master..sp_cycle_errorlog
 

I created a text file called Cycle_Errorlogs.bat which changes to Drive C, goes to the correct directory, and runs SQLCMD with the SQL script above.  Notice that the connection string to the embedded edition is a bit weird - full details here

C:\
cd\Program Files\Microsoft SQL Server\90\Tools\binn\
sqlcmd -E -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i "c:\scripts\Cycle_Errorlog.sql"

And I set up a scheduled task to run the batch file daily.

Three months on, WSUS is still filling up Drive F with updates that can't be deployed, the WSUS Administrator is tearing his hair out, but drive C has plenty of room, and the server isn't crashing. 

Book Review - The Phoenix Project

A parable of life for IT folk, told from the point of view of mild-mannered Bill Palmer who is suddenly promoted out of his comfortable middle-management niche to Vice President of IT Operations. Then everything starts to go wrong.  

The payroll fails. This is a BAD thing. Trying to fix it, they mess up the SAN (storage area network), another bad thing. Bill and his team sit down to create a change management system to stop this from happening in the future. Then the auditors strike - to comply with the rules, they have to do something about a stack of issues six inches high. But they can't do that because the number one priority is Phoenix, which will save the company from bankruptcy (yet another bad thing).

Luckily Bill has the advice of Erik his mentor to fall back on, as well as his common sense. They beat back the dreaded auditors, help Phoenix limp into production, and introduce far better ways of doing things which rapidly overtake Phoenix and leave their competitors struggling in their wake. I say a parable, rather than a novel - the authors want you to behave in a certain way with your IT and so they show the mistakes to avoid and good practices for you to follow. And surprisingly, I rather enjoyed it.

Size Property is NULL so indexing fails

Here’s an oddball story.  As part of the weekly maintenance plan, I rebuild the indexes.  Usually this is fine – even on big databases – they have a whole weekend to sort themselves out in, after all.

But then one day I got a message to say the job had failed

Executing the query "ALTER INDEX [IX_Events_eventId] ON [dbo].[Events] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY  = OFF, ONLINE = OFF )

" failed with the following error: "Could not allocate a new page for database ‘Gandalf01' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Insufficient disk space?  Thinking that 2 terabytes ought to be enough disk space to turn round in for most DBs, I right clicked on the database and asked for properties - it told me that the property size is not available. 

One of the joys of SQL Server is that there are almost always at least two ways to skin a cat.  I ran this code:

SELECT SUM(size)*1.0/128 AS [size in MB] FROM [Gandalf01].sys.database_files

It worked and told me the database size (and as I suspected, it wasn't much).

I tried the GUI again and this time the property dialog came up fine and showed me the size, as expected.

I tried my re-index again and it worked. 

I've seen something like this before - the GUI refuses to tell me the database properties because the database owner has somehow got set to NULL. 

EXEC sp_helpdb   --reveals that the database owner is NULL and the GUI refuses to work

EXEC sp_changedbowner [SA] -- changes the owner to SA and the GUI now works.

Conceivably the owner is someone who has now left - given that Fred has left and his access removed, it doesn't seem unreasonable that his database might no longer have an owner.  But how can it no longer have a size?  I would be interested to know if anyone has an explanation (other than "It's a bug, Jack") 

So, Farewell Then, Port 1433

The default port for SQL Server is 1433.  So what port will a hacker breaking in to your system try first?  Got it in one. 

Change the port, goes the theory, and you make life more difficult for a hacker to get at your systems.  It might not prevent a determined attacker, who just starts with Port 1 and goes on to 9999 or whatever the theoretical maximum is, but it will put off the casual browser.  Discussion here. 

It isn't difficult to do, although if you have lots of servers it can be a bit boring.  Here's a quick Summary:

• Log onto server and run SQL Server Configuration manager to set new port; restart services. 
• Turn on firewall and allow access to that new port.
• In SSMS registered servers, specify Servername,Port e.g. SQL2012Dev,4321

Here's a step by step procedure with a little bit more detail. 

1)   First of all check which port is currently in use:

exec master..xp_readerrorlog 0, 1, N'Server is listening on' 

GO

The answer will probably be 1433, but just check that your predecessor hasn't done this exercise for you already! 

2)   Log on to the server using Remote Desktop Connection


3)   Run SQL Server Connection Manager.  There are a couple of places where it specifies the port number.  First select SQL Native Client 11.0 Configuration / Client protocols / RCP/IP
– and set the default port to your chosen value:

Next one is SQL Server Network Configuration / Protocols / tcp/ip / ip addresses.  There are six ports - set them all to your chosen value.

Your changes will not work until you stop and restart the SQL Server Services:

4)   The next step is to modify the Windows Firewall so that it allows your chosen port to get through the firewall. On the Administrative Tools menu, select Server Manager, and then Configuration and Windows Firewall. The Firewall should be on, but if it isn't you had better turn it on.

This is what it looks like if the Firewall is OFF:

Right click on Windows Firewall on the left of the screen, and select Properties from the pull down menu that appears.  If the Firewall State is Off, set it to On (recommended) for each of the three profile tabs. 

 



This is what it looks like when the Firewall is ON:

 

5)   Select the Inbound Rules from the menu on the left hand side.  SQL Server is probably close to the top, but you may need to sort the columns. 

In the Properties, select the Protocols and Ports tab, and specify the port of your choice. 

That was the Inbound Rules - now repeat that for the Outbound Rules. 


6)   Now go back to SQL Server Management Studio, and edit the Registered Server information.  It doesn't need much, just enter a comma after the server name and then the new port number
e.g. SQL2012Dev,4321

7)   And then just to be sure, log on to that server and go back to the code we started with:

 

 

 

exec master..xp_readerrorlog 0, 1, N'Server is listening on'

 

GO

 

 

The answer should be the new port number.