Collation

Collation can be set at all different levels in SQL, from the server, down to the column level. And it's a pain when it does't match up. Fortunately, there is a fast workaround for T-SQL queries.

SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid = b.employeeid

If the collation on employeeid in table a does not match the collation for employeeid in table b, there will be a nasty error, along the lines of "Cannot resolve collation conflict for equal to operation"

Easy enough to deal with - tell SQL to use what *should* be the default for the database(s) in question. This worked when I was dealing with two databases with totally different collations on the same server - the default server collation didn't trickle down to a database imported from another, older, server.

SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid COLLATE DATABASE_DEFAULT = b.employeeid COLLATE DATABASE_DEFAULT

This is a lot simpler to implement than any solution that involves trying to re-collate one of the columns to the other table/database/column's collation. It's vastly more generic.

Execute xp_cmdshell

Now: we don't enable xp_cmdshell lightly, as it allows a degree of control over the operating system that renders the SQL server vulnerable. However, when we do enable it, it can only be executed by members of the sysadmin role. Sometimes, this isn't quite what we want: we only want the account to be able to execute xp_cmdshell, not to be able to muck about with other SQL system settings.

So.

First, make sure that you have SA access on the server yourself. Then make sure the account that you want to use has access to the relevant databases on the SQL server. It can be a SQL user account or a Windows account: it doesn't matter. For the purpose of this snippet, I'm using a SQL account called 'user'. I'm going to go out on a limb, and guess that you already know how to create an SQL user account.

Then, grant the user execute rights on xp_cmdshell

GRANT EXEC ON xp_cmdshell TO [user];

Next, we need a proxy account which does have sysadmin access, and we'll use this account to run xp_cmdshell to run calls by users who are not members of the sysadmin role. This account must be a windows account. Give it access only to those drives and folders which it requires for the purposes of executing anticipated xp_cmdshell calls.

The proxy created is ##xp_cmdshell_proxy_account##, and in order to create it, you need both the Windows Login which has sysadmin access and its password.

EXEC sp_xp_cmdshell_proxy_account ‘domain\sysadminlogin’, ‘password’;

Check that this has created by querying the sys.credentials table

SELECT * FROM sys.credentials

Finally, verify that the SQL user has access to execute xp_cmdshell with the following code

EXECUTE AS LOGIN = 'user'
GO
EXEC xp_cmdshell 'whoami.exe'
REVERT

The result should be the Domain\Sysadmin account specified earlier.

How far has my backup or restore got?

I had a backup that was hanging: eventually I ended up restarting the server. It wasn't running through the GUI of SSMS, though. It was running from a process on the server, some 3rd party software. Stopping the software didn't stop the backup - it wouldn't. It's a file operation, and these things are pretty-much unkillable within SQL. Stopping the backup was possible solely by restarting the Full Text Search Engine on the Server (and, because there were two instances of SQL, both instances of FTE had to be restarted). However, I didn't quite twig this early enough in the process. It proved impossible to stop SQL because the backup was hanging. Eventually we powered down the server. However, I could tell how far the backup had got. A very nifty bit of script:

SELECT r.session_id,r.command,

CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],

CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

CONVERT(VARCHAR(1000),(

SELECT SUBSTRING(text,r.statement_start_offset/2,

CASE WHEN r.statement_end_offset = -1 THEN 1000

ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))

FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

It's based on the sys.dm_exec_requests view, so works for SQL 2005 and above. The code was here but had a slight typo which meant it wouldn't work for restores in the first instance.

It still doesn't solve the problem of SQL sitting on 100% for a restore for an age and a bit - this is due to the way SQL restores work. First, they restore the data to the files, in the data-copy phase: this is the bit that racks up to 100%. Then, any committed transactions that were present in the transaction log at backup time are rolled forward, and any incomplete transactions are rolled back. This is the redo and undo phase, and SQL doesn't record the progress of this. If the log file is particularly large, and the database is in SIMPLE recovery, this can take quite a while to work through...

Code Snippet

To execute sp_helprotect on all databases on a server, saving the results into a temporary table, and then displaying the results, including the database name. Makes use of sp_msforeachdb, an undocumented stored procedure which is very nifty for cycling through each database on a server.

It took me a while to work out how to do what I wanted to do...

This code is provided as-is, with no warranties. Test it before using it in earnest: and if you want to share the joy, please credit me. I wouldn't have managed it without the code here.

CREATE TABLE #temptable

(

[Owner] VARCHAR(50),

[Object] VARCHAR(100),

grantee VARCHAR(50),

grantor VARCHAR(50),

protecttype VARCHAR(50),

[Action] VARCHAR(50),

[Column] VARCHAR(50),

dbname VARCHAR(50)

)



DECLARE @STATEMENT VARCHAR(500)



SET @STATEMENT = 'Use [' + '?' + ']
INSERT INTO #temptable
([Owner],
[Object],
Grantee,
Grantor,
ProtectType,
[Action],
[Column]
)
EXEC (''sp_helprotect'')
UPDATE #temptable SET dbname = db_name()
WHERE dbname IS NULL'



EXEC MASTER.dbo.Sp_msforeachdb @command1=@STATEMENT



GO



SELECT *

FROM #temptable

ORDER BY dbname



GO



DROP TABLE #temptable
GO

Database Mail and Reporting Services: a high level how to

Preparation:
Set up SMTP Mail account, ensuring that the SQL Server's IP Address is whitelisted against the internal relay if required (this allows emails to be sent internally).
Ensure AWE addressing is enabled.

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'awe enabled', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

If necessary, follow the instructions to enable the Lock Pages in Memory Option, according to company security policies, and restart SQL.

Configuration

Easiest to do this using the Database Mail Configuration Wizard. The following steps make more sense if you step through the wizard at the same time.

• Setup Database Mail
• Enable Database Mail
• Create a new profile for SQL Server Mail
• Use the add button to bring up the dialogue to input the account details.
• Add in the SMTP details: your port and SSL settings should be double checked with the sysadmin.
• Chose 'Basic Authentication' to log onto the SMTP server, which uses the account that you've just specified, rahter than the SQL Database Engine or Anonymous Authentication (Anonymous Authentication should be avoided).
• Make this the default mail profile, and either set the privacy for specific accounts, or make it public.

Enable the Database Mails XP Parameter:

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'Database Mail XPs', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

Then test that the email is running OK, either by executing the following store procedure, or by right clicking on Database Mail and choosing 'Send Test Email'. Check the results in the Database Mail Log (it's an option in SSMS and can be found in the same spot as the SQL Agent Logs and the SQL Server Logs). Using the Database Mail Log is simpler than using the Database Mail Troubleshooting option (which didn't actually work for me).

USE msdb
GO
EXEC SP_SEND_DBMAIL @profilename = 'ProfileName'
@recipients = 'sweetsql@blogspot.com'
@subject = 'Test SQL Mail'
@body = 'Hurrah, this test has been successful!'
GO

Finally, it's necessary to configure Reporting Services to recognise the existence of the email, so that it can be used for an automatically generated scheduled emailed report. Use the Reporting Services Configuration Manager, add in the details of the mail profile and the smtp server, and save.

The reports that are emailed need to use a data source with saved credentials, rather than a data source which passes on the credentials of the user viewing the report. Happily, parameterised reports can be set up to use a specific set of parameters for emailing purposes: this dialogue can be found when you add an (email) subscription to the report.

SQL Configuration Manager Suddenly Unavailable

Last night, I installed SP1 onto a SQL 2008 Cluster. This morning, looking at another issue, I decided to mosey into the Configuration Manager, in order to double check which Ports SQL was operating on.

Imagine my horror when I got the following error, regardless of the account I used to try and open the tool:

"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers or above with SQL Server Configuration Manager. Invalid class [0x80041010]"

Googling around suggested several solutions involving changing folder permissions, and also running something called mofcomp. This in particular seemed relevant to my situation, where an installation had recently been run on the server. It would seem that, occasionally, the .mof files get corrupted during an installation. They're Managed Object Format files, and used for storing system configuration information.

They can be found in the Shared folder for the 32 bit components of your SQL Server, for SQL 2005 and above. Thus, on a 64 bit cluster, they were here:

C:\Program Files (x86)\Microsoft SQL Server\100\Shared

And running this command in cmd fixed the problem. Phew.

C:\Program Files (x86)\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files (x86)\Microsoft SQL Server\10\Shared\sqlmgmproviderxpsp2up.mof"

How to transfer logins and passwords

A couple of years ago, when I was a very junior DBA, we upgraded our main database (and everything else on the server), which contained all the information about everything at the University that employed me at the time, (students, faculty, staff, courses: you name it, it was in there) from SQL 2000 to SQL 2005, moving it across servers at the same time. This database was used by many members of staff on a daily basis, via a custom built Delphi interface. When it came to migrating all the usernames and passwords, we made use of this interface to log the users' keystrokes to find out their passwords, and input this all into a great script to recreate them on the new server. Where a user had not changed their password from the inital password given, or where a user's password did not fit the new server's security policies, or a user had not logged in during the window we were monitoring keystrokes, we reset the password. We then forced all users to change their passwords on their next login, and it worked pretty well. It wasn't my decision to use this method, and I'm not sure I'd chose it now: it all depends on the business needs.

At the moment, I'm setting up database mirroring. I need to transfer over the logins from my principal, source server to my mirror server. These are all system accounts, where I know the password (heck, it's in the password safe), but I don't want to create them all manually. Nor do I want to use IDERA's brilliant SQL Permissions tool - I don't need all the user account information - just to script out the logins themselves. After all, the user account information will be contained in the databases that I'm mirroring, once I've restored them to the server. Ideally, I'd like to script them all out in one go. Ideally, I'd like them to have the passwords in the script too.

Fortunately, I can: and without knowing the passwords. Microsoft have created a stored procedure, sp_help_revlogin, that can be used to transfer logins between instances of SQL 2005 and SQL 2008 on different servers (For SQL 2000 and SQL 7.0, check this KB article). It gets created in the master database (yes, I know, goes against all received wisdom about NOT making changes to the master database), along with a second stored procedure, sp_hexadecimal. Executing sp_help_revlogin results in a script that contains all the logins on the server, their default databases, and hexadecimal representations of the binary strings that hold login and password information. Run the script generated by sp_help_revlogin on the destination server, and the logins will be created. You will need to have databases with the correct names on the destination server, which need not be restored versions of the source databases, as the script will not run without the presence of the logins' default databases. They will not have access to connect to any of the databases, as user accounts have not been linked to the logins, but it's easy enough to do that (and there are many scripts available). If you can, it makes sense to restore the databases from the principal server to the mirror server, leaving them in a fully functional state, before running the logins script. And, naturally, if you're not setting up mirroring, then you would do this anyway. With mirroring, you need to make a call: create empty versions of the databases, and restore over them, or restore the databases twice over.

That I could, essentially, copy the logins and passwords across, without having to monitor anything, or know the passwords, was a bit of a revelation. I wish we had known this two years ago.

A response from Lightspeed

Remember this post? Well, I emailed Lightspeed. Their response?

"By default, the database is set to autogrow and autoshrink to save space."

In the last week, I have had the following.

Initial size 5430MB
Manually grown database to 8000MB
Autogrow to 8800MB
Autoshrink to 5430MB
Autogrow to 5900MB

There is plenty of space on the drive on which the database files reside (C:\, because Lightspeed recommend that SQL be installed on the C:\drive running under the local system account).

