I want to shrink a TLog file, but its not happening as log_reuse_wait_desc for the database shows LOG_BACKUP. But I want to avoid doing TLog backup as it would impact the backup chain. Is there a way around this?
View Complete Post
Hi:We are usingÃÂ SQL 2005 (SP3) and I have catered a maintenance plan that back ups the database. I read several articles about the back up of SQL 2005 which confused me and raised few questions in my mind. I am not very familiar with SQL 2005 , so feeling uncomfortable what I am doing for taking the back up of the database. Every hour our database is pulling data from another server which generates reports and charts for the management. Data volume is fairly huge and must have to have very well planed maintenance in case of disaster we can recover maximum data. Maximum one hour's data loss is appreciated.Now what I'm doing - 1. Taking backup of our DB once in a dayÃÂ at 12:00AM and 2. Backing up TLog file every hour.Database back up type if full (Recovery Model: Full). Instead of creating separate backup files, I'm appending the existing file (If Backup File Exist: Append).For the TLog backup system I'm doing the same - not creating any separate backup file for hourly backup, appending the existing file.Now my confusions are:Am I taking the backup correctly, or doing anything wrong (company will be in big risk without any perfect backup plan)From different articles I found that people suggeste
We have a database supplied by a third party which contains a "diary" table. The table contains an operator name, an entry time and a diary memo entry column. This column is defined as VARCHAR(1000).
We have noticed during migration to this new system this memo text column has been populated with CHAR(1000) values and so every entry in the table is exactly 1000 characters long, padded with trailing spaces. The actual maximum length of a memo is around
200 characters, not including the trailing space, and many entries are only around 20 characters long. Just to fill you in further, the table contains around 100,000,000 entries. Consequently the extra whitespace is very costly in terms of disk space.
We have updated the column performing an RTRIM() to remove the wasted whitespace. Selects now return the correct data with no trailing blanks.
The issue we have is that the physical size of the table has not changed at all. It is currently taking up around 50GB with most of the space storing space characters.
We have tried a DBCC SHRINK DATABASE but the table has not changed size at all. We were expecting this to release the whitespace, but it looks like it has just rearranged the rows in the pages and not actually released the space within each row.
We have also tried backing up and restoring the database to a test server but thi
We run wss 3.0 with sql server express. Our database is fairly large at 4gigs now. I want to try shrinking it. Is this recommended or will it impact sharepoint at all?
I was going to follow this guide:
the MSDB log is about 15 GB whereas the data file is only 6 GB. Is there a way to shrink the MSDB log?
I have a database on my testing server being used by one of our consultants that has a ridiculously sized log file. The database itself is 100Mb odd, but the log file is in excess of 94 Gb.
I've tried shrinking the file itself using Tasks > Shrink > Files > LogFile. I've tried all the options (Release Unused Space, Reorganise pages and Empty File by Migrating) to no avail. The properties of the Log File show that the Available free
space is 94478 Mb (> 99%).
I've tried creating a backup of the database itself, deleting the database and then restoring from the backup but the log file is re-created with it's huge space allocation. This file's size means I can't restore previous versions of my development database
because there isn't enough space left on the Hdd for me to copy the backup to and it's inaccessible from anywhere but the local hard-drive (at least from my understanding of the restore process and the directories I can access.)
Anyone have any ideas on how I can reduce the size of this log file?
Thanks for your time,
I have a database that is 844GB.
Its available free space is 655GB (77%)
It consists of 3 datafiles & a log file:
Data1.mdf Initial Size = 174GB Autogrowth = None
Data2.ndf Initial Size = 256GB Autogrowth = None
Data3.ndf Initial Size = 256GB Autogrowth = None
Log1.ldf Initial Size = 100GB Autogrowth = 10%
How do I shrink this down and release the unused space?
What would be the safest method to move just TLog to another drive. How do you compare detaching and attaching database with backing up the TLog and restoring it with move option?
I'm trying use DBCC ShrinkFile for shrinking a log file but getting the following error:
Could not locate file 'log' for database 'db' in sys.database_files. The file either does not exist, or was dropped.
I know the context is right, I checked a number of times. Then
I used SSMS and right clicking on database and shrinking the log file from there. I chose the option release all unused space while doing it. Now it worked but 30MB log file became 500 KB. Did this operation
deleted the log file? How the file became so small?