[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1291
  • Last Modified:

mdf files to big in SQL server 2005

Hi There,
I hope someone can advise me on what to do here.

I am running my database on SQL Server 2005. I had created a backup maintance job myself using the  SQL Server Management Studio,when using a maintance plan i set up this to delete files based on the age of the file at task run time older then 1 week.

however some one else wanted to use MOM and do the back up there and this i think is appending the backup each day which now leads to the back up  for this one database being  4,350,784 KB and this is causing problems with my database load.

I want to either be able to set up something in MOM for the database backup to delete anything over 1 week old or stop this backup altogether and go back to using Management Studio.

Can anyone advise me on what is the best to do and what to do if i do go back to using management studio as how do i decress the size of the mdf file as i have already tried to shrink and now this is also hanging on me??
Please help.

Thanks,
Putoch



0
Putoch
Asked:
Putoch
  • 19
  • 17
1 Solution
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
If you can utilize Integration Services, you may utilize the history cleaning utility...
0
 
PutochAuthor Commented:
I don't know what you mean?
I have connect to SSIS but how do you mean utilize the cleaning utility.

also i think i might have mis read what i was looking at.
The .mdf file is not from a back up its actually the database file and so has nothing to do with backups would it?
There for how do i make the mdf file smaller or what can i do to control it
what is the max size of an mdf file on MS SQL Server 2005

Thanks
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<I have connect to SSIS but how do you mean utilize the cleaning utility.>>
You may program by drag and drop several things in SSIS, including backup and cleaning backup history.

<<There for how do i make the mdf file smaller or what can i do to control it >>
You may attempt *dbcc shrinkfile*  command line to reduce the size of the MDF file.  

Hope this helps...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
YveauCommented:
When the backup is appending the new backup to the existing media (file), make sure to use the 'init' option in the backup statement. If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.

So check the corresponding radio button in some GUI tool, or script it like:
BACKUP DATABASE [master] TO  DISK = N'X:\master_backup.bak' WITH INIT,  ...

Hope this helps ...
0
 
PutochAuthor Commented:
Thanks for the advice
as i explained before i can't even use the shrink file option as this seems to hang?
Is 4GB to big for a .mdf file?
How can i shrink this if i can't use the shrink file option ?

Please help
Putoch.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<as i explained before i can't even use the shrink file option as this seems to hang? >>
dbcc shrinkfile may take some time to complete.  If it does not complete that either means that the db is corrupt or that you are having a too strong IO contention that prevents the shrink to execute or complete..

<<Is 4GB to big for a .mdf file? >>
No.  It is relatively small for a db.  I currently work on a 26 Tb db and I consider it *normal*
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<How can i shrink this if i can't use the shrink file option ?>>
It's simple, you can't.
0
 
PutochAuthor Commented:
God really, Ok if you think 4gb is not to big and that it could take some time to shrink i will try and do this again
And see if i get back an error how long would you estimate it to run before i would know that its just hanging and not actually shrinking?
or is that like asking how long is a piece of string?
0
 
PutochAuthor Commented:
Also when i am useing Management studio to Shrink,
do i choose Shrink database or Shrink Files and what is the difference

thank you
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<And see if i get back an error how long would you estimate it to run before i would know that its just hanging and not actually shrinking?>>
Really depends on hardware configuration but for a 4Go db, it should hardly be taking more than 10 minutes  to complete.  It really also depends on how much load is on the server at the time you launch the shrinkfile as the server may not be able to run it if too many concurrent connexions are working.  You may consider launching it at non busy times.  If it still does not run then you probably have a problem preventing it to run (more like a db corrupt)...

<<do i choose Shrink database or Shrink Files and what is the difference>>
Either are fine.
On production times, I suggest using *dbcc shrinkfile* it won't affect conurrency performance as much as dbcc shrinkdatabase.  To my knowledge shrinkfile attempts reducing the size of the physical files *individually* (log LDF  OR data MDF OR secondary NDF files), while shrinkdatabase attempts all of them at once (log LDF  AND data MDF AND secondary NDF files).

Hope this helps...
0
 
PutochAuthor Commented:
Thanks Racimo for this advice,
i went to shrink the file and now it is giving me an error;

Shrink failed for Datafile 'name'. (Microsoft.SQLServer.Smo)

Additional information:
An exception occured while executing a  transact-SQL statement or batch
(Microsoft.SQLServer.ConnectionInfo)

A severe error occured on the current command. The results, if any, should be discarded
(Microsoft SQL Server)

This error seems to be saying some of the fiiles are currupt is this correct? What should i do?
Sorry for all these questions but there is no one else here to help me with this.

Thank you
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:

<<This error seems to be saying some of the fiiles are currupt is this correct? >>
Yep.  It seems like it.

<<What should i do?>>
First confirm that the file is corrupt.  run a *dbcc checkdb* on the user db's.  If the corrupt is confirmed, you may attempt a repair or a restore of a previous uncorrupted version of the db (depending on when the corrupt took place)...

Hope this helps...
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
How old are the disks anyway.  The problem seems physical.(controller or disks)
0
 
PutochAuthor Commented:
Also just to clarify the space i have when i go to shrink the file it says
Currently allocated space: 4248.81 MB
Available free space: 1.81MB (0%)

Then the options on teh Shrink action should i use which radio button:
1.Release unused space
2.Reorganize pages beforew releasing unsed space Shrink file to: what size (4.248)MB
3.Empty file by migrating the data to other files in the same file group
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:


As I said, check your db before attempting new shrinks...You must confirm that your db is not corrupt before attempting a shrinkfile that may things worse...
0
 
PutochAuthor Commented:
ok going to check now thank you
0
 
PutochAuthor Commented:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'irishbroadbandDW'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The System admin guys are going to check if there are any errors monitored now also
is this not strange that no errors are occuring yet i can't shrink what else could possiabley interfer with this.The disk were got in 2006 only
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<The System admin guys are going to check if there are any errors monitored now also
is this not strange that no errors are occuring yet i can't shrink what else could possiabley interfer with this.The disk were got in 2006 only>>
Do a checkdb on the system db's as well (especially msdb and master)
Could you take a look at the controller?  Also in Performance Monitor, check Physical Disk --> Avg ms per write and Avg ms per read...If there is a strong contention on the disk, you will see it there if the values are above 100...

Hope this helps...
0
 
PutochAuthor Commented:
Ok the shrink files have run!!
Ihave ran one of my  package loads now and it has ran at the same speed as always and didn't hang this time.

Thank you for your advice on this, however does this mean that i will manually have to shrink the database files when this 'might' happen again or how can i avoid this?

thank you
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Ok the shrink files have run!!
Ihave ran one of my  package loads now and it has ran at the same speed as always and didn't hang this time.>>It seems the checkdb may have corrected some issues..I am glad it could help...

<<however does this mean that i will manually have to shrink the database files when this 'might' happen again or how can i avoid this?>>
You can program a job on the agent and run a routine checkdb on all db's once a month.  To automate you may use the following...

exec sp_msforeachdb 'dbcc checkdb(?)'
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Is your problem solved?
0
 
PutochAuthor Commented:
i just seen this message after i worte the one above.
'Do a checkdb on the system db's as well (especially msdb and master)'
I did a check on msdb is was ok and then on the master i get this error:
Msg 8921, Level 16, State 1, Line 2
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'mssqlsystemresource'.
Msg 701, Level 17, State 123, Line 2

doing a checkdb on tempdb i get no errors so is it a case i need to shrink the tempdb mdf files also?

There is insufficient system memory to run this query.
0
 
PutochAuthor Commented:


Sorry didn't put in the complete error message

 Msg 701, Level 17, State 123, Line 2
There is insufficient system memory to run this query.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Msg 8921, Level 16, State 1, Line 2
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.>>
I am afraid master is corrupt.  You may consider restoring it from a backup (if you have one available).  If you can't restore from a backup you will need to rebuild master.

<<doing a checkdb on tempdb i get no errors so is it a case i need to shrink the tempdb mdf files also?>>
There is no need to do that.  

Hope this helps...
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<< Msg 701, Level 17, State 123, Line 2
There is insufficient system memory to run this query.
back to top >>

How much RAM do you have?  Increasing the RAM should help...else you can run the shrinkfile at time of low resource consumption...
0
 
PutochAuthor Commented:
how do i rebuild the master is this from the system databse?
i am looking to see where the system database files are being backed up and i can't see them would they be automatically backed up to some drive when who ever initially set the Server up?
0
 
PutochAuthor Commented:
i've go 4GB of Ram?
0
 
PutochAuthor Commented:
Sorry found the tempdb mdf files on the program files
0
 
PutochAuthor Commented:
If the master is corrupt why would if give an out of sufficent memory error? and on the tempdb?
0
 
PutochAuthor Commented:
Sorry for my continuious questions
I see where to restore the database.
So i go into Management studio and choose Master (i think its this is it as this is where the error was occuring?)
Then i have to choose Task/restore
then in the restore panal i am asked to selece the database to restore
I am getting confused here as it doesn't allow you to resore the master dabase or the tempdb it offers me some of the other database.
Do i choose the database i run all my Loadsoff ?

Then it says to speciy the source and location of the backup sets to restore? again which database should i choose? and do i choose database or device?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<i am looking to see where the system database files are being backed up and i can't see them would they be automatically backed up to some drive when who ever initially set the Server up?>>
If it was not backed up explicitely you may consider it not to be backed up at all..

<<how do i rebuild the master is this from the system databse? >>
http://msdn2.microsoft.com/en-us/library/aa213831(SQL.80).aspx

<<If the master is corrupt why would if give an out of sufficent memory error?>>
Hard to say. Both maybe separate problems but if master is corrupt, the system could have some troubles allocating memory for handling concurrency.  Best thing to do is making sure your master is fit first.

<<and on the tempdb?>>
For the moment do not worry that much about tempdb...

<<Then it says to speciy the source and location of the backup sets to restore? again which database should i choose? and do i choose database or device? I am getting confused here as it doesn't allow you to resore the master dabase or the tempdb it offers me some of the other database.
>>
master is not restored the same way a user db is restored.  The following link may help...
http://www.dbarecovery.com/restoremasterdb.html
0
 
PutochAuthor Commented:
Ok i will have a look at all these thank you
i have just ran the
use [master]
dbcc checkdb
and this time i get no errors? This is so strange why would it change all of a sudden
i am now able to continue running the packages that previously returned the insusfficient  memory error? this is so strange i really don't understand what is going on and i don't think  ireally did any thing specifically to resolve the problem as in the end i didn't have to restore the database etc? i have been looking up stuff on the internet but not really getting the information i need.

Thank you for your help and patience all along.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
use [master]
dbcc checkdb
and this time i get no errors?
probably means the first run corrected the problem.  You are all set ..no need to rebuild...
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
you need to set up a maintenance plan to perdiodically check the integrity of your yuser and system db.  And most of all, you need to make sure you also backup your system db's..
0
 
PutochAuthor Commented:
Thank you so much for your help, i have no background on this and am just trying to make my way throgh this there is a lot of readin on my half to be done to catch up.
when you say ''probably means the first run corrected the problem.  You are all set ..no need to rebuild...'' Do you mean by running teh dbcc checkdb this can fix the errors , my understanding was that it jsut listed if there was any errors in it ?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Do you mean by running teh dbcc checkdb this can fix the errors , my understanding was that it jsut listed if there was any errors in it ?>>
For more info on checkdb...

http://msdn2.microsoft.com/en-us/library/ms176064.aspx
0
 
PutochAuthor Commented:
Hi Racimo i wonder could you help me here again
i want to move the master files. i was reading how to do this and had to stop the server
however i have to start the server with the command line only using the master parameter only
i have already entered in
NET START MSSQLSERVER/f/T360
and it told me the service was started however i need to
type in
NET START MSSQL$instancename/f /T3608
However im not sure what the instance name i  am ment to be tyoing in ? wouldyou be able to direct mein the right direction please/
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 19
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now