Moreover, autoshrink is set to false for every single database on the server, so I don't know what exactly is causing this shrinkage, but it ain't SQL. In fact, I would posit that it is completely inaccurate to say that the databases are autoshrinking, as this shrinkage does not show up in the 'Data/Log files Autogrow/Autoshrink' events part of the Disk Usage reports for the databases. It just happens, it doesn't show up in the logs, and it is really rather annoying me.

Credentials, Proxies, and SSIS SQL Agent Jobs

What I want to do:
Give one user access to see and execute one SSIS package as a SQL Agent Job, but none of the rest. Although the package exports data from one database on Server A to a new database on Server B, this does not count as a multi-server job in SQL terms. I've assumed that the package itself has been created and works as a SQL Agent Job under a sysadmin login, but I don't want to give this guy sysadmin access. He's a developer.

1. Create the credential, based on an existing login, on Server B. If you use a Windows login, it will want the correct password for that login, so don't get clever and make one up (can you spot the mistake I made?).





















2. Make sure that the credential's login has the correct access on Server B
3. Create the proxy, point it at the credential.
4. Add principals to the Proxy i.e. the accounts that you want to have the rights of the proxy when executing the SQL Agent Job.



















5. Create the job, and make the user's Login the owner of the job.
6. Give the user's Login the following rights in MSDB: db_ssisoperator and SQLAgentUserRole
7. Test.

I found it very helpful to test this process out using my non-sysadmin Windows account: it meant I could test out the process while being able to see all error messages on my own machine.

Doubtless, there are better explanations of how to do this out there: and I am not sure what I would do if I needed to allow multiple users to execute the same package under such constraints. I am sure someone will let me know...

Taking it futher

The awesome SQLRockStar came out with a nifty tip about fragmented Virtual Log Files. SQL has this tendency to split the transaction log file out into different chunks. It might be physically contiguous, but, logically, it is fragmented, and this affects performance.



The tip teaches us how to use Policy Based Management in SQL 2008 to work out which databases have extensive Virtual Log File fragmentation. So far, so good. But hark! I hear you cry! I only have one SQL 2008 Server. Everything else is still SQL 2000 or SQL 2005. There is no Policy Based Management tool there.


Panic not. If you have set up a Central Management Server in SQL 2008, and registered your servers within it, you can evaluate the Policy against all your servers. Just right click on the group you want to check, pick 'Evaluate Policies', choose your source (i.e. the SQL 2008 box on which you set up the initial policy), and let it run. It tends to look as though it's stuck if one of the servers in the group you've chosen isn't running SQL or is turned off, but leave it to run, and, eventually it'll stop trying to connect to that box, and move onto the next one.

Once you know what's up, you can follow the instructions here. I did make up a handy script to make life a little easier, and I cannot emphasise enough that you must back everything up properly before you get going. It will save a vast amount of heartache in the long run. Honest truly.

USE [databasename]
-- Backup the Log File

BACKUP LOG [databasename] TO devicename
GO

-- Set recovery to simple mode, otherwise the log file refuses to shrink

ALTER DATABASE [databasename]
SET recovery simple
GO

-- Shrink the log file

DBCC shrinkfile ( transactionloglogicalfilename , truncateonly )
GO

-- Grow it in ONE BIG STEP
ALTER DATABASE [databasename]

MODIFY FILE

(

NAME = transactionloglogicalfilename

, SIZE = newtotalsize --MB

)
GO

-- Set recovery back to full mode

ALTER DATABASE databasename
SET recovery FULL
GO

Finally, run a full backup, so that all future transaction log backups have something to work from.

(I used the instant SQL formatter to format the SQL for this post. It looks, um, interesting....)

Error Number: 3241 Severity: 16 state: 73

When performing a restore from a SQL 2005 .bak file to a SQL 2000 Server, the above is likely to happen.

Another hint that things aren't going to work is the "Name" field being filled with the value *** INCOMPLETE ***.

*head*desk*

SSIS Package fails from SQL Agent

