April 20, 2010 at 7:35 am
Jeffrey Williams-493691 (4/20/2010)
Well, you shouldn't be shrinking the database - it is just a waste of time since it will grow again.What are the sizes of the mdf/ldf file?
What is the recovery model for the database?
If recovery model is full or bulk-logged, are you backing up the transaction log?
Yes, shrink does not help but I was trying everything as the original problem is that I am unable to Backup the database as mentioned at the start of this post.
Since the backup was giving errors, I copied the database and tried a backup of the copied database. The backup of the copied database works. The copied database mdf is 3+GB and ldf 1+ MB, same as the original DB.
When I back up the copied database, Simple Recovery mode, the backup file size is only around 900 KB (against the mdf of 3.2 GB).
So I was wondering whether I should go ahead with using the copied database as this is the database whch can be backed up.
I have no problem backing up the System databases or test databases. So I suspect problem with this particular database.
CHECKDB does not throw out any errors in the original and copied database.
Original database sp_spaceused shows Reserved as 2.9 GB, Data as 2 GB, Index as 180,088 KB and Unused as 714,512 KB
Copied database shows as 859,864 KB, 522,696 KB, 330,872 KB and 6296 KB respectively.
Thanks
April 20, 2010 at 3:30 pm
Try rebuilding all indexes in that database - and then see if it reports different usage. I am not sure why your backup failed - did you ever check the maintenance plan history and see what that tells you?
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
April 21, 2010 at 1:22 am
Jeffrey Williams-493691 (4/20/2010)
Try rebuilding all indexes in that database - and then see if it reports different usage. I am not sure why your backup failed - did you ever check the maintenance plan history and see what that tells you?
Rebuild Index was done but didnt help. Yesterday, I did a copy again but this time I used the Detach /Attach method and it gave an error. I tried again and still same error. After this I was able to backup the original database! Probably some corruption somewhere which the attach/detach probably corrected, but problem has been solved atleast for now. Thanks for all the help from the forum members.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply