I need some advice re DB errors.

[ScottishFencer]

Hi There,

I have inherited a bunch of DBs which seem to periodically fail. The reason is "Insufficient disk space". Short of replacing the disk what other options do I have?

If I re-partition the disk (using say Partition Magic) how does SQL server deal with it?

Additionally I have a DB that appears to be causing the problem. I am tempted to back up the DB and either move it or find some way of taking data out of it (archive onto disk?).

I know that it is difficult to give answers to such vague questions but any help would be appreciate.

PS Can anyone recommend some good SQL Server Administration books - both as reference and desktop reference.

 

[ScottishFencer]

Some further information. I get the error message:

"The log file for database 'DBName' is full. Back up the transaction log for the database to free up some log space.."

This seems to be happening periodically.

I've had a look through a few MS pages and they seem to indicate shrinking and truncating the log however their descriptions of what the impact is are a bit vague.

I believe that in the past the other admin has deleted the transaction logs (ldfs?) and then backed up the DB. Is this a wise strategy? The documentation suggests not.

 

[SQLBill]

This keeps coming up in this forum...a search would find lots of information/solutions.

Basically, you probably don't allow your log and data files to automatically grow, or you don't allow them to grow enough.

In Enterprise Manager, right click on the database, select Properties, and go to each of the tabs for Data files and Log files. Check to see that Autogrow is allowed. Check to see that they are allowed to grow by an appropriate amount.

Make sure you are doing full and transaction log backups.

To quickly clear out the log file...run:
BACKUP LOG dbname WITH TRUNCATE_ONLY

Run the DBCC SHRINKFILE command for the log file.

Immediately do a FULL backup.

-SQLBill

Posting advice: FAQ481-4875

 

[ScottishFencer]

thanks for that SQL Bill. I've seen this today. I was looking at the backup tool and the option to schedule regular transaction log backup is greyed out.

As I've mentioned I have been left "holding the baby" so to speak.

 

[SQLBill]

Is this for a user database or a system database?

BTW- good reference/reading material is the BOL.

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Also, is the database in SIMPLE or FULL recovery mode?

(In Enterprise Manager, expand to the database, right click on the database, select Properties, go to Options tab. While you are there, go to the Data and Log tabs and see if you are allowing Autogrow and how you allow autogrow - percent or MB).

-SQLBill

Posting advice: FAQ481-4875

 

[ScottishFencer]

Thanks for the link SQLBill - will check it out.

This is for a system database. It's a SQL server backend with an Access front end. Using the link that you posted I've read up on the "simple recovery model". It makes no mention of this option being greyed out but I assume this is the reason why.

I've also had a look at some other settings and notice that the account used for the DB is the local system account. Now: I would like to setup SQL Mail and I know that you need to use a network account so that you can use Exchange. Additionally it seems to be a "not recommended" setting in SQL Server. I suppose I could set up a user account in Active Directory and use this account instead (which seems to be the recommended course) but I am worried about the possible wider impact that this will have on database accessibility.

The database is in simple recovery mode; the Data file and Logs are set to autogrow by 10%.

FYI: I have requested some additional DBA training. I was hired as the "general techie" here and not as a DBA. As there are no plans to replace the previous incumbent it falls on me to overhaul the network and get it into a more professional state. I have some concerns - as can be seen by some other postings I have made in other forums here. Not averse to learning new things so I'm looking at all of this as a decent challenge.

 

[SQLBill]

The 'not recommended' part, I believe, is that it's not advised for you to use a NETWORK ADMIN login. Create a network login specifically for this and provide it only the permissions that it really needs.

Your autogrow might not be allowed to grow large enough. You need to estimate how fast the database grows and adjust for that. I don't like percentages. My log file on my Master is set for 500 MB.

-SQLBill

Posting advice: FAQ481-4875

 

[ScottishFencer]

Thanks SQLBill:

So I've created a network account. It's a user account. According to SQL Online; EM will create the necessary permissions for this account when it gets assigned to the DB - at the same time (reading the KN articles) there appears to be some contradiction about whether this account will actually get the right permissions ... confusing. MS seem to say that EM will create the right permissions and that it won't ...which means that I will have to alter the registry ... so which is true? Let's assume that I am unable to get the DB to work properly and I have to re-assign the local system account while I have a think - are there repercussions that I have to consider? I am attempting to be extremely cautious as I've been bitten by this network setup before.

 

[SQLBill]

You should be able to change the login account with no problems. We've done it. When I set up my SQL Server, I remember creating a login and then during setup telling SQL Server what login to use and it setup everything fine.

It's been a while though.

Good luck.

-SQLBill

Posting advice: FAQ481-4875