 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Other SQL Server Topics / August 2005SQL DB VERY BLOATED
About a month ago I setup a Server and named it SQL1 I setup a maintenance plan and all was well. I needed to rename server to SQL. I went onto the server today to take a look around and noticed that the DB is 133GB instead of 35GB. When I when to make some changed on the maintenance plan I got an error "MS-SQL Error 14274 cannot delete job that originated from an MSX server". An article I read mentioned that I should go to Query Analyzer and run select @@servername to verify the name; it comes back "NULL". I tried the same command on a second standalone server and it came back correctly. When I got to Enterprise Manger and do a return all rows on MSDB à sysjobs I see 10 jobs w/ originating_server pointing to SQL1 (the old server name) Please Help Thanks, How can I remove the jobs in the MSDB from the sysjobs and reclaim the space? Thanks, > About a month ago I setup a Server and named it SQL1 I setup a maintenance > plan and all was well. I needed to rename server to SQL. I went onto the [quoted text clipped - 11 lines] > > Thanks, Hi, Jobs will be stored in MSDB database and will not occupy any space. Looks like your database is FULL recovery model and you do not have TRansaction log backup. Due to this your transaction log backup has grown abnormally. See the log file size by executing DBCC SQLPERF(Logspace) If you see the Size of the file is huge then you have to :- 1. Backup the transaction log using the below command. backup log <dbname> to disk='c:\backup\dbname.trn' 3. Shrink the database log file dbcc shrinkfile('logical_ldf_name','truncateonly') Thanks Hari SQL Server MVP > How can I remove the jobs in the MSDB from the sysjobs and reclaim the > space? [quoted text clipped - 16 lines] >> >> Thanks, Do I need the equal amount of space to backup the TRN? the command read 79GB do I need that much available? Thanks, > Hi, > [quoted text clipped - 40 lines] >>> >>> Thanks, > Do I need the equal amount of space to backup the TRN? the command > read 79GB do I need that much available? If you are not backing up the transaction log (presumably because you don't need to) and are are only performing full database backups, then you do not need to run in full recovery mode. Backup the log with the truncate_only option (which truncates the log file only) and then perform a full database backup (better even to perform the full backup first and then truncate the log file). Then change the recovery mode to simple on the database. Then you have to shrink the log file down to a smaller size. Figure out what size you want the log file and then run DBCC SHRINKFILE to shrink the file (do this off-hours if possible). In simple recovery mode, you must perform database backups if you want some recoverability in case of drive failure or corruption. The transaction log will automatically truncate on its own.  SignatureDavid Gugick Quest Software www.imceda.com www.quest.com 1. Where do I change the DB to recovery mode? 2. Do I need to run a backup before the srink for the srink to work or as a prcaution? Thaks, >> Do I need the equal amount of space to backup the TRN? the command >> read 79GB do I need that much available? [quoted text clipped - 15 lines] > some recoverability in case of drive failure or corruption. The > transaction log will automatically truncate on its own. > 1. Where do I change the DB to recovery mode? > 2. Do I need to run a backup before the srink for the srink to work > or as a prcaution? Easiest to do this from SQL Enterprise Manager in database properties. Changing the recovery mode is not going to hurt the database, except that you'll lose the transaction log once you change it to simple. If you want to be as careful as possible: 1- Run a complete database backup 2- If you don't care about the log file information in the log file, then run a backup on the log file with the truncate only option (does not reduce the size of the log file itself) 3- Change the recover model to simple 4- Run DBCC SHRINKFILE to reduce the huge log file to a more appropriate size 5- Make sure there is sufficient space in the database and log files and they are both set to auto-grow as a precaution (from SQL EM database properties) 6- Schedule database backups at a frequency that is appropriate for your production database. Since you are not performing log file backups (can't in simple recovery), you will only be able to recover a database to a point as recent as the last database backup. For most production systems this is insufficient. If it is insufficient for you as well, then you really should be running in full recovery and will need to schedule transaction log backups as well at an interval consistent with your recovery requirements. If you change the recovery model from simple to full (or bulk logged) you must immediately perform a full database backup to prevent the log file from continuing to auto-truncate. SignatureDavid Gugick Quest Software www.imceda.com www.quest.com 1-Can I run it to a tape device? from within EM? 2- If I run a backup on the logfile (and is doesn't truncate) why am I doing this? 5- I ONLY have 1.5 GB left on the server. Is there a way of blowing the LDF file it's @ 80GB. >> 1. Where do I change the DB to recovery mode? >> 2. Do I need to run a backup before the srink for the srink to work [quoted text clipped - 27 lines] > must immediately perform a full database backup to prevent the log file > from continuing to auto-truncate. > 1-Can I run it to a tape device? from within EM? If you don't have room for the database backup somewhere on the server or another PC (not recommended for quick backups) then you have other problems > 2- If I run a backup on the logfile (and is doesn't truncate) why am I > doing this? The truncate only option truncates the data in teh log file > 5- I ONLY have 1.5 GB left on the server. If you run DBCC SHRINKFILE on the log file after truncating the log, you will recover some disk space. > Is there a way of blowing the LDF file it's @ 80GB. Yes. Using DBCC SHRINKFILE to get it to a size that works for your database. 1- What's your backup frequency? 2- Is this a production box? 3- How much data are you willing to lose in case of drive failure or database corruption? 1 hour? 1 day? 4- Where are you going to put the database backups? If possible, backup locally to another drive array on the server and then dump to tape or NAS. Keep a few days available on disk if possible in case of recovery. SignatureDavid Gugick Quest Software www.imceda.com www.quest.com Can I run DBCC SHRINKFILE on a DB that is "in use" I can't down the server for a long time? I'd rather it be a little sluggish until this is all straightened out. Thanks, Yes...it can be in use when running shrinkfile. Things will be slower but you can run it. -Sue >Can I run DBCC SHRINKFILE on a DB that is "in use" I can't down the server >for a long time? I'd rather it be a little sluggish until this is all >straightened out. > >Thanks, > Can I run DBCC SHRINKFILE on a DB that is "in use" I can't down the > server for a long time? I'd rather it be a little sluggish until this > is all straightened out. > > Thanks, Yes, but you have to truncate the log file first. SignatureDavid Gugick Quest Software www.imceda.com www.quest.com Hi, Execute the below query from Query Analyzer Select * froom Master..sysservers If you see the old server there then Execute sp_dropserver <Oldserver> and then Execute sp_dropserver 'SQL',Local After this stop and start the MSSQL Server Service --------------------------------------- Incase if you do not have any entry in sysservers table query then:- sp_dropserver 'SQL',Local After this stop and start the MSSQL Server Service Thanks Hari SQL Server MVP > About a month ago I setup a Server and named it SQL1 I setup a maintenance > plan and all was well. I needed to rename server to SQL. I went onto the [quoted text clipped - 11 lines] > > Thanks, After following you instructions... there is no longer a sysjobs in the MSDB. Is this normal? > Hi, > [quoted text clipped - 38 lines] >> >> Thanks, > After following you instructions... there is no longer a sysjobs in > the MSDB. Is this normal? No. The commands you ran would not drop the sysjobs table. How did you detect the sysjobs table is missing? SignatureDavid Gugick Quest Software www.imceda.com www.quest.com I went from Enterprise Manage DB --> MSDB --> TABLES --> there's no sysjob table? >> After following you instructions... there is no longer a sysjobs in >> the MSDB. Is this normal? > > No. The commands you ran would not drop the sysjobs table. How did you > detect the sysjobs table is missing? This should get you started... SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ > About a month ago I setup a Server and named it SQL1 I setup a maintenance plan and all was well. > I needed to rename server to SQL. I went onto the server today to take a look around and noticed [quoted text clipped - 8 lines] > > Thanks, |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.