November 14, 2011 at 10:35 pm
Hi all, before start thanks in advance, and l'll try to be clear beside if it's the wrong place to post my question please feel free to point me in the right direction.
Hey guys, l'm facing issues about free available space, the server owner refuses to extend drives and well anyway.
In order to gain some free space i tried to shrink a database following the next steps.
First i got the allocate, used and Available space from dbo.sysfiles...well then i ran
USE [MyDatabase]
GO
DBCC SHRINKFILE (N'MyDatabase_Data')
GO
But here comes my issue... in fact the free available space was reduced(i thought that the idea was to gain more free space)!!!!! and the statement never finished, can you please advice me on this situation, thanks guys in advance
November 14, 2011 at 11:41 pm
Few Questions:
• What’s the database recovery model?
• How frequently you backup (full / log)?
• What’s the DB size?
As a quick start if your recovery model is FULL, please take Transaction Log Backup.
How to: Back Up a Transaction Log (SQL Server Management Studio)
http://qa.sqlservercentral.com/Forums/Post.aspx?SessionID=2m30u555czrpwungg2y3xyqz
November 15, 2011 at 2:16 am
If your database is maintained properly (I'm assuming it is), shrinking the data files will have no effect in the long run (files will likely grow again), so I think you'd better find a way to convince the server owner that you need more storage.
If your database is in FULL recovery (if it's a production instance, it should definitely be) you could mitigate storage space issues running more frequent transaction log backups.
Be aware that shrinking data files brings massive logical fragmentation and you would probably end up rebuilding indexes after the shrink, which causes the files to grow again.
-- Gianluca Sartori
November 15, 2011 at 4:56 am
islas.tonatiuh (11/14/2011)
But here comes my issue... in fact the free available space was reduced(i thought that the idea was to gain more free space)!!!!!
shrinking file will reduce free space within the data/log file. It will release the freed-up space to the operating system.
November 15, 2011 at 7:02 am
Hi all,
Yes guys, actually, the database is in Full Recovery Model, there's a job how takes a full and log backups in a regular interval time. That's weird for me, because the database it's under a maintenance plan and for some reason when i want to shrink it i noticed that the free space available in the drive is reduce in opposition to gain more free space.
November 15, 2011 at 7:03 am
Oh, sorry, the DB size is 10 GB.
November 15, 2011 at 8:19 am
actually, the database is in Full Recovery Model, there's a job how takes a full and log backups in a regular interval time.
Good. What's the frequency of Log Backups? When did you backup (log) last?
Shrinking would not be a good idea for your case. As Gianluca said it will result in Fragmentation.
November 15, 2011 at 9:09 am
The maintenance plan is performed every night at 1 am full backup, and the *.trn files are not performed in continuous dates also i noticed they size is almost 5 GB or 2GB in some cases.
November 15, 2011 at 9:24 am
the *.trn files are not performed in continuous dates
That's the reason for Space Consumption.
Transaction Log Truncation
If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. Log truncation automatically frees space in the logical log for reuse by the transaction log.
Except when delayed for some reason, log truncation occurs automatically as follows:
• Under the simple recovery model, after a checkpoint.
• Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.
November 15, 2011 at 11:43 am
Ok, i think i got it, just correct me if i understood well...
First.- I need to take a full backup from Database.
Second.- Take a backup from my log file(same data base).
Third.- Take continuous backups fro log after that and after that somebody suggest to check fragmentation and if so rebuild indexes?
Thanks
November 15, 2011 at 11:58 am
Frequency of backups depends upon the amount of activity on the database. As a general rule I do nightly full backups and transaction log backups every 30 minutes on our systems. You must to BOTH kinds of backups on a regular basis.
Once you start doing regular transaction log backups you can get a better idea of how much space it actually needs and adjust the size accordingly.
Other maintenance tasks are on an as-needed basis depending upon resources and performance issues.
Here's a good place to start for looking at DBA maintenance tasks (I'm linking directly to the section on backups): http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/#_Toc209585599
Use this as your starting place and go do some research/reading. This site is a great place to start.
November 15, 2011 at 5:01 pm
Thank you Pam, and thank you guys i think that's moment to read and start to put in practice all these comments and help, thank you very much, and i promise contribute some time with this forum.
November 16, 2011 at 1:12 am
Here's a good place to start for looking at DBA maintenance tasks (I'm linking directly to the section on backups): http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/#_Toc209585599%5B/quote%5D
Nice One 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply