| Author |
Topic |
|
jon3k
Starting Member
49 Posts |
 Posted - 2004-02-05 : 16:29:07
|
| Ok, I've got a database with a transaction log that was created with unrestricted growth. The log is up to 6.5GB now, and theres only 1.25MB of free space on the disk. How can I clear out the TLog, or re-create it and limit the file growth? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-05 : 16:31:26
|
| First, you need free space in the LDF file. So if your database recovery model is set to FULL, run a backup of the transaction log. If it is SIMPLE, run BACKUP LOG DBName WITH NO_LOG.Then shrink the LDF using DBCC SHRINKFILE.If your recovery model is set to FULL, are you performing regular transaction log backups?Tara |
 |
|
|
jon3k
Starting Member
49 Posts |
Posted - 2004-02-05 : 16:41:55
|
| Can't I just do a DUMP TRANSACTION WITH NO_LOG ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-05 : 16:44:15
|
| DUMP TRANSACTION? What version of SQL are you using?BACKUP LOG is what you want starting at 7.0. But DUMP TRAN works in 7.0 as well for backward compatibility. Just use BACKUP LOG.And yes you can do that, but it won't shrink the file. It'll just create free space inside the file.Tara |
|
|
|
jon3k
Starting Member
49 Posts |
Posted - 2004-02-05 : 16:52:31
|
| Ok, its using a full recovery model, but the tlog is 6GB and theres only 1.5MB of free disk space, so I can't backup the log.What are my other options? This is SQL2000 by the way. |
|
|
|
jon3k
Starting Member
49 Posts |
Posted - 2004-02-05 : 16:53:36
|
| Also - can I just dump the log then shrink it? I've got to get this down to a reasonable size (like 1/10th the current size). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-05 : 16:56:15
|
| Yes you can dump the log then shrink it. That's what I mentioned in the first post.Are you performing regular transaction log backups since your recovery model is set to FULL? Since you don't have enough space to backup the log, you just run:BACKUP LOG DBName WITH NO_LOGIt truncates the transaction log without backing it up.DUMP TRAN is for SQL 6.5.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-05 : 16:56:32
|
| How big is your hard drive...is there anything you can get rid of or move?Do you have any mapped drives to the box? You can dump it across the network...but that'll be slow...but it's not that big...Brett8-) |
|
|
|
jon3k
Starting Member
49 Posts |
Posted - 2004-02-05 : 17:05:14
|
quote:
Originally posted by tduggan
Yes you can dump the log then shrink it. That's what I mentioned in the first post.Are you performing regular transaction log backups since your recovery model is set to FULL? Since you don't have enough space to backup the log, you just run:BACKUP LOG DBName WITH NO_LOGIt truncates the transaction log without backing it up.DUMP TRAN is for SQL 6.5.Tara
Yes, I'm performing regular backups, all of which have failed apparently. When the database was created (long before my time) it was created with unrestricted growth. I'm performing a transaction log backup now, with the truncate log option set.Sorry I misunderstood you, thanks for all of your help, I really appreciate it. |
|
|
|
jon3k
Starting Member
49 Posts |
Posted - 2004-02-05 : 17:05:46
|
quote:
Originally posted by X002548
How big is your hard drive...is there anything you can get rid of or move?Do you have any mapped drives to the box? You can dump it across the network...but that'll be slow...but it's not that big...
I tried creating a mapped drive and backing it up that way, but it wouldn't recognize the mapped drive, only the two local partitions on the machine. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-05 : 17:07:11
|
| Did you use Query Analyzer for the backup to a mapped drive? Enterprise Manager won't see the mapped drive, but you can do it through Query Analyzer.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-05 : 17:08:07
|
| BTW, you do want unrestricted growth for the transaction log. But you need to make sure that the backups are successful so that it doesn't have to keep growing due to no free space in the log.Tara |
|
|
|
jon3k
Starting Member
49 Posts |
Posted - 2004-02-05 : 17:23:46
|
| Ok, when I try and backup the tlog with the "Remove inactive entries from transaction log" option checked, I get the following:-------------------------Write on 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\CITRIX_db_200402010200.BAK' failed, status = 112. See the SQL Server error log for more details.BACKUP LOG is terminating abnormally.-------------------------I'm trying to check the error log now, but its taking forever to load. I'm assuming it has something to do with not having enough free disk space on the drive. I'm trying to backup and truncate a tlog thats 6.XGB to a drive with like 2.87GB free. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-05 : 17:26:45
|
| When you pull up the error log, it loads it into RAM. If you haven't restarted the SQL service or rebooted the box lately or run sp_cycle_errorlog, then the log is probably large (maybe around 10MB or so). Run the command in Query Analyzer. Do not use Enterprise Manager for this. And do not write to a file. You want to do NO_LOG option.Tara |
|
|
|
jon3k
Starting Member
49 Posts |
Posted - 2004-02-05 : 17:33:30
|
| Ok, I backed up the tlog with NO LOG, it executed successfully. Now I guess I need to shrink it?Whats the DBCC SHRINKFILE syntax? I'm using:DBCC SHRINKFILE 'D:\SQL\LOGS\CITRIX_Log.LDF', 1024Which returns:Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'D:\SQL\LOGS\CITRIX_Log.LDF'. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-05 : 17:35:26
|
| Here's the example from SQL Server Books Online:USE UserDBGODBCC SHRINKFILE (DataFil1, 7)GOChange DataFil1 to the logical name of the LDF file. In your command, you've got the physical file name. Your logical name is probably CITRIX_Log.Tara |
|
|
|
jon3k
Starting Member
49 Posts |
Posted - 2004-02-05 : 17:45:11
|
thank god, its down to 1GB now  So, how do I stop this from happening again? You mentioned that I should leave it at unrestricted growth, but make sure the logs are created. If the backup is created, is that space removed from the active tlog? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-05 : 17:47:50
|
| If a transaction log backups is successful, then there should be enough free space in the file to support the transactions. If there isn't enough free space, then the log is forced to grow. We backup our transaction logs every 15 minutes due to the high volume of traffic and also because that's the maximum amount of data that we can afford to lose in case of a disaster. For other shops, backing up every hour is enough. You'll need to examine the transaction log utilization in between backups to determine what is right for your environment. It can easily be viewed in Enterprise Manager by right clicking on the database, then going to view, taskpad. It'll show you in color the MDF and LDF file, how much is in use for each, and how much is free for each.Tara |
|
|
|
gwhiz
Yak Posting Veteran
78 Posts |
Posted - 2004-02-05 : 17:53:48
|
| I would actually not leave the transaction log growth unrestricted. I would set it to something like 4GB or the current size of your database, that way if it does fill up again you will have the room to back it up. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-05 : 17:59:55
|
| gwhiz,And what happens when someone runs something like DBCC DBREINDEX and it needs the space? It'll error out if it can't grow anymore due to the limit. DBCC DBREINDEX obviously wouldn't be run by the application, but it's just an example of something that takes up a lot of transaction log space. Alerts should be created instead so that when the drive is almost out of disk space, someone is notified to look into the situation.Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-02-06 : 12:32:45
|
| I thought the proper SQL2K statement was:"Backup Log DBNAME with TRUNCATE_ONLY", not NO_LOG. NO_LOG was 6.5, wasn't it?If you are setting it to unrestricted filegrowth and you are going to run a REINDEX on it which will cause the DB to grow pretty big, make SURE you run a SHRINKDB afterward to shrink it back down. Also I would recommend doing a full backup before the reindex, setting the DB to DBO use only, doing the reindex, doing a shrinkdb, and then doing another full backup....backing up REINDEX (and shrinkdb) transactions is a rather pointless waste of space. |
|
|
|
Next Page
|
Transaction log to big!