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 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
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
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!!!
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
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
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?
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 witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error
Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
On the witness server the following sql
select * from sys.database_mirroring_witnesses
returns 1 row with the previous mirroring information.
How do I remove old information from witness server? Thanks.
I have a simle question for you:
I created a employee table:
create table employees (
employeeid int primary key,
lastname nvarchar(20) not null,
firstname nvarchar(20) not null,
birthdate datetime,
city nvarchar(15),
country nvarchar(15)
)
and then I created table orders and established a relationship between them:
create table orders (
orderid int primary key,
employeeid int references employees(employeeid),
orderdate datetime
)
The relationship was created successfully.
But I don't know how to establish a relationship between two tables if they were already exist.
Could you tell me how to do it?
many thanks :p
How can I establish a remote connection for using remote database in SQL Server 2K5?
Thanks.
Hello,
I have a problem connecting to my SQL SERVER 2005 express. I followed carefully the instructions to open a database connection but I still get the same error down below
Error message received:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I have changed the permission of the database by granting all access to myself but it didn't, I have also changed all the Pipes to enable but nothing. Here is the code
Software: Visual Basic.net Express 2008
Dim Strconnection As String
Dim con As SqlConnection
Dim ds As New DataSet
Strconnection = "Data Source=localhost;Initial Catalog=Regina;Integrated Security=True"
con = New SqlConnection(Strconnection)
con.Open() ==> Error happened here.
MsgBox("Database opened")
con.Close()
I DON'T KNOW WHERE TO LOOK AND FIND AN ANSWER TO CORRECT THAT MISTAKE.
Thank you in advance.
Ailiam A.
I recently migrated SQL Server 2005 which we use for our internet site and other internal applications, to it's own server. SQL used to be on the development PC. However now I am having security issues.
I keep getting an error message when I try to connect to the server from the development PC that the SQL server is not set up or does not allow remote connections. I have made sure that ASPNET is a user. I have also run aspnet_regsql to set up the internet users database.
What other security features do I need to check? I am more of a VB developer than a SQL admin so any help is appreciated.
FYI - This is a small network that does not run any form of Windows Server. I am using IIS 5.1. TCP/IP and Named Pipes is enabled.
Thanks
Hi,
I am trying to connect via the Management Studio Express. I share a LAN with the server. In Netowrk connections I can see the server and I can even access the "Shared" folders. However,when I try to connect I get an error that says the error may be caused by the server not allowing remote connections. I have already done the following:
With the Surface Area Configuration facility I have set the server to allow remote connections
I have selected TCP/IP as the preferred mode of connection.
I have selected Windows authentification as mode of authorisation
What else can I do??
Please help.
Derekprinsloo
I am training to be a DBA in a company running about 30 machines with MS SQL Server (2000 and 2005). Last week I went to a class where the instructor recommended establishing a performance baseline using windows performance monitor. He also advised to run perfmon remotely so as to not effect the performance.
What I am wondering is since I have so many different machines to baseline, can I run perfmon on one box, using a seperate counter log for each server? I would like to get a nice week-long baseline for each machine, but I also don't want to get bad data by running too many logs at the same time.
My plan is to do a small set of counters for processor, memory, disk, and the SQL server instance(about 10 counters total).
If anyone has experience in this area, I would appreciate any advice that you might have.
Thanks,
Matt
i cannot access my database as i am keep getting the following error:
"When connecting to SQL server 2005, this failure may be caused by the fact that under the default settings SQL server does not allow remote connections. (provider:shared memory provider, error: 40 - could not open a connection to SQL server)"
when i click on the properties window i cannot see my table nor can i make a new connection as the same error is shown, could you please advice me how i can fix this?