How do I shrink my transaction log file ? The physical file are 10Gb in size but only contains 100Mb data so I want to shrink it to save diskspace. I tried DBCC SHRINKFILE command but the file dont shrink.
Any other methods to shrink a transaction log file ? Like backup/restore ?
Help !
Thanks
In my database named aa ,there are always some words like this:
2004-05-12 09:44:48.96 spid73 Starting up database 'aa'
2004-05-12 09:44:49.17 spid73 Starting up database 'aa'
2004-05-12 09:44:49.36 spid73 Starting up database 'aa'
2004-05-12 09:44:49.56 spid73 Starting up database 'aa'
2004-05-12 09:44:53.37 spid79 Starting up database 'aa'
2004-05-12 10:02:41.96 spid73 Starting up database 'aa'.
2004-05-12 10:02:42.24 spid73 Starting up database 'aa'.
2004-05-12 10:02:43.61 spid73 Starting up database 'aa'.
2004-05-12 10:02:43.80 spid73 Starting up database 'aa'.
2004-05-12 10:02:44.02 spid73 Starting up database 'aa'.
2004-05-12 10:02:44.24 spid73 Starting up database 'aa'.
2004-05-12 10:36:42.84 spid82 Starting up database 'aa'.
who can tell me how to resolve this problem?
Hi, maybe someone has some clues to the following situation. Suppose from a SQL 7 database, only the log .ldf files are lost. All datafiles are present. Suppose there is no backup of this database. SQL Server will put the database in the SUSPECT status. The following question is very interesting to me: Can one recover from this situation??? I ask this, because when datafiles are lost, it's obvious that you have lost data, but since the transaction log files are a "bit special", I just wonder how one can survive such a situation.
I know that IF a database has only ONE log file associated with it, and this one log file gets lost, SQL Server will create on startup a new log file. But with multiple log files, it does not behave in this sympathic manner. Is there perhaps any way to recover (for example, an undocumented dbcc command or so) ?
I surely will appreciate any tip or suggestion !! Thanks!!!
Hello guys!
I would like to clear the logfile. Could anybody tell me how to do this?
What I would like most would be to run SQL-Server without any log-file (while development). But I suppose that this is not possible.
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
How to erase the content of logfile?
View 2 Replies View RelatedDear all,
I am using SAP with MSSQl 2005. My transaction logfile is full. I took backup of transaction logfile and it should be truncated and shrink after the backup, but it did nothing. There is no error but the file size remain same.
I tried from SQL server management studio selecting database--right click--shrink--file. Then i try to release unused space, shrink, but not worked.
I also checked these steps while the SAP system was stopped.
Can anyone guide me what to do now?
Thanks
Imran Hasware
If you have problems shrinking a transaction log no matter what commands you issue, here's a way to shrink the tranlog:
1. Right click on the properties of the database and go to the options tab.
2. Change the recovery mode to simple.
3. Right click again on the database - go to all tasks - shrink database.
4. Shrink the database.
5. Change the recovery mode back to what you had it as.
I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.
Hi there,
I want to write a sql query (for SQL7 & 2K) that gives the size and space used of the log file for a given database. I can do it for data files using sysfiles/sysindexes, but can see how I can do it for the logfile.
I know I can use DMO or dbcc sqlperf(logspace), but I want to understand how and where the information is kept in system tables.
Hi, i am not familiar with MSSQL so maybe it's not as difficult to you as it is to me - in brief:
SQL version: Microsoft SQL Server 7.00 - 7.00.699 (Intel X86)
Errorlog shows:
Starting up database 'CH2'.
Opening file E:CH2DATA1CH2DATA1.mdf.
Opening file E:CH2DATA2CH2DATA2.ndf.
Opening file E:CH2DATA3CH2DATA3.ndf.
File D:CH2LOG1CH2LOG1.ldf does not exist - unable to activate.
Enterprise Manager
Shows database CH2 (suspect)
Executing script:
sp_add_log_file_recover_suspect_db CH2, CH2LOG1, 'D:CH2LOG1CH2LOG1.ldf', 1000
Shows errormessage "The database 'CH2' does not exist."
How can i get a new logfile?
What if i created a new db 'CH2' and then replaced the data files?
What do i have to tell MSSQL to create a new logfile on startup?
All scenarios shown in Books On Line only seem to solve problems
if you do have a logfile and need more space.
Greetings from Switzerland /Lothar
I am trying to move a logfile from one disk to another. I've detached the database, moved the logfile to another disk and reattached the database. I used the sp_detach and sp_attach_db to specify the paths of the *.MDF and *.LDF. I've tried it through Enterprise Manager and with boths ways I get an error message saying: "Error 9003: The LSN (201:48:2) passed to log scan in database 'DatabaseName' is invalid". Does anyone know what this means and how I can get around this. I've even tried to attach the database with the logfile in the original path and I still get that error. Need Help Please!
Thanks
Hi
I have a business case where I have a database with a size of 2 GB. In my business case I want to delete 70% of the contents in the database. During the delete the size of the database(including the logfile) grows up to 10 GB. When I look on the database in the SQL Server Enterprice Manager there are only 700 MB used of these 10 GB.
The deletes are performed with ordinary SQL-statements "DELETE FROM X WHERE Y = 1 AND Z = 2..."
Thankfull for hints on how to solve this problem.
Regards,
Peter
Gurus,
Everyweek I am truncating the logfiles. I want to automate the truncate logfiles on every sunday. Anyone can help me to automate this process..
Thanks
-- Krishna
I have a database that is used to store a lot of data. We load the data on adaily basis, several thousand records per day. The Log file is not needed,so whats the best way to delete the records in it and reduce the sizeThanksDerrick
View 1 Replies View Related
Hi All
What is the Database ->All Tasks -->ShrikDatabase ->Files --> in ShrikAction Shrikfile to ...Here there is minium MB
what is the Minimum MB,If I shrik the Log file minimum MB is there any possiblility to loss data.
Has anyone had this problem or know how to prevent it? I received an error message yesterday from my SQL server (v7.0 with SP2) saying that the logfile for the tempdb was full. The logfile for tempdb is set by default to unlimited growth and the drive it is on has 24GB free. What causes this error to happen?
View 3 Replies View RelatedHello
Can anyone help, I have a master database on my server which has an extremely large log file size. I need to shrink this down and so I have tried using dbcc shrinkfile (master, 50, truncateonly) and dbcc shrinkdatabase although it does not seem to reduce in size
Please can anyone help
Is it possible to move (detach and attach) the physical data and log file (.mdf &.ldf) to another location?
View 1 Replies View RelatedHi,I want to take backup of database logfile periodically andautomatically. What should i do then.....ThanksHoque
View 1 Replies View RelatedIf I can get around taking a few databases offline, I'd like to. I need to get logfiles on a lower-capacity drive.
Ideally, I wouldn't like to have multiple logfiles swimming around. But, I was thinking that if I created a second logfile, and then backed up the transaction log, I might be able to remove the first logfile?
The other thought I had, was creating a second logfile, and then turning off autogrow on the first log.
Maybe there is a better solution? Maybe neither of these will work?
Thoughts?
Hi there,
I recently saw that the transaction log files of user dbs grow undefinitely in SQL Server 2000 - one of our customers had a 11 GB log file which totally slowed down the server.
Another customer of ours uses one of my applications logging all actions in a MSDE database file and I fear that the corresponding transaction log file will grow and block the system too - is there any way that I could shrink and set the max size of the transaction log file through SQL?
I already know the command "SHRINK FILE ('filename')" but I haven't found a SQL command to set the max size.
Thank you for any hints!
Sascha
As per the old times, do we still need to have the SQL server database's data file and logfiles seprately while we are even using Fusion IO?
In my case, all the drives G and H are from Fusion IO.
So, will it really matter if both my Data and log are on G drive or should I still have them separately on G and H Drives.
Is there a possibility to change the default instalation directory forthe logfiles for sql server 2000? I would like the default databasefiles to be placed on teh d: drive and the logfiles on the e: drive?Sjaak van Esdonk
View 2 Replies View RelatedCan anyone tell me why my SQL2000 database has grown aprox 15 % and my Log file 20,000 % when I attach it to SQL2005 .I've Thousands of Databases to Upgrade, but with the log file increasing to more than the size of the Database Its going to be a struggle !
It also takes a fair ammount of time to attach,
I suspect there is some reindexing going on , as when I try to reattach to SQL 2000 I get index errors ?
Is the re anything I can do in advance to reduce the database growth ?
I know I can truncate the log afterward but the peak diskspace consumed during my Migration may be an issue !
Thanks for any help
I have a problem that looks like it has not been discussed before inthese groups.I have a simple SQLAgent job that runs sp_who (could be anything, butlet's just say sp_who for this example). I have set the jobstep towrite to an output file "T:out.txt". If the job is owned by anadmin, it runs fine and writes the output file. If it is owned by anon-admin user, it gets the following error msg:Warning: cannot write logfile t:out.txt. Error 1059 : Circularservice dependency was specified. The step failed.I know about setting up the SQLAgent CMDExec proxy account, and havedone that. In fact, both SQLAgent and the SQLAgent cmdexec proxy usethe same domain account, which is in the administrator group of thelocal server. So, I know that security is not the issue.When a simple job runs and writes to an output file, what service orservice group could it be trying to start or modify? I looked throughthe list of Services, and could not find any circular dependencies.Is there a utility to detect this? Why would running under onecontext (as an admin) be ok while the other context (non-admin on SQL,but using the same admin domain service account) fails?Thanks in advance for any info you might have.
View 3 Replies View RelatedI am having difficulty restoring a database (DB_1) with 2 datafiles and one log file, DB Structure is as following:
Filegroup PRIMARY with file name 'fnm_data' with physical file name and location D:dbfile_1.mdf,
Filegroup 'FG1' with file name 'fgnm1_data1' with physical file name and location F:dbfile_FG1.mdf,
One log file with file name 'fnm_log' with physical file name and location (E:loglog_db.ldf)
{Note} Logfile resides on E drive whereas Datafiles reside on several other drives.
I need to restore the DB on another machine. And I need to move the log file to a drive other than E.In this case it is K drive. Hence I have used the following code,
restore log DB_1 from disk='M:kupccciclog.txt'
WITH RESTRICTED_USER , MOVE 'fnm_log'
TO 'K:DBlog_db.ldf', recovery, replace,RESTART
go
When I try to restore from backup files, I keep error message saying "Physical file name E:loglog_db.ldf may be incorrect." 'fnm_log' cannot be restored. Use Move command to identify a valid location for the file.
Even though I am using Move command to move the log file.
Then I found that if I create a drive with E: and a folder named 'log' then restore program runs alright. That is, when E:log exists then code does run smoothly. All the restore code is looking for the presence of path of the log file from where it is been backed up. In this case it is E:log folder. Once the database is restored I could kill that directory and nothing happens. Also log file has been restored on K:DB only.
Why the restore code is looking for the initial drive letter and path even though I have used Move command? Is there an issue in my code?
Any help will be highly appreciated.
When trying to install Business Contact Manager (BCM) for Outlook 2007, the setup failed and I was refered to a log file in my Local Settings/Temp folder. The log actually says that Business Contact Manager was installed sucessfully! BCM is supposed to install SQL Express 2005 as an instance or as instance if SQL Express is already installed. There is an MSSMLBIZ instance in Services..
Who can I send the Log File to for analysis and the fix feedback?
When I first went into Computer Management and clicked on Services and Applications in the left panel, the error message appeared "Snap-in failed to intialize. Name: SQL Server Configuration Manager CLSID:{CA9F8727-31DF-41D2-975C-887D84903967} This message diappeared when I clicked on Services and Applications again. Under Services, there are 3 SQL services - one is an application that was uninstalled 3-4 weeks ago and I disabled this service. The other 2 are: SQL Server (MSSMLBIZ) and the other one is SQL Server (SQLEXPRESS) When I tried to start either of the last 2, the message appeared: Services "Could not start the SQL Server (MSSMLBIZ) service on Local Computer. Error 3: The system cannot find the path specified. Under Program Files/Microsoft SQL Server/MSSGL.1 folder is mostly empty. So, it seems like the Path in the Registry is not valid and that nothing is being installed in the MSSQL.1 folder. If so, how do I fix this?
How do I get the BCM SQL instance to install and run properly? what do the messages in Services mean and how do I resolve these.
Thank you!
Gary
I have a SQL 7.0 sp3 server and I am trying to shrink a production database for use on other servers. The database/log sizes are 9601mb / 138mb with 772mb / 128mb free. I truncate a table that contains binary data. After this the database/log sizes are 9601mb / 138mb with 6634mb / 111mb free. The next step is to use EM to issue a shrink db. After this the database/log sizes are 6807mb / 378mb with 9.56mb / 365mb free.
After the use of truncate the the data in the datafile is about 2967mb.
After the shrink db the data in the datafile is about 6797mb.
The size of the data grew after the shrink db was issued. I looked at the tables and the space is largely associated with two tables that have a binary field, but the number or rows are still the same. I ran a checkdb just to make sure that there was not a problem and no problems were reported. I have used this process many times and this is the first time I have come up with these results. Any suggestions?
Thanks,
Glen
if I shrink the db will it enable me to create another db with that space released by the first db?
View 1 Replies View RelatedHow can i shrink my log file? It is like 900 megs and i
was it to be more like 400 megs.
Please Help
Melissa Sener
Project Manager
Square Yard
Hi, My name is Alexander and came´s to ask about Sql Server log.
Do you know why sometimes the log not truncate? All time
comes increasing!
I setup "Automatic Shrink" and it swims happens. If you can help me!
Thanks in advanced
Hi everybody:
Actually I have a 8.5 Gb database but the correct size that I need
to due disk space is 5 Gb. When I make a backup, the size of the dump is
4Gb, so I wonder if I create a 4- 4.5Gb my dump db will fit in it, but
not. Other way is create antoher db with 4 - 4.5 Gb and transfer via
sql-transfer or sql-bcp but I got a message that my device is too
short.
So I ask : What are the correct ways to reduce the size of a
database ?
Thanks in advance.
Hello,
We just migrated our DB from one SQL Server to Another. Both DB's used to be the same size at 11GB. The new SQL Server is a whopping 33GB, and I can't seem to find out why. The new DM has fewer tables (the data is identical).
Does anyone know how I can compact or shrink my DB, know why or how it grew so much and didn't go back down to normal size, and maybe where I can search properties or change settings to find out what is going on and prevent it from happening again.
I know MS Access has a shrink utility, but don't know where it is in SQL Server.
Thanks, in advance for your help.
Rodney
Hello,
I have a 24go sqlserver 2000 database, but only 5go used, when i use the shrink command in em there is no change.
can i modify the property of the db (auto-shrink => on ) actually it's on OFF.