I've been configuring a test version of one of our Virtual Servers, which was created by cloning the 'Live' server using VMWare. I managed to negotiate round the @@Servername issue , and made sure to rename the databases so that we were quite clear that we were on the test server.

It took a couple of days, and the addition of the new server into the SSIS package which checks for failed jobs, before I noticed that the Scheduled Maintenance Plans to rebuild indexes were failing on the new server: because, in part, they were trying to connect to the old server. So, I changed things around, pointed the plans at the new server, waited 24 hours, and still they failed.

Odd.

There was nothing in the job history for the Maintenance Plan, and only this in the Job History Log (servername blanked to protect the guilty).

"Unable to start execution of step 1 (reason: line (1): Syntax error). The step failed".

A similar job gave the error:

"The command line parameters are invalid. The step failed." Again, no errors...

Looking inside the job, the command line seemed reasonable, giving, as it did "/SQL "Maintenance Plans\Weekly Check Integrity and Rebuild Indexes" /SERVER Servername /CHECKPOINTING OFF /SET "\Package\Weekly.Disable";false /REPORTING E"

What I should have done next was run that in sqlcmd. What I did was start searching Google. I found a helpful discussion thread, and peered at the SQL Agent Job Step.

Now, given that the job had been scheduled via the Job Scheduler within the maintenance plan, all should have been well. However, it wasn't. There was a missing "\" in the Maintenance Plan path.

Browsing to the package using the button with the three dots to the right of the Package Path made sure I was looking in the right place: and, to my surprise, the Package Path changed every-so-slightly once I'd clicked OK.

A backslash appeared, and the job ran successfully.

Now, there are other reasons why an SSIS package would fail when called from a SQL Agent Job, and Microsoft describes them here: they apply regardless of the Service Pack you have on your SQL 2005 instance.

As far as I can tell, however, the issue I've described here only applies to SQL 2005 RTM. The server is on the list to be service packed, and, now that we have the test version, we can check that the third party software won't throw a wobbly before applying it to live.

All's well that ends well....

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

So, having decided to use the built-in Standard Reports on SQL 2005 to check how one of my databases had been auto-growing and shrinking, as the free space looked a little suspect (as did the fact that the database had shrunk between Friday morning and Monday morning for no apparent reason other than a reindex...), I got the error in the title:

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Fair enough. According to SQLSkills, this is a compatibility issue. I was using SQL 2008 Management Studio, and was checking a SQL 2005 database. Usually, this doesn't cause a problem, as the reporting tools were first introduced in SQL 2005.

Unfortunately, this database turned out to have a compatibility level of 80 - SQL 2000. Running the report in SSMS 2005 gave the following error:

Unabel to display the report because the database has a compatibility level of 80. To view this report, you need to use the Database Properties dialog to change the compatibility level to SQL 2005 (90).

This has the advantage of being rather more explicit, but none the less irritating...

Sometimes, it ought to be obvious....

Over the Christmas break, we moved our Raiser's Edge database files, so they weren't sitting on the c:\ drive (where they installed as default), but on the data drive for that server. One of my colleagues used the BlackBaud Manager for the system, rather than me doing anything clever in the back end.

At which point, the backup job, created via BlackBaud, failed.

First it failed owing to permissions. It decided that it couldn't connect to the (local) server. Easy enough to fix.

Then it failed owing to that hoary old chestnut "[SQLSTATE 42000] (Error 22029)". You know. That really useful error message that has you looking in six different places to find out why it failed: diskspace, file locked, unable to access backup file location etc etc etc. No SQL maintenance plan that I could right click and 'View History' - the thing's done by a T-SQL string invoking xp_sqlmaint. Well, the string *looked* OK: same as the version that did execute successfully when run manually.

Yup. String looked fine. Beautiful syntax. Until the second cup of coffee kicked in.

It helps if you're trying to backup a database that exists! In the process of moving the database files about, the database got renamed. The user connections got updated to reflect this new database name, but not the backup job.

Backups are now running.

Change DB Owner

USE Database
EXEC sp_changedbowner 'NewOwner'

This morning, I 'ave been mostly deleting old SQL Agent Jobs

And these have been those annoying jobs that are, apparently, undeletable and unwanted maintenance jobs.

Job #1

A rather ancient backup job, or, rather, the remnants thereof, sitting in SQL 2005 Agent but with no maintenance plan behind it, no subplan schedule within it, and generally going 'Ha Ha' in a manner eerily reminiscent of Nelson Muntz. Assuming that the following error message can be translated as 'Ha Ha!'.

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.The statement has been terminated. (Microsoft SQL Server, Error: 547)

First, I had a look in the sysjobs table


SELECT *
FROM msdb.dbo.sysjobs
WHERE name like 'plan name%'

This gave me the ID for the job, and then I was able to run the following against the sysmaintplan_subplans table


SELECT *
FROM sysmaintplan_subplans
WHERE job_id = '28B010B4-5FCD-4802-8405-808E2C9EA352'

(to confirm that I was looking at the correct job)


DELETE sysmaintplan_subplans
WHERE job_id = '28B010B4-5FCD-4802-8405-808E2C9EA352'

Then I was able to delete the job via Job Activity Monitor.

Job #2

This was sitting on a SQL 2000 SP4 box, which had been created in the time honoured fashion: take an image of the original server and rename it. You get the most infuriating error

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

For this one to be fixed, you need to restart SQL. First, find out what the name of the original server was by running

SELECT @@SERVERNAME

Then, rename it with the correct name

sp_dropserver 'OldServerName'

sp_addserver 'CorrectServerName', 'local'

Then, restart SQL. I got lucky. No-one was using the Server.

Run the following


UPDATE msdb..sysjobs
SET originating_server = 'CorrectServerName'
WHERE originating_server = 'OldServerName'

Bob's your Uncle, Charlie's your Aunt, and it should now be possible to delete the recalcitrant job.

This is a known problem that was introduced in SQL 2000 SP3, and has apparently persisted. Grrrr.

(This week i 'ave mostly been.... ref)

Rebuild Index Failed

"failed with the following error: "Online index operations can only be performed in Enterprise edition of SQL Server.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

Or, perhaps, the failure reason is that the SQL Server in question is Standard Edition, and that's pretty much it?

Other suggestions here (if you're not in Standard Edition!).

It would be nice, though, if SQL were clever enough to know when it's running Standard Edition, and not give you the option of an Online Index Operation when you're building the maintenance plan, now wouldn't it?! Or is that a Moon-on-a-Stick request?

Because I am a doofus

And, because, primarily, this is my repository of Useful SQL Information.

Master
Contains serverwide information about SQL

• logins
• linked server info
• configuration info
• info about user databases - locations of db files, key properties etc

Vital: SQL cannot start without it.

Resource

AKA mssqlsystemreources.mdf in the file system. Rather completely invisible in SSMS. Contains all the system objects deployed in SQL: so all the system stored procedures and system views (although these appear logically in the db to which they belong). It makes life easier when performing upgrades or applying Service Packs to have all this information in one place. Do not go looking for it. You will not find it except as a file.

Cannot be backed up.

Model

Template database: all databases need a base set of objects called the system catalog. (I know, American Spelling). When you create a new database, it uses the model database as the basis for its structure. So, if you want a people table in every single database you create, create it in the model database.

MSDB

Stores information for SQL Server Agent, Service Broker, Database Mail, Log Shipping etc. SQL Server Agent job information gets stored here i.e. job parameters, schedule, execution history. MSDB also stores backups and maintenance plan information, as well as information about the servers and tables involved in log shipping.

If you're backing up the database logs every 15 minutes or so, and you have lots of databases in Full Recovery Mode, you need to make sure that you have a regular tidyup of MSDB with a cleanup task, to make sure it doesn't get too big.

Distribution

Used during replication, it stores metadata and history information and transaction information for transactional replication. Until replication is set up, the distribution database is invisible in SSMS, however, the data files are installed by default.

TempDB

It's all in the name for this one: temporary data and data objects. Temporary Tables. Temporary Stored Procedures. That sort of malarkey. Also used for sort operations for large queries, some indexing, and during dbcc checkdb (database_name, repair_rebuild). Thus tends to get a bit out of control on occasion.

Gets recreated every single time SQL Server Service is started (very handy if it's gotten a little Out Of Control recently).

Cannot be backed up.

SPFun

Or what I learned about Service Packs.

A SQL Maintenance Plan job had been running fine on my SQL 2005 RTM machine for months: until I joined the company, and made the mistake of having a look at it and doing some tidying. Next thing I know?

"Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.".

"But, but, but, but!" I cried "I've just stopped it faffing about with Adventureworks, and removed that database from the server. I haven't touched the syntax directly, I dealt with this in SQL Management Studio. The syntax, surely, should be fine?"

I shrugged, recreated the job... and the same thing happened the next time it ran.

Hmmm.

It would seem that this particular error happens when SP2 is not yet installed on one's SQL box, and one edits a job via a newer version of SSMS. If one plays about with the job on the box, the problem is solved. Or, if one does the sensible thing, and installs the service packs.

I foresee some out of hours work for us here.

SQL Integrity Checks

The job failed "Integrity Checks Job for DB Maintenance Plan 'DB Maintenance Plan1' sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." That's the job history. The history against the Maintenance plan (I only learned about this more detailed history last week, and I think it's fab), said "[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode."

There are all sorts of ways of dealing with this.

Firstly, you can untick the box that says "Attempt to repair any minor problems" within the database maintenance plan. This is the same as changing the end of the SQL statement in the scheduled job to read "-CkDB" instead of "-CkDBRepair" If the job fails, this will flag up that there are errors within the database. Run DBCC CHECKDB at some point when you get in, and investigate from there. Databases need to be in single user mode to repair themselves. Sometimes, this is not possible (for example, SQL boxes that support websites with 24/7 availability) and you need scheduled downtime.

Secondly, you can ensure that users are not logged in. In one case, I shifted the job to run at 23:45 instead of at 19:30 - there were a couple of extremely enthusiastic users who were still logged in at 19:30.

How did I find out who they were? Script... On all servers, I find that an admindb is very helpful. So, I created a new table:


USE [admindb]
GO
/****** Object: Table [dbo].[tbl_sweetsql_usersloggedin] Script Date: 07/17/2009 10:15:11 ****** (C) Sweet SQL Lass/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_sweetsql_usersloggedin](
[loginame] [varchar](50) NOT NULL,
[logintime] [datetime] NULL,
[currenttime] [datetime] NULL,
[hostname] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

and then ran the following as a scheduled job:

USE [master]
GO
/****** Script Date: 07/17/2009 10:15:11 ****** (C) Sweet SQL Lass/
INSERT INTO
admindb..tbl_sweetsql_usersloggedin
SELECT loginame,
login_time,
getdate(),
hostname
FROM
sysprocesses
WHERE dbid = 7

(use
SELECT *
FROM sysdatabases
to find out what the dbid is).

The above code works on SQL 2000. For SQL 2005/8, use sys.sysprocesses and sys.databases views to get the same information. This is published under a creative commons license. (c) Sweet SQL Lass.

If the users are really lazy, and just didn't log out, and you want to repair things in the middle of the night, then you may need to use some SQL code to deal with this and throw them out of the database. There is plenty out there (and I have a version a colleague produced, and am seeking permission to publish, because it is rather nifty). Personally, I wouldn't recommend this. At the very least, warn your users that they will get logged out.

Heck. Microsoft doesn't recommend automatically repairing your database as part of an Integrity Check job. So who am I to go against that idea?

Finally, don't forget - log the information for your Maintenance Jobs to a Report File. It makes life much easier if you've done something really daft, like drop a database from the server without bothering to remove it from the Maintenance Job first.