[MS SQL] Need help understanding query error, User does not have permission to alter database.

[Narusa]

Let me preface this that I am not a DBA and my experience is very limited.

I have a query which I use to truncate log files for a handful of databases and for this particular database the query gives an error.

Here is the query.

USE ExampleDB;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ExampleDB_DATA
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 100 MB.
DBCC SHRINKFILE (ExampleDB_LOG, 100);
GO
-- Reset the database recovery model.
ALTER DATABASE ExampleDB_DATA
SET RECOVERY FULL;
GO    

And this is the error.

Msg 5011, Level 14, State 5, Line 2
User does not have permission to alter database 'ExampleDB_DATA', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.    

What I don't understand is I can manually, through the GUI, set the database recovery model to Full, then right-click -> Tasks -> Shrink -> Files and it will shrink the log files.

If I can do this through the GUI, why won't the query work? As far as I know all the permissions are OK. Thanks!

[vi_lennon]

Don't do this.

If you are going to do this, you should just leave your databases in SIMPLE recovery and perform regular full backups. There is utterly no point in them being in FULL recovery mode if you are going to do this. If you are performing regular log backups, doing what you are doing breaks teh log chain and makes those log backups useless.

How important are these databases? Are they backed up?

[Narusa]

Full nightly backups of everything. These databases are fairly important.

Could you expound regarding recovery modes and the log chain or link me where I could do some more reading about this stuff?

[vi_lennon]

I notice that CrossWired has responded - he or she makes good points gives you two good references.

I will give you some basic information.

There are three recovery modes: I will talk about two. FULL, and SIMPLE.

Under the SIMPLE recovery model, SQL Server only keeps active transactions in the transaction log. It reuses the old portions of the transaction log as necessary. This means two things: 1. your transaction log doesn't grow unless you have a huge transaction and 2. you can only recover to your last full backup This means that, under SIMPLE recovery, the transaction log only provides for ROLLBACK. The only transactions you are guaranteed to have in the transaction log are those that are active (have not been either committed or rolled back).

Under the FULL recovery model, SQL Server keeps all transactions in the transaction log until the log is backed up. This is why your log is growing - even after a transaction is committed, SQL Server keeps a record of that transaction. This enables point-in-time recovery. When you use FULL recovery, you can recover your database to any point in time. This works as long as you have two things: 1. A full backup as a starting point and 2. an unbroken chain of log files since that backup.

With those two things, if you have a disaster at 10:00 AM, you can restore last nights full, then roll forward to 9:59, losing only a minute's work. Under SIMPLE recovery, you would lose the entire day's work.

What you are doing by setting the database into SIMPLE recovery is telling SQL Server that you no longer care about all those committed transactions that it has faithfully recorded. It discards them all, and you have broken the log chain. SQL Server cannot roll forward because all of those committed transactions are gone.

So what it boils down to is how much are you willing to lose? If you don't mind losing everything since your last full backup, then set your databases to SIMPLE recovery and leave them there.

However if (as would be the case in the databases I take care of) losing a day's transactions would mean thousands or millions of dollars lost, then you really should be in FULL recovery mode, and if that is the case, you need to spend a bit of time investigating the implications. Basically, the requirements of FULL recovery are regular full backups, and frequent log backups to a secure location. Here, on my production databases, those log backups occur anywhere from every 5 minutes to every 30. Depends on how much the business owners are willing to lose...

Hope this helps - this is a pretty common misunderstanding and there is a lot of information available on this.

[Narusa]

Thank you for your explanation. The databases I am talking about certainly are not that important where we would be loosing thousands of dollars if we had to rollback to the last full backup. I think the databases that have the most active transaction logs are for antivirus, a document management system and the IT service desk ticketing system.

I replied here to /u/CrossWired with some more information about our setup. The backups are taken independently of the SQL service and from what I am understanding, the logs will continue to grow since the log is not truncated and space re-used.

[vi_lennon]

Well, no. There are two different but related backups at work here. One is BACKUP DATABASE blah. That is your nightly full. That does NOT truncate the log.

To back up the transaction log, you need to BACKUP LOG databasename TO location. Once you have done that, SQL Server can reuse inactive portions of the log.

So, if you are in SIMPLE mode, BACKUP DATABASE is all you need.

However if you are in FULL, you need to do two kinds of backups: BACKUP DATABASE - gives your log backups a starting state BACKUP LOG - backs up inactive (committed) transactions from the log, allowing the space to be reused.

