| Author |
Topic |
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
 Posted - 2002-11-06 : 13:46:34
|
| I have a database call it DatabaseA and it has a .ldf file associated with that database on the c:drive which has 18Gigs. My network admin is telling me that this file in C:\Program Files\Microsoft SQL Server\MSSQL\Data\DatabaseA_log.ldfis taking up all the space on the C: drive. Please advise on proper course of action in order to reduce space and prevent further occurences. The database only has 40 Megs Of Data. It's being backed up using the full recovery model. |
|
|
rharmon
Starting Member
41 Posts |
Posted - 2002-11-06 : 13:49:26
|
| Can you shrink the transaction log by right clicking on the database, selecting all tasks/shrink files? |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-11-06 : 14:31:44
|
| Thank You |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-11-06 : 16:26:24
|
| What is the recommended method for automating this process? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-11-06 : 16:37:09
|
| You could schedule a job to shrink it, but the first thing that I would do is move DatabaseA off the C drive. This is not the optimal location for a database. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-06 : 16:38:29
|
| I don't know about SQL 7.0, but 2000 allows you to set a schedule when you right-click and choose the Shrink Database feature.Also look up DBCC SHRINKDATABASE and DBCC SHRINKFILE, you can put them into a T-SQL job step and set a schedule for the job. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-11-06 : 17:28:35
|
| If you want to automate shrinking your data and log files, SQL Server does give you the "autoshrink" option (see sp_dboption in BOL)... However, since shrinking files is a resource-intensive process I strongly suggest that you try to investigate what might be causing your log to grow and perhaps find a way to prevent excessive log growth instead of relying on an "autoshrink" or a scheduled DBCC SHRINKFILE. Your answer might be simply to increase the frequency of your log backups -- this will decrease the probability of log filling up, and if the log doesn't fill up, it won't need to increase the size of the log file! Also, look at any bulk operations that you might be running... Perhaps you can switch your db into bulk-logged recovery mode? That will help keep your log size under control as well. (Just be sure you understand the impact of changing the recovery model).Edited by - izaltsman on 11/06/2002 17:45:11 |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-11-06 : 18:29:53
|
| I'm stuck with the full recovery model and currently there is a log backup every 15 minutes. The reason the log gets full is because every 15 minutes there is also a dts that refreshes 20-30 megs of data.I think I will have to schedule a shrink job.What is involved in moving the ldf file to another drive when it's been created, any heads up would be appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-11-06 : 18:50:34
|
| Just detach the database (this allows access to the files), move the files to the desired location, then attach the database. When you attach the database, be sure to specify the new location of the files.Here is an example on how to move the pubs database:EXEC sp_detach_db 'pubs', 'true'--Move the files and change the path before you run the next commandEXEC sp_attach_db @dbname = N'pubs', @filename1 = N'F:\MSSQL\Data\pubs.mdf', @filename2 = N'F:\MSSQL\Data\pubs_log.ldf'Detaching the database takes down the database, so be sure to do this during a maintenance window if this is a production system. |
|
|
|
|
Emergency Action Needed