I just peeked at my DNN setup and I found that I have a transaction log about 98 gigs large, compared to a DNN database that is only about 250 megs. Crazy, huh?
Do you happen to know what I need that transaction log for? Can I just delete it or will it break my SQL db? Is there a way that I can keep only maybe a week of transactions in it so it doesn't grow so dang large?
One of my production databases is currently 51 mb. The transaction log is well over 5 gig. I have tried truncating and then shrinking the log through the use of SQL utilities. This does not work! How can I quickly resolve this problem without tampering with the production environment?
I am not a DBA and I run a personal web site that has gotten pretty large. I have never done anything to maintain my sql server, and now my transaction log is 10 Gigs and my data is only like 300 Megs. I am starting to get a memory leak with the sql service. What should I do? Is it bad to have a huge transaction log. I am not familiar with any of this stuff, so someone please point me in the right direction.
Hi all our It Admin is having issues with the backups. He was doing full backups every 4 hours with backupExec, which means thats way too much. But now hes trying to do a simple recovery now, because obviously the transaction logs have not been truncated. Its a big mess, I'm not involved in this part, they handle the backing up and permissions. Transaction logs are huge??
Recently I did an upgrade form SQL 7 to SQL 2000. Everything went smoothly except there is a little problem with an Optimization job (reorganize data and index pages/remove unused space from database files). The job itself runs fine. But when I do a transaction log backup, it generates a huge log backup file. I don't remember that there was such a huge-sized transaction log backup file when I ran it on SQL 70. Is SQL 2000 different from SQL 7 on this aspect?
I have inherited a SQL 2000 server, and am therefore an absolute beginner of SQL2000.
I know this has been covered before, but I don't know how to use the KB as I don't know how to run the commands/script. http://support.microsoft.com/kb/272318/
I can no longer backup the SQL database because the 'transaction log backup' file is about 17GB. The SQL database is only about 2GB! The partition that it is backed up onto fills up every day.
I want to append the column to the transaction table(60 million records in it.) ..
Our transaction table is being used in production.. but i have very less amount of time ..
Instead of alter table.. (IF we use the alter to take backup of table and do the processing it will take more time). Is there any way to append the column to the transaction table ..
Hi there - can anyone advise on the following issue. We have recently performed some server side tracing on a particular SQL instance over 24hr period. We are now attempting to load these into a database for analysis. Here lies the problem.
When we are loading the profiler trace files (one at a time) into the database the transaction log is growing at an excessive rate. Even though the database is in SIMPLE mode.
We are loading the traces using the command:
INSERT INTO sqlTableToLoad SELECT * FROM ::fn_trace_gettable('MytraceFileName', DEFAULT)
Can anyone advise how we could possibly get round this issue as we're running out of space due to the transaction log.
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON Begin distributed Tran update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 and DONE = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
I am getting this error :Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
I have SQL Server 2005 Express Edition with Advanced Services running on a small web server. It all runs fine, but every now and then the log files grow and grow and eventually use up all the disk space of 30GB. As a quick fix, restarting SQL a couple of times clears out the logs and everything is up and running again. Any ideas on how to stop this happening?
Hi all, I found my database log file is 26GB and the database file is just about 280MB. We are doing full backup everyday. However, my sql server seems running very slow now and please advise:
1. How can I decrease/truncate my log file? 2. Would the huge size of the log file be the reasons slowing up my sql server? 3. Would anyone give me direction knowing more on the transaction log? Thank you and appreciated!
Hi guys, its my first post! Its also like my first time really diving into sql. We are using sharepoint on site here along with sql server 2005, one of our log files is 255 GBs and needs to be made smaller very fast!! We are almost out of disk space and the log is growing fast.
I am very new to sql and dont even know where to go to enter commands, so youll have to bear with me here. I've read about truncating and shrinking and some other things, I am just worried and dont want to mess anything up. I know this is probably a simple task, but like I said, with the truncate command I was reading about, I dont even know where to go to type it in!!! If someone could please help it would be much appreciated. Thanks so much.
I have a huge dataset in MS SQL Server, over 11 million records of machine data taken every four seconds. I really don't need samples of this data at this interval. I'd like to run a query to retrieve my data for every 30 minute interval. I know enough SQL and DTS to SELECT was fields I want, and how to direct it to a CSV file, but I'm not sure how to manipulate the TimeStamp field in the query to only pull data every 450 records.
I have a problem and I am sure someone here can help. Lat night my DB was working fine, as it has been . This morning I get to the office and now everything has gone to hell in a handbag .
I can no longer connect to my sql2005 DB I get this error when trying to place an order on our order page.
"There was a problem with the website: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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) The error has been logged."
I also notice the sql agent will not start. Any the former owner of the Co. had a eval version of sql management studio the must have just expired(could this be causing it?)
I have a .bak file of 72gb. But my database size is only 32gb, I got this value from sp_spaceused? Anyone know why the .bak file is so big?. Is it possible to reduce the size? How could i reduce it?
Hello, I have a very big T-SQL script (~24mb) and I need the SQL Server on my hosted site to execute it.
Right now, I have a web page that uses Sql.Connection.ExecuteNonQuery () to do it, but the .NET process runs out of memory when I load the file into a C# string.
How would I go about executing this T-SQL script on the server? Is there such a command: EXECUTE SCRIPT "myscript.sql" FROM DISC ?
Hi this is regarding SQL Sever 2000. ( it was upgraded form sql7). its log file is increasing in very high manner. say 40 gb, 50 gb and now 57 gb. Mdf file is around 15 mb. we created back up and tried to restore to another system. its asking 57 gb free space. how to proceeed with file recovery. we have backups but it askes more space for log file. how to retrieve the data. rgds Pramod
Good evening: We're porting an old app written in ASP.NET 1.1433 and SQL 2000 to ASP.NET 2.0 and SQL 2005. In the old app we have a few data grids that are populated from a dataset pulled from the database. We use a SQL query that we build based on more than 10 different user inputs, the result of which is an enormously complicated SQL string. We'd like to move this processing into a SPROC in the 2005 database. Rather than writing stored procedures to create the SQL SELECT statement, is it possible to pass an entire select state ment to a SPROC and have it executed within? We're trying to capitalize on paging in 2005 using ...ROW_NUMBER() OVER (ORDER BY PM ASC)... and building the string using IF ELSE statements is mind numbling complex and tedious. And suggestions woudl be great. Thanks, Brad
I need to alter a table (expand the column size for varchar(10) to varchar(255)) and the table has 200 million rows. Please suggest me the best and the fastest method to achieve it. The database is on SQL 7.0
I am using SQL Server 7 and have about 5 databases. One of them has a data file of about 10 Meg, and most of the others are larger. I do a nightly backup to both a local and mapped drive. On both, the size of the backup file for this database is more than 500 Meg, but the rest appear to be an appropriate size. Does anyone know why this would be happening? The database works fine, it does not get a lot of insert/delete activity and I run DBCC every weekend. If anyone has any ideas I would sure like to hear from them.
Does anybody know why BCP on v6.5 grabs so much memory for SQL Server? I have a few table imports where the BCP process will consume over 460MB of RAM during the imports.
The BCP cmd file is executed via an xp_cmdshell call. The server has 2+GB of RAM, but the BCP process effectively flushes large amounts of data from the buffer. It takes quite along time for the cache to recover from this, and after this, the rest of the nightly processes run much slower, as they end up having to hit the drives to retrieve information that should already be in cache.
If anyone can shed some light on this it would be much appreciated.
I have a huge log file (285M) on SQL Server 7. The database itself is about 10M. How can I reduce the log file ? Is it possible to build it again from scratch ?
I tried the Truncate Transaction Log but it didn't help.