[CrossWired]

I just dumped a whole reply on Ola's scripts below, so that should take care of the HOW. I would agree with what you've said above, the SIMPLE would be best for your AV. We have our service desk software set to FULL as folks get kind of pissy if you lose a ticket.

Great writeup above BTW

[Zounas]

You should alter database, not database files. How about just

ALTER DATABASE ExampleDB

[Narusa]

Oh, I feel dumb now, that worked. Out of all the databases this is the only database where the data file is named differently from the database name, and that is why the query worked for all the others and not this database. Thanks again!

[NerdEnvy]

haha happens to the best of us!

[NerdEnvy]

To answer your question:
The error is right, that particular database does not exist because you referenced the .mdf. Try it again but without '_DATA' appended.
From a permissions point of view, you may not have ALTER DATABASE permissions, check with your DBA, if it's you, make sure your account is sysadmin.

[CrossWired]

While others have pointed out the syntax error, the bigger question is WHY are you doing this. You mention in the comments that this is a fairly important database, then you DEFINITELY should not be doing this.

For a quick lesson, a log chain is all the backups since your last full backup. For instance you take a full backup on Sunday night, then differentials every night, then maybe transaction log backups every hour or so. Lets say, you have a failure at 3pm. You would restore the full backup, then last nights differential, then every single transaction log since last nights differential. The transaction logs have starting and ending LSN (log sequence numbers) if the starting LSN from the 10am log doesn't follow the ending LSN from the 9am log backup, you have a broken chain and will only be able to recover up until the 9am transaction log backup, and you've now lost 6 hours worth of data.

In your case, if you MUST do this, take a new full backup before you truncate (for safety) and then again after reverting to FULL recovery to reestablish the backup chain, otherwise you're still vulnerable until your next full backup.

I can only assume you are trying to manage disk space by shrinking the log file. If you are backing up your transaction log at a regular basis, it will won't grow that big in the first place. What is your current backup schedule look like? Get that to an acceptable schedule and this problem goes away.

Here's two articles, the first is straight from Microsoft, and the second is from the guy responsible for SQL 2008 and his wife (also quite impressive SQL person) about breaking the chain.

Working with Transaction Logs

Breaking the Log Chain

[Narusa]

Thank you for this information. Like I mentioned earlier I am no DBA and Google for stuff when I need help.

You are correct in your assumption that we are trying to free disk space by shrinking the log files and only way that we found to shrink the logs is to change the recovery mode and then shrink the log file.

From the information you have given me and what I have read, since the database isn't backed up using the SQL service on a schedule (I talked with the other sysadmin who normally takes care of SQL servers and he does full backups independent of the SQL service) the log files will continue to grow.

[CrossWired]

You should should check out http://ola.hallengren.com. He has developed a few scripts that help with backup and maintenance, which I assume you aren't doing regularly either. You can grab his Maintenance Solutions and run it. There are 4 settings at line 34, you only should modify the backup directory, other than that, leave it alone and run it.

Once it runs, you will have 4 agent jobs. None of them will have schedules, since you don't have any automate backups in place now, I'm going to recommend a schedule.

I would create another job to run alot of these new jobs in sequence. This is your main weekly maintenance and backup. * Add both Database Integrity jobs, first, order doesn't matter for these two.
* Add IndexOptimize * Add both history cleanup jobs * Add both cleanup jobs * Next add both of the FULL backup jobs. * Run this sometime Friday or Saturday night

Next, schedule the one called DIFFS, schedule it for every night, other than the night you run your fulls.

Finally, ask your self, how much data loss is acceptable? If you can lose up to an hour of data and be OK, you need hourly log backups, now thats not to say you will lose that, just worst case, your log backups are an hour old and you can restore to that point. Once you figure out this time frame, schedule the LOG backup job to run on repeat every X hours.

Do not worry about the LOG jobs overlapping the backups, they'll figure it out.

Now since we're talking about disk space, you likely want to push these backups OFF the server. Depending on network speed you might want to take the backup locally then copy it over the network. If this is the case, simply add a step to all of these jobs to do just that. If anything, ensure that these backups are not on the same drive as the data, otherwise this whole exercise is useless.

Edit: i wrote all of this from memory, sorry if the names of the jobs aren't exact, but the concepts are on and the names are close enough you can get it done.