June 1, 2009 at 6:25 pm
We ran out of disk space on a server. I've freed up 2 gig, and all the databases look ok except for one. It's not accessible through the GUI. If I do sp_helpdb, it's not in the list. What's the best course of action ?? Restart SQL and hope for the best, or is there a more database specific tack ?
A couple of error logs are:
06/01/2009 18:13:07,spid75,Unknown,E:\MSSQL.1\MSSQL\LOG\'MyDatabase'_1.ldf: Operating system error 112(There is not enough space on the disk.) encountered.
06/01/2009 18:13:07,spid75,Unknown,The transaction log for database 'MyDatabase' is full. To find out why space in the log cannot be reused
I looked at log_reuse_wait_desc and it = "LOG_BACKUP", like a couple of others. Most of the databases are "NOTHING"
06/01/2009 18:13:07,spid11s,Unknown,Could not write a checkpoint record in database ID 5 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
06/01/2009 18:13:07,spid11s,Unknown,Automatic checkpointing is disabled in database 'MyDatabase' because the log is out of space. Automatic checkpointing will be enabled when the database owner successfully checkpoints the database. Contact the database owner to either truncate the log file or add more disk space to the log. Then retry the CHECKPOINT statement.
I ran CHECKPOINT while connected to master, with no apparent change. Can't connect to the problem DB.
06/01/2009 18:13:10,spid75,Unknown,Could not allocate a new page for database 'MyDatabase' because of insufficient disk space in filegroup 'PRIMARY'.
06/01/2009 18:13:10,spid75,Unknown,During undoing of a logged operation in database 'MyDatabase'
06/01/2009 18:13:10,spid75,Unknown,The log for database 'MyDatabase' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
06/01/2009 18:13:14,spid22s,Unknown,SQL Server has encountered 8 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
06/01/2009 18:13:14,spid22s,Unknown,SQL Server has encountered 8 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
06/01/2009 18:13:14,spid22s,Unknown,SQL Server has encountered 8 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
June 1, 2009 at 8:25 pm
How many databases do you have on this instance?
How many of these databases are in full recovery model?
How often are you backing up the transaction log for each database that is in full recovery model?
For all of the databases that are in full recovery model where the log_reuse_wait_desc = 'LOG BACKUP' is telling you that those database need to have a log backup performed. Until the log backup is performed, the transaction log for that database will keep on growing.
What you need to do is backup the transaction logs for all of those databases. Once backed up, you need to shrink the log files back to an acceptable size - but not back to 0 because they are just going to grow.
Once you have done that - you should have additional space available again and should be able to get the unavailable database back online. Once online, backup the transaction log and shrink the file back to an acceptable size.
Next, you need to create a maintenance plan that backs up the transaction logs for all of those databases. How often you back them up depends on what your business requirements are, and how much data loss you can afford. You can start with every hour until you follow up with the users.
And finally, read the article in my signature on managing transaction logs. This article has all of the information you need to understand why you need to manage them.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 1, 2009 at 9:51 pm
Thanks for the reply
How many databases do you have on this instance?
- 46 including system db
How many of these databases are in full recovery model?
- All user dbs are full recovery
How often are you backing up the transaction log for each database that is in full recovery model?
- Full every night. I am backing up t-logs every 15 minutes. Still not frequent enough ?! The stuck db LDF is 5 gig anyway, but I cannot perform a backup. The backups for the others is fine and I have 2 Gig available now.
I took a look at the historical size of the t-log backups, and they are often 250 Meg, and sometimes 500 meg. Today I had 4 t-log backups over 1 Gig each at 11:30 am, 1:15 pm, 2:45 pm and 4:15 pm and quite a few 100-200 Meg backups.
June 1, 2009 at 10:18 pm
Check whether the log file size is limited to any particular value for that DB.
If accessible take a Log backup of that database and shrink the log file.
If you still not able to access the DB can you please put the output of SP_HELPDB.
June 1, 2009 at 10:31 pm
I cannot get any info on 1 database. sp_helpdb does not list it, although it is in sys.databases
June 1, 2009 at 10:51 pm
Can you view the name of that database in SSMS?
June 1, 2009 at 11:02 pm
Yes, I can see the database in SSMS GUI, But I get an error if I try to display properties, and there is no + sign to expand it.
"Database MyDatabase cannot be opened due to inaccessible files or insufficient memory or disk space."
EDIT: I restarted SQL. Then the database showed "In Recovery" for a little while. I refreshed and it looks ok now. I am taking a t-log backup, then will run a new full backup.
Still curious about why the LDF grew so large with backups every 15 minutes.
June 1, 2009 at 11:41 pm
Taking backups wont shrink the log files. that is the reason why they grew.
You have to run the dbcc shrinkfile or shrinkdatabase command to reduce the physical size of the log file
June 1, 2009 at 11:59 pm
homebrew01 (6/1/2009)
Yes, I can see the database in SSMS GUI, But I get an error if I try to display properties, and there is no + sign to expand it."Database MyDatabase cannot be opened due to inaccessible files or insufficient memory or disk space."
EDIT: I restarted SQL. Then the database showed "In Recovery" for a little while. I refreshed and it looks ok now. I am taking a t-log backup, then will run a new full backup.
Still curious about why the LDF grew so large with backups every 15 minutes.
Most likely, the log files grew larger because of additional activity on the database. Backing up the transaction logs every 15 minutes is good to hear and I wouldn't back them up more often than that.
No telling what the additional activies were, but - some things you can look at are new indexes, new index rebuild jobs, large data loads, etc...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2009 at 12:20 am
Ratheesh.K.Nair (6/1/2009)
Taking backups wont shrink the log files. that is the reason why they grew.You have to run the dbcc shrinkfile or shrinkdatabase command to reduce the physical size of the log file
I know they won't shrink on their own, but shouldn't the transaction log space get re-used ?
June 2, 2009 at 12:25 am
You also could have a long running open transaction that is keeping the log file from being re-used. Another thing to look at is whether or not log shipping, database mirroring or replication has been enabled on those databases.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2009 at 4:05 am
No log shipping, mirroring or replication. I'll have to figure out what type of activity was going on.
Thanks for all the help.
June 2, 2009 at 4:18 am
check the size of Log files and shrink them either by SSMS
or by using this query
dbcc shrinkfile ( logical file name,target size)
Also make sure there are no open transactions...
June 2, 2009 at 5:28 am
homebrew01 (6/2/2009)
Ratheesh.K.Nair (6/1/2009)
Taking backups wont shrink the log files. that is the reason why they grew.You have to run the dbcc shrinkfile or shrinkdatabase command to reduce the physical size of the log file
I know they won't shrink on their own, but shouldn't the transaction log space get re-used ?
Yes, the Tlog should be re-used. Have you got any open/explicit transactions? and also look at your autogrowth setting for the Tlog and what is the output of DBCC SQLPERF?
June 2, 2009 at 6:01 am
No open transactions now. DBCC SQLPERF(LOGSPACE) showed 4.5 Gig. I've shrunk it to 1.5 Gig which should be enough. I need to get more disk !